adding extra users

Hi,





I am following the latest docs on creating additional users.

Quote:

1. Create an object of type JCartUserHlp as SYS for each user/role to be configured.

I ran the script jcart_user_hlp.sql for this step

Quote:

2. Call the member procedure config_user as SYS to grant general (non-index-specific) privileges to the user/role. 3. Call the member procedure grants_on_jcidx as SYS to grant index-specific privileges to the user/role.

Not sure on the syntax for these calls. Could you give me an example?





I have tried

Code:

Call jcartuserhlp.config_user;

Cheers





Paul

Hi Paul,





The procedure called sample in the code contains an example. The code itself is also documented. It may be useful to review for examples and scenarios the following draft document: http://www.chemaxon.com/shared/pkovacs/ugm2006_workshop/privileges/readme.txt





Cheers,


Peter

I am trying to setup an extra JChem user. I have the cartridge installed into Schema A. User B has his own schema SCHEMA B and I am logged in as User B when I try and create the SYS.JCartUserHlp object. User B has execute privliges on the JCartUserHlp object and I have also created a public synonym called JCartUserHlp which points to JCartUserHlp.





When I try and run it under the context of User B, I get the error





Message "ORA-01403: no data found


ORA-06512: at "SYS.JCARTUSERHLP", line 92


ORA-06512: at line 1" String





Do you know what that means/how to remedy it? One solution might be to login as system/manager, but I cannot login to Oracle via VB.NET this way.

Quote:

One solution might be to login as system/manager, but I cannot login to Oracle via VB.NET this way.

Try to login as sys using sqlplus.





Peter

Hi Peter,





I have tried to run the sample PL/SQL when logged into SQLP*PLUS with SYSDBA privleges (as System/Manager; also tried as SYS/SYS). I have pasted the entry into SQL*PLUS along with the full error:-

Code:

SQL> DECLARE   2  helper SYS.JCartUserHlp;   3  BEGIN   4  helper := SYS.JCartUserHlp('SchA', 'SchA.JChemProperties', 'localhost', ' 8090', 'UserB', 1, 'logtbl', 0);   5  helper.config_user();   6  helper.grants_on_jcidx('UserB', 'SchA.JChem_Vitic_IDX1', 1, 1, 1, 1);   7  end;   8  / DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at "SYS.JCARTUSERHLP", line 92 ORA-06512: at line 6

Any ideas?





using:-


JChem TEST_2006_06_06


Oracle 9i





Paul

Hi Paul,





The jc_idxtype index must be created first before you can use the helper.grants_on_jcidx(...) procedure.





Also, I think that the index name you specified is invalid: 'SchA.JChem_Vitic_IDX1'. The second parameter to helper.grants_on_jcidx must be the name of the index. If you created it in the 'SchA' schema, the first parameter should read 'SchA', instead of 'UserB'.





Cheers,





Peter

Peter,





Thanks for your help. I have it working now with another user.





Is it possible to use the method you describe in http://www.chemaxon.com/shared/pkovacs/ugm2006_workshop/privileges/readme.txt to setup multiple users accessing tables in their own schema (which is different to the cartridge owners schema)?





Cheers





Paul

Paul,

Quote:

Is it possible to use the method you describe in http://www.chemaxon.com/shared/pkovacs/ugm2006_workshop/privileges/readme.txt to setup multiple users accessing different tables in the same table owners schema?

I believe so, yes, though I did not test specifically this kind of setup.





You need to execute grants_on_jcidx(..) repeatedly for each table (or more properly: for each jc_idxtype index).





Cheers,


Peter

Might seem like an obvious question, but does the script for setting up additional users only allow them to query against the JChem table and not edit the data?





Cheers





Paul

If the with_update parameter of the member procedure grants_on_jcidx is not 0 (and is not null), then the user will be able to update.





Cheers





Peter

Thanks Peter.





I cannot get the insert to work when logged in as an additional user (JChem). The JChem tables are in a different schema (SchA). I have run the scripts to setup an additional user to access a JChem table in another schema as described in the sample you provided earlier in this topic. Here is the PL/SQL which I am executing under the JChem user:-

Code:

Declare cdidarr SchA.CD_ID_ARRAY; begin cdidarr := SchA.jc_insert(<molfile>, 'JChem_Vitic', null, 'true', 'false', null); end;

I have replaced the mofile with <molfile> for readability.





I get the error:-

Code:

