Database Security and Security Auditing Scripts

List Unix OS users that can startup, shutdown and admin Databases

echo “Users that can startup, shutdown and admin Oracle Databases:” echo grep `grep ^dba /etc/group | cut -d: -f3` /etc/passwd

List security related profile information

conn / as sysdba col profile format a20 col limit format a20 select profile, resource_name, limit from dba_profiles where resource_name like ‘%PASSWORD%’ or resource_name like ‘%LOGIN%’ /

Security related database initialization parameters and password file users

conn / as sysdba tti “Security related initialization parameters:” select name || ‘=’ || value “PARAMTER” from sys.v_$parameter where name in (‘remote_login_passwordfile’, ‘remote_os_authent’, ‘os_authent_prefix’, ‘dblink_encrypt_login’, ‘audit_trail’, ‘transaction_auditing’) / tti “Password file users:” select * from sys.v_$pwfile_users /

Database users with deadly roles assigned to them

conn / as sysdba select grantee, granted_role, admin_option from sys.dba_role_privs where granted_role in (‘DBA’, ‘AQ_ADMINISTRATOR_ROLE’, ‘EXP_FULL_DATABASE’, ‘IMP_FULL_DATABASE’, ‘OEM_MONITOR’) and grantee not in (‘SYS’, ‘SYSTEM’, ‘OUTLN’, ‘AQ_ADMINISTRATOR_ROLE’, ‘DBA’, ‘EXP_FULL_DATABASE’, ‘IMP_FULL_DATABASE’, ‘OEM_MONITOR’, ‘CTXSYS’, ‘DBSNMP’, ‘IFSSYS’, ‘IFSSYS$CM’, ‘MDSYS’, ‘ORDPLUGINS’, ‘ORDSYS’, ‘TIMESERIES_DBA’) /

Database users with deadly system privileges assigned to them

conn / as sysdba select grantee, privilege, admin_option from sys.dba_sys_privs where (privilege like ‘% ANY %’ or privilege in (‘BECOME USER’, ‘UNLIMITED TABLESPACE’) or admin_option = ‘YES’) and grantee not in (‘SYS’, ‘SYSTEM’, ‘OUTLN’, ‘AQ_ADMINISTRATOR_ROLE’, ‘DBA’, ‘EXP_FULL_DATABASE’, ‘IMP_FULL_DATABASE’, ‘OEM_MONITOR’, ‘CTXSYS’, ‘DBSNMP’, ‘IFSSYS’, ‘IFSSYS$CM’, ‘MDSYS’, ‘ORDPLUGINS’, ‘ORDSYS’, ‘TIMESERIES_DBA’) /

List database auditing information

conn / as sysdba tti “Auditing Initialisation Parameters:” select name || ‘=’ || value PARAMETER from sys.v_$parameter where name like ‘%audit%’ / tti “Statement Audits Enabled on this Database” column user_name format a10 column audit_option format a40 select * from sys.dba_stmt_audit_opts / tti “Privilege Audits Enabled on this Database” select * from dba_priv_audit_opts / tti…

Audit User Logins and start traces for selected users (User Login Trigger)

CONNECT / AS SYSDBA DROP TABLE user_login_audit; CREATE TABLE user_login_audit ( login_time DATE, username VARCHAR2(30), machine VARCHAR2(30), command VARCHAR2(128) ); CREATE OR REPLACE TRIGGER user_login_trig AFTER LOGON ON scott.SCHEMA DECLARE v_username sys.v_$session.username%TYPE; v_machine sys.v_$session.machine%TYPE; v_command sys.v_$session.command%TYPE; BEGIN SELECT username, machine, command INTO v_username, v_machine, v_command FROM sys.v_$session WHERE audsid = USERENV(‘SESSIONID’) AND audsid != 0…