Raastech Blog: Create a read-only database account to the MDS

Wednesday, December 22, 2010

Create a read-only database account to the MDS

This note describes how to grant your developers read-only access to the MDS. This is to allow them to navigate and browse the MDS from within JDeveloper 11g.

Let's assume that the database account you installed SOA Suite 11g to is called DEV_MDS and you want a read-only account called DEV_MDS_READONLY.

CREATE USER dev_mds_readonly IDENTIFIED BY welcome1;

GRANT resource, connect TO dev_mds_readonly;

spool c:\temp\tempscript.sql

SELECT 'GRANT select ON ' || ' dev_mds.' || table_name || ' TO dev_mds_readonly;'
FROM dba_tables
WHERE OWNER = 'DEV_MDS';

SELECT 'CREATE SYNONYM dev_mds_readonly.' || table_name || ' for ' || owner || '.' || table_name || ';'
FROM dba_tables
WHERE OWNER = 'DEV_MDS';

spool off

@c:\temp\tempscript.sql

CREATE SYNONYM dev_mds_readonly.mds_internal_common FOR dev_mds.mds_internal_common;

CREATE SYNONYM dev_mds_readonly.mds_internal_shredded FOR dev_mds.mds_internal_shredded;

CREATE SYNONYM dev_mds_readonly.mds_internal_utils FOR dev_mds.mds_internal_utils;

GRANT execute ON dev_mds.mds_internal_common TO dev_mds_readonly;

GRANT execute ON dev_mds.mds_internal_shredded TO dev_mds_readonly;

GRANT execute ON dev_mds.mds_internal_utils TO dev_mds_readonly;

Applicable Versions:

Oracle SOA Suite 11g (11.1.1.x)

3 comments:

Anonymous said...: Apart from the above steps, I also needed to grant SELECT privilege on DEV_MDS tables to mds_readonly, otherwise I couldn't see the partitions and couldn't create an MDS connection in JDev using the read only account.

SELECT 'grant SELECT on '||'dev_mds.' || table_name|| ' to mds_readonly;'
FROM dba_tables
WHERE OWNER = 'DEV_MDS';; October 24, 2011 at 2:45 PM
Anonymous said...: Thank you anonymous. I forgot to add those!

I also added some execute permissions, so this script should now work on PS3.; January 23, 2012 at 5:01 PM
Anonymous said...: Another approach...

CREATE ROLE soa_readonly;

SELECT 'GRANT select ON ' || ' dev_mds.' || table_name || ' TO soa_readonly;' FROM dba_tables WHERE OWNER = 'DEV_MDS';

SELECT 'GRANT select ON ' || ' dev_mds.' || table_name || ' TO soa_readonly;' FROM dba_tables WHERE OWNER = 'DEV_SOAINFRA';

SELECT 'CREATE PUBLIC SYNONYM ' || table_name || ' for ' || owner || '.' || table_name || ';' FROM dba_tables WHERE OWNER = 'DEV_MDS';

GRANT execute ON dev_mds.mds_internal_common TO soa_readonly;

GRANT execute ON dev_mds.mds_internal_shredded TO soa_readonly;

GRANT execute ON dev_mds.mds_internal_utils TO soa_readonly;

CREATE PUBLIC SYNONYM mds_internal_common for dev_mds.mds_internal_common;

CREATE PUBLIC SYNONYM mds_internal_shredded for dev_mds.mds_internal_shredded;

CREATE PUBLIC SYNONYM mds_internal_utils for dev_mds.mds_internal_utils;

CREATE USER ahmed IDENTIFIED BY welcome1;

GRANT resource, connect, soa_readonly TO ahmed;; May 10, 2012 at 11:46 PM