ORA-01031: insufficient privileges

We are using JChem 5.2.1 on Oracle 10.0.2.4 on linux.  The cartridge is installed into jchem schema and permissions are granted to chemdb user.  Index was successfully created by chemdb user and operators are working correctly.

When utilizing jchem operators in an anonymous PL/SQL block everything works correctly but when creating function, procedure or package we encounter ORA-01031: insufficient privileges error.  I believe this is related to Oracle's limitation on role based permission to objects don't extend into PL/SQL blocks except for anonymous.  Is there any work around without installing the cartridge into each schema that requires cartridge functionality?

The workaround I am aware of is to grant privileges directly to users on the JCHEM schema objects. Please, see also: http://www.chemaxon.com/forum/viewpost21904.html#21904

Thanks

Peter

We wrote a procedure to grant execute on all packages, functions and types to a user but we continue to see the ORA-010131 error.  I've attached the screen shot from SQL Developer for a more detail message.  Unless there is a work around for this, you may want to add a note to your install documentation under "Configuring JChem Cartridge users" that PL/SQL will not be able to make cartridge calls.

I am not quite sure if this is specific to JChem Cartridge. I seem to get the same error even when attempting to create a simple table in a PL/SQL stored procedure. (Whereas creating a similar table works in a top level SQL call.) See the attached shell script.

Thinking about it a bit: table creation is prohibited because the user has CREATE TABLE privileges through the resources role. (And privileges obtained through roles are suspended in PL/SQL programs.) You may have had the ORA-010131 error during index creation, because metadata tables are lazily created during the creation of the first index. If you grant CREATE TABLE and CREATE SEQUENCE privilege to the user directly, index creation will work from withen PL/SQL procedures as well. Please, see the update shell script attached. (Symetrically, the user will need DROP TABLE and DROP SEQUENCE privileges directly granted to them in order to successfully drop the last index in their schema.)

I have updated the documentation accordingly.

Thanks Peter.  I found what I was looking for in your shell script (privman_pkg.grants_on_jcobjs).  I'm able to make cartridge calls now in PL/SQL blocks.  I look forward to reviewing the updated documents on direct object grant.

I forgot to add the attachment to my previous post. It is now added there.

Also, the updated part of the doc is here: http://www.chemaxon.com/jchem/doc/admin/cartridge.html#users (point 8.). (I had to re-add it to the site, because my previous change was overwritten as the site was updated with the 5.2.1.1 version which had been built earlier. It will be included starting with 5.2.2.)

Hello, I have very similar problems.
You mention a point 8 in http://www.chemaxon.com/jchem/doc/admin/cartridge.html#users but there are only 7 points. Could you post point 8 since I think that is what I am missing.

Best regards

Mats

 

I am running:
SQL> select jchem_core_pkg.getenvironment() from dual;
JCHEM_CORE_PKG.GETENVIRONMENT()
--------------------------------------------------------------------------------
Oracle environment:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

JChem Server environment:
Java VM vendor: Sun Microsystems Inc.
Java version: 1.5.0_09
Java VM version: 1.5.0_09-b03

JCHEM_CORE_PKG.GETENVIRONMENT()
--------------------------------------------------------------------------------
JChem version: 5.3.1
JChem Index version: 5030100
JDBC driver version: 11.1.0.7.0-Production

Hi Mats,

Step 8. has been, in the meantime, moved up one outline level as (optional) step 2. in the documentation:

For users who need to access JChem Cartridge functionality from within PL/SQL programs, the same privileges that have been granted to the JCC_BASIC_ROLE (described in the previous sequence of steps) must be also directly granted to these users. The reason is that privileges obtained through a role are ignored by Oracle for access control excercised in a PL/SQL program. This involves calling
dbms_java.grant_permission
privman_pkg.grants_on_jcobjs
loadjava
with the appropriate parameters (as described above).


In addition, if users are also to create jc_idxtype indices from within PL/SQL programs, they will need to have CREATE TABLE and CREATE SEQUENCE privileges directly granted to them. (Privileges associated with RESOURCE role will be ignored as well.) Symetrically, users will need DROP TABLE and DROP SEQUENCE privileges directly granted to them in order to successfully drop indices in their schemata.

Best regrads,

Peter

Thanks Peter,

then I have a bit of a problem because I did those steps (I think).
I have a user called CPR_COMPOUND that owns a table BIOFOCUS. There is a domain index on the CD_STRUCTURE table named JC_IDX_BIOFOCUS.

 

The CPR_COMPOUND user has no problems searching this table:

SQL> SELECT  count(CPR_COMPOUND.BIOFOCUS.CD_SMILES)
  2  FROM CPR_COMPOUND.BIOFOCUS
  3  WHERE (jc_containsb(CPR_COMPOUND.BIOFOCUS.CD_STRUCTURE,
  4  (select bmolfile from HIVE.tempmolfile where pkey = 85092731)) = 1);

COUNT(CPR_COMPOUND.BIOFOCUS.CD_SMILES)
--------------------------------------
                                     6

 

I now try to give the user MATSDA permissions to search this table:

