Oracle FGA 的使用和cleanup audit trails

Oracle 細粒度審計（FGA）是針對某個OBJECT物件上的操作進行的審計。可以使用它來實現對某個具體表上操作的審計，也可以通過篩選條件進行更細粒度的審計。
FGA 主要是通過 DBMS_FGA這個包來實現審計，DMBS_AUDIT_MGMT來實現audit trails的刪除。
首先要更正一個誤區，看到很多部落格上在寫到FGA時都強調要啟用系統引數audit_trail，而這個引數是靜態的必須重啟才能生效。但是在測試的時候我發現這個引數是否啟用對FGA
都沒有影響。在查閱了官方文件之後知道這個引數和FGA是否生效時沒有關係的，audit_trail是決定是否啟用standard auditing的引數。官檔相關解釋如下：

You do not need to set initialization parameters to enable fine-grained auditing. Instead of setting initialization parameters such as AUDIT_TRAIL, you use the DBMS_FGA PL/SQL package to add and remove fine-grained auditing policies as necessary applying them to the specific operations or objects you want to monitor.

oracle的審計一共有兩種：
1、standard auditing

In standard auditing, you audit SQL statements, privileges, schema objects, and network activity. You configure standard auditing by using the AUDIT SQL statement and NOAUDIT to remove this configuration. You can write the audit records to either the database audit trail or to operating system audit files.

2、FGA（Fine-Grained Auditing）
It provides granular auditing of queries, and INSERT, UPDATE, and DELETE operations。
audit引數決定是否啟用standard auditing，啟用FGA需在需要audit的表上add_policy 並enable。

一、DBMS_FGA有一下幾個procedure構成：
    ADD_POLICY建立審計策略
    DISABLE_POLICY 禁用相應審計策略
    ENABLE_POLICY啟用策略
    DROP_POLICY刪除策略
在這個包的呼叫過程中需要理解的一個引數是audit_trail這個引數共有如下幾個值：
?Setting audit_trail to DBMS_FGA.DB sends the audit trail to the SYS.FGA_LOG$ table in the database and omits SQL Text and SQL Bind.
?Setting audit_trail to DBMS_FGA.DB + DBMS_FGA.EXTENDED sends the audit trail to the SYS.FGA_LOG$ table in the database and includes SQL Text and SQL Bind.
?Setting audit_trail to DBMS_FGA.XML writes the audit trail in XML files sent to the operating system and omits SQL Text and SQL Bind.
?Setting audit_trail to DBMS_FGA.XML + DBMS_FGA.EXTENDED writes the audit trail in XML files sent to the operating system and includes SQL Text and SQL Bind.
FGA的審計資訊預設放在sys.fga_log$中。
二、DBMS_AUDIT_MGMT
    這個package主要用來cleanup audit trail。10g及以前主要是通過手動進清除：
    DELETE FROM SYS.AUD$;
    DELETE FROM SYS.FGA_LOG$;
    OS和XML等檔案通過手動刪除方式進行清理。
    11g以後就可以通過DBMS_AUDIT_MGMT這個package進行清理。這個package包含以下幾個procedure：
   

通過DBMS_AUDIT_MGMT包下的子過程進行手動或定期清理,功能如下

Subprogram	Description
CLEAN_AUDIT_TRAIL Procedure	Deletes audit trail records/files that have been archived
CLEAR_LAST_ARCHIVE_TIMESTAMP Procedure	Clears the timestamp set by the SET_LAST_ARCHIVE_TIMESTAMP Procedure
CREATE_PURGE_JOB Procedure	Creates a purge job for periodically deleting the audit trail records/files
DEINIT_CLEANUP Procedure	Undoes the setup and initialization performed by the INIT_CLEANUP Procedure
DROP_PURGE_JOB Procedure	Drops the purge job created using the CREATE_PURGE_JOB Procedure
INIT_CLEANUP Procedure	Sets up the audit management infrastructure and sets a default cleanup interval for audit trail records/files
IS_CLEANUP_INITIALIZED Function	Checks to see if the INIT_CLEANUP Procedure has been run for an audit trail type
SET_LAST_ARCHIVE_TIMESTAMP Procedure	Sets a timestamp indicating when the audit records/files were last archived
SET_PURGE_JOB_INTERVAL Procedure	Sets the interval at which the CLEAN_AUDIT_TRAIL Procedure is called for the purge job that you specify
SET_PURGE_JOB_STATUS Procedure	Enables or disables the purge job that you specify

 

