Example Creating Common Role

1. Log into the oracle instance using SQL*Plus or other oracle scripting tool as SYSTEM user. 2. Create roles SCHEMA_DEVELOPER and SCHEMA_USER. CREATE ROLE "SCHEMA_DEVELOPER" NOT IDENTIFIED; GRANT CREATE SESSION TO "SCHEMA_DEVELOPER"; GRANT SELECT ANY DICTIONARY TO "SCHEMA_DEVELOPER"; GRANT ALTER SESSION TO "SCHEMA_DEVELOPER"; GRANT CREATE CLUSTER TO "SCHEMA_DEVELOPER"; GRANT CREATE DATABASE LINK TO "SCHEMA_DEVELOPER"; GRANT CREATE PROCEDURE TO "SCHEMA_DEVELOPER"; GRANT CREATE PUBLIC SYNONYM TO "SCHEMA_DEVELOPER"; GRANT CREATE SEQUENCE TO "SCHEMA_DEVELOPER"; GRANT CREATE TABLE TO "SCHEMA_DEVELOPER"; GRANT CREATE TRIGGER TO "SCHEMA_DEVELOPER"; GRANT CREATE VIEW TO "SCHEMA_DEVELOPER"; GRANT DROP PUBLIC SYNONYM TO "SCHEMA_DEVELOPER"; CREATE ROLE "SCHEMA_USER" NOT IDENTIFIED; GRANT CREATE SESSION TO "SCHEMA_USER"; GRANT SELECT ANY DICTIONARY TO "SCHEMA_USER"; 3. Create the tablespaces (make sure to update the filepath for the datafile according to the server you are working with). Variables are enclosed in curley braces {}. Use this script as a guide: CREATE TABLESPACE "COMMONDATA" LOGGING DATAFILE '{PATH_TO_DATAFOLDER}\COMMONDATA01.DBF' SIZE 10M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; CREATE TABLESPACE "COMMONINDX" NOLOGGING DATAFILE '{PATH_TO_DATAFOLDER}\COMMONINDX01.DBF' SIZE 5M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; 4. Create the user and role using this script as a guide. (Note: if you are using Oracle 10g or higher you will need to remove lines that set quota unlimited on temp). CREATE USER "COMMON" profile "DEFAULT" identified by "{COMMON_PASSWORD}" default tablespace "COMMONDATA" temporary tablespace "TEMP" quota unlimited on COMMONDATA quota unlimited on COMMONINDX account UNLOCK; CREATE ROLE "COMMON_TABLE_USER" NOT IDENTIFIED; 5. Open a command prompt window and import a copy of the common schema from a .dmp file. If you dont have a .dmp file find another instance with the common schema and export it. Use these scripts as a guide: For importing the schema: imp common/{COMMON_PASSWORD}@{INSTANCE_NAME} file=common.dmp log=commonimp.log fromuser=common touser=common For exporting the schema: exp common/{COMMON_PASSWORD}@{INSTANCE_NAME} file=common.dmp log=commonexp.log 7. Run the following scripts one at a time to generate grant scripts for the roles needed. Select the statements that were created by these scripts and run them in order to grant the correct priveleges: SELECT 'grant SELECT, REFERENCES on "COMMON".' || table_name "Grant Privileges", 'TO ' || role || ';' "To Role" FROM dba_tables , dba_roles WHERE owner = 'COMMON' AND table_name = any (select table_name from dba_tables where table_name like 'CL_%') AND role = any (select role from dba_roles where role like 'COMMON%') ORDER BY role, table_name; col "Grant Privileges" format a50 SELECT 'grant SELECT, INSERT, UPDATE on "COMMON".' || table_name "Grant Privileges", 'TO ' || role || ';' "To Role" FROM dba_tables , dba_roles WHERE owner = 'COMMON' AND table_name = any (select table_name from dba_tables where table_name like 'CT_%') AND role = any (select role from dba_roles where role like 'COMMON%') ORDER BY role, table_name; 8. Grant schema role permissions: GRANT SCHEMA_DEVELOPER TO COMMON; GRANT COMMON_TABLE_USER TO SCHEMA_DEVELOPER; GRANT COMMON_TABLE_USER TO SCHEMA_USER;