How to get all the privileges of an Oracle database user.

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

Leave a Reply

Your email address will not be published.