Insertion with new granted user problem

Hello,

I'm trying to insert using other user then Jchem owner ( new user have all necessary grants) and the I'm getting an error because it adds automaticaly the name of the schema running the resquest and adds it to my table name (I've a synonym "sub_strucutre" to kiet.sub_structure on CNFUSION), this is my func call and error msg:

DECLARE

cd_id cd_id_array;

BEGIN

cd_id := kiet.jchem_table_pkg.jc_insert('Brc1ccccc1','sub_structure','kiet.JCHEMPROPERTIES','true','false');

END;

ORA-20105: Table 'CNFUSION.sub_structure' does not exists

ORA-06512: à "KIET.JCHEM_CORE_PKG", ligne 45

ORA-06512: à "KIET.JCHEM_TABLE_PKG", ligne 23

ORA-06512: à ligne 4

when trying using another call within my php script:

DECLARE

cd_id cd_id_array;

BEGIN

cd_id := kiet.jchem_table_pkg.jc_insert(:molfile1,'kiet.sub_structure','kiet.JCHEMPROPERTIES','true','false');

END;



Warning: ociexecute(): ORA-29532: call Java stopped by uncaught Java exception : java.sql.SQLException: ORA-01031: insuffisants privileges 

Hello,

While executing PL/SQL programs -- including anonymous blocks --, Oracle ignores privileges granted through roles. For JCC operators/functions to work in such case, the definer of the program has to have the privileges on JCC-objects directly granted to them (as opposed to being granted through a role).

Let me know if this doesn't help.

Peter

Didn't quite get it, can you give me an example to clarify it .... what is the usual "adding new oracle user" to cartridge senario, thanks.

Example:

When you execute 

config-util.bat list-sqls-for-jcc-user-privs

, apply it to a specific role instead of a role.

Usually, you grant the JCC_BASIC_ROLE role to the user. If you use stored procedures and the like, you grant the user privileges on JCC object using the above script.

config-util.bat list-sqls-for-jcc-user-privs

 config-util.bat list-sqls-for-jcc-user-privs  

The only options they offer are the ones that I have introduced when calling the script !

I hope the attached image helps.

Peter

Yep thanks, and that's why I did for my user "CNFUSION" too

the problem is that the error message shows CNFUSION.sub_structure table while it's supposed to go to KIET.sub_strucutre ... also I have a synonym in my CNFUSION schema that point sub_structure to kiet.substructure ..... for example when I run this query (1) on CNFUSION I get the output (2) :

(1) and (2) 
select cd_id from sub_structure where cd_id = 367;



CD_ID                  

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

367                    



1 rows selected

I see...so this is a synonyms issue rather than a privilege issue.

Please, look at the helper functions in the privman_pkg which are supposed to help manage your synonyms (pointing to JCC objects). Let me know you need help with them.

Peter

if I don't want to use synonims, can I just pass the table this way 'jchem_schema_name.table_name' ?

I have removed synonyms to make it more clear, this is the request and the error 

DECLARE

cd_id cd_id_array;

BEGIN

cd_id := kiet.jchem_table_pkg.jc_insert('Brc1ccccc1','kiet.sub_structure',null ,'true','false');

END;

 Erreur commençant à la ligne 1 de la commande :

DECLARE

cd_id cd_id_array;

BEGIN

cd_id := kiet.jchem_table_pkg.jc_insert('Brc1ccccc1','kiet.sub_structure',null ,'true','false');

END;

Error report :

ORA-29532: Java call stopped by uncaught Java exception : oracle.jdbc.driver.OracleSQLException: ORA-00942: Table ou vue inexistant

ORA-06512: à "KIET.JCHEM_TABLE_PKG", ligne 30

ORA-06512: à "KIET.JCHEM_TABLE_PKG", ligne 20

ORA-06512: à ligne 4

29532. 00000 -  "Java call terminated by uncaught Java exception: %s"

*Cause:    A Java exception or error was signaled and could not be

           resolved by the Java code.

*Action:   Modify Java code, if this behavior is not intended.

 

Which schema has JCC been installed in?

Kiet

What does the following command return:

select distinct * from dba_synonyms  where table_owner = 'KIET' and table_name = 'JCHEM_CORE_PKG';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK                                                                                                                          

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

PUBLIC                         JCHEM_CORE_PKG                 KIET                           JCHEM_CORE_PKG                                                                                                                                                  

1 rows selected

So you didn't drop the synonyms, after all?

I was talking about my personnal synonyms in the CNFUSION schema !

Why did you need "personal synonyms"? Did they point to a different JCC instance?

sorry peter, this is the right error cause I forgot on the last message to put kiet.jchem_prop_table_name in the second field of the jc_insert function.

DECLARE

cd_id cd_id_array;

BEGIN

cd_id := kiet.jchem_table_pkg.jc_insert('Brc1ccccc1','kiet.sub_structure','kiet.JCHEMPROPERTIES' ,'false','true');

END;

ORA-29532: appel Java arrêté par une exception Java non interceptée : oracle.jdbc.driver.OracleSQLException: ORA-01031: privilèges insuffisants

ORA-06512: à "KIET.JCHEM_TABLE_PKG", ligne 30

ORA-06512: à "KIET.JCHEM_TABLE_PKG", ligne 20

ORA-06512: à ligne 4

29532. 00000 -  "Java call terminated by uncaught Java exception: %s"

*Cause:    A Java exception or error was signaled and could not be

           resolved by the Java code.

*Action:   Modify Java code, if this behavior is not intended.

forget about the synonyms now cause I'm no longer using them.

Please, could you post the trace* files from the jchem/cartridge/logs directory?

Ah I see know. This is not only about setting up a JCC user, but also one that is using another user's index. The relevant helper command is this:

.\config-util.bat config-index-user

If I create a new schema and apply config-util.sh list-sqls-for-jcc-user-privs to it and give him all permissions on jchem tables, why can't I use jc_insert and why I get this error message about priv.. 

ORA-29532: appel Java arrêté par une exception Java non interceptée : oracle.jdbc.driver.OracleSQLException: ORA-01031: insuffisant privileges 

ORA-06512: à "KIET.JCHEM_TABLE_PKG", ligne 30

ORA-06512: à "KIET.JCHEM_TABLE_PKG", ligne 20

ORA-06512: à ligne 4

29532. 00000 -  "Java call terminated by uncaught Java exception: %s"

*Cause:    A Java exception or error was signaled and could not be

           resolved by the Java code.

*Action:   Modify Java code, if this behavior is not intended.

For users using other users' JChem indexes, you have to use

.\config-util.bat config-index-user

I got an error , the role jcc_basic_role doesn't exit like I've mentionned here https://www.chemaxon.com/forum/ftopic10686.html&start=0&postdays=0&postorder=asc&highlight=

The name of the index (JC_IDX) [JC_IDX]:
 
Give search permission to cnfusion2 on JC_IDX (y, n) [n]: y
 
Give insert permission to cnfusion2 on JC_IDX (y, n) [n]: y
 
Give update permission to cnfusion2 on JC_IDX (y, n) [n]: y
 
Give delete permission to cnfusion2 on JC_IDX (y, n) [n]: y
 
-- ---- SQLs to execute as kiet:
call privman_pkg.grants_on_jcobjs('kiet', 'kiet')
grant JCC_BASIC_ROLE to cnfusion2
call kiet.privman_pkg.grants_on_jcidx('cnfusion2', 'kiet', 'JC_IDX', 1, 1, 1, 1)
-- ---- SQLs to execute as kiet:
call privman_pkg.grants_on_jcobjs('kiet', 'kiet')
grant JCC_BASIC_ROLE to cnfusion2
call kiet.privman_pkg.grants_on_jcidx('cnfusion2', 'kiet', 'JC_IDX', 1, 1, 1, 1)
 
Do you want to execute the SQL statements (y, n) [y]: y
SEVERE: Error while executing `grant JCC_BASIC_ROLE to cnfusion2` as kiet
SEVERE: ORA-01919: le rôle 'JCC_BASIC_ROLE' n'existe pas

Now everything works well thank you Peter but do you think that jcc_basic_role error is not important?

It is not important in your case, I'd say, since you already granted the privileges to the user, which would otherwise have been granted through the role.

This function of the config-util program could/should certainly be improved to make the step of granting the role optional and to allow the user to specify the role name at their will. For completeness, the user also should be offered the option to grant the required privileges directly to the user.

On an unrelated note: I recommend not to store user data in the schema where JChem Cartridge has been installed. You might get into trouble during JCC upgrade. JCC doesn't handle the extra complexity of dealing with user data in the JCC installation schema in general, nor does the JCC upgrade program which may remove objects it finds in the installation schema not needed by the new version.

It is not important in your case, I'd say, since you already granted the privileges to the user, which would otherwise have been granted through the role.

This function of the config-util program could/should certainly be improved to make the step of granting the role optional and to allow the user to specify the role name at their will. For completeness, the user also should be offered the option to grant the required privileges directly to the user.

On an unrelated note: I recommend not to store user data in the schema where JChem Cartridge has been installed. You might get into trouble during JCC upgrade. JCC doesn't handle the extra complexity of dealing with user data in the JCC installation schema in general, nor does the JCC upgrade program which may remove objects it finds in the installation schema not needed by the new version.

Ok thanks Peter, but what do you mean by user data cause I've added cutomised fields to JC mol tables, also I have several other personnal tables in JCC schema.

I meant that the JCC-installation schema should be exclusively used for installation and upgrade.

By user data, I meant all objects which you created either "manuall" using SQLs or through the use of JCB API or JCB tools.This applies to the columns you added to the JCB tables as well, of course, but if you create the JCB-tables in a schema separate from the JCC-installation schema, these columns will be in that separate schema as well. :-)

In more specific terms, if you installed JCC in the KIET schema, I recommend to create a KIET_USER schema and create your JCB-tables, columns, indexes, sequences and who-knows-what in the KIET_USER schema.

But how can I create a JC molecule table out of the JC installation schema? when using Jchem manager?

Thanks.

Exactly the same way as in the JCC installation schema.

Ok thanks Peter, and congrats for the 6.0 version and happy 15th birthday ^_^

Cheers,

Idris.

Just connect to the "other" schema with JChemManager instead of the JCC installation schema.

Ok I just want to make sure that I don't have to do something else, ;-)

JCB table creation is exactly the same.

When JCC indexing and searching enters the scene, enters the need for granting privileges on the objects in the JCC-installation schema already discussed.

And thanks for the well-wishes! :-)

Peter