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 =>
