版本信息：
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
数据准备：
QUENTIN@PROD>drop table t;
create table t (x number,y number,z number);
insert into t values (1,1,1);
insert into t values (2,null,2);
insert into t values (null,3,3);
insert into t values (null,null,4);
commit;
Table dropped.
QUENTIN@PROD>
Table created.
QUENTIN@PROD>
1 row created.
QUENTIN@PROD>
1 row created.
QUENTIN@PROD>
1 row created.
QUENTIN@PROD>
1 row created.
QUENTIN@PROD>
Commit complete.
删除原先的policy：
QUENTIN@PROD>exec dbms_fga.drop_policy('QUENTIN','T','Q_POLICY');
PL/SQL procedure successfully completed.
新增审计策略。要求：x和y任意一列的值不为空的前提下，对x,y,z三列进行增、删、改、查的操作进行审计。此处使用的audit_condition为'x is not null or y is not null'
QUENTIN@PROD>exec DBMS_FGA.ADD_POLICY ( object_schema => 'QUENTIN', object_name => 'T', policy_name => 'q_policy', audit_condition => 'x is not null or y is not null', audit_column => 'x,y,z', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types => 'INSERT, UPDATE,DELETE,SELECT', audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED, audit_column_opts => DBMS_FGA.ANY_COLUMNS);
PL/SQL procedure successfully completed.
查询测试：
QUENTIN@PROD>select x,y,z from t where z=1;
select x,y,z from t where z=2;
select x,y,z from t where z=3;
select x,y,z from t where z=4;
X Y Z
---------- ---------- ----------
1 1 1
QUENTIN@PROD>
X Y Z
---------- ---------- ----------
2 2
QUENTIN@PROD>
X Y Z
---------- ---------- ----------
3 3
QUENTIN@PROD>
X Y Z
---------- ---------- ----------
4
QUENTIN@PROD>select ENTRYID,STATEMENT_TYPE,SQL_TEXT from dba_fga_audit_trail order by ENTRYID;
ENTRYID STATEME SQL_TEXT
---------- ------- ------------------------------
1 SELECT select x,y,z from t where z=1
2 SELECT select x,y,z from t where z=2
3 SELECT select x,y,z from t where z=3
可以得到正确的审计记录。
测试DELETE：报错ORA-28138
QUENTIN@PROD>delete from t where z=1;
delete from t where z=1
*
ERROR at line 1:
ORA-28138: Error in Policy Predicate
无法获得DELETE操作的审计记录
QUENTIN@PROD>select ENTRYID,STATEMENT_TYPE,SQL_TEXT from dba_fga_audit_trail order by ENTRYID;
ENTRYID STATEME SQL_TEXT
---------- ------- ------------------------------
1 SELECT select x,y,z from t where z=1
2 SELECT select x,y,z from t where z=2
3 SELECT select x,y,z from t where z=3
测试INSERT：报错ORA-28138
QUENTIN@PROD>insert into t values (1,1,1);
insert into t values (1,1,1)
*
ERROR at line 1:
ORA-28138: Error in Policy Predicate
无法获得INSERT操作的审计记录
QUENTIN@PROD>select ENTRYID,STATEMENT_TYPE,SQL_TEXT from dba_fga_audit_trail order by ENTRYID;
ENTRYID STATEME SQL_TEXT
---------- ------- ------------------------------
1 SELECT select x,y,z from t where z=1
2 SELECT select x,y,z from t where z=2
3 SELECT select x,y,z from t where z=3
测试UPDATE：报错ORA-28138
QUENTIN@PROD>update t set x=null,y=null where z=1
*
ERROR at line 1:
ORA-28138: Error in Policy Predicate
无法获得UPDATE操作的审计记录
QUENTIN@PROD>select ENTRYID,STATEMENT_TYPE,SQL_TEXT from dba_fga_audit_trail order by ENTRYID;
ENTRYID STATEME SQL_TEXT
---------- ------- ------------------------------
1 SELECT select x,y,z from t where z=1
2 SELECT select x,y,z from t where z=2
3 SELECT select x,y,z from t where z=3
重建审计策略，使用的audit_condition为'coalesce(x,y) is not null'
QUENTIN@PROD>exec dbms_fga.drop_policy('QUENTIN','T','Q_POLICY');
PL/SQL procedure successfully completed.
QUENTIN@PROD>exec DBMS_FGA.ADD_POLICY ( object_schema => 'QUENTIN', object_name => 'T', policy_name => 'q_policy', audit_condition => 'coalesce(x,y) is not null', audit_column => 'x,y,z', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types => 'INSERT, UPDATE,DELETE,SELECT', audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED, audit_column_opts => DBMS_FGA.ANY_COLUMNS);
PL/SQL procedure successfully completed.
测试SELECT，可以正常获取审计记录
QUENTIN@PROD>select x,y,z from t where z=1;
select x,y,z from t where z=2;
select x,y,z from t where z=3;
select x,y,z from t where z=4;
select ENTRYID,STATEMENT_TYPE,SQL_TEXT from dba_fga_audit_trail order by ENTRYID;
X Y Z
---------- ---------- ----------
1 1 1
QUENTIN@PROD>
X Y Z
---------- ---------- ----------
2 2
QUENTIN@PROD>
X Y Z
---------- ---------- ----------
3 3
QUENTIN@PROD>
X Y Z
---------- ---------- ----------
4
QUENTIN@PROD>
ENTRYID STATEME SQL_TEXT
---------- ------- ------------------------------
1 SELECT select x,y,z from t where z=1
2 SELECT select x,y,z from t where z=2
3 SELECT select x,y,z from t where z=3
4 SELECT select x,y,z from t where z=1
5 SELECT select x,y,z from t where z=2
6 SELECT select x,y,z from t where z=3
6 rows selected.
测试DELETE，可以正常获取审计记录
QUENTIN@PROD>delete from t where z=1;
delete from t where z=2;
delete from t where z=3;
delete from t where z=4;
select ENTRYID,STATEMENT_TYPE,SQL_TEXT from dba_fga_audit_trail order by ENTRYID;
1 row deleted.
QUENTIN@PROD>
1 row deleted.
QUENTIN@PROD>
1 row deleted.
QUENTIN@PROD>
1 row deleted.
QUENTIN@PROD>
ENTRYID STATEME SQL_TEXT
---------- ------- ------------------------------
1 SELECT select x,y,z from t where z=1
2 SELECT select x,y,z from t where z=2
3 SELECT select x,y,z from t where z=3
4 SELECT select x,y,z from t where z=1
5 SELECT select x,y,z from t where z=2
6 SELECT select x,y,z from t where z=3
7 DELETE delete from t where z=1
8 DELETE delete from t where z=2
9 DELETE delete from t where z=3
9 rows selected.
QUENTIN@PROD>rollback;
Rollback complete.
就算事务回滚，审计记录一样被保存下来。
QUENTIN@PROD>select ENTRYID,STATEMENT_TYPE,SQL_TEXT from dba_fga_audit_trail order by ENTRYID;
ENTRYID STATEME SQL_TEXT
---------- ------- ------------------------------
1 SELECT select x,y,z from t where z=1
2 SELECT select x,y,z from t where z=2
3 SELECT select x,y,z from t where z=3
4 SELECT select x,y,z from t where z=1
5 SELECT select x,y,z from t where z=2
6 SELECT select x,y,z from t where z=3
7 DELETE delete from t where z=1
8 DELETE delete from t where z=2
9 DELETE delete from t where z=3
9 rows selected.
QUENTIN@PROD>select * from t;
X Y Z
---------- ---------- ----------
1 1 1
2 2
3 3
4
测试INSERT，可以正常获取审计记录
QUENTIN@PROD>insert into t values (1,1,1);
insert into t values (2,null,2);
insert into t values (null,3,3);
insert into t values (null,null,4);
commit;
select ENTRYID,STATEMENT_TYPE,SQL_TEXT from dba_fga_audit_trail order by ENTRYID;
1 row created.
QUENTIN@PROD>
1 row created.
QUENTIN@PROD>
1 row created.
QUENTIN@PROD>
1 row created.
QUENTIN@PROD>
Commit complete.
QUENTIN@PROD>/
ENTRYID STATEME SQL_TEXT
---------- ------- --------------------------------------------------
1 SELECT select x,y,z from t where z=1
2 SELECT select x,y,z from t where z=2
3 SELECT select x,y,z from t where z=3
4 SELECT select x,y,z from t where z=1
5 SELECT select x,y,z from t where z=2
6 SELECT select x,y,z from t where z=3
7 DELETE delete from t where z=1
8 DELETE delete from t where z=2
9 DELETE delete from t where z=3
10 SELECT select * from t
11 INSERT insert into t values (1,1,1)
12 INSERT insert into t values (2,null,2)
13 INSERT insert into t values (null,3,3)
测试UPDATE，可以正常获取审计记录
QUENTIN@PROD>update t set x=null,y=null where z=1;
update t set y=2 where z=2;
update t set x=3 where z=3;
update t set x=4,y=4 where z=4;
commit;
2 rows updated.
QUENTIN@PROD>
2 rows updated.
QUENTIN@PROD>
2 rows updated.
QUENTIN@PROD>
2 rows updated.
QUENTIN@PROD>
Commit complete.
QUENTIN@PROD>select ENTRYID,STATEMENT_TYPE,SQL_TEXT from dba_fga_audit_trail order by ENTRYID;
ENTRYID STATEME SQL_TEXT
---------- ------- --------------------------------------------------
1 SELECT select x,y,z from t where z=1
2 SELECT select x,y,z from t where z=2
3 SELECT select x,y,z from t where z=3
4 SELECT select x,y,z from t where z=1
5 SELECT select x,y,z from t where z=2
6 SELECT select x,y,z from t where z=3
7 DELETE delete from t where z=1
8 DELETE delete from t where z=2
9 DELETE delete from t where z=3
10 SELECT select * from t
11 INSERT insert into t values (1,1,1)
12 INSERT insert into t values (2,null,2)
13 INSERT insert into t values (null,3,3)
14 SELECT select * from t
15 UPDATE update t set x=null,y=null where z=1
16 UPDATE update t set y=2 where z=2
17 UPDATE update t set x=3 where z=3
18 UPDATE update t set x=4,y=4 where z=4
18 rows selected.
关于函数COALESCE的表述：
COALESCE (expr1, expr2)的功能相当于以下语句：
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
关于审计策略中audit_condition的表述：
The audit_condition must be a boolean expression that can be evaluated using the values in the row being inserted, updated, or deleted. This condition can be NULL (or omitted), which is interpreted as TRUE, but it cannot contain the following elements:
Subqueries or sequences
Any direct use of SYSDATE, UID, USER or USERENV functions. However, a user-defined function and other SQL functions can use these functions to return the desired information.
Any use of the pseudo columns LEVEL, PRIOR, or ROWNUM.
Specifying an audit condition of "1=1" to force auditing of all specified statements ("statement_types") affecting the specified column ("audit_column") is no longer needed to achieve this purpose. NULL will cause audit even if no rows were processed, so that all actions on a table with this policy are audited.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25857612/viewspace-766081/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
下一篇：
没有了~
请登录后发表评论
登录
全部评论
