Monday, August 18, 2014

Find what are ADMIN and non ADMIN users

SELECT 'Predefined Administrative User Accounts', count(*)
  FROM dba_users
 WHERE username in
      ('ANONYMOUS', 'CTXSYS',   'DBSNMP', 'EXFSYS', 'LBACSYS',
       'MDSYS',     'MGMT_VIEW','OLAPSYS','OWBSYS', 'ORDPLUGINS',
       'ORDSYS',    'OUTLN',    'SI_INFORMTN_SCHEMA','SYS',
       'SYSMAN',    'SYSTEM',   'TSMSYS', 'WK_TEST', 'WKSYS',
       'WKPROXY',   'WMSYS',    'XDB')
UNION
SELECT 'Default Sample Schema User Accounts', count(*)
  FROM dba_users
 WHERE username in ('BI','HR','OE','PM','IX','SH')
UNION
SELECT 'Predefined Non-Administrative User Accounts', count(*)
  FROM dba_users
 WHERE username in
      ('APEX_PUBLIC_USER','DIP',   'FLOWS_30000','FLOWS_FILES','MDDATA',
       'ORACLE_OCM',      'PUBLIC','SPATIAL_CSW_ADMIN_USER',
       'SPATIAL_WFS_ADMIN_USR',    'XS$NULL');




SELECT 'Administrative User    : ', username, account_status, lock_date, expiry_date
  FROM dba_users
 WHERE username in
      ('ANONYMOUS', 'CTXSYS',   'DBSNMP', 'EXFSYS', 'LBACSYS',
       'MDSYS',     'MGMT_VIEW','OLAPSYS','OWBSYS', 'ORDPLUGINS',
       'ORDSYS',    'OUTLN',    'SI_INFORMTN_SCHEMA','SYS',
       'SYSMAN',    'SYSTEM',   'TSMSYS', 'WK_TEST', 'WKSYS',
       'WKPROXY',   'WMSYS',    'XDB')
UNION
SELECT 'Sample Schema User     : ', username, account_status, lock_date, expiry_date
  FROM dba_users
 WHERE username in ('BI','HR','OE','PM','IX','SH')
union
SELECT 'Non-Administrative User: ', username, account_status, lock_date, expiry_date
  FROM dba_users
 WHERE username in
      ('APEX_PUBLIC_USER','DIP',   'FLOWS_30000','FLOWS_FILES','MDDATA',
       'ORACLE_OCM',      'PUBLIC','SPATIAL_CSW_ADMIN_USER',
       'SPATIAL_WFS_ADMIN_USR',    'XS$NULL');






No comments:

Post a Comment