Query to show all privileges for a oracle user

 

/*

Querying DBA/USER Privilege Views

A database administrator (DBA) for Oracle can simply execute a query to view the rows in DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS to retrieve information about user privileges related to the system, tables, and roles, respectively.

For example, a DBA wishing to view all system privileges granted to all users would issue the following query:

*/

SELECT * FROM DBA_SYS_PRIVS;

/*

The DBA_SYS_PRIVS view contains three columns of data:

    GRANTEE is the name, role, or user that was assigned the privilege.

    PRIVILEGE is the privilege that is assigned.

    ADMIN_OPTION indicates if the granted privilege also includes the ADMIN option.

To determine which users have direct grant access to a table we’ll use the DBA_TAB_PRIVS view:

*/

SELECT * FROM DBA_TAB_PRIVS;

/*

You can check the official documentation for more information about the columns returned from this query, but the critical columns are:

    GRANTEE is the name of the user with granted access.

    TABLE_NAME is the name of the object (table, index, sequence, etc).

    PRIVILEGE is the privilege assigned to the GRANTEE for the associated object.

Finally, querying the DBA_ROLE_PRIVS view has much of the same information but applicable to roles instead, where the GRANTED_ROLE column specifies the role in question:

*/

SELECT * FROM DBA_ROLE_PRIVS;

/*

Querying the Current User’s Privileges

SELECT * FROM USER_SYS_PRIVS;

/*

Since the USER_ privilege views are effectively the same as their DBA_ counterparts, but specific to the current user only, the type of returned data and column names are all identical to those when querying DBA_ views intead.

*/