資訊專欄INFORMATION COLUMN
本方案使用 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那活兒
文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。
轉載請注明本文地址：http://specialneedsforspecialkids.com/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,...
閱讀 1346·2023-01-11 13:20
閱讀 1684·2023-01-11 13:20
閱讀 1132·2023-01-11 13:20
閱讀 1858·2023-01-11 13:20
閱讀 4100·2023-01-11 13:20
閱讀 2704·2023-01-11 13:20
閱讀 1385·2023-01-11 13:20
閱讀 3597·2023-01-11 13:20
