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;