Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, polyvalent.

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

Answered by: Chris Saxon - Last updated: February 25, 2020 - 4:26 pm UTC

Category: SQL - Version: 12 c

Viewed 100+ times

You Asked

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

and we 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