本方案使用 Oracle 12C 全新的统一审计功能，针对数据库用户重要敏感操作进行审计，至少记录以下几项信息：
客户端访问时间
客户端 IP 地址
客户端使用的数据库账号
操作名称
操作涉及的数据库对象
执行的 SQL 语句
使用统一审计，须具备以下条件：
Oracle 12C 及以上版本。 混合审计模式（默认）或纯粹的统一审计模式。 混合审计模式下初始化参数 audit_trail 设置为 DB 或 DB, EXTENDEND。
创建专用表空间
▼▼▼SQL> CREATE TABLESPACE audit_tbs01 DATAFILE '+DATA' SIZE 30G AUTOEXTEND OFF; SQL> ALTER TABLESPACE audit_tbs01 ADD DATAFILE '+DATA' SIZE 30G AUTOEXTEND OFF;
调整分区间隔
调整审计数据内部表分区间隔为 1 天。
▼▼▼BEGIN dbms_audit_mgmt.alter_partition_interval(interval_number => 1, interval_frequency => 'DAY'); END;
设置审计记录位置
▼▼▼BEGIN dbms_audit_mgmt.set_audit_trail_location(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_UNIFIED, audit_trail_location_value => 'AUDIT_TBS01'); END;
查看已经启用的审计策略
▼▼▼col policy_name FOR a25 col entity_name FOR a25 col entity_type FOR a15 col success FOR a10 col failure FOR a10 SELECT * FROM audit_unified_enabled_policies;
查看默认启用的审计策略
▼▼▼col policy_name FOR a25 col audit_condition FOR a15 col condition_eval_opt FOR a20 col audit_option FOR a30 col audit_option_type FOR a20 col object_schema FOR a15 col object_name FOR a35 col object_type FOR a15 SELECT * FROM audit_unified_policies WHERE policy_name IN('ORA_SECURECONFIG','ORA_LOGON_FAILURES');
策略 AUD_STANDARD_ACTION
审计重要的操作，以下 SQL 用于构造创建策略的语句。
▼▼▼SELECT 'CREATE AUDIT POLICY aud_standard_action ACTIONS ' || listagg(NAME, ',') || ';' FROM auditable_system_actions WHERE component = 'Standard' AND NAME IN ('ALTER DATABASE DICTIONARY', 'ALTER DATABASE LINK', 'ALTER FUNCTION', 'ALTER INDEX', 'ALTER PACKAGE', 'ALTER PACKAGE BODY', 'ALTER SEQUENCE', 'ALTER TABLE', 'ALTER USER', 'CHANGE PASSWORD', 'CREATE DATABASE LINK', 'CREATE DIRECTORY', 'CREATE TABLE', 'CREATE USER', 'DROP FUNCTION', 'DROP INDEX', 'DROP PACKAGE', 'DROP PACKAGE BODY', 'DROP PROCEDURE', 'DROP ROLE', 'DROP SEQUENCE', 'DROP TABLE', 'DROP USER', 'TRUNCATE TABLE');
创建策略。
▼▼▼SQL> CREATE AUDIT POLICY aud_standard_action ACTIONS CREATE TABLE,DROP INDEX,ALTER INDEX,DROP TABLE,ALTER SEQUENCE,ALTER TABLE,DROP SEQUENCE,CREATE DATABASE LINK,ALTER USER,CREATE USER,DROP USER,DROP ROLE,DROP PROCEDURE,TRUNCATE TABLE,ALTER FUNCTION,DROP FUNCTION,ALTER PACKAGE,DROP PACKAGE,ALTER PACKAGE BODY,DROP PACKAGE BODY,CREATE DIRECTORY,CHANGE PASSWORD,ALTER DATABASE LINK,ALTER DATABASE DICTIONARY;
策略 AUD_COMPONENT_DATAPUMP
审计数据泵导出操作。
▼▼▼SQL> CREATE AUDIT POLICY aud_component_datapump ACTIONS COMPONENT=DATAPUMP EXPORT;
启用自定义审计策略
▼▼▼SQL> AUDIT POLICY aud_standard_action; SQL> AUDIT POLICY aud_component_datapump;
保留策略
保留最近 90 天的审计记录
每天 05:30 更新 1 次归档时间戳
每天 06:00 执行 1 次清理操作
清理作业
PUSH_AUDIT_TSTAMP_UNIFIED
每天 05:30 执行 1 次，将审计记录最后归档时间更新为 90 天之前。
▼▼▼BEGIN dbms_scheduler.create_job(job_name => 'PUSH_AUDIT_TSTAMP_UNIFIED', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN AUDSYS.dbms_audit_mgmt.set_last_archive_timestamp(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,last_archive_time=>sys_extract_utc(systimestamp-90));END;', number_of_arguments => 0, start_date => SYSDATE, repeat_interval => 'freq=DAILY;interval=1;byhour=5;byminute=30;bysecond=0', enabled => TRUE, auto_drop => FALSE); END;
PURGE_AUDIT_UNIFIED
每天 06:00 执行 1 次，根据最后归档时间执行清理操作。
▼▼▼BEGIN dbms_scheduler.create_job(job_name => 'PURGE_AUDIT_UNIFIED', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN dbms_audit_mgmt.clean_audit_trail(audit_trail_type => dbms_audit_mgmt.audit_trail_unified,use_last_arch_timestamp => TRUE);END;', number_of_arguments => 0, start_date => SYSDATE, repeat_interval => 'freq=DAILY;interval=1;byhour=6;byminute=0;bysecond=0', enabled => TRUE, auto_drop => FALSE); END;
创建公共策略
▼▼▼cdb$root> CREATE AUDIT POLICY aud_standard_action ACTIONS CREATE TABLE, DROP TABLE CONTAINER=ALL;
启用公共策略
策略将在 cdb$root 和所有 PDB 中生效。
▼▼▼cdb$root> AUDIT POLICY aud_standard_action;
PUSH_AUDIT_TSTAMP_UNIFIED
每天 05:30 执行 1 次，将 cdb$root 和 所有 PDB 审计记录最后归档时间更新为 90 天之前。
以下语句在 cdb$root 执行。
▼▼▼BEGIN dbms_scheduler.create_job(job_name => 'PUSH_AUDIT_TSTAMP_UNIFIED', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN AUDSYS.dbms_audit_mgmt.set_last_archive_timestamp(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,last_archive_time=>sys_extract_utc(systimestamp-90),container=>dbms_audit_mgmt.container_all);END;', number_of_arguments => 0, start_date => SYSDATE, repeat_interval => 'freq=DAILY;interval=1;byhour=5;byminute=30;bysecond=0', enabled => TRUE, auto_drop => FALSE); END;
每天 06:00 执行 1 次，根据最后归档时间对 cdb$root 和所有 PDB 执行清理操作。
以下语句在 cdb$root 执行。
▼▼▼BEGIN dbms_scheduler.create_job(job_name => 'PURGE_AUDIT_UNIFIED', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN dbms_audit_mgmt.clean_audit_trail(audit_trail_type => dbms_audit_mgmt.audit_trail_unified,use_last_arch_timestamp => TRUE,container=>dbms_audit_mgmt.container_all);END;', number_of_arguments => 0, start_date => SYSDATE, repeat_interval => 'freq=DAILY;interval=1;byhour=6;byminute=0;bysecond=0', enabled => TRUE, auto_drop => FALSE); END;
查看所有审计策略
▼▼▼col policy_name FOR a30 SELECT DISTINCT policy_name FROM audit_unified_policies;
查询已经启用的审计策略
▼▼▼col policy_name FOR a25 col entity_name FOR a25 col entity_type FOR a15 col success FOR a10 col failure FOR a10 SELECT * FROM audit_unified_enabled_policies;
查询审计记录
以下 SQL 查询最近 100 条审计记录。
▼▼▼col audit_type FOR a11 col TIMESTAMP FOR a25 col ipaddr FOR a16 col policies FOR a20 col osuser FOR a8 col dbuser FOR a8 col currusr FOR a8 col actname FOR a20 col objschema FOR a10 col objname FOR a30 col sysprivused FOR a25 col syspriv FOR a15 col userhost FOR a15 col terminal FOR a8 col client FOR a40 col retcode FOR 99999 SET line 255 SET pagesize 50; SELECT to_char(u.event_timestamp, 'DD/MON/RR-hh24:mi:ss.ff') TIMESTAMP, regexp_substr(u.authentication_type, '\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}') AS ipaddr, u.os_username osuser, u.dbusername dbuser, u.action_name actname, u.return_code retcode, u.object_schema objschema, u.object_name objname, u.system_privilege_used sysprivused, u.userhost, u.client_program_name client, u.terminal FROM unified_audit_trail u ORDER BY 1 DESC FETCH FIRST 100 rows ONLY;
查询内部表使用情况
▼▼▼col segment_name FOR a35 col partition_name FOR a20 col segment_type FOR a20 col tablespace_name FOR a15 col mbytes FOR 999,990.00 SELECT segment_name, segment_type, partition_name, bytes / 1024 / 1024 AS MBytes, tablespace_name FROM dba_segments WHERE owner = 'AUDSYS';
查询内部表分区情况
▼▼▼col table_name FOR a20 col partitioning_type FOR a15 col DEFAULT_TBS FOR a15 col INTERVAL FOR a35 SELECT table_name, partitioning_type, def_tablespace_name, INTERVAL FROM dba_part_tables WHERE owner = 'AUDSYS'; col table_name FOR a15 col partition_name FOR a15 col high_value FOR a35 col partition_position FOR 999999 col num_rows FOR 999,999,999,999 col last_analyzed FOR a20 SELECT table_name, partition_name, high_value, partition_position, num_rows, last_analyzed FROM dba_tab_partitions WHERE table_owner = 'AUDSYS';
查询清理作业
▼▼▼col owner FOR a12 col job_name FOR a25 col run_count FOR 999,999 col start_date FOR a20 col last_start_date FOR a20 col next_run_date FOR a20 col repeat_interval FOR a55 col job_style FOR a10 col creator FOR a10 col job_type FOR a15 col last_run_duration FOR 990.000 SELECT j.owner, j.job_name, j.run_count, to_char(j.start_date, 'DD-MON-RR hh24:mi:ss') start_date, to_char(j.last_start_date, 'DD-MON-RR hh24:mi:ss') last_start_date, to_char(j.next_run_date, 'DD-MON-RR hh24:mi:ss') next_run_date, j.repeat_interval, j.job_style, j.job_creator creator, j.job_type, j.enabled, j.state FROM dba_scheduler_jobs j WHERE job_name IN ('PUSH_AUDIT_TSTAMP_UNIFIED','PURGE_AUDIT_UNIFIED');
查询清理作业运行情况
▼▼▼col log_date FOR a40 col job_name FOR a25 col status FOR a15 col actual_start_date FOR a40 col inst_id FOR 99 SELECT log_date, owner, job_name, status, error#, actual_start_date, instance_id inst_id FROM dba_scheduler_job_run_details WHERE job_name IN ('PUSH_AUDIT_TSTAMP_UNIFIED','PURGE_AUDIT_UNIFIED') ORDER BY 1 DESC;
更多精彩干货分享
点击下方名片关注
IT那活儿
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
