1. The JCC_BASIC_ROLE index administration role

Please see the JChem cartridge administrator's guide for details. For the purposes of this example we assume you should need to have these roles defined and available. In the latest 5.7 cartridge installation process the JCC_BASIC_ROLE is created for you. However it is not 'given out' until it is explicitly granted to your 'IJC_IDXOWNER' user. Subsequently, any user (e.g. 'IJC_IDXOWNER') granted this role will be able to create and own JChem cartridge tables/indexes. The older approach to setting up a cartridge index user employs the use of JCC_IDX_ROLE and this is mentioned below where required for backwards compatibility. The new approach negates the need to set up this role.

2. Create the user and set up privileges, 5.7 and pre- 5.7 cartridge.

There is now two routes which can be employed to get the job done. The older pre 5.7 route is described first and the new 5.7, easier route is described after it. You are encouraged to upgrade to version 5.7 of JChem and adopt the easier route!

For both old and new approach, first connect to your Oracle instance as the system user and create the user who will own the index, 'IJC_IDXOWNER' and grant the connect, resource and JCC_BASIC_ROLE roles.

        SQL> create user IJC_IDXOWNER identified by password;

        User created.

        SQL> grant connect, resource, JCC_BASIC_ROLE to IJC_IDXOWNER;

        Grant succeeded.

3. Verify the roles.

As the IJC_IDXOWNER, verify that you have the expected roles which will allow connection and administration of local cartridge type tables:

        sqlplus IJC_IDXOWNER/IJC_IDXOWNER

        SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 15 14:34:03 2010

        <torol>   (c) 1982, 2007, Oracle.  All Rights Reserved.


        Connected to:
        Oracle Database 10g Release 10.2.0.4.0 - Production

        SQL> select * from user_role_privs;

        USERNAME		       GRANTED_ROLE		      ADM DEF OS_
        ------------------------------ ------------------------------ --- --- ---
        IJC_IDXOWNER			       CONNECT                  NO  YES NO
        IJC_IDXOWNER			       JCC_BASIC_ROLE		NO  YES NO
        IJC_IDXOWNER			       RESOURCE 		NO  YES NO

