Wednesday, November 26, 2008

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:































































































































































































































































































































































































































































































































































































PRIVILEGEGRANTED_FROM_ROLEINHERITED_FROMLEVEL
ADMINISTER DATABASE TRIGGERIMP_FULL_DATABASE
1
ADMINISTER RESOURCE MANAGERIMP_FULL_DATABASE
1
ALTER ANY PROCEDUREIMP_FULL_DATABASE
1
ALTER ANY TABLEIMP_FULL_DATABASE
1
ALTER ANY TRIGGERIMP_FULL_DATABASE
1
ALTER ANY TYPEIMP_FULL_DATABASE
1
ALTER SESSION

0
ALTER SESSIONCONNECTADX_DEVELOPER2
ANALYZE ANYIMP_FULL_DATABASE
1
AUDIT ANYIMP_FULL_DATABASE
1
BECOME USERIMP_FULL_DATABASE
1
COMMENT ANY TABLEIMP_FULL_DATABASE
1
CREATE ANY CLUSTERIMP_FULL_DATABASE
1
CREATE ANY CONTEXTIMP_FULL_DATABASE
1
CREATE ANY DIMENSIONIMP_FULL_DATABASE
1
CREATE ANY DIRECTORYIMP_FULL_DATABASE
1
CREATE ANY INDEXIMP_FULL_DATABASE
1
CREATE ANY INDEXTYPEIMP_FULL_DATABASE
1
CREATE ANY LIBRARYIMP_FULL_DATABASE
1
CREATE ANY OPERATORIMP_FULL_DATABASE
1
CREATE ANY PROCEDUREIMP_FULL_DATABASE
1
CREATE ANY SEQUENCEIMP_FULL_DATABASE
1
CREATE ANY SNAPSHOTIMP_FULL_DATABASE
1
CREATE ANY SYNONYMIMP_FULL_DATABASE
1
CREATE ANY TABLEIMP_FULL_DATABASE
1
CREATE ANY TRIGGERIMP_FULL_DATABASE
1
CREATE ANY TYPEIMP_FULL_DATABASE
1
CREATE ANY VIEWIMP_FULL_DATABASE
1
CREATE CLUSTERCONNECTADX_DEVELOPER2
CREATE CLUSTERRESOURCE
1
CREATE DATABASE LINKCONNECTADX_DEVELOPER2
CREATE INDEXTYPERESOURCE
1
CREATE OPERATORRESOURCE
1
CREATE PROCEDUREADX_DEVELOPER
1
CREATE PROCEDURERESOURCE
1
CREATE PROFILEIMP_FULL_DATABASE
1
CREATE PUBLIC SYNONYMIMP_FULL_DATABASE
1
CREATE ROLEIMP_FULL_DATABASE
1
CREATE ROLLBACK SEGMENTIMP_FULL_DATABASE
1
CREATE SEQUENCECONNECTADX_DEVELOPER2
CREATE SEQUENCERESOURCE
1
CREATE SESSION

0
CREATE SESSIONCONNECTADX_DEVELOPER2
CREATE SNAPSHOTADX_ADDITIONAL_GRANTSADX_DEVELOPER2
CREATE SYNONYMCONNECTADX_DEVELOPER2
CREATE TABLECONNECTADX_DEVELOPER2
CREATE TABLERESOURCE
1
CREATE TABLESPACEIMP_FULL_DATABASE
1
CREATE TRIGGERADX_DEVELOPER
1
CREATE TRIGGERRESOURCE
1
CREATE TYPERESOURCE
1
CREATE USERIMP_FULL_DATABASE
1
CREATE VIEWCONNECTADX_DEVELOPER2
DELETE ANY TABLE

0
DROP ANY CLUSTERIMP_FULL_DATABASE
1
DROP ANY CONTEXTIMP_FULL_DATABASE
1
DROP ANY DIMENSIONIMP_FULL_DATABASE
1
DROP ANY DIRECTORYIMP_FULL_DATABASE
1
DROP ANY INDEXIMP_FULL_DATABASE
1
DROP ANY INDEXTYPEIMP_FULL_DATABASE
1
DROP ANY LIBRARYIMP_FULL_DATABASE
1
DROP ANY OPERATORIMP_FULL_DATABASE
1
DROP ANY OUTLINEIMP_FULL_DATABASE
1
DROP ANY PROCEDUREIMP_FULL_DATABASE
1
DROP ANY ROLEIMP_FULL_DATABASE
1
DROP ANY SEQUENCEIMP_FULL_DATABASE
1
DROP ANY SNAPSHOTIMP_FULL_DATABASE
1
DROP ANY SYNONYMIMP_FULL_DATABASE
1
DROP ANY TABLEIMP_FULL_DATABASE
1
DROP ANY TRIGGERIMP_FULL_DATABASE
1
DROP ANY TYPEIMP_FULL_DATABASE
1
DROP ANY VIEWIMP_FULL_DATABASE
1
DROP PROFILEIMP_FULL_DATABASE
1
DROP PUBLIC DATABASE LINKIMP_FULL_DATABASE
1
DROP PUBLIC SYNONYMIMP_FULL_DATABASE
1
DROP ROLLBACK SEGMENTIMP_FULL_DATABASE
1
DROP TABLESPACEIMP_FULL_DATABASE
1
DROP USERIMP_FULL_DATABASE
1
EXECUTE ANY PROCEDURE

0
EXECUTE ANY PROCEDUREADX_DEVELOPER
1
EXECUTE ANY PROCEDUREIMP_FULL_DATABASE
1
EXECUTE ANY TYPEIMP_FULL_DATABASE
1
GLOBAL QUERY REWRITEIMP_FULL_DATABASE
1
INSERT ANY TABLE

0
INSERT ANY TABLEIMP_FULL_DATABASE
1
MANAGE ANY QUEUEIMP_FULL_DATABASE
1
SELECT ANY SEQUENCE

0
SELECT ANY SEQUENCEADX_DEVELOPER
1
SELECT ANY TABLE

0
SELECT ANY TABLEADX_DEVELOPER
1
SELECT ANY TABLEIMP_FULL_DATABASE
1
UNLIMITED TABLESPACE

0
UPDATE ANY TABLE

0
UPDATE ANY TABLEIMP_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.

1 comment:

Peter said...

Great Post. Does exactly what I need. Thanks!

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles