ORA-01031: insufficient privileges with JChem Cartridge

Quote:

Hi, We're currently trying to set up new users to use the JChem cartridge. All these users are granted with a certain role. And this role has been granted with execute privileges on all the functions, packages, operators, types, and indextypes as described in the 'Configuring JChem Cartridge users' section of the Installation Guide. Also, we did the other steps of the 'Configuring JChem Cartridge users' section, we executed the dbms_java.grant_permission procedure to each individual user, as well as GRANT SELECT ON JCHEM.JC_IDX_PROPERTY and GRANT SELECT ON JCHEM.JCHEM_IDXSCAN_NO_SQ TO the role. We also created the synonyms for the JChem objects to the individual users. We're using a regular Oracle structure table that uses a blob to store the structures. We created the indexes for the structure field. And this table is *Not* in the JChem cartridge owner's schema. This structure table is in another schema. After creating the indexes, we granted select on the IDX_..JCX and the IDX_...JCX_UL tables. Also we granted the role to have select privilege on the JCHEMPROPERTIES, JC_IDX_PROPERTY, JC_IDX_UDOP tables and the IDX_..._JCX_SQ, IDX_...JCX_USQ, and the JCHEMIDX_SEQ sequences. Whenever we open a connection, we always call the jchem_core_pkg.use_password() procedure with each individual user's password. But when we do a substructure search, we sometimes get this error: java.sql.SQLException: 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-01031: insufficient privileges ORA-06512: at "JCHEM.JCHEM_BLOB_PKG", line 64 ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 590 Could you tell me what privilege it's looking for in that code and what am I missing?

Please, could you post the corresponding stack trace from the Tomcat logs? (For Tomcat 4.x, it is <tomcat-home>/logs/catalina.out; for Tomcat 5.x it is <tomcat-home>/logs/stdout_<date>.log)





Please, note that in the upcoming JChem 3.2 release we provide PL/SQL code that automates to a great degree privileges management for JChem Cartridge





Thanks

Below is the stack trace regarding the insufficient privileges error mentioned in this forum topic:

Code:

2006-10-10 15:22:05.349 [null]: Creating FpMaker for chemaxon.jchem.cartridge.servlets.JChemMisc$MolPropQueryParams[stdrConfig=, stdrConfigParam=tru, fpProps=[512, 2, 6], smilesLength=1000]... java.sql.SQLException: ORA-01031: insufficient privileges    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1679)    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1645)    at chemaxon.jchem.db.StructureCache.deleteOldUpdateLogs(StructureCache.java:294)    at chemaxon.jchem.db.StructureCache.load(StructureCache.java:256)    at chemaxon.jchem.db.StructureCache.loadIfNeeded(StructureCache.java:744)    at chemaxon.jchem.db.JChemSearch.loadCacheIfNeeded(JChemSearch.java:2315)    at chemaxon.jchem.db.JChemSearch.search1(JChemSearch.java:2069)    at chemaxon.jchem.db.JChemSearch.search(JChemSearch.java:1916)    at chemaxon.jchem.db.JChemSearch.setRunning(JChemSearch.java:1802)    at chemaxon.jchem.db.JChemSearch.run(JChemSearch.java:1822)    at chemaxon.jchem.interop.core.InteropJChemSearch.run(InteropJChemSearch.java:414)    at chemaxon.jchem.interop.core.InteropJChemSearch.search0(InteropJChemSearch.java:364)    at chemaxon.jchem.interop.core.InteropJChemSearch.search(InteropJChemSearch.java:332)    at chemaxon.jchem.interop.core.InteropJChemSearch.search(InteropJChemSearch.java:301)    at chemaxon.jchem.cartridge.servlets.JChemCached.doSearch(JChemCached.java:529)    at chemaxon.jchem.cartridge.servlets.JChemCached.doPerform(JChemCached.java:325)    at chemaxon.jchem.cartridge.servlets.JChemCached.process(JChemCached.java:84)    at chemaxon.jchem.cartridge.servlets.JChemServletBase.doPost(JChemServletBase.java:232)    at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)    at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:868)    at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:663)    at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)    at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)    at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)    at java.lang.Thread.run(Unknown Source)

IDX_..JCX tables:


SELECT privilege is sufficient for searching only.


UPDATE and/or DELETE and/or INSERT may be also needed, if the user wants to UPDATE and/or DELETE and/or INSERT into the column, on which this indexed has been created.





IDX_...JCX_UL tables:


Both SELECT and DELETE privileges are mandatory even if the other user is to be allowed only to search.


INSERT privilege is needed, if the other user wants to UPDATE, DELETE or INSERT the indexed column.





JCHEMPROPERTIES and JC_IDX_PROPERTY:


In addition to the SELECT privilege, INSERT and/or UPDATE and/or DELETE privileges are required, if the other user wants CREATE and/or ALTER and/or DROP indexes in the first user's schema.





The particular error you reported is caused by the user not having DELETE privilege on the IDX_...JCX_UL table.





As I noted in my previous mail, JChem 3.2 (which has recently been released) includes PL/SQL code that automates to a great degree privilege management for JChem Cartridge. I suggest either to upgrade to JChem 3.2 or (if your current JChem version is recent) try jcart_user_hlp.sql script included in JChem 3.2.