List Oracle privileges granted to a user or role
Queries to list the system privileges, table privileges and role-inherited grants of an Oracle user, using the DBA dictionary views.
Oracle records every privilege in the data dictionary. The queries below list what a user holds directly, through roles, or on individual tables and views. The approach comes from a public Stack Overflow answer on listing the grants a user has received.
Replace APP_USER and APP_ROLE with the user or role you want to inspect.
System privileges
SELECT PRIVILEGE
FROM sys.dba_sys_privs
WHERE grantee = 'APP_USER'
UNION
SELECT PRIVILEGE
FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role)
WHERE rp.grantee = 'APP_USER'
ORDER BY 1;
Direct grants on tables and views
SELECT owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv
FROM table_privileges
WHERE grantee = 'APP_USER'
ORDER BY owner, table_name;
SELECT * FROM dba_tab_privs WHERE grantee = 'APP_USER';
Indirect grants on tables and views (through roles)
SELECT DISTINCT owner, table_name, PRIVILEGE
FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
WHERE rtp.role = 'APP_ROLE'
ORDER BY owner, table_name;
SELECT DISTINCT owner, table_name, PRIVILEGE
FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
WHERE rp.grantee = 'APP_USER'
ORDER BY owner, table_name;
Copy a role and its grants as DDL
DBMS_METADATA generates the statements that recreate a role and its grants, for example to move them to another database:
SELECT dbms_metadata.get_ddl('ROLE', role) FROM dba_roles;
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', '&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', '&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT', '&&your_role_name') FROM dual;
Notes
- The
table_privilegesview in the direct-grants query is used as written in the original note. The documented dictionary views for table privileges areDBA_TAB_PRIVSandUSER_TAB_PRIVS, and the query above that usesDBA_TAB_PRIVSis the reference form. Check the view names on your Oracle release before relying on the first query.