Setting up cartridge tables for use in IJC

    If you are using version 5.7 of the JChem cartridge please consult the JChem Cartridge installation guide.

    This guide describes how to create a standard table in an Oracle user 'IJC_IDXOWNER', add a JChem cartridge index to it. Next, access this index from another non-default schema named 'IJC_IDXUSER'. Previously, the tricky part of this was getting the Oracle level permissions correct for each of these users, but now there is a much improved administration for this.

    The example assumes:

    • The table owner where we create the cartridge table and associated index is 'IJC_IDXOWNER'.

    • The JChem cartridge owner (where the cart is installed) is 'JCHEM'.

    • The Instant JChem user is 'IJC_IDXUSER' and we access the cartridge index in the 'IJC_IDXOWNER' schema from here.

    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.
    Copy

    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
    Copy

    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');
    Copy

    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.

    Setting up a user to be able to own a JChem cartridge index/table

    This could be your 'IJC_IDXOWNER' user if you want them to be able to create and use JChem cartridge indexes on tables and thus effectively 'own' the index. To make this clear we call this user 'IJC_IDXOWNER':

    1. Create an Oracle user 'IJC_IDXOWNER'.

    1. Grant CONNECT, RESOURCE and JCC_BASIC_ROLE roles to this user in Oracle as DBA.

    1. On JChem cartridge server, in cartridge directory run the config-util.sh (or .bat) script with the list-sqls-for-jcc-user-privs argument.

    1. Answer the questions correctly, at the prompt.

    1. Perform the operations described as the DBA and JChem users.

    1. The user 'IJC_IDXOWNER', is now able to create the JChem and Standard tables with associated cartridge indexes, by right click in the entities tab, select Structures table and use the drop down of table types.

    An example of this config-util.sh script being called, with the 'list-sqls-for-jcc-user-privs' option is below:
    Copy

    Once the script has run, you need to perform the grant operations described between the CUT HERE parts of the output, to give the 'IJC_IDXOWNER' user the necessary access to the JChem cartridge functions.

    $ ./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
    Copy

    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.
    Copy

    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.
    Copy

    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.
    Copy

    Verify the data is in the table:

            SQL> select * from structures;

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

    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
    Copy

    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.
    Copy

    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.
    Copy

    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.
    Copy

    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.
    Copy

    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.
    Copy

    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.
    Copy

    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.
    Copy

    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.
    Copy

    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
    Copy

    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.

    Setting up a user to be able to use a cartridge index/table owned by another user

    IJC can access tables from Oracle schemas other than its own. To do this add the required schemas in the Database schemas property of the schema section of the schema editor. See here for details. For this to work with JChem cartridge indexes the IJC user must be granted extra privileges on the index. To make this clear we call this user 'IJC_IDXUSER':

    1. Create an Oracle user 'IJC_IDXUSER'.

    1. Grant CONNECT, RESOURCE and JCC_BASIC_ROLE roles to this user in Oracle as the DBA.

    1. Grant SELECT, INSERT, UPDATE and DELETE privileges on the JCHEMPROPERTIES and JCHEMPROPERTIES_CR tables in the index owner's schema ('IJC_IDXOWNER') to the 'IJC_IDXUSER' schema.

    1. Grant SELECT privileges on the JC_IDX_PROPERTY table in the index owner's schema to the 'IJC_IDXUSER'.

    1. On JChem cartridge server run the config-util.sh (or .bat) script with the config-index-user argument

    1. Answer the questions correctly, at the prompt.

    1. Perform the operations described as the DBA and JChem users

    1. Grant SELECT privileges on the sequence used for generation of IDs in the structure table (optional, you can use the Null value generator if you are not wanting to insert new rows). This step is really only relevant for Standard tables + index which have a potentially unknown sequence name.

    1. The user IJC_IDXUSER, is now able promote (Schema editor, tables tab) either JChem or Standard tables that have associated cartridge indexes that are owned by another user, for example IJC_IDXOWNER. The access level of each depends upon the previous configuration options.

    An example of using the config-util.sh script with the config-index-user option is given below. Please note the index name prompt expects a single index name. If you have multiple names you will need to repeat the final privman_pkg call for each index that you wish to use.

```
[oracle@fedora10 cartridge]$ ./config-util.sh config-index-user

Name of the Oracle host. [localhost]:

Oracle listener port [1521]:

Name of the Oracle instance [mydb]: orcl

Name of the cartridge owner [jchem]:
Password of the cartridge owner:

The role for basic JChem Cartridge user privileges [JCC_BASIC_ROLE]:

The owner of the index: ijc_idxowner
The password of the index owner:

The user of the index: ijc_idxuser

The name of the index (IDX_BUILDING_BLOCKS_GB): IDX_BUILDING_BLOCKS_GB -- if more than one index, need to specify one and repeat from script.

Give search permission to ijc on IDX_BUILDING_BLOCKS_GB (y, n) [n]: y

Give insert permission to ijc on IDX_BUILDING_BLOCKS_GB (y, n) [n]: n

Give update permission to ijc on IDX_BUILDING_BLOCKS_GB (y, n) [n]: n

Give delete permission to ijc on IDX_BUILDING_BLOCKS_GB (y, n) [n]: n

-- ---- SQLs to execute as jchem:
call privman_pkg.grants_on_jcobjs('jchem', 'ijc_idxowner')
grant JCC_BASIC_ROLE to ijc
-- ---- SQLs to execute as ijc_idxowner:
call privman_pkg.grants_on_jcidx('ijc_idxuser', 'ijc_idxowner', 'IDX_BUILDING_BLOCKS_GB', 1, 0, 0, 0) -- need to qualify with jchem. & repeat for each index...

Do you want to execute the SQL statements (y, n) [y]: n

+ set +x

```

Grant the privileges as described (unless you chose to let the script do this for you). Don't forget, you will need to repeat the above calls for each index you wish to grant access to, only the one you specify is done for you!
    Copy

    You can now use 'IJC_IDXUSER' to access the (any various configured levels of access) 'IJC_IDXOWNER' tables by:

    1. Connect to the 'IJC_IDXUSER' schema using InstantJChem application.

    1. Include the 'IJC_IDXOWNER' schema as an additional schema (schema tab -> database schemas then add).

    1. Promote the relevant 'IJC_IDXOWNER.STRUCTURES' table to a Standard entity + JChem cartridge in IJC.

    1. Confirm that you can see the 3 structures and run structure searches successfully from the grid view.

    images/download/attachments/1805836/security.png