ora-01031 gets thrown

Hi.


JCHEM_CORE_PKG.GETENVIRONMENT()


--------------------------------------------------------------------------------


Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production


PL/SQL Release 9.2.0.6.0 - Production


CORE 9.2.0.6.0 Production


TNS for Solaris: Version 9.2.0.6.0 - Production


NLSRTL Version 9.2.0.6.0 - Production


NLSRTL Version 9.2.0.6.0 - Production


JChem version in the database: 3.1.4


JChem version in the Tomcat server: 3.1.4


java.vm.version: 1.4.2_10-b03


java.vm.vendor: Sun Microsystems Inc.


Apache Tomcat/5.5.12





JCHEM_CORE_PKG.GETENVIRONMENT()


--------------------------------------------------------------------------------


Major JDBC version in Tomcat: 1


Minor JDBC version in Tomcat: 0








Sometimes when I query a database I get ora-01031 thrown.


I find the following in catalina.out


java.sql.SQLException: ORA-01031: otillr�klig beh�ighet





at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)


at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)


at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)


at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:626)


at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:113)


at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:475)


at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1027)


at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1515)


at chemaxon.jchem.db.StructureCache.deleteOldUpdateLogs(StructureCache.java:291)


at chemaxon.jchem.db.StructureCache.load(StructureCache.java:253)


at chemaxon.jchem.db.StructureCache.loadIfNeeded(StructureCache.java:648)


at chemaxon.jchem.db.JChemSearch.loadCacheIfNeeded(JChemSearch.java:2304)


at chemaxon.jchem.db.JChemSearch.search1(JChemSearch.java:2062)


at chemaxon.jchem.db.JChemSearch.search(JChemSearch.java:1909)


at chemaxon.jchem.db.JChemSearch.setRunning(JChemSearch.java:1796)


at chemaxon.jchem.db.JChemSearch.run(JChemSearch.java:1816)


at chemaxon.jchem.cartridge.servlets.JChemCached.run(JChemCached.java:851)


at chemaxon.jchem.cartridge.servlets.JChemCached.doSearch(JChemCached.java:808)


at chemaxon.jchem.cartridge.servlets.JChemCached.doPerform(JChemCached.java:610)


at chemaxon.jchem.cartridge.servlets.JChemCached.process(JChemCached.java:137)


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(Thread.java:534)








It looks like its trying to delete old update logs, but does not have the rights to do that.


Could you please tell me what rights on what tables must I grant.


Regards,


Mikael

Mikael,





I reproduce below the complete description about granting access rights. Although probably only step 3.) and 4.) are relevant for the problem you have just reported, please, review all the steps to see if there are any more of them that may be relevant in your environment.





Please, note that you either have to configure a kind of super user with almost blank privileges for the JDBC connection in Tomcat, or you have to execute the jchem_core_pkg.use_password stored procedure with the current user's password at the beginning of each database session. In the latter case the use_password procedure makes it sure that JDBC connection in Tomcat which is acting on behalf of the current JChem Cartridge operation uses the same userid as the database session in which the JChem Cartridge operation has been originated.





1.)


Execute the following SQL statement as the user in whose schema JChem Cartridge has been installed (it is 'jchem' for these examples):

Code:

select 'grant execute on ' || a.OBJECT_NAME || ' to myjchemuser;' from all_objects a where lower(a.OWNER) = 'jchem' and a.OBJECT_TYPE in ('PACKAGE', 'FUNCTION', 'OPERATOR', 'TYPE', 'INDEXTYPE', 'PROCEDURE', 'PACKAGE BODY', 'TYPE BODY') union all select 'grant select on ' || a.OBJECT_NAME || ' to myjchemuser;' from all_objects a where lower(a.OWNER) = 'jchem' and a.OBJECT_TYPE in ('SEQUENCE', 'TABLE');

2.)


Execute the SQL statements as 'jchem' generated in step 1.).





3.)


Execute the following statement as the user in whose schema the index has been created (it is 'index_owner' for these exaples):

Code:

select 'grant select on ' || table_name || ' to myjchemuser' from user_tables where lower(table_name) like 'jchemindex%' union all select 'grant update on ' || table_name || ' to myjchemuser' from user_tables where lower(table_name) like 'jchemindex%' union all select 'grant insert on ' || table_name || ' to myjchemuser' from user_tables where lower(table_name) like 'jchemindex%'

Execute this statement for each jc_idxtype index in index_onwer's schema replacing 'jchemindex' with the appropriate index name.





4.)


Execute the SQL statements as 'index_owner' generated in step 3.).





5.)


Execute the following statement as 'index_owner'

Code:

select 'grant select on ' || object_name || ' to myjchemuser' from user_objects where lower(object_name) like 'jchemindex%' and object_type = 'SEQUENCE'

Execute this statement for each jc_idxtype index in index_onwer's schema replacing 'jchemindex' with the appropriate index name.





6.)


Execute the SQL statements as 'index_owner' generated in step 5.).





Of course, you have to grant the appropriate privileges to users on the indexed structure tables as well.





Peter