[Q] sharing structures in Oracle view - ChemAxon Forum Archive

User bf3dbc99cf

04-02-2014 09:59:32

Dear ChemAxon,

Using InstantJChem in oracle, I'd like to share my compound structure (oracle view, DataTree, Entity, RelationShip, Form view) with my colleageue ORACLE users.

I can share the structure of COMPOUND TABLE, but I cannot share structures via Oracle VIEW.

As instructed in "Setting up cartridge tables for use in IJC", (http://www.chemaxon.com/instantjchem/ijc_latest/docs/admin/tips/carttable/index.html)

1. Make compound table :

I have created a user named 'MASTER'.

As MASTER, I created a vendor list table VENDOR, and a structure table COMPOUND using JCMan, and promoted it in InstantJChem.

create table VENDOR (

ID number(10) primary key,

NAME varchar2(100)

);

create table COMPOUND (

CD_ID number,

CD_STRUCTURE CLOB,

ID_VENDOR number(10) references VENDOR(ID)

);

User MASTER can see the structures as gridview of COMPOUND in IJC. OK.

2. Make compound view :

Now, I create an oracle composite view COMPOUND_VIEW;

create or replace view COMPOUND_VIEW

select CD_ID, CD_STRUCTURE, VENDOR.NAME

from COMPOUND, VENDOR

where COMPOUND.ID_VENDOR = VENDOR.ID

order by CD_ID;

User MASTER can see the structures as gridview of COMPOUND_VIEW in IJC. OK.

3. Share COMPOUND table :

I create a SLAVE oracle user, and give permission on COMPOUND to SLAVE as in the web page.

As SLAVE, I can see structures in gridview of COMPOUND table entity in IJC. OK.

4. Share COMPOUND_VIEW :

Oracle user SLAVE can see the view COMPOUND_VIEW, and can do substructure search on this view. OK.

select cd_id from MASTER.COMPOUND_VIEW where JC_COMPARE( cd_structure, 'c1ccccc1NC(=O)Nc2ccccc2C', 't:s' ) = 1;

But cannot see structures in gridview of COMPOUND_VIEW entity in IJC.

In COMPOUND_VIEW entity promoted, the CD_STRUCTURE column seems to be recognized as image(.jpg, ... ).

FAIL!!!

5. Share COMPOUND table, create view

As MASTER, I granted select on COMPOUND, VENDOR to SLAVE.

As SLAVE, I create a COMPOUND_VIEW such as

create or replace view COMPOUND_VIEW2

select CD_ID, CD_STRUCTURE, VENDOR.NAME

from MASTER.COMPOUND, MASTER.VENDOR

where COMPOUND.ID_VENDOR = VENDOR.ID

order by CD_ID;

I can do substructure search on COMPOUND_VIEW2.

But I cannot promote this COMPOUND_VIEW2 to structural entity in IJC. FAIL!!!

How can the user SLAVE see the structures in COMPOUND_VIEW in IJC?

Your instruction page does not contain information on how to share oracle view.

Regards,

Chong Hak Chae,

ChemAxon 2bdd02d1e5

05-02-2014 13:46:19

Dear Chong Hak Chae,

After a long experimentation I found possible solution.

There are several additional thing that has to be granted, these are described at:
http://www.chemaxon.com/jchem/doc/admin/cartridge.html#consider_using_roles

Note that you did not call call jchem.privman_pkg.grants_on_jcobjs('jchem', 'SLAVE'); for the SLAVE user. Maybe only this helps.

Also reading this whole chapter might be useful:
http://www.chemaxon.com/jchem/doc/admin/cartridge.html#users

I also called jchem_owner.PRIVMAN_PKG.SYNS_FOR_JCOBJS('jchem_owner','jchem_user'); for both MASTER and SLAVE under an admin user. But this is probably not necessary.

Hope it helps.

Filip

User bf3dbc99cf

06-02-2014 00:25:45

Dear Filip,

Thank you for your kind reply. I am in desperate state on this problem.

As you told, I have already called : call jchem.privman_pkg.grants_on_jcobjs('jchem', 'SLAVE');

But the MASTER.COMPOUND_VIEW does not promote into Molecule in SLAVE user. (see Fig)

I can grant search on index MASTER.JC_IDX_COMPOUND of MASTER.COMPOUND table to SLAVE.

But I cannot grant search on index MASTER.COMPOUND_VIEW to SLAVE, there is no index on view.

If possible, please send me your scenario (oracle scripts) for sharing structure in oracle view in InstantJChem.

Regards,

==============================================================================

as SYSDBA:

create user SLAVE identified by SLAVE123;

grant connect, resource, create synonym, create session, jcc_basic_role, create view to SLAVE;

call dbms_java.grant_permission( 'SLAVE','SYS:java.net.SocketPermission', '127.0.0.1:1100', 'connect,resolve' );

as JCHEM:

call privman_pkg.grants_on_jcobjs('jchem', 'MASTER');

call privman_pkg.grants_on_jcobjs('jchem', 'SLAVE');

call jchem.PRIVMAN_PKG.SYNS_FOR_JCOBJS('JCHEM','MASTER');

call jchem.PRIVMAN_PKG.SYNS_FOR_JCOBJS('JCHEM','SLAVE');

grant JCC_BASIC_ROLE to SLAVE;

as SLAVE:

call jchem_core_pkg.use_password('SLAVE123');

as MASTER:

grant select,insert,update,delete on JCHEMPROPERTIES to SLAVE;

grant select,insert,update,delete on JCHEMPROPERTIES_CR to SLAVE;

grant SELECT on JC_IDX_PROPERTY to SLAVE;

call jchem.privman_pkg.grants_on_jcidx('SLAVE', 'MASTER', 'JC_IDX_COMPOUND', 1, 0, 0, 0);

as SLAVE:

select cd_id from MASTER.compound where JC_COMPARE( cd_structure, 'c1ccccc1NC(=O)Nc2ccccc2C', 't:s' ) = 1; ==> OK

as MASTER:

grant select on JC_IDX_COMPOUND_JCX_SQ to SLAVE;

grant select on JC_IDX_COMPOUND_JCX_USQ to SLAVE;

grant select on COMPOUND_SQ to SLAVE;

grant select on COMPOUND_USQ to SLAVE;

grant select on COMPOUND_VIEW to SLAVE;

select cd_id from MASTER.compound_view where JC_COMPARE( cd_structure, 'c1ccccc1NC(=O)Nc2ccccc2C', 't:s' ) = 1; ==> OK

select cd_id from MASTER.compound where JC_COMPARE( cd_structure, 'c1ccccc1NC(=O)Nc2ccccc2C', 't:s' ) = 1; ==> OK

SELECT 'GRANT SELECT ON MASTER.' || object_name || ' TO SLAVE;' from user_objects where object_type = 'TABLE'; ==> give select permission to SLAVE

SELECT 'GRANT SELECT ON MASTER.' || object_name || ' TO SLAVE;' from user_objects where object_type = 'VIEW'; ==> give select permission to SLAVE

SELECT 'GRANT SELECT ON MASTER.' || object_name || ' TO SLAVE;' from user_objects where object_type = 'SEQUENCE'; ==> give select permission to SLAVE

grant insert, select, delete, update on ijc_user to SLAVE;

grant insert, select, delete, update on ijc_item_user to SLAVE;

grant insert, select, delete, update on IJC_CUSTOM_ITEMS to SLAVE;

grant insert, select, delete, update on IJC_CUSTOM_ITEMS_SHARING to SLAVE;

grant insert, select, delete, update on ijc_schema to SLAVE;

ChemAxon 2bdd02d1e5

06-02-2014 14:20:07

I have one more idea. Try to call these 2 statements as sysdba:

call jchem.PRIVMAN_PKG.SYNS_FOR_JCOBJS('JCHEM','MASTER');

call jchem.PRIVMAN_PKG.SYNS_FOR_JCOBJS('JCHEM','SLAVE');

Otherwise I'm moving this forum to JCChem Cartridge support area. They should know more details about this privilege configuration.

ChemAxon 2bdd02d1e5

06-02-2014 16:55:08

One more thing. Did you add MASTER`s schema to be visible in SLAVE schema in IJC? As described at http://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/editing_database/multi_schema.html

Also you have to grant at least SELECT to MASTER.JCHEMPROPERTIES table to SLAVE. (Described at http://www.chemaxon.com/instantjchem/ijc_latest/docs/admin/tips/carttable/index.html)

Thanks.

Filip

User bf3dbc99cf

07-02-2014 00:17:46

Dear Filip,

Thank you for your GREAT help. Now SLAVE is able to see the COMPOUND_VIEW of MASTER user.

The problem was that I omitted the schema name MASTER. when I create the COMPOUND_VIEW as MASTER user.

as MASTER:

create or replace view COMPOUND_VIEW

select CD_ID, CD_STRUCTURE, VENDOR.NAME

from MASTER.COMPOUND, MASTER.VENDOR

where COMPOUND.ID_VENDOR = VENDOR.ID

order by CD_ID;

grant select on COMPOUND_VIEW to SLAVE;

With this, SLAVE can promote MASTER.COMPOUND_VIEW to molecule entity, and can see the chemical structures.

Another question is:

Can SLAVE see the Entity, DataTree, FormView of MASTER?

Regards,

Chong Hak Chae,