Configuring and Administering Oracle SOA Suite 11g - Oracle SOA Suite 11g Administrator's Topic - page 285

Follow these instructions to create a generic database role that can be granted
to developer accounts. The following instructions assume that the schema name
for the MDS is DEV_MDS:

1. Log in to the database with a user account with the SYSDBA privileges:

sqlplus "/ AS SYSDBA"

2. Create a role called SOA_READONLY:

CREATE ROLE soa_readonly;

3. Capture the output of the following commands and execute each of them:

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

4. Grant execute permissions to SOA_READONLY for specific packages:

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;

5. Create public synonyms for these packages:

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;

6. Create your developer account:

CREATE USER developer IDENTIFIED BY welcome1;
GRANT resource, connect, soa_readonly TO developer;