Permissions

I have installed JCHEM 3.1.6 on my Oracle 9.2.0 Production database. I can make the following query from the JCHEM and PTB schema but not from my own user even though I have made a JCHEM_USER role that has all privilegies that PTB has and granted the role to my user.





SELECT /*+ CHOOSE */ STR.COMPOUND.MOLFILE


FROM JCHEM.MOLTABLE, STR.COMPOUND


WHERE ((jc_containsb(JCHEM.MOLTABLE.CD_STRUCTURE, (select bmolfile from PTB.tempmolfile where pkey = 27062153)) = 1)) AND JCHEM.MOLTABLE.STR_ID=STR.COMPOUND.COMPOUNDID





ORA-29902: error in executing ODCIIndexStart() routine


ORA-29532: Java call terminated by uncaught Java exception: java.lang.RuntimeException: The following exception has been thrown by the servlet:


Exception: ORA-00942: table or view does not exist


ORA-06512: at "JCHEM.JCHEM_BLOB_PKG", line 0


ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 468





I can select from all tables involved in the query and I’ve made the following grants.





grant SELECT ANY TABLE to JCHEM_USER;


grant SELECT ANY SEQUENCE to JCHEM_USER;


grant DELETE ANY TABLE to JCHEM_USER;


grant CREATE ANY TABLE to JCHEM_USER;


grant CREATE ANY INDEX to JCHEM_USER;


grant CREATE ANY SEQUENCE to JCHEM_USER;


grant CREATE ANY TRIGGER to JCHEM_USER;


grant INSERT ANY TABLE to JCHEM_USER;


grant UPDATE ANY TABLE to JCHEM_USER;


grant DROP ANY TABLE to JCHEM_USER;


grant DROP ANY SEQUENCE to JCHEM_USER;


grant DROP ANY TRIGGER to JCHEM_USER;








I'm really out of ideas here so I would appreciate some input very much!

Have you restarted Tomcat after granting the JCHEM_USER role to your user (let's call it MY_USER)? If you tried to do substructure search as MY_USER before granting to it the JCHEM_USER role, a database session for MY_USER is being used in Tomcat that is not "aware" of the new role granted to MY_USER. JChem Cartridge currently provides no way of closing (and re-opening) database sessions in Tomcat for specific users other than restarting Tomcat itself.





Regards,


Peter

Yes I have restarted tomcat. The role works for other users but I gave


myself some privilegies before I made the role. Then I gave myself the role and took away the


privilegies given specifically to me when the role was working and now it's


not working for my user.

Quote:

The role works for other users

Do you mean: other users who have been granted this role are able to properly use JChem Cartridge?





Peter

Yes, the role works for other users.


/Johanna

Revoke the role from your user, grant the role again to your user and restart Tomcat.





Peter

Thanks Peter, now it's working!


/Johanna

Quote:

grant SELECT ANY TABLE to JCHEM_USER; grant SELECT ANY SEQUENCE to JCHEM_USER; grant DELETE ANY TABLE to JCHEM_USER; grant CREATE ANY TABLE to JCHEM_USER; grant CREATE ANY INDEX to JCHEM_USER; grant CREATE ANY SEQUENCE to JCHEM_USER; grant CREATE ANY TRIGGER to JCHEM_USER; grant INSERT ANY TABLE to JCHEM_USER; grant UPDATE ANY TABLE to JCHEM_USER; grant DROP ANY TABLE to JCHEM_USER; grant DROP ANY SEQUENCE to JCHEM_USER; grant DROP ANY TRIGGER to JCHEM_USER;

Note that this looks like giving unnecessarily much power to your users -- at least as far as JChem Cartridge is concerned. You can find code that helps you give finer-grained privileges here: http://www.chemaxon.com/forum/viewpost5875.html#5875





Peter