DBA FUS‎ > ‎

Check for privileges with 'ANY' keyword granted

posted Sep 13, 2010, 6:17 AM by Sachchida Ojha
select grantee
from dba_sys_privs s, dba_users u
where s.grantee = u.username and
   u.account_status not like '%LOCKED%' and
   privilege like '%ANY%' and
   grantee <> 'DBA' and
   grantee not in ('SYS','SYSTEM','MDSYS','CTXSYS',
         'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
         'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
         'OSE$HTTP$ADMIN','OUTLN','TRACESVR','REPADMIN',
         'PM','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
         'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
         'WMSYS','XDB','WKPROXY','LBACSYS','OLAPSVR', 'PERFSTAT')
union
select grantee
from dba_role_privs r, dba_users u
where    r.grantee = u.username and
  u.account_status not like '%LOCKED%' and
  granted_role in
    ( select grantee
      from dba_sys_privs
      where privilege like '%ANY%' and
        grantee <> 'DBA'
    ) and
    grantee not in ('SYS','SYSTEM','MDSYS','CTXSYS',
         'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
         'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
         'OSE$HTTP$ADMIN','OUTLN','TRACESVR','REPADMIN',
         'PM','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
         'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
         'WMSYS','XDB','WKPROXY','LBACSYS','OLAPSVR', 'PERFSTAT')
union
select grantee
from dba_role_privs r, dba_users u
where r.grantee = u.username and
  u.account_status not like '%LOCKED%' and
  granted_role <> 'DBA' and
  granted_role in
     ( select grantee
       from dba_role_privs
       where granted_role in
          ( select grantee
            from dba_sys_privs
            where privilege like '%ANY%' and
              grantee <> 'DBA'
          )
     ) and
  grantee not in ('SYS','SYSTEM','MDSYS','CTXSYS',
     'ORDSYS','ORDPLUGINS','AURORA$JIS$UTILITY$',
     'HR','ODM','ODM_MTR','OE','OLAPDBA','OLAPSYS',
     'OSE$HTTP$ADMIN','OUTLN','TRACESVR','REPADMIN',
     'PM','QS','QS_ADM','QS_CB','QS_CBADM', 'DBSNMP',
     'QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SH','WKSYS',
     'WMSYS','XDB','WKPROXY','LBACSYS','OLAPSVR', 'PERFSTAT');
Comments