Script:收集数据库安全风险评估信息

以下脚本可以用于收集数据库安全风险评估信息:

REM list database vulnerability assessment info

set escape on;
set linesize 140 ;
spool db_vulnerability_assessment.log

Select role
  from dba_roles r
 where role not in ('CONNECT',
                    'RESOURCE',
                    'DBA',
                    'SELECT_CATALOG_ROLE',
                    'EXECUTE_CATALOG_ROLE',
                    'DELETE_CATALOG_ROLE',
                    'EXP_FULL_DATABASE',
                    'WM_ADMIN_ROLE',
                    'IMP_FULL_DATABASE',
                    'RECOVERY_CATALOG_OWNER',
                    'AQ_ADMINISTRATOR_ROLE',
                    'AQ_USER_ROLE',
                    'GLOBAL_AQ_USER_ROLE',
                    'OEM_MONITOR',
                    'HS_ADMIN_ROLE')
   and not exists
 (Select 1 from dba_role_privs p where p.granted_role = r.role)
/

select tp.grantee, tp.table_name
  from dba_tab_privs tp, dba_users u
 where tp.owner = 'SYS'
   and (tp.table_name like 'V_$%' or tp.table_name like 'G_V$')
   and tp.grantee = u.username
   and u.username not in ('SYS',
                          'SYSTEM',
                          'SYSMAN',
                          'EXFSYS',
                          'WMSYS',
                          'OLAPSYS',
                          'OUTLN',
                          'DBSNMP',
                          'ORDSYS',
                          'ORDPLUGINS',
                          'MDSYS',
                          'DMSYS',
                          'CTXSYS',
                          'AURORA$ORB$UNAUTHENTICATED',
                          'XDB',
                          'FLOWS_030000',
                          'FLOWS_FILES')
 order by 1, 2
/


select *
  from (select 'Hidden User in DBA_USERS' ddview, name
          from sys.user$
         where type# = 1
        minus
        select 'Hidden User in DBA_USERS', username from SYS.dba_users) q1
union all
select *
  from (select 'Hidden User in ALL_USERS', name
          from sys.user$
         where type# = 1
        minus
        select 'Hidden User in ALL_USERS', username from SYS.all_users) q2
/



select grantee, granted_role
  from dba_role_privs
 where grantee in (select role from dba_roles)
 order by grantee, granted_role
/

select grantee, privilege, admin_option
  from dba_sys_privs sp, dba_users u
 where sp.GRANTEE = u.username
   and grantee not in ('SYS', 'SYSTEM')
   and privilege in (select name
                       from sys.system_privilege_map
                      where 0 = 1
                         or name like '%ANY%'
                         or name like '%DATABASE%'
                         or name like '%DIRECTORY%'
                         or name like '%LIBRARY%'
                         or name like '%LINK%'
                         or name like '%PROFILE%'
                         or name like '%RESTRICTED%'
                         or name like 'SYS%'
                         or name like '%SYSTEM%'
                         or name like '%TABLESPACE%'
                         or name like '%USER%')
 order by 1
/

select role,
       (select count(*)
          from dba_role_privs rp
         where rp.granted_role = r.role) GRANT_COUNT
  from dba_roles r
 where r.role in ('DBA', 'CONNECT', 'RESOURCE')
 order by 1
/

select grantee, granted_role, admin_option
  from dba_role_privs rp, dba_users u
 where rp.grantee = u.username
   and grantee not in ('SYS', 'SYSTEM')
   and granted_role in (select role
                          from dba_roles
                         where 0 = 1
                            or role like '%CATALOG%'
                            or role like '%DATABASE%'
                            or role like '%DBA%')
 order by 1
/

select distinct profile, resource_name, actual_limit 
from (select P.Profile, p.resource_Name,
             decode(p.limit, 'UNLIMITED', '9999999999999999999', 
                   'NULL', null, to_number(p.limit)) limit,
             limit actual_limit
      from ( select profile, resource_name, 
                    decode(resource_name,  'IDLE_TIME', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                           'FAILED_LOGIN_ATTEMPTS', decode(limit, 'DEFAULT', '10', limit),
                                           'PASSWORD_LIFE_TIME', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                           'PASSWORD_REUSE_MAX', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                           'PASSWORD_REUSE_TIME', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                           'PASSWORD_GRACE_TIME', decode(limit, 'DEFAULT', 'UNLIMITED', limit),
                                'PASSWORD_VERIFY_FUNCTION', decode(limit, 'NULL', '0', null, 0, 'DEFAULT', 0, 1), limit) limit
              from   dba_profiles
              where resource_name in ('IDLE_TIME', 'FAILED_LOGIN_ATTEMPTS',
                                      'PASSWORD_LIFE_TIME', 'PASSWORD_REUSE_MAX',
                                      'PASSWORD_REUSE_TIME','PASSWORD_GRACE_TIME',
                                      'PASSWORD_VERIFY_FUNCTION')) p ) 