ORA-29532: Java call terminated by uncaught Java exception: java.lang.ExceptionInInitializerError ORA-06512: at "VITIC.JCHEM_TABLE_PKG", line 0 ORA-06512: at "VITIC.JC_INSERT", line 8 ORA-06512: at line 1

I had to grant execute privileges on CD_ID_ARRAY and JC_INSERT to the JChem user.





Any ideas?





Cheers





Paul

Please, could you post the Java stack trace from the corresponding Oracle session trace file. (The recently modified file from somewhere $ORACLE_BASE/admin/<your-db>/udump.)





Thanks





Peter

Oracle session stack trace attached.





N.B. renamed the extension to .txt as it would not let me post a .trc file

By all appearances, you did not execute the grants_on_jcobjs member procedure for this particular user. This is the procedure which grants the necessary privileges to a user on the objects in the JChem owner's schema. This procedure must be executed once for each user who will be using JChem Cartridge -- regardless of the jc_idxtype indexes they will use.





Cheers,





Peter





PS:





grants_on_jcidx grants the necessary privileges on index specific objects. It must executed for each user for each jc_idxtype index which they will be using: if user A is to use index X and Y, grants_on_jcidx must be executed two times for user A; if user B is to use index W, Y and Z, it must be executed three times for user B. Obviously, the index, on which grants_on_jcidx is being called, must already exist.

Hi Peter,





I have run the grants_on_jcobjs member function as SYS (and also the syns_for_jcobjs), and I get the same error.





Could you check the PL/SQL I executed (shown below for grants_on_jcobjs):-





SQL> declare


2 helper sys.jcartuserhlp;


3 begin


4 helper := sys.jcartuserhlp('Owner', 'Owner.JChemProperties', 'localhost', '


8090', 'JChem', 1, 'logtbl', 0);


5 helper.grants_on_jcobjs();


6 end;


7 /





There were no errors reported.





Cheers





Paul

Hi Paul,





Your use of grants_on_jcobjs appears correct on its own, but I am not sure why you qualify jc_insert with SchA in your other statement. I would not qualify it at all or would qualify it with Owner, like:

Code:

Owner.jc_insert(...)

Or is Owner just an alias for SchA?





Although, it is probably not the source of the present problem, there is something else in your jc_insert statement which will eventually cause problem: if you stated with grants_on_jcobjs that the jchemproperties table of the user JChem will be "Owner.JChemProperties" than you should also have specified this to jc_insert like:

Code:

...Owner.jc_insert(<molfile>, 'JChem_Vitic', 'Owner.JChemProperties',...)

Currently the default jchemproperties table is always CURRENT_USER.jchemproperties (JChem.jchemproperties in the case of the user JChem) and it cannot be changed/overridden -- even though it is actually a good idea as a future enhancement in JChem Cartridge to make the default jchemproperties table configurable. (With the benefit of hindsight, it would have been more for us to use <jchem-owner's-schema>.jchemproperties as the default, but...and we can not change it now "just like that" without breaking backward compatibility.) If the jchemproperties table the JChem user will use is different from JChem.jchemproperties, then you have to specify it wherever the JChem Cartridge API accepts a jchemproperties table parameter.





Cheers,





Peter

Hi,





Sorry, I decided Owner was a better descriptor than SchA.....





I have explicitly referenced all Db objects in the call to jc_insert now using the schema prefix as you recommended and it still doesn't work. I am going to re-run the scripts that setup users etc. just to make sure I didn't miss a step. Just to check (assuming that the owner has already been setup and configured) I should





1. Create new user


2. call config_user() as SYS to setup user created in 1.


3. create structure table (if not already present)


4. create index (if not already present)


5. run grants_on_jcidx for user created in 1. specifying 1 for all the options at the end (granting full permissions).


6. run grants_on_jcobjs for user created in 1.





After that calls to jc_insert should work OK?





Cheers





Paul

Hi,





Sorry, I guess I confused you a bit... You can skip step 6., as grants_on_jcobjs is included in config_user().





Apart from that, the procedure as you described it should be OK.





Of course, the schema "Owner" should have a JChem Cartridge installation present by the time you start setup JChem Cartridge users...





Cheers,





Peter

OK,





Thanks Peter - I'll try with a fresh user and let you know how I get on.





Cheers





Paul

Hi Peter,





I have tried this with a fresh user. I dropped all other JChem users I had created except for one I keep for testing.





I went through the following steps to setup an additional user to access the JChem table in the cartridge owners schema (please note the cartridge is installed into the owners schema; I didn't create a separate user as the owner as you did in your example).





1. run config_user against cartridge owner


2. created a new user


3. run config_user against the new user


4. created a new structure table in the cartridge owners schema


5. created an index on the cd_structure field of the JChem table.


6. run grant_on_jcidx against new user granting full permissions





At this point, when I tried to call jc_insert, I got an error saying the Cd_Id_Array type was not declared. So I granted execute permissions on this object in the cartridge owners schema to the new user and re-tried. I got the same error but for the jc_insert operator.





Next I ran grant_on_jcobjs() against the new user to setup permissions on the objects/operators etc. in the owners schema.





I still got the same error. I followed the PL/SQL from the example you provided for configuring the new user replacing the call to config_user with a call to grant_on_jcobjs.





It seems that config_user isn't calling grant_on_jcobjs and also that synonyms/perms on the operators etc. are not being setup?





FYI using Oracle 9.2 (on windows server 2003)


latest test Version TEST_2006_06_06





Cheers





Paul

Hi Paul,





I retested it on 9i. Works for me.





1.) I created a user called paulc


2.) Installed JChem Cartridge in the schema paulc


3.) Ran config_user against paulc.

