本方案使用 Oracle 12C 全新的统一审计功能，针对数据库用户重要敏感操作进行审计，至少记录以下几项信息：
客户端访问时间
客户端 IP 地址
客户端使用的数据库账号
操作名称
操作涉及的数据库对象
执行的 SQL 语句
使用统一审计，须具备以下条件：
▼▼▼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);
审计重要的操作，以下 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;
审计数据泵导出操作。
▼▼▼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 次归档时间戳
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那活儿
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/129942.html
ORACLE数据库开启审计性能影响测试 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; ...
摘要：摘要阿里云数据管理企业版，作为数据管理产品大家族里的新成员，于年月开启公测，今年月底正式发布商业化版本。阿里云企业版是阿里巴巴集团从年开始逐步积累迭代出来的研发全自助数据库服务平台。 摘要： 阿里云数据管理DMS企业版，作为数据管理产品大家族里的新成员，于2017年11月开启公测，今年1月底正式发布商业化版本。 作为业界领先的面向企业的数据库DevOps解决方案，DMS企业版旨在帮助企...
摘要：属于虚拟交换机，其对数据包的处理完全依赖于，并不像传统交换机靠硬件进行流量转发，因此对宿主主机的资源占用也非常严重，极大的降低了宿主主机的性能。原标题：虚拟化及云环境下数据库审计技术探讨随着越来越多的企业用户将传统的业务系统迁移至虚拟化环境或是云服务商提供的云平台，数据的泄露及篡改风险变的越发严峻，针对数据安全的防护以及事后审计追溯也变得越来越困难。究其原因，主要是传统的数据库审计解决方案是...
摘要：阿里云成为唯一入选的中国产品。在阿里云的众多产品中，和共同构成了服务能力的核心。作为大数据能力赋能的重要手段，出现在了等阿里云专有云解决方案中。利用云计算技术，互联网公司得以快速的将自身的大数据处理能力对外赋能。 1.前言 本文基于Now Tech: Cloud Data Warehouse, Q1 2018 (Published: by Noel Yuhanna, March 13,...
阅读 1229·2023-01-11 13:20
阅读 1536·2023-01-11 13:20
阅读 991·2023-01-11 13:20
阅读 1644·2023-01-11 13:20
阅读 3952·2023-01-11 13:20
阅读 2446·2023-01-11 13:20
阅读 1284·2023-01-11 13:20
阅读 3438·2023-01-11 13:20
