Security

Roles

Privileges to a Role are not Inheritied by PL/SQL (procs etc.) and Views

For a PL/SQL app to interact with external schema, the schema it is running from (ex: scott) must be explicitly granted the privilege on the object.

Example: GRANT SELECT ON etl.table42 TO scott;

Per Doc ID 13615.1

Create

CREATE ROLE <RoleName>;

CREATE ROLE data_mgr;

Example Session

 CREATE ROLE data_steward_r;
 CREATE ROLE data_steward_rw;

 GRANT READ ANY TABLE TO data_steward_r;

 GRANT READ ANY TABLE   TO data_steward_rw;
 GRANT INSERT ANY TABLE TO data_steward_rw;
 GRANT UPDATE ANY TABLE TO data_steward_rw;
 GRANT DELETE ANY TABLE TO data_steward_rw;

 GRANT ROLE data_steward_r  TO scott;
 GRANT ROLE data_steward_rw TO betty;

Data Steward Option

 -- This Allows a User to Perform DDL and DML on All User Tables in Database
 GRANT ALL PRIVILEGES TO <Username|Role>;

Does not allow many DBA operations however.

Display

-- All Roles in Database

SELECT * FROM dba_roles ORDER BY role;

-- A Particular Role

SELECT * FROM dba_role_privs WHERE granted_role='ROLE_NAME';

-- All Roles a User is a Member Of

COL grantee      FORMAT a15
COL granted_role FORMAT a15
SELECT * FROM dba_role_privs WHERE grantee = '&v_Username';

-- All Users in a Particular Role

SELECT grantee FROM dba_role_privs WHERE granted_role = '&v_RollName' ORDER BY grantee;

-- Priviledges for Roles

SELECT username, granted_role, default_role, admin_option FROM user_role_privs;

-- Roles Created by Oracle (when database created)

SELECT role FROM dba_roles 
WHERE ORACLE_MAINTAINED = 'Y'
ORDER BY role;

-- Just User Created (not created on database creation)

SELECT role FROM dba_roles 
WHERE ORACLE_MAINTAINED = 'N'
ORDER BY role;

Drop

DROP ROLE <RoleName>;

DROP ROLE data_mgr;