While investigating of how to get all the privileges of an Oracle database user I played with the following queries:
select COUNT(*) from DICTIONARY; select USERNAME from SYS.ALL_USERS; select * from DBA_USERS; select * from DBA_ROLES; select * from DBA_SYS_PRIVS where grantee = 'CONNECT'; select * from DBA_SYS_PRIVS where grantee = 'RESOURCE'; select * from DBA_SYS_PRIVS; select * from SESSION_PRIVS; select * from DBA_TAB_PRIVS where GRANTEE <> 'PUBLIC'; select count(*) from DBA_TAB_PRIVS; select * from DBA_COL_PRIVS; select * from ROLE_TAB_PRIVS;
and with the queries that gets similar information for the current user:
select * from USER_ROLE_PRIVS; select * from USER_ROLE_PRIVS where USERNAME <> 'SYS'; select * from USER_TAB_PRIVS_MADE where GRANTEE <> 'PUBLIC'; select count(*) from USER_TAB_PRIVS_MADE; select * from USER_COL_PRIVS_MADE where GRANTEE <> 'PUBLIC'; select * from USER_TAB_PRIVS_RECD; select * from USER_COL_PRIVS_RECD;
After that I did a google search and found an interesting article with a link to PL-SQL script that displays the hierarchy of the user roles and privileges like this:
User => HR has been granted the following privileges ==================================================================== SYS PRIV => ALTER SESSION grantable => NO SYS PRIV => CREATE DATABASE LINK grantable => NO SYS PRIV => CREATE SEQUENCE grantable => NO SYS PRIV => CREATE SESSION grantable => NO SYS PRIV => CREATE SYNONYM grantable => NO SYS PRIV => CREATE VIEW grantable => NO SYS PRIV => UNLIMITED TABLESPACE grantable => NO TABLE PRIV => EXECUTE object => SYS.DBMS_STATS grantable => NO ROLE => RESOURCE which contains => SYS PRIV => CREATE CLUSTER grantable => NO SYS PRIV => CREATE INDEXTYPE grantable => NO SYS PRIV => CREATE OPERATOR grantable => NO SYS PRIV => CREATE PROCEDURE grantable => NO SYS PRIV => CREATE SEQUENCE grantable => NO SYS PRIV => CREATE TABLE grantable => NO SYS PRIV => CREATE TRIGGER grantable => NO SYS PRIV => CREATE TYPE grantable => NO ROLE => SODA_APP which contains => TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_ADMIN grantable => NO TABLE PRIV => READ object => XDB.JSON$USER_COLLECTION_METADATA grantable =>
or
User => PM has been granted the following privileges ==================================================================== SYS PRIV => UNLIMITED TABLESPACE grantable => NO ROLE => CONNECT which contains => SYS PRIV => CREATE SESSION grantable => NO SYS PRIV => SET CONTAINER grantable => NO ROLE => RESOURCE which contains => SYS PRIV => CREATE CLUSTER grantable => NO SYS PRIV => CREATE INDEXTYPE grantable => NO SYS PRIV => CREATE OPERATOR grantable => NO SYS PRIV => CREATE PROCEDURE grantable => NO SYS PRIV => CREATE SEQUENCE grantable => NO SYS PRIV => CREATE TABLE grantable => NO SYS PRIV => CREATE TRIGGER grantable => NO SYS PRIV => CREATE TYPE grantable => NO ROLE => SODA_APP which contains =>