Contents
Role Related Views
* Viewing Privilege and Role Information
Database Roles
All Database Roles
SELECT * FROM DBA_ROLES;
All System Privileges
SELECT * FROM DBA_SYS_PRIVS;
All Column-specific Privileges
SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE FROM DBA_COL_PRIVS;
All Roles Granted to Users and Other Roles
SELECT * FROM DBA_ROLE_PRIVS;
Role Specific
All the roles granted to a role
SELECT GRANTED_ROLE, ADMIN_OPTION FROM ROLE_ROLE_PRIVS WHERE ROLE = 'MY_ROLE';
All the system privileges granted to a role
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'MY_ROLE';
All the object privileges granted to a role
SELECT TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS WHERE ROLE = 'MY_ROLE';
User Specific
All roles granted to a user
DESC user_role_privs; SELECT * FROM user_role_privs WHERE username='USER2';
All object privileges granted to a user
* Not including column-specific privileges
SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS WHERE GRANTEE = 'USER2';
Current Session
All system privileges in current session
SELECT * FROM SESSION_PRIVS;
All roles in current session
SELECT * FROM SESSION_ROLES;
Examples
Grant ALL Table Privileges Belong to One User to Another
CREATE ROLE MY_ROLE; GRANT MY_ROLE TO USER2; BEGIN FOR tbl IN (SELECT TABLE_NAME FROM all_tables WHERE owner = 'USER1') LOOP dbms_output.put_line('Granting table: ' || tbl.table_name || ' to MY_ROLE'); EXECUTE IMMEDIATE 'GRANT ALL ON USER1.'||tbl.table_name||' TO MY_ROLE'; END LOOP; END;