ORA-1031

Hi.


JCHEM_CORE_PKG.GETENVIRONMENT()


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


Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bi


PL/SQL Release 10.1.0.5.0 - Production


CORE 10.1.0.5.0 Production


TNS for Linux: Version 10.1.0.5.0 - Production


NLSRTL Version 10.1.0.5.0 - Production


NLSRTL Version 10.1.0.5.0 - Production


JChem version in the database: 3.1.5


JChem version in the Tomcat server: 3.1.5


java.vm.version: 1.5.0_06-b05


java.vm.vendor: Sun Microsystems Inc.


Apache Tomcat/5.5.15





JCHEM_CORE_PKG.GETENVIRONMENT()


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


Major JDBC version in Tomcat: 10


Minor JDBC version in Tomcat: 2








When I run a massive jc_delete against a table and at the same time


running a jc_contains against the same table I get the following error.


Exception: ORA-1031 insufficient privileges


ORA-06512 at JCHEM.JCHEM_BLOB_PKG line 76


ORA-06512 at JCHEM_JC_IDXTYPE_IM line 513





Catalina.out shows the following


java.sql.SQLException: ORA-01031: insufficient privileges





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


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


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


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


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


at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:945)


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


at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1686)


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


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:731)


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.similarity(JChemCached.java:772)


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


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:869)


at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:667)


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:595)








What have I missed to configure ?


/Mikael

Hi,





The config_jcart_users.grants_on_jctab procedure is supposed to do what is needed here (http://www.chemaxon.com/forum/ftopic1236.html&highlight=grantsonjctab).





Which user is configured for the JDBC connection in Tomcat?





Peter

Hi.


The user configured is JCHEM (the owner of the cartridge).


I used that procedure to grant select rights to an oracle role.


Then I granted that role to the user running queries.


If I don't do massive deletes it works perfectly, but as soon as I start


to do massive deletes the searches fails.


/Mikael

Hi,





I suggest to configure a non-existent user for the JDBC in Tomcat (do not forget to save the changes). If you use the jchem_core_pkg.use_password procedure in the database sessions, configuring a valid user for the JDBC in Tomcat adds an unnecessary source of possible confusion. (If your users call the jchem_core_pkg.use_password procedure at least once during Tomcat's uptime, the configured JDBC identity in Tomcat will be replaced with the identities of the actual callers. If jchem_core_pkg.use_password is not called, the configured identity will be used, which might not have the necessary privileges. If the configured identity is garbage, you will get an appropriate error message in case jchem_core_pkg.use_password has not been called.)





Let me know if this gets you any further.





Note that a bug related to jchem_core_pkg.use_password() has been fixed in JChem 3.1.6:

Quote:

If the user calls jchem_core_pkg.use_password() with the wrong password, using jchem_core_pkg.use_password() again with the right password will have no effect unless Tomcat is restarted. (Brrrr.) This problem will be fixed in the next JChem version. In the meantime, I suggest to architect your front-end application so that the password is checked in another way (e.g. executing something like: SELECT * FROM jchem.jchemproperties) before it is handed over to jchem_core_pkg.use_password().

Peter

We did use the jchem_core_pkg.use_password() and did still


get the errors, there must be something else.


/Mikael

Hi.


I have now verified with a non-user configured as the jdbc connector


and using a small database.


The error "insufficient privileges" occurs both when I am deleting and inserting


for every user EXCEPT the owner of the structure table.





/Mikael

Mikael,





Thank you very much for the additional information. I am going to try to reproduce the problem. Please, could you tell me about how many deletions/insertions it takes for the problem to be easily reproduced?





Thank you





Peter

Quote:

I used that procedure to grant select rights to an oracle role.

Sorry I have looked more closely at what you wrote. The procedure I was referring to looks like this:

Code:

procedure grants_on_jctab(user_name varchar2, table_owner varchar2, table_name varchar2) as   begin       execute immediate 'grant select on ' || table_owner || '.' || table_name || ' to ' || user_name;       execute immediate 'grant select on ' || table_owner || '.' || table_name || '_sq to ' || user_name;       execute immediate 'grant select on ' || table_owner || '.' || table_name || '_usq to ' || user_name;       execute immediate 'grant insert on ' || table_owner || '.' || table_name || ' to ' || user_name;       execute immediate 'grant update on ' || table_owner || '.' || table_name || ' to ' || user_name;       execute immediate 'grant delete on ' || table_owner || '.' || table_name || ' to ' || user_name;       -- We assume that the index has been created in the table owner's schema       execute immediate 'grant select on ' || table_owner || '.jc_idx_property to ' || user_name;       execute immediate 'grant select on ' || table_owner || '.' || table_name || '_UL to ' || user_name;       execute immediate 'grant insert on ' || table_owner || '.' || table_name || '_UL to ' || user_name;       execute immediate 'grant update on ' || table_owner || '.' || table_name || '_UL to ' || user_name;       execute immediate 'grant delete on ' || table_owner || '.' || table_name || '_UL to ' || user_name;   end;

Note the GRANT DELETEs at the end of the procedure. Granting SELECT is not enough. Is it not the problem?





Peter

Hi.


The procedure does have the delete statement.


However, I fail to see the need of having delete privileges


when I search.


/Mikael

Hi,





I am trying to reproduce the problem. Apparently, you've gotten farther in this jungle than my precious self. When using roles I get this:

Quote:

select count(*) from cims.compound where jc_contains(cd_smiles, 'c1ccccc1') = 1 * ERROR at line 1: ORA-20101: Please, create domain index on the column referenced in the operator jc_contains of the table with DATA_OBJECT_ID=89857. You can find out the name of the table by executing: SELECT owner, object_name INTO schema_name, table_name FROM sys.dba_objects WHERE DATA_OBJECT_ID = 89857 ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 36 ORA-06512: at "JCHEM.CONTAINS_FUNC", line 7

even though the index is there. So I do not even make it to Tomcat (where your problem occurs).





Still, I guess the problem might be a result of our not handling the case where a given role must be explicitely set for the user (with SET ROLE) in order to enable the privileges assigned to the role. (It is not yet clear to me, when SET ROLE is exactly needed. When the role is assigned to a user with GRANT <role> TO <user>, the privileges assigned to <role> seem to automaticatically take effect right at the beginning of the database session.) But before I can validate this assumption, I have to work my way through the error above.

Quote:

However, I fail to see the need of having delete privileges when I search.

JChem caches the fingerprints and structures stored in database tables in order to improve search performance. The cache must know that there is need for updating itself, so jc_insert inserts "update logs" in a dedicated table. The structure cache reads the update logs (if there are any) each time before a new search is started and updates itself if necessary. The update logs must be purged from time to time. This is currently done by the structure cache immediately before a search starts. Hence the need for delete privilege during search. We are considering ways to remove this need.





I continue working on this problem.





Peter

Mikael,





The problem in my previous post turned out to be the result of a mistake I made as I was setting up the test case. This problem was unrelated to the original problem you reported.





So far I could not reproduce the problem. Here are the steps I performed:





1.) Create and configure the user CIMS by executing as SYS:

Code:

declare begin config_jcart_users.create_user('cims', 'tiger', 1); config_jcart_users.grants_on_jcobjs('cims'); config_jcart_users.syns_for_jcobjs('cims'); end;

2.) Create a JChem table COMPOUND as CIMS using JCHEM.JCHEMPROPERTIES.





3.) Insert 10k SMILES into COMPOUND as CIMS.





4.) Create index on COMPOUND as CIMS using:

Code:

create index xcompound on compound(cd_smiles) indextype is jchem.jc_idxtype parameters('JChemPropertiesTable=jchem.jchemproperties');

5.) Create a role called CIMS_READER by executing as SYS:

Code:

create role cims_reader identified by cr_tiger;

6.) Configure CIMS_READER by executing as SYS:

Code:

declare begin config_jcart_users.create_user('cims_reader', 'tiger', 0); config_jcart_users.grants_on_jcobjs('cims_reader'); end;

7.) As SYS:

Code:

create user chemist identified by tiger;

8.) As SYS:

Code:

grant cims_reader to chemist;

9.) As SYS:

Code:

call config_jcart_users.syns_for_jcobjs('chemist');

10.) As SYS:

Code:

call config_jcart_users.grants_on_jctab('cims_reader', 'cims', 'compound');

11.) As CHEMIST:

Code:

select count(*) from cims.compound where jc_contains(cd_smiles, 'c1ccccc1') = 1;

(Successful)





12.) As CIMS, insert 10k SMILES into COMPOUND:

Code:

declare     type cur_typ is ref cursor;     c cur_typ;     query_str varchar2(200);     smiles varchar2(4000);     cdidarr cd_id_array; begin     query_str := 'select cd_smiles from jchemuser.jc_nci_10k';     open c for query_str;     loop         fetch c into smiles;         exit when c%notfound;         cdidarr := jc_insert(smiles, 'compound', 'jchem.jchemproperties', 'false', 'false', '');     end loop; end;

Commit after the insertion completes.





13.) As CHEMIST:

Code:

select count(*) from cims.compound where jc_contains(cd_smiles, 'c1ccccc1') = 1;

(Successful)





I checked the table cims.compound_ul (where the update logs are stored by jc_insert for the JChem table COMPOUND) and saw that there were only 1k update logs in it, which indicates that purging of the compound_ul update-log table (deleting ~9k update-log records) was successful in step 13.). Without privilege problems.





Please, let me know how I should change my test case in order to more closely reflect the context where the privilege problem occurs. Is there a step missing? Should I


- create and assign the role differently;


- insert more than 10k structures;


- perform any of the steps concurrently (in search of a potential concurrency problem);


- ...


?





Peter

Hi.


Yes, the problem occurs when I try to search at the


same time as I do insert or deletes, so you must


try to search the same time as you do the insert.


/Mikael

It may or may not be relevant: do you commit after each insert or just at the end of a large batch?





Thanks


Peter

Hi.


I commit after each insert or delete.


/Mikael

Hi,





How many concurrent database sessions are performing inserts and how many concurrent sessions are performing searches? Is the problem reproduceable with just two sessions: with one of them constantly searching and the other constantly inserting?





How many processors do you have in your system? Is Tomcat located on the same server as Oracle?





Thanks


Peter

Hi.


One session is making the inserts and one is searching.


We are running on a 2 CPU machine (hyperthreading).


Tomcat is located on the same server as Oracle.


/Mikael

Hi,





It will take a while to setup on our Dual Xeon test machine the environment you are using.





On a hyperthreaded P4, I could not reproduce the problem.





Peter

Hi.


I think I found the problem.


By trapping the 1031 to a trace file


I found out that it tried to delete rows from the _ul table.


By granting delete on that table to my role, the problem seems to have gone away.





PROCEDURE select_on_jctab(user_name VARCHAR2, table_owner VARCHAR2, table_name VARCHAR2) AS


BEGIN


EXECUTE IMMEDIATE 'grant select on ' || table_owner || '.' || table_name || ' to ' || user_name;


-- We assume that the index has been created in the table owner's schema


EXECUTE IMMEDIATE 'grant select on ' || table_owner || '.jc_idx_property to ' || user_name;


EXECUTE IMMEDIATE 'grant select on ' || table_owner || '.' || table_name || '_UL to ' || user_name;


EXECUTE IMMEDIATE 'grant delete on ' || table_owner || '.' || table_name || '_UL to ' || user_name;


END;





/Mikael

Hi,





Glad you found the problem.





The DELETE privilege on the _UL table was what I was trying to point out in my post of Wed Apr 12, 2006 1:34 pm.





Peter

I realized that the config_jcart_users package posted on http://www.chemaxon.com/forum/ftopic1236.html granted more privileges than actually needed. Starting with version 3.2, JChem will include PL/SQL code which will allow a finer grained and more general management of access rights than available with the config_jcart_users package. See http://www.chemaxon.com/forum/ftopic1236.html for a "preview" version of the code.