14
05
19
 Leave a comment

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

Posted by | Oracle |

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 <&gt; '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 <&gt; 'SYS';
select * from USER_TAB_PRIVS_MADE where GRANTEE <&gt; 'PUBLIC';
select count(*) from USER_TAB_PRIVS_MADE;
select * from USER_COL_PRIVS_MADE where GRANTEE <&gt; '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 =&gt; HR has been granted the following privileges
====================================================================
	SYS PRIV =&gt; ALTER SESSION grantable =&gt; NO
	SYS PRIV =&gt; CREATE DATABASE LINK grantable =&gt; NO
	SYS PRIV =&gt; CREATE SEQUENCE grantable =&gt; NO
	SYS PRIV =&gt; CREATE SESSION grantable =&gt; NO
	SYS PRIV =&gt; CREATE SYNONYM grantable =&gt; NO
	SYS PRIV =&gt; CREATE VIEW grantable =&gt; NO
	SYS PRIV =&gt; UNLIMITED TABLESPACE grantable =&gt; NO
	TABLE PRIV =&gt; EXECUTE object =&gt; SYS.DBMS_STATS grantable =&gt; NO
	ROLE =&gt; RESOURCE which contains =&gt;
		SYS PRIV =&gt; CREATE CLUSTER grantable =&gt; NO
		SYS PRIV =&gt; CREATE INDEXTYPE grantable =&gt; NO
		SYS PRIV =&gt; CREATE OPERATOR grantable =&gt; NO
		SYS PRIV =&gt; CREATE PROCEDURE grantable =&gt; NO
		SYS PRIV =&gt; CREATE SEQUENCE grantable =&gt; NO
		SYS PRIV =&gt; CREATE TABLE grantable =&gt; NO
		SYS PRIV =&gt; CREATE TRIGGER grantable =&gt; NO
		SYS PRIV =&gt; CREATE TYPE grantable =&gt; NO
		ROLE =&gt; SODA_APP which contains =&gt;
			TABLE PRIV =&gt; EXECUTE object =&gt; XDB.DBMS_SODA_ADMIN grantable =&gt; NO
			TABLE PRIV =&gt; READ object =&gt; XDB.JSON$USER_COLLECTION_METADATA grantable =&gt;

or

User =&gt; PM has been granted the following privileges
====================================================================
	SYS PRIV =&gt; UNLIMITED TABLESPACE grantable =&gt; NO
	ROLE =&gt; CONNECT which contains =&gt;
		SYS PRIV =&gt; CREATE SESSION grantable =&gt; NO
		SYS PRIV =&gt; SET CONTAINER grantable =&gt; NO
	ROLE =&gt; RESOURCE which contains =&gt;
		SYS PRIV =&gt; CREATE CLUSTER grantable =&gt; NO
		SYS PRIV =&gt; CREATE INDEXTYPE grantable =&gt; NO
		SYS PRIV =&gt; CREATE OPERATOR grantable =&gt; NO
		SYS PRIV =&gt; CREATE PROCEDURE grantable =&gt; NO
		SYS PRIV =&gt; CREATE SEQUENCE grantable =&gt; NO
		SYS PRIV =&gt; CREATE TABLE grantable =&gt; NO
		SYS PRIV =&gt; CREATE TRIGGER grantable =&gt; NO
		SYS PRIV =&gt; CREATE TYPE grantable =&gt; NO
		ROLE =&gt; SODA_APP which contains =&gt;

Leave a Reply

Your email address will not be published. Required fields are marked *