Search the Site

Sponsors

bottom corner

Oracle role hierarchy report

This article provides one way to report the hierarchy of roles in an Oracle environment.

This page is filed under keyword(s): oracle.

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.

LEVELGRANTED_ROLEGRANTEEGRANTEE_TYPE
1CONNECT------ADMINSR
2ADMINS------------ANDERUa
2ADMINS------------MARYUx
2ADMINS------------ZOEUa
1CONNECT------EMPLOYEESR
2EMPLOYEES------------ACCOUNTINGR
3ACCOUNTING------------------DEBBIEUa
2EMPLOYEES------------OPERATIONSR
3OPERATIONS------------------PETERUa
3OPERATIONS------------------WILLEMUa

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 11 Mar 2015

bottom corner