DBA FUS‎ > ‎

Check which user has been granted with the 'RESOURCE' role

posted Sep 13, 2010, 6:20 AM by Sachchida Ojha
select grantee
from dba_role_privs r, dba_users u
where granted_role ='RESOURCE' and
   r.grantee = u.username and
   u.account_status not like '%LOCKED%' and
   grantee not in ('SYS','SYSTEM','DBSNMP')
union
select grantee
from dba_role_privs r, dba_users u
where r.grantee = u.username and
  u.account_status not like '%LOCKED%' and
  grantee not in ('SYS','SYSTEM','DBSNMP') and
  granted_role in
    ( select grantee
      from dba_role_privs
      where granted_role = 'RESOURCE' and
        grantee <> 'DBA');

Comments