Necessary privileges for users

Hi.


JCHEM_CORE_PKG.GETENVIRONMENT()


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


Oracle Database 10g Release 10.2.0.1.0 - 64bit Production


PL/SQL Release 10.2.0.1.0 - Production


CORE 10.2.0.1.0 Production


TNS for Linux: Version 10.2.0.1.0 - Production


NLSRTL Version 10.2.0.1.0 - Production


NLSRTL Version 10.2.0.1.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








Here is my scenario.


The cartridge owner is JCHEM (also configured for the odbc connection).


I have a schema user named CIMS which should have a structure table called COMPOUND.


I have a regular user named CHEMIST who needs select,insert,update,delete privileges on CIMS.COMPOUND.





Now: Make a tep by step explanation on how to create the COMPOUND table, create the index and the granting of necesssary privileges on the objects to make this work.


/Mikael

What operations will CIMS want to do on COMPOUND?





In which schema should the jc_idxtype indexes be created?





What is the schema-qualified name of the JChemProperties table to be used?

Quote:

The cartridge owner is JCHEM (also configured for the odbc connection).

I assume you mean the jdbc connection in Tomcat.

Hi.


CIMS also need to do select,insert,update and deletes.


SCHEMA. the index should be put in CIMS schema.


The JChemproperties table is in JCHEM schema (or should I put one in every schema that will have one or more strucutre tables ?).


/Mikael

Do you have any requirements as to which user should be able to create and drop jc_idxtype indexes?





Is it acceptable in your environment to call jchem_core_pkg.use_password('<password>') for each user (for both the CIMS and CHEMIST class of users with the respective password of any individual user) at least once during the uptime of Tomcat (e.g. at the beginning of each database session)? This way there is no need for the JDBC properties in Tomcat to contain a valid database user with a valid password. The alternative to calling the use_password stored procedure is to have a valid database user with a valid password configured for the JDBC connection in Tomcat with read and write privileges across all schemata involved. Which approach do you prefer?





Your structure tables are JChem tables, I assume (tables created with either the jcman command line tool or using the JChemManger class from the JChem Java API). Is this correct?

Hi.


No requirement about the jc_idxtype.


Yes. each user will call the jchem_core_pkg.use_password


Yes, the structure tables are created by jcman.


Regards,


/Mikael

I attached a few convenience stored procedures packaged in config_jcart_users to help you grant the necessary privileges.





Install the package in the SYS schema. Adjust the package variables, if necessary and execute the following procedure in config_jcart_users in the order they appear below.





Create the user CIMS, if neccessary and grant basic privileges to them:

Code:

call config_jcart_users.create_user('cims', 'tiger', 1);

The first parameter is the user name, the second the password and the third parameter indicates whether the user is allowed to create jc_idxtype indexes or not (>=1: is allowed, =0: not allowed). (If the user is already created, you do not need the password.) Since you have no particular requirement as to who should create the index, I suggest that CIMS creates the indexes in its own schema on JChem tables it owns. It is assumed throughout this process that CIMS creates the index on COMPOUND.





Grant privileges on the stored procedure in the JCHEM schema:

Code:

call config_jcart_users.grants_on_jcobjs('cims');

Create private synonyms for the necessary objects in the JCHEM schema:

Code:

call config_jcart_users.syns_for_jcobjs('cims');

Do the same steps for CHEMIST, with the exception that CHEMIST will not be able to create index:

Code:

call config_jcart_users.create_user('chemist', 'tiger', 0); call config_jcart_users.grants_on_jcobjs('chemist'); call config_jcart_users.syns_for_jcobjs('chemist');

Start JChemManager and connect to your database as CIMS. It is important to make sure that you select the jchemproperties table which has to be used: jchem.JChemProperties. (It is a good idea to use one single jchemproperties table for all users as you suggested.)





Create the table COMPOUND and populate it, if necessary. It is important that the table is managed through the apprioriate jchemproperties table (jchem.JChemProperties). If your existing COMPOUND table has not been created with this in mind, you might need to recreate it.





Login with sqlplus as CIMS and call jchem_core_pkg.use_password() with CIMS's password.





Create a jc_idxtype index on the table. Make sure the jchemproperties table is properly set:

Code:

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

Execute the following as SYS:

Code:

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

And basically that should be it.





Do not forget to always specify the appropriate jchemproperties table for DML operations:

Code:

jc_insert('c1ccccc1', 'cims.compound', 'jchem.jchemproperties'...); call jc_update('Brc1ccccc1', 'cims.compound', 1002, 'jchem.jchemproperties'); call jc_delete('cims.compound', 'where cd_id = 1002', 'jchem.jchemproperties');

As I was testing/verifying the steps, I discovered a relly nasty bug: 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

Hi.


This almost works.


Looking at the procedure grants_on_jctab


It references objects that I cannot find in a newly created schema.


the table_name _UL looks to be created after transactions have been made, and I find nowhere the objects _sq and _usq ? What are those ?


Do the users need to have access to the _UL table ?





/Mikael

Quote:

Looking at the procedure grants_on_jctab It references objects that I cannot find in a newly created schema.

You can use the grants_on_jctab procedure after the given JChem table has been created and jc_idxtype-indexed. You may want to create a convenience procedure for CIMS which wraps up the creation of the jc_idxtype index and the grants_on_jctab procedure.

Quote:

Do the users need to have access to the _UL table ?

Yes. (This table is used to track update operations on the JChem table. jc_insert, jc_delete, and jc_update inserts record into this table. These records are then used by the structure cache in Tomcat to update itself. The old update records are deleted from this table during search operations.)





P.

Yes, but the _UL table is not created default.


Is it created the first time transactions appear against the main table ?








Hopw about the _sq and _usq ?

Did not you forget to regenerate your table after JChem upgrade?





Please, could you try to freshly create a JChem table? (You are using JChem version 3.1.5, aren't you?) Are the equivalent objects missing for the freshly created table as well?

Quote:

Did not you forget to regenerate your table after JChem upgrade?

A minor correction:





When upgrading from pre-3.1.1 version regenerating by command-line ("jcman r <table>") is not sufficient.





One of the following should be done:


- Start up the jcman GUI. It will automatically offer to create the log tables (and also regeneration) if needed.


- Alternatively "jcman u" can be called from command-line for the same purpose.





This only applies to JChem-tables of course:


If you index a column in a non-JChem (arbitrary) table, dropping the index before upgrade and then recreating it does the job in itself.





Szilard

Hi.


I have verified today, and the _UL table gets created (somthing went wrong yesterday).


So, what are the tables _sq and _usq for ?


I dont see those.


/Mikael

Look under the Sequences heading of your database browser, or use either of the dba_objects or all_objects views. These are sequences.

Starting with version 3.2, JChem will include PL/SQL code which will allow a more general management of access rights for jc_idxtype indexes than it was possible with config_users.sql in my previous posts. Look in the sample static procedure in the attached sql file jcart_user_hlp.sql for a usage example. See the comments in the PL/SQL code for a description of the constructor and method parameters.