Next, start a new session as the 'IJC_IDXOWNER' and initiate it using either method below. Note you should use the 'IJC_IDXOWNER' password here, in this example 'password' is used:

        call JCHEM.jchem_core_pkg.init();
        
        call JCHEM.jchem_core_pkg.use_password('IJC_IDXOWNER's password');

From cartridge version 5.7, onwards , you can now run the config-util.sh (or .bat) script with the list-sqls-for-jcc-user-privs argument in order to configure the 'IJC_IDXOWNER'. The information is repeated here.

$ ./config-util.sh list-sqls-for-jcc-user-privs

Name of the cartridge owner [jchem]:

The host name of the JChem Server [localhost]:

The port at which the JChem Server accepts connections [1099]:

Target Oracle version [11.2]: 10.2

The target Oracle user [zazie]: ijc_idxowner

-------------------------- CUT HERE ----------------------------
-- To be executed as DBA:
call dbms_java.grant_permission('IJC_IDXOWNER', 'SYS:java.net.SocketPermission', 'localhost:1099', 'resolve,connect');

-- To be executed as jchem:
call jchem.privman_pkg.grants_on_jcobjs('jchem', 'ijc_idxowner');
-------------------------- CUT HERE ----------------------------

+ set +x

4. Create a standard table and add to it a cartridge index.

With this configuration correctly in place, it should now be possible to create chemical structure based objects that are accessible when connected as the 'IJC_IDXOWNER' user. Further to this, it should be possible to create either a JChemBase or Standard (non-JChemBase) table with a cartridge index directly from the IJC user interface, connected to this user. For this example, we choose to create the latter table type manually, as below since this simulates the common scenario of integrating legacy data sources, that are not in any standard JChem format.

        SQL> create table structures (
        2  id number(10,0) primary key,
        3  structure clob
        4  );

        Table created.

        SQL> create sequence seq_structures;

        Sequence created.

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

        Call completed.

        SQL> create index idx_structures on structures(structure) indextype is jchem.jc_idxtype;

        Index created.

Verify that you can see at least these tables in your 'IJC_IDXOWNER' schema:

        SQL>  select table_name from user_tables;

        TABLE_NAME
        ------------------------------
        JCHEMPROPERTIES
        STRUCTURES
        JC_IDX_PROPERTY
        JC_IDX_UDOP
        IDX_STRUCTURES_JCX
        IDX_STRUCTURES_JCX_UL
        
        6 rows selected.

Next insert some dummy data:

        SQL> insert into structures (id, structure) values (seq_structures.nextVal, 'C');

        1 row created.

        SQL> insert into structures (id, structure) values (seq_structures.nextVal, 'CC');

        1 row created.

        SQL> insert into structures (id, structure) values (seq_structures.nextVal, 'CCC');

        1 row created.

        SQL> commit;

        Commit complete.

Verify the data is in the table:

        SQL> select * from structures;

	ID      STRUCTURE
        --------------- ---------
	 1      C
	 2      CC
	 3      CCC

Verify the cartridge operators work on the indexed column of the table:

        SQL> select * from structures where jc_compare(structure, 'CC', 't:s') = 1;

	ID      STRUCTURE
        --------------- ---------
	 2      CC
	 3      CCC

Everything should now be working directly in the 'IJC_IDXOWNER' schema. Next, we can give controlled access to this IJC_IDXOWNER's tables and indexes to a read only user 'IJC_IDXUSER'.

5. Giving the 'IJC_IDXUSER' user access to the 'IJC_IDXOWNER' table and index.

If you want this user to be able to own cartridge index then please see above regarding the JCC_BASIC_ROLE and prior to version 5.7 of the cartridge this role was also required for the the index user.

Prior to cartridge version 5.7 an additional role is required, namely JCC_IDX_ROLE. Any user granted this role (e.g. 'IJC_IDXUSER') will be able to access JChem cartridge indexes owned by someone else (e.g. 'IJC_IDXOWNER'), providing those indexes are configured correctly for the role, as explained below. The JCC_IDX_ROLE is somewhat more bespoke to your specific set up (set of index) and thus needs to be created and configured by the administrator.

Prior to cartridge version 5.7, you will need to complete the full configuration of this role.

In addition, a bespoke role JCC_IDX_ROLE is required to be created and configured. For information on setting up a bespoke role like JCC_IDX_ROLE, please see the relevant section [ |JC:jchem_doc_admin_cartridge#users] of the JChem cartridge administrator's guide for more details. In particular note the .init() call and general table grants.

        SQL> CREATE ROLE JCC_IDX_ROLE;

        Role created.

Next, connected as the cartridge owner we must grant permissions on any index that is to be made accessible by the granting of this role. This role can be updated with additional indexes as the need arises. Notice the JCHEM qualifier:

        SQL> call jchem.privman_pkg.grants_on_jcidx('JCC_IDX_ROLE', 'IJC_IDXOWNER', 'IDX_STRUCTURES', 1, 0, 0, 0);

        Call completed.

Note that the four 1 and 0 arguments in this example only grant access for searching the index. If you insert, update or delete access as well change the 2nd, 3rd or 4th numbers to ones accordingly. It is now possible to grant the role to the IJC_IDXUSER user and access is given. Please see the cartridge administrators guide for fine details on setting up roles like JCC_IDX_ROLE.

        SQL> grant JCC_IDX_ROLE to IJC_IDXUSER;

        Grant succeeded.

In order for the cartridge table to be visible from the IJC user the following grants must be applied for each cartridge table/index. In this example the following is required

        SQL> grant select on IJC_IDXOWNER.structures_jcx to JCC_IDX_ROLE;

        Grant succeeded.

        SQL> grant select on IJC_IDXOWNER.structures_jcx_ul to JCC_IDX_ROLE;

        Grant succeeded.

In addition IJC currently requires you to grant permissions to the cartridge property table.

        SQL> grant select on IJC_IDXOWNER.jc_idx_property to JCC_IDX_ROLE;

        Grant succeeded.

Also, in IJC versions prior to 5.5 you must grant permissions to the _JSU cartridge table (since 5.5 this table is optional):

        SQL> grant select on IJC_IDXOWNER.idx_structures_jsu to JCC_IDX_ROLE;

        Grant succeeded.

Verify that the grants were successful. Connect to Oracle as IJC_IDXUSER and do this (note: as mentioned above, you may not see the IDX_STRUCTURES_JSU table):

        SQL> select table_name from all_tables where owner = 'IJC_IDXOWNER';

        TABLE_NAME
        ------------------------------
        STRUCTURES
        JC_IDX_PROPERTY
        IDX_STRUCTURES_JSU
        JCHEMPROPERTIES
        IDX_STRUCTURES_JCX
        IDX_STRUCTURES_JCX_UL

        6 rows selected.

Also, if you want to be able to insert new rows then you must also be able to see the sequence:

        SQL>  grant select on IJC_IDXOWNER.seq_structures to JCC_IDX_ROLE;

        Grant succeeded.

Now you can confirm that you can run a structure search. Connect to Oracle as 'IJC' and then:

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

        Call completed.

        SQL> select * from IJC_IDXOWNER.structures where JCHEM.jc_compare(structure, 'CC', 't:s') = 1;

	ID      STRUCTURE
        --------------- ---------
	 2      CC
	 3      CCC

From cartridge version 5.7, onwards , you can now run the config-util.sh (or .bat) script with the config-index-user hopefully removing the general pain from this configuration. The associated elements of the role are still in place but the role itself is not formally required.