Can I list privileges that were granted DIRECTLY to a user in Oracle?

May 11, 2022

Seems like simple selects from session_privs and user_sys_privs give all current privileges, but I cannot differentiate which of them are inherited from a role and which were granted directly.

Is there a way of listing ONLY directly granted privileges?

>Solution :

If you query dictionary, here’s some interesting result:

SQL> select * from dictionary where lower(table_name) like '%user%privs%' order by table_name;

TABLE_NAME                     COMMENTS
------------------------------ ----------------------------------------------------------------------
USER_AQ_AGENT_PRIVS
USER_COL_PRIVS                 Grants on columns for which the user is the owner, grantor or grantee
USER_COL_PRIVS_MADE            All grants on columns of objects owned by the user
USER_COL_PRIVS_RECD            Grants on columns for which the user is the grantee
USER_ROLE_PRIVS                Roles granted to current user
USER_RSRC_CONSUMER_GROUP_PRIVS Switch privileges for consumer groups for the user
USER_RSRC_MANAGER_SYSTEM_PRIVS system privileges for the resource manager for the user
USER_SYS_PRIVS                 System privileges granted to current user
USER_TAB_PRIVS                 Grants on objects for which the user is the owner, grantor or grantee
USER_TAB_PRIVS_MADE            All grants on objects owned by the user
USER_TAB_PRIVS_RECD            Grants on objects for which the user is the grantee

11 rows selected.

SQL>

Views you might be interested in – as they show privileges granted directly to you – are