Implement fine-grained auditing
Implement fine-grained auditing
从Oracle9i开始，就可以使用DBMS_FGA对指定的表的SELECT语句进行审计，但是在9i中只能对select语句进行审计，在10g中可以实现对DML的审计功能。
1、在审计策略生效之前，必须对表进行分析，因为只有在CBO模式，DBMS_FGA才能正确的工作
分析该表，让其使用CBO优化模式
SQL> 易做图yze table hr.employees compute statistics;
下面的语句是删除掉统计信息：
易做图yze table table_name delete statistics;
2、指定audit_condition可以设定监控条件(例如select某部分记录)
3、可以指定audit_column来审计专门的字段
4、statement_types包括“SELECT,UPDATE,INSERT.DELETE”四种，如需多种以','分开
5,被审计的只能是非SYS用户，SYS用户没有办法审计
Summary of DBMS_FGA Subprograms
Subprogram Description
ADD_POLICY Procedure Creates an audit policy using the supplied predicate as the audit condition
DISABLE_POLICY Procedure Disables an audit policy
DROP_POLICY Procedure Drops an audit policy
ENABLE_POLICY Procedure Enables an audit policy
salary&commission_pct(下面表示两个同时查询的时候，就审计，否则不审计)
begin
dbms_fga.add_policy(
OBJECT_SCHEMA=>'HR',
OBJECT_NAME=>'EMPLOYEES',
POLICY_NAME=>'policy1',
AUDIT_CONDITION =>'department_id=60',
AUDIT_COLUMN=>'salary,commission_pct',
HANDLER_SCHEMA=>null,
HANDLER_MODULE=>null,
ENABLE=>true,
STATEMENT_TYPES=>'select',
audit_column_opts=>DBMS_FGA.ALL_COLUMNS
);
end;
/
conn hr/hr
SQL>select 'salary,commission_pct' from employees （两个字段同时查询才审计）
begin
dbms_fga.drop_policy(
OBJECT_SCHEMA=>'HR',
OBJECT_NAME=>'EMPLOYEES',
POLICY_NAME=>'policy1');
end;
/
salary|commission_pct (下面表示只要查询两个中的任何一个的时候就审计)
begin
dbms_fga.add_policy(
OBJECT_SCHEMA=>'HR',
OBJECT_NAME=>'EMPLOYEES',
POLICY_NAME=>'policy2',
AUDIT_CONDITION =>'department_id=60',
AUDIT_COLUMN=>'salary,commission_pct',
HANDLER_SCHEMA=>null,
HANDLER_MODULE=>null,
ENABLE=>true,
STATEMENT_TYPES=>'select',
audit_column_opts=>DBMS_FGA.ANY_COLUMNS
);
end;
/
conn hr/hr
（下面三种情况查询都审计）
SQL>select 'salary,commission_pct' from employees
SQL>select 'salary from employees
SQL>select commission_pct' from employees
如果不写audit_column_opts，则相当于ANY_COLUMNS
begin
dbms_fga.add_policy(
OBJECT_SCHEMA=>'HR',
OBJECT_NAME=>'EMPLOYEES',
POLICY_NAME=>'ABC',
AUDIT_CONDITION =>null,
AUDIT_COLUMN=>'salary,commission_pct',
HANDLER_SCHEMA=>null,
HANDLER_MODULE=>null,
ENABLE=>true,
STATEMENT_TYPES=>'select'
);
end;
/
SYS验证下FGA是否生效：
SQL> select count(*) from fga_log$;
最后看一下，如何查看审计的结果：
select * from dba_audit_policies; --所有policy的列表
select db_user,timestamp,sql_text,sql_bind from dba_fga_audit_trail; --审计结果
SELECT text FROM dba_Views where view_name=upper('DBA_FGA_AUDIT_TRAIL'); --查看记录来源
