LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon