Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, polyvalent.

Asked: February 25, 2020 - 10:00 am UTC

Last updated: February 25, 2020 - 4:26 pm UTC

Version: 12 c

Viewed 1000+ times

You Asked

using DBA_ROLE_PRIVS and DBA_USERS, how can i determine users with DBA role (also hidden role)

and Chris said...

I'm not sure what you mean by "hidden role".

If you're looking for roles granted DBA, and users with these roles, you can find them by querying dba_role_privs. And walking the grant tree to find users that have these higher roles:

create role r;
grant dba to r;
grant r to u identified by u;

select grantee 
from   dba_role_privs
where  exists (
  select * from dba_users u
  where  grantee = u.username
)
start with granted_role = 'DBA'
connect by prior grantee = granted_role;

GRANTEE   
CHRIS      
CHRIS      
U          
SYS        
SYSTEM     


Is this answer out of date? If it is, please let us know via a Comment