Code:

declare     helper JCartUserHlp;   begin     helper := JCartUserHlp(       'paulc',       'paulc.jchemproperties',       'localhost',       '8090',       'paulc',       1,       'logtbl',       0       ); helper.config_user(); end; /

I get:

Quote:

ERROR at line 1: ORA-01471: cannot create a synonym with same name as object ORA-06512: at "SYS.JCARTUSERHLP", line 6 ORA-06512: at "SYS.JCARTUSERHLP", line 76 ORA-06512: at "SYS.JCARTUSERHLP", line 20 ORA-06512: at line 15

which I find more or less logical. Did you get the same error?


4.) I created the jchemproperties table for paulc:

Code:

../bin/jcman.bat t --driver oracle.jdbc.OracleDriver --dburl 'jdbc:oracle:thin:@localhost:1521:mydb' --login paulc --proptable jchemproperties --password tiger -s

5.) Created a user called pkovacs


6.) Ran config_user against pkovacs

Code:

declare     helper JCartUserHlp;   begin     helper := JCartUserHlp(       'paulc',       'paulc.jchemproperties',       'localhost',       '8090',       'pkovacs',       1,       'logtbl',       0       ); helper.config_user(); end; /

7.) Created a JChem-table called jctbl in the schema of paulc:

Code:

../bin/jcman.bat c jctbl --driver oracle.jdbc.OracleDriver --dburl 'jdbc:oracle:thin:@localhost:1521:mydb' --login paulc --proptable jchemproperties --password tiger -s

8.) Created and index as paulc:

Code:

create index jcxjctbl on jctbl(cd_smiles) indextype is paulc.jc_idxtype parameters('jchempropertiestable=paulc.jchemproperties');

9.) Ran grants_on_jcidx on the index for pkovacs:

Code:

declare     helper JCartUserHlp;   begin     helper := JCartUserHlp(       'paulc',       'paulc.jchemproperties',       'localhost',       '8090',       'pkovacs',       1,       'logtbl',       0       ); helper.grants_on_jcidx(       'paulc', --idx_schema       'jcxjctbl', --idx_name varchar2,       1, --with_search boolean,       1, --with_insert boolean,       1, --with_update boolean,       1  --with_delete boolean     ); end; /

10.) Ran a jc_insert as pkovacs on the JChem table:

Code:

declare   2  cdidarr paulc.cd_id_array;   3  begin   4  cdidarr := paulc.jc_insert('c1ccccc1', 'paulc.jctbl', 'paulc.jchemproperties', 'true', 'false', null);   5  end;   6  /

The insert completed successfully.





Did you do anything which was different from what I did?





Cheers





Peter

Hi Peter,





I didn't get an error when I ran config_user against the cartridge owner.





Also, I didn't specify the JChemProperties table when I created the index.





I have been away on holiday for a week and it is possible I have got confused as to which user the cartridge is installed into. I'll drop the cartridge and re-install and follow the steps you have outlined to see if that works. I'll let you know how I get on.





Cheers





Paul

Hi Paul,





If you're going to repeat this exercise anyway, I suggest to delete the old records from your logtable before you start over so we have the opportunity to inspect the grant statements et al. executed this time in case the the problem persists.





Thanks





Peter

Hi Peter,





I have uninstalled JChem and installed the latest test version.





