Oracle role hierarchy report

The SQL below can be used to provide a list of roles that inherits the CONNECT role, and with the use of START WITH clause, it will also iterate through all the roles beneath those roles, thus providing a hierarchy report.

select level, drp.granted_role, rpad('-',6*level,'-')||drp.grantee as grantee,
case
	when u.username is not null and account_status='OPEN' then 'Ua'
	when u.username is not null and account_status<>'OPEN' then 'Ux'
	when r.role is not null then 'R'
end as grantee_type
from dba_role_privs drp, dba_users u, dba_roles r
where drp.grantee=u.username(+) and drp.grantee=r.role(+)
start with drp.granted_role='CONNECT'
connect by prior drp.grantee=drp.granted_role;

Below is a sample of what may be returned.

LEVEL GRANTED_ROLE GRANTEE GRANTEE_TYPE
1 CONNECT ——ADMINS R
2 ADMINS ————ANDER Ua
2 ADMINS ————MARY Ux
2 ADMINS ————ZOE Ua
1 CONNECT ——EMPLOYEES R
2 EMPLOYEES ————ACCOUNTING R
3 ACCOUNTING ——————DEBBIE Ua
2 EMPLOYEES ————OPERATIONS R
3 OPERATIONS ——————PETER Ua
3 OPERATIONS ——————WILLEM Ua

Leave a Reply

Your email address will not be published.