SQL> call dbms_java.grant_permission( 'MATSDA', 'SYS:java.net.SocketPermission', 'localhost', 'connect,resolve');
Call completed.
SQL> call privman_pkg.grants_on_jcobjs('JCHEM2', 'MATSDA');
Call completed.
SQL> call jchem2.privman_pkg.grants_on_jcidx('MATSDA', 'CPR_COMPOUND', 'JC_IDX_BIOFOCUS',1,0,0,0);
Call completed.

 

And then I run the loadjava:
[oracle@cproracle cartridge]$ loadjava -grant MATSDA -user jchem2/password@virtbh10 jcart.jar

 

 

I then try to search as MATSDA:

[oracle@cproracle ~]$  sqlplus MATSDA@virtbh10
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 25 09:24:27 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> call jchem_core_pkg.use_password('password');

Call completed.

SQL> SELECT  count(CPR_COMPOUND.BIOFOCUS.CD_SMILES)
  2  FROM CPR_COMPOUND.BIOFOCUS
  3  WHERE (jc_containsb(CPR_COMPOUND.BIOFOCUS.CD_STRUCTURE,
  4  (select bmolfile from HIVE.tempmolfile where pkey = 85092731)) = 1);
SELECT  count(CPR_COMPOUND.BIOFOCUS.CD_SMILES)
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-01031: insufficient privileges
ORA-06512: at "JCHEM2.JC_IDXTYPE_IM", line 403

 

 

So I think I am missing something.

 

Regards,
Mats

I guess the key is this step:

"3. If applicable, create
privileges for those users who need to use
jc_idxtype index(es) owned by another user:

Depending on how JChem Server is set up to connect back
to
Oracle, this step can be as simple as granting the
appropriate
privilege (the required combination of SELECT, INSERT,
UPDATE
and DELETE privileges on the structure table to the
non-owner
user."

 

If I add "oracle.server.login"
and "oracle.server.password" to cartridge/conf/jcart.properties

and set the oracle.server.login to the owner of the structure table (CPR_COMPOUND), will that be enough to be able to just grant SELECT, INSERT, UPDATE and DELETE
privileges to the users that need to access the data?

Or do I need to grant some more permissions to CPR_COMPOUND?

 

Best,
Mats

Hi Mats

ERROR at line 1:

ORA-29902: error in executing ODCIIndexStart() routine

ORA-01031: insufficient privileges
ORA-06512: at "JCHEM2.JC_IDXTYPE_IM", line 403

A similar configuration works on my end with JChem 5.3.1. The privileges you granted appear to be sufficient for this to work.

Would it be possible to post here the corresponding Java stack trace from the Oracle session file (in the udump directory)? And related error messages from the jcart*.log file in the jchem/cartridge/logs/ directory -- if there are any.

 

will that be enough to be able to just grant SELECT, INSERT, UPDATE and DELETE 
privileges to the users that need to access the data?

Yes. If you have multiple index-owners (not just CPR_COMPOUND), the user configured as oracle.server.login must have the combined privileges on all JChem index objects.

Best regards,

Peter

Thanks Peter, there are not much in the logs, I get the following in udump:

 

[root@cproracle VIRTBH10]# more  udump/virtbh10_ora_30749.trc
/u02/app/oracle/admin/VIRTBH10/udump/virtbh10_ora_30749.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u02/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      cproracle.sund.root.ku.dk
Release:        2.6.18-92.1.22.el5PAE
Version:        #1 SMP Tue Dec 16 12:36:25 EST 2008
Machine:        i686
Instance name: VIRTBH10
Redo thread mounted by this instance: 1
Oracle process number: 31
Unix process pid: 30749, image: oracle _at_ cproracle.sund.root.ku.dk

*** ACTION NAME:() 2010-03-26 17:26:44.198
*** MODULE NAME:(SQL*Plus) 2010-03-26 17:26:44.198
*** SERVICE NAME:(VIRTBH10) 2010-03-26 17:26:44.198
*** SESSION ID:(113.35013) 2010-03-26 17:26:44.198
2010-03-26 17:26:44.193 [527785, MATSDA, MATSDA]: WARNING - chemaxon.jchem.cartridge.JcMetaDataFunctions: Name of the connected Oracle instance could not be obtained.

 

 

And the Jcart log:

[oracle@cproracle logs]$ more jcart1.log
Mar 26, 2010 5:26:24 PM chemaxon.jchem.cluster.NodeManager run
INFO: Listening on network interface 0.0.0.0/0.0.0.0 at port 1099...
Mar 26, 2010 5:26:46 PM chemaxon.jchem.cartridge.servlets.JCartConnectionManager createJCartDataSource
INFO: Using chemaxon.jchem.cartridge.util.cpool.Oracle10gDataSource

 

Best regads,
Mats

 

 

 

 

Mats,

there are not much in the logs

On a user's request, I tried to make the error messages clearer for 5.3. I suspect that while they became clearer, they also become less helpful. I will revert this for the next release.

P.

Hi,

JChem 5.3.2 has been released with more informative error messages.

Peter