A hierarchical view of system privileges held by the current user

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

A quick posting to save a query for future times, something that can come in handy when you need to check if you hold a certain system privilege and from whence it comes.

I am currently working for a large company where they have in place a hierarchical scheme of roles and i wanted to know why i was able to create a database link without apparently holding the privilege directly or through a role, so i wrote the following query that allows me to drill down the privileges level by level, reconstructing the history of recursive role grants.

select
privilege,
null as granted_from_role,
null as inherited_from,
0 as "LEVEL"
from user_sys_privs
union all
select rsp.privilege, rp.granted_role, rp.role inherited_from, rp.lev
from role_sys_privs rsp,
    (select 1 as lev, null as role, granted_role
       from user_role_privs
     union all
     select level + 1, role, granted_role
       from role_role_privs
     connect by role = prior(granted_role)
     start with role in (select granted_role from user_role_privs)
    ) rp
where rsp.role = rp.granted_role
order by 1,2 nulls first,3 nulls first,4;

What i got is the following report:

PRIVILEGE	GRANTED_FROM_ROLE	INHERITED_FROM	LEVEL
ADMINISTER DATABASE TRIGGER	IMP_FULL_DATABASE		1
ADMINISTER RESOURCE MANAGER	IMP_FULL_DATABASE		1
ALTER ANY PROCEDURE	IMP_FULL_DATABASE		1
ALTER ANY TABLE	IMP_FULL_DATABASE		1
ALTER ANY TRIGGER	IMP_FULL_DATABASE		1
ALTER ANY TYPE	IMP_FULL_DATABASE		1
ALTER SESSION			0
ALTER SESSION	CONNECT	ADX_DEVELOPER	2
ANALYZE ANY	IMP_FULL_DATABASE		1
AUDIT ANY	IMP_FULL_DATABASE		1
BECOME USER	IMP_FULL_DATABASE		1
COMMENT ANY TABLE	IMP_FULL_DATABASE		1
CREATE ANY CLUSTER	IMP_FULL_DATABASE		1
CREATE ANY CONTEXT	IMP_FULL_DATABASE		1
CREATE ANY DIMENSION	IMP_FULL_DATABASE		1
CREATE ANY DIRECTORY	IMP_FULL_DATABASE		1
CREATE ANY INDEX	IMP_FULL_DATABASE		1
CREATE ANY INDEXTYPE	IMP_FULL_DATABASE		1
CREATE ANY LIBRARY	IMP_FULL_DATABASE		1
CREATE ANY OPERATOR	IMP_FULL_DATABASE		1
CREATE ANY PROCEDURE	IMP_FULL_DATABASE		1
CREATE ANY SEQUENCE	IMP_FULL_DATABASE		1
CREATE ANY SNAPSHOT	IMP_FULL_DATABASE		1
CREATE ANY SYNONYM	IMP_FULL_DATABASE		1
CREATE ANY TABLE	IMP_FULL_DATABASE		1
CREATE ANY TRIGGER	IMP_FULL_DATABASE		1
CREATE ANY TYPE	IMP_FULL_DATABASE		1
CREATE ANY VIEW	IMP_FULL_DATABASE		1
CREATE CLUSTER	CONNECT	ADX_DEVELOPER	2
CREATE CLUSTER	RESOURCE		1
CREATE DATABASE LINK	CONNECT	ADX_DEVELOPER	2
CREATE INDEXTYPE	RESOURCE		1
CREATE OPERATOR	RESOURCE		1
CREATE PROCEDURE	ADX_DEVELOPER		1
CREATE PROCEDURE	RESOURCE		1
CREATE PROFILE	IMP_FULL_DATABASE		1
CREATE PUBLIC SYNONYM	IMP_FULL_DATABASE		1
CREATE ROLE	IMP_FULL_DATABASE		1
CREATE ROLLBACK SEGMENT	IMP_FULL_DATABASE		1
CREATE SEQUENCE	CONNECT	ADX_DEVELOPER	2
CREATE SEQUENCE	RESOURCE		1
CREATE SESSION			0
CREATE SESSION	CONNECT	ADX_DEVELOPER	2
CREATE SNAPSHOT	ADX_ADDITIONAL_GRANTS	ADX_DEVELOPER	2
CREATE SYNONYM	CONNECT	ADX_DEVELOPER	2
CREATE TABLE	CONNECT	ADX_DEVELOPER	2
CREATE TABLE	RESOURCE		1
CREATE TABLESPACE	IMP_FULL_DATABASE		1
CREATE TRIGGER	ADX_DEVELOPER		1
CREATE TRIGGER	RESOURCE		1
CREATE TYPE	RESOURCE		1
CREATE USER	IMP_FULL_DATABASE		1
CREATE VIEW	CONNECT	ADX_DEVELOPER	2
DELETE ANY TABLE			0
DROP ANY CLUSTER	IMP_FULL_DATABASE		1
DROP ANY CONTEXT	IMP_FULL_DATABASE		1
DROP ANY DIMENSION	IMP_FULL_DATABASE		1
DROP ANY DIRECTORY	IMP_FULL_DATABASE		1
DROP ANY INDEX	IMP_FULL_DATABASE		1
DROP ANY INDEXTYPE	IMP_FULL_DATABASE		1
DROP ANY LIBRARY	IMP_FULL_DATABASE		1
DROP ANY OPERATOR	IMP_FULL_DATABASE		1
DROP ANY OUTLINE	IMP_FULL_DATABASE		1
DROP ANY PROCEDURE	IMP_FULL_DATABASE		1
DROP ANY ROLE	IMP_FULL_DATABASE		1
DROP ANY SEQUENCE	IMP_FULL_DATABASE		1
DROP ANY SNAPSHOT	IMP_FULL_DATABASE		1
DROP ANY SYNONYM	IMP_FULL_DATABASE		1
DROP ANY TABLE	IMP_FULL_DATABASE		1
DROP ANY TRIGGER	IMP_FULL_DATABASE		1
DROP ANY TYPE	IMP_FULL_DATABASE		1
DROP ANY VIEW	IMP_FULL_DATABASE		1
DROP PROFILE	IMP_FULL_DATABASE		1
DROP PUBLIC DATABASE LINK	IMP_FULL_DATABASE		1
DROP PUBLIC SYNONYM	IMP_FULL_DATABASE		1
DROP ROLLBACK SEGMENT	IMP_FULL_DATABASE		1
DROP TABLESPACE	IMP_FULL_DATABASE		1
DROP USER	IMP_FULL_DATABASE		1
EXECUTE ANY PROCEDURE			0
EXECUTE ANY PROCEDURE	ADX_DEVELOPER		1
EXECUTE ANY PROCEDURE	IMP_FULL_DATABASE		1
EXECUTE ANY TYPE	IMP_FULL_DATABASE		1
GLOBAL QUERY REWRITE	IMP_FULL_DATABASE		1
INSERT ANY TABLE			0
INSERT ANY TABLE	IMP_FULL_DATABASE		1
MANAGE ANY QUEUE	IMP_FULL_DATABASE		1
SELECT ANY SEQUENCE			0
SELECT ANY SEQUENCE	ADX_DEVELOPER		1
SELECT ANY TABLE			0
SELECT ANY TABLE	ADX_DEVELOPER		1
SELECT ANY TABLE	IMP_FULL_DATABASE		1
UNLIMITED TABLESPACE			0
UPDATE ANY TABLE			0
UPDATE ANY TABLE	IMP_FULL_DATABASE		1

Which means that i am able to create a database link in virtue of the privilege granted via CONNECT role, that in turn is granted to role ADX_DEVELOPER, that is finally granted to my user.
Blanks in the column GRANTED_FROM_ROLE mean that the privilege has been granted directly to the user.