Roles
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;
Drop
DROP ROLE <RoleName>;
DROP ROLE data_mgr;