Oracle FGA 的使用和cleanup audit trails

最新推荐文章于 2023-12-29 08:00:00 发布

cqis22460

最新推荐文章于 2023-12-29 08:00:00 发布

阅读量313

点赞数

文章标签：数据库

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26723566/viewspace-1801362/