mysql风险评估_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

/