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.
1 comment:
Great Post. Does exactly what I need. Thanks!
Post a Comment