I followed the steps you outlined except the step where you ran config_user against the cartridge owner (ITIC) as I assumed you were doing this to illustrate it is not necessary.





I get the following error when I run the jc_insert example you ran in your example as the additional user configured for access.

Code:

cdidarr ITIC.cd_id_array;         * ERROR at line 2: ORA-06550: line 2, column 9: PLS-00201: identifier 'ITIC.CD_ID_ARRAY' must be declared ORA-06550: line 2, column 9: PL/SQL: Item ignored ORA-06550: line 4, column 1: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 4, column 1: PL/SQL: Statement ignored

It seems as though the additional user doesn't have permissions to access the objects in the owners schema? It seems as though I am definately missing something.





Cheers





Paul

Hi Paul,





Could you post the list of grant/create synonyms/etc. statements from your logtable?





Cheers,





Peter

Hi Peter,





I cleared the log table before starting afresh with creating new users.


There were 81 entries in all:-

Code:

grant create synonym to Itic call dbms_java.grant_permission( 'ITIC', 'SYS:java.net.SocketPermission', 'local host', 'resolve' ) call dbms_java.grant_permission( 'ITIC', 'SYS:java.net.SocketPermission', 'local host:8090', 'connect,resolve' ) grant select on Itic.jc_idx_property to Itic grant select on Itic.JChemProperties to Itic grant connect to Itic1 grant resource to Itic1 grant create synonym to Itic1 call dbms_java.grant_permission( 'ITIC1', 'SYS:java.net.SocketPermission', 'loca lhost', 'resolve' ) call dbms_java.grant_permission( 'ITIC1', 'SYS:java.net.SocketPermission', 'loca lhost:8090', 'connect,resolve' ) grant select on Itic.jc_idx_property to Itic1 grant select on Itic.JChemProperties to Itic1 grant insert on Itic.JChemProperties to Itic1 grant update on Itic.JChemProperties to Itic1 grant delete on Itic.JChemProperties to Itic1 grant select on ITIC.jc_idx_property to Itic1 grant select on ITIC.JCHEMTEST to Itic1 grant select on ITIC.JCHEMTEST to Itic1 grant select on ITIC.JCHEMTEST_UL to Itic1 grant delete on ITIC.JCHEMTEST_UL to Itic1 grant select on ITIC.jc_idx_property to Itic1 grant insert on ITIC.JCHEMTEST to Itic1 grant select on ITIC.JCHEMTEST to Itic1 grant update on ITIC.JCHEMTEST to Itic1 grant insert on ITIC.JCHEMTEST to Itic1 grant select on ITIC.JCHEMTEST_sq to Itic1 grant select on ITIC.JCHEMTEST_usq to Itic1 grant select on ITIC.JCHEMTEST_UL to Itic1 grant insert on ITIC.JCHEMTEST_UL to Itic1 grant delete on ITIC.JCHEMTEST_UL to Itic1 grant select on ITIC.jc_idx_property to Itic1 grant select on ITIC.JCHEMTEST to Itic1 grant update on ITIC.JCHEMTEST to Itic1 grant update on ITIC.JCHEMTEST to Itic1 grant select on ITIC.JCHEMTEST_sq to Itic1 grant select on ITIC.JCHEMTEST_usq to Itic1 grant select on ITIC.JCHEMTEST_UL to Itic1 grant insert on ITIC.JCHEMTEST_UL to Itic1 grant delete on ITIC.JCHEMTEST_UL to Itic1 grant select on ITIC.jc_idx_property to Itic1 grant delete on ITIC.JCHEMTEST to Itic1 grant delete on ITIC.JCHEMTEST to Itic1 grant select on ITIC.JCHEMTEST_sq to Itic1 grant select on ITIC.JCHEMTEST_usq to Itic1 grant select on ITIC.JCHEMTEST_UL to Itic1 grant insert on ITIC.JCHEMTEST_UL to Itic1 grant delete on ITIC.JCHEMTEST_UL to Itic1 grant select on ITIC.jc_idx_property to Itic1 grant select on ITIC.JCHEMTEST to Itic1 grant select on ITIC.JCHEMTEST to Itic1 grant select on ITIC.JCHEMTEST_UL to Itic1 grant delete on ITIC.JCHEMTEST_UL to Itic1 grant select on ITIC.jc_idx_property to Itic1 grant insert on ITIC.JCHEMTEST to Itic1 grant select on ITIC.JCHEMTEST to Itic1 grant update on ITIC.JCHEMTEST to Itic1 grant insert on ITIC.JCHEMTEST to Itic1 grant select on ITIC.JCHEMTEST_sq to Itic1 grant select on ITIC.JCHEMTEST_usq to Itic1 grant select on ITIC.JCHEMTEST_UL to Itic1 grant insert on ITIC.JCHEMTEST_UL to Itic1 grant delete on ITIC.JCHEMTEST_UL to Itic1 grant select on ITIC.jc_idx_property to Itic1 grant select on ITIC.JCHEMTEST to Itic1 grant update on ITIC.JCHEMTEST to Itic1 grant connect to Itic grant resource to Itic grant update on ITIC.JCHEMTEST to Itic1 grant select on ITIC.JCHEMTEST_sq to Itic1 grant select on ITIC.JCHEMTEST_usq to Itic1 grant select on ITIC.JCHEMTEST_UL to Itic1 grant insert on ITIC.JCHEMTEST_UL to Itic1 grant delete on ITIC.JCHEMTEST_UL to Itic1 grant select on ITIC.jc_idx_property to Itic1 grant delete on ITIC.JCHEMTEST to Itic1 grant delete on ITIC.JCHEMTEST to Itic1 grant select on ITIC.JCHEMTEST_sq to Itic1 grant select on ITIC.JCHEMTEST_usq to Itic1 grant select on ITIC.JCHEMTEST_UL to Itic1 grant insert on ITIC.JCHEMTEST_UL to Itic1 grant delete on ITIC.JCHEMTEST_UL to Itic1

None of the statments appear to be granting perms on operators or functions which is what I would have expected given that you mentioned grant_on_jcobjs should be called as part of config_user.





Cheers





Paul

Hi Paul,





I am completely at my wits' end.





I attach the jcart_user_hlp.sql which I have (and you should have). Please, compare it with yours and let me know if you find any difference.





If look into the code, you can see that config_user calls grants_on_jcobjs as well as syns_for_jcobjs. The only reason for your problem I can think of is that the complex SELECTs which create SQL statements on the owner's objects mysteriously return nothing.





In order to check this, you could try, for example, to execute the following query in sqplus as sys to see what it gives:

Code:

select 'create or replace synonym ' || user_name || '.' || a.OBJECT_NAME             || ' for ' || a.OWNER || '.' || a.OBJECT_NAME as syn_text         from all_objects a         where lower(a.OWNER) = 'itic1'             and a.OBJECT_TYPE in ('PACKAGE', 'FUNCTION', 'OPERATOR', 'TYPE', 'INDEXTYPE', 'PROCEDURE', 'PACKAGE BODY', 'TYPE BODY');

Let me know what the statement returns.





Cheers,





Peter

Hi Peter,





I am confused too. Could you please re-send the jcart_user_hlp.sql as I didn't get it last time, and when I ran the SQL statement, I got ORA-00904: "USER_NAME": invalid identifier.





Am I doing something wrong? I entered it into sqlplus as sys/sys as sysdba.





Paul

Oops sorry, I forgot to attach. This time you can find it attached.

Quote:

when I ran the SQL statement, I got ORA-00904: "USER_NAME": invalid identifier.

One more oops... user_name is a variable in the script (itic1). Also, the owner is itic and not itic1 -- contrary to what I wrote in my previous post. Here you have a better one:

Code:

select 'create or replace synonym itic1.'  || a.OBJECT_NAME ||' for ' || a.OWNER || '.' || a.OBJECT_NAME as syn_text from all_objects a where lower(a.OWNER) = 'itic' and a.OBJECT_TYPE in ('PACKAGE', 'FUNCTION', 'OPERATOR', 'TYPE', 'INDEXTYPE', 'PROCEDURE', 'PACKAGE BODY', 'TYPE BODY');

Cheers,





Peter

Hi Peter,





The script you sent me is identical to the one I have.





I have hit on it - the username in the select statement is always converted to lower case which means if the username contains any upper case characters it will fail. I happened to be using a username with all upper case characters (i.e. ITIC). I also have the habit of capitalizing the first character of usernames which is probably why it didn't work before either.





I'll try with a new user which has all lower case characters and see if that works (which I expect it will).





I'll let you know what happens.





Cheers





Paul

I guess you just need to change the way you specify them while creating JCartUserHlp. Just specify them in lower case.





Anyway, I am going to convert user names in the script on the other side of the equations as well.





Cheers,





P.

It is working OK now.





can insert a structure via pl/sql





Thanks for all your help.





Cheers





Paul