如何启用fga功能,fine-grained auditing oracle
发布网友
发布时间:2022-04-10 20:28
我来回答
共1个回答
热心网友
时间:2022-04-10 21:57
Implement fine-grained auditing
从Oracle9i开始,就可以使用DBMS_FGA对指定的表的SELECT语句进行审计,但是在9i中只能对select语句进行审计,在10g中可以实现对DML的审计功能。
1、在审计策略生效之前,必须对表进行分析,因为只有在CBO模式,DBMS_FGA才能正确的工作
分析该表,让其使用CBO优化模式
SQL> analyze table hr.employees compute statistics;
下面的语句是删除掉统计信息:
analyze 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 Procere Creates an audit policy using the supplied predicate as the audit condition
DISABLE_POLICY Procere Disables an audit policy
DROP_POLICY Procere Drops an audit policy
ENABLE_POLICY Procere 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'); --查看记录来源