Check all privileges of User in Oracle

There are many views (VIEW in the SYS schema) that contain the privileges for a user:

• dba_sys_privs : System privileges granted to users and roles
• dba_tab_privs : All grants on objects in the database
• dba_role_privs : Roles granted to users and roles
• table_privileges : Grants on objects for which the user is the grantor, grantee, owner,
  or an enabled role or PUBLIC is the grantee

Below query shows all privileges, roles on objects present in Oracle Database to a particular user objects where

• TYPE : Type of Privilege or ROLE assigned to User.
• GRANTEE : User who have the permission of the object.
• PRIV : Define what privilege, role or object has
• TABNM : Name of the object on which privilege was granted.
• COLNM : Name of the column (if applicable).
• PRIV : Privilege on the object.
• AD : Whether the privilege was granted with the GRANT option. Equal to YES or NO.
• OWNER : Owner of the object

SELECT 'ROLE'       typ,
       grantee      grantee,
       granted_role priv,
       admin_option ad,
       '--'         tabnm,
       '--'         colnm,
       '--'         owner
FROM   dba_role_privs
WHERE  grantee = :cp_grantee
UNION
SELECT 'SYSTEM'     typ,
       grantee      grantee,
       privilege    priv,
       admin_option ad,
       '--'         tabnm,
       '--'         colnm,
       '--'         owner
FROM   dba_sys_privs
WHERE  grantee = :cp_grantee
UNION
SELECT 'TABLE'    typ,
       grantee    grantee,
       privilege  priv,
       grantable  ad,
       table_name tabnm,
       '--'       colnm,
       owner      owner
FROM   dba_tab_privs
WHERE  grantee = :cp_grantee
UNION
SELECT 'COLUMN'    typ,
       grantee     grantee,
       privilege   priv,
       grantable   ad,
       table_name  tabnm,
       column_name colnm,
       owner       owner
FROM   dba_col_privs
WHERE  grantee = :cp_grantee
ORDER  BY 5; 

Sample O/P:

Note:

--To see which table privileges are granted by you to other users.
SELECT * FROM   user_tab_privs_made;

--To see which table privileges are granted to you by other users
SELECT * FROM   user_tab_privs_recd;

--To see which column level privileges are granted by you to other users.
SELECT * FROM   user_col_privs_made

--To see which column level privileges are granted to you by other users
SELECT * FROM   user_col_privs_recd;

--To see which privileges are granted to roles
SELECT * FROM   user_role_privs;