where 1=0 
or    (RESOURCE_NAME = 'IDLE_TIME' AND LIMIT > 60)
or    (RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS' AND LIMIT > 3)
or    (RESOURCE_NAME = 'PASSWORD_LIFE_TIME' AND LIMIT > 90)
or    (RESOURCE_NAME = 'PASSWORD_REUSE_MAX' AND LIMIT > 20)
or    (RESOURCE_NAME = 'PASSWORD_REUSE_TIME' AND LIMIT > 180)
or    (RESOURCE_NAME = 'PASSWORD_GRACE_TIME' AND LIMIT > 3)
or    (RESOURCE_NAME = 'PASSWORD_VERIFY_FUNCTION' AND LIMIT = 0)
order by 1,2
/

Select s.owner, s.synonym_name, s.table_owner, s.table_name
  from sys.DBA_synonyms s
 where not exists (Select 'x'
          from sys.DBA_objects o
         where o.owner = s.table_owner
           and o.object_name = s.table_name)
   and db_link is null
   and s.owner <> 'PUBLIC'
 order by 1
/

Select distinct profile
  from dba_profiles
minus
Select distinct profile from dba_users
/

select table_name
  from dba_tab_privs
 where owner = 'SYS'
   and grantee = 'PUBLIC'
   and table_name in ('UTL_SMTP',
                      'UTL_TCP',
                      'UTL_HTTP',
                      'UTL_FILE',
                      'DBMS_RANDOM',
                      'DBMS_LOB',
                      'DBMS_SYS_SQL',
                      'DBMS_BACKUP_RESTORE',
                      'EMD_SYSTEM',
                      'DBMS_NAMESPACE',
                      'DBMS_SCHEDULER')
 order by 1
/ 
 

select username, password from dba_users order by 1
/


select tp.grantee, tp.table_name, tp.privilege
  from dba_tab_privs tp, dba_users u, dba_tables t
 where tp.owner = 'SYS'
   and tp.grantee = u.username
   and tp.owner = t.owner
   and tp.table_name = t.table_name
   and u.username not in ('SYS',
                          'SYSTEM',
                          'SYSMAN',
                          'EXFSYS',
                          'WMSYS',
                          'OLAPSYS',
                          'OUTLN',
                          'DBSNMP',
                          'ORDSYS',
                          'ORDPLUGINS',
                          'MDSYS',
                          'CTXSYS',
                          'AURORA$ORB$UNAUTHENTICATED',
                          'XDB',
                          'FLOWS_030000',
                          'FLOWS_FILES')
 order by 1, 2, 3
/
 
 select sp.grantee, sp.privilege
   from dba_sys_privs sp, dba_users u
  where sp.admin_option = 'YES'
    and u.username = sp.grantee
    and u.username not in ('SYS',
                           'SYSTEM',
                           'SYSMAN',
                           'EXFSYS',
                           'WMSYS',
                           'OLAPSYS',
                           'OUTLN',
                           'DBSNMP',
                           'ORDSYS',
                           'ORDPLUGINS',
                           'MDSYS',
                           'CTXSYS',
                           'AURORA$ORB$UNAUTHENTICATED',
                           'XDB',
                           'FLOWS_030000',
                           'FLOWS_FILES')
  order by 1, 2
/
  
  select p.grantee, p.owner, p.table_name, p.privilege
    from dba_tab_privs p, dba_users u
   where p.grantable = 'YES'
     and u.USERNAME = p.grantee
     and u.username not in ('SYS',
                            'SYSTEM',
                            'SYSMAN',
                            'EXFSYS',
                            'WMSYS',
                            'OLAPSYS',
                            'OUTLN',
                            'DBSNMP',
                            'ORDSYS',
                            'ORDPLUGINS',
                            'MDSYS',
                            'CTXSYS',
                            'AURORA$ORB$UNAUTHENTICATED',
                            'XDB',
                            'FLOWS_030000',
                            'FLOWS_FILES')
   order by 1, 2, 3, 4
/

select username
  from dba_users
 where account_status!='EXPIRED \& LOCKED'
 order by 1
/

Select s.synonym_name, s.table_owner, s.table_name
  from sys.DBA_synonyms s
 where not exists (Select 'x'
          from sys.DBA_objects o
         where o.owner = s.table_owner
           and o.object_name = s.table_name)
   and db_link is null
   and s.owner = 'PUBLIC'
 order by 1
/

select r.grantee, r.granted_role
  from dba_role_privs r, dba_users u
 where r.admin_option = 'YES'
   and u.username = r.grantee
   and u.username not in ('SYS',
                          'SYSTEM',
                          'SYSMAN',
                          'EXFSYS',
                          'WMSYS',
                          'OLAPSYS',
                          'OUTLN',
                          'DBSNMP',
                          'ORDSYS',
                          'ORDPLUGINS',
                          'MDSYS',
                          'CTXSYS',
                          'AURORA$ORB$UNAUTHENTICATED',
                          'XDB',
                          'FLOWS_030000',
                          'FLOWS_FILES')
 order by 1, 2
/


select username
  from dba_users
 where password = 'EXTERNAL'
 order by username
/