<!--

管理員在2009年8月13日編輯了該文章文章。

--&gt三、實際應用
-----------------FGA policy的建立：
下面我們模擬對一個測試表TABLE_TEST上的所有delete,update 操作進行審計列A值大於1的都會被審計到
begin
DBMS_FGA.ADD_POLICY(
OBJECT_SCHEMA =>'KYLE',
OBJECT_NAME =>'TABLE_TEST',
POLICY_NAME =>'AUDIT_TABLE_TEST',
AUDIT_CONDITION =>'A<1',
AUDIT_COLUMN =>'A',   
ENABLE =>TRUE,
STATEMENT_TYPES =>'DELETE,UPDATE');
end;
/
檢視策略是否啟用
col object_schema for a20
col object_name for a30
col policy_name for a40
col enabled for a5
set pagesize 300
select object_schema,object_name ,policy_name,enabled from dba_audit_policies where policy_name='AUDIT_TABLE_TEST';


OBJECT_SCHEMA                  OBJECT_NAME           POLICY_NAME                    ENA
------------------------------ -----------------------------        ------------------------------               ---
KYLE                          TABLE_TEST                   AUDIT_TABLE_TEST                  YES
檢視審計結果
col obj$schema for a12
col obj$name for a30
col policyname for a40
col lsqltext for a50
set pagesize 300
select obj$schema,obj$name,policyname,lsqltext from sys.fga_log$ where obj$name='TABLE_TEST' AND obj$schema='KYLE';

----------------------Audit trail 的清理
1、檢查是否進行cleanup的初始化設定
set serverout on
BEGIN
IF DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD) THEN
DBMS_OUTPUT.PUT_LINE('INITIALIZED');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT INITIALIZED');
END IF;
END;
/
如果沒有需要呼叫init_cleanup procedure進行初始化
begin
dbms_audit_mgmt.init_cleanup(
audit_trail_type=>dbms_audit_mgmt.audit_trail_FGA_STD,
default_cleanup_interval=>24);
end;
/
這裡interval的單位為hours，就是每天都執行一次clearup audit trail的操作
init_cleanup（）procedure實際上只是為interval和last_arch_timestamp設定一個預設引數值
2、設定 last_arch_timestamp的值。
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
   last_archive_time => TRUNC(SYSTIMESTAMP)-40)
);
END;
/
設定audit trail 保留時間會45天。超過45天的審計資訊會被刪除。
3、-Create purge job
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
  audit_trail_purge_interval => 24,  
  audit_trail_purge_name => 'CLEANUP_FGA_STD',
  use_last_arch_timestamp => TRUE);
END;
/
這一步實際上是建立了一個job進行定期的執行dbms_audit_mgmt.clean_audit_trail這個procedure。
需要注意的是use_last_arch_timestamp 這個引數的使用，官方文件的解釋為：

Specifies whether the last archived timestamp should be used for deciding on the records that should be deleted.

A value of TRUE indicates that only audit records created before the last archive timestamp should be deleted.

A value of FALSE indicates that all audit records should be deleted.

The default value is TRUE. Oracle recommends using this value, as this helps guard against inadvertent deletion of records

audit_trail_type 這個引數的值如下：
AUDIT_TRAIL_AUD_STD   Standard database audit records in the SYS.AUD$ table
AUDIT_TRAIL_DB_STD     Both standard audit (SYS.AUD$) and FGA audit(SYS.FGA_LOG$) records
AUDIT_TRAIL_FGA_STD    Standard database fine-grained auditing (FGA) records in theSYS.FGA_LOG$ table
AUDIT_TRAIL_FILES    Both operating system (OS) and XML audit trails
AUDIT_TRAIL_OS     Operating system audit trail. This refers to the audit records stored in operating system files.
AUDIT_TRAIL_XML      XML audit trail. This refers to the audit records stored in XML files.    

可以通過如下SQL語句查詢建立的job資訊：
--Check job information
 SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS ;
--Check JOB Scheduler information
 SELECT job_name, next_run_date, state, enabled FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%' ;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26723566/viewspace-1801362/，如需轉載，請註明出處，否則將追究法律責任。