Posts
查询触发器
提问1:有基于SELECT的触发器吗?
回答:没有
提问2:如何使一个查询触发更新操作
回答:如下
两个表:
conn a/a drop table t1; drop table t2; create table t1 (a number, b number); create table t2 (a number, b number); truncate table t1; truncate table t2; insert into t1 values(1,1); insert into t1 values(2,2); commit;
表t2等于
insert into t2 select a, sum(b)-avg(b) from t1 group by a; commit; select * from t1; select * from t2;
SQL> select * from t1;
A B
---------- ----------
1 1
2 2
SQL> select * from t2;
A B
---------- ----------
1 0
2 0
SQL>
t1随时更新, 当查询t2时,t2的字段b根据t1更新为最新的值
方法1:
建立视图, 使用自治事务(autonomous transaction)函数更新表和返回值. 程序不直接查询表, 而改查询这个视图
使用自治事务是因为在一个查询里不能有DML操作(insert,update,delete)
create or replace function f_t2(p_a in t1.a%type) return t1.b%type is pragma autonomous_transaction; v_b t1.b%type; begin select sum(b)-avg(b) into v_b from t1 where a = p_a; update t2 set b = v_b where a = p_a; commit; return v_b; end; / create or replace view v2 as select a, f_t2(a) as b from t2;
测试
select * from v2 where a=1; select * from t2;
SQL> select * from v2 where a=1;
A B
---------- ----------
1 0
SQL> select * from t2;
A B
---------- ----------
1 0
2 0
SQL>
t1插入几条数据, 查询v2, 查看结果
insert into t1 values (1,2); insert into t1 values (2,2); commit; select * from t1; select * from t2; select * from v2 where a=1; select * from t2; select * from v2 where a=2; select * from t2;
SQL> insert into t1 values (1,2);
1 row created.
SQL> insert into t1 values (2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
A B
---------- ----------
1 1
2 2
1 2
2 2
SQL> select * from t2;
A B
---------- ----------
1 0
2 0
SQL> select * from v2 where a=1;
A B
---------- ----------
1 1.5
SQL> select * from t2;
A B
---------- ----------
1 1.5
2 0
SQL> select * from v2 where a=2;
A B
---------- ----------
2 2
SQL> select * from t2;
A B
---------- ----------
1 1.5
2 2
SQL>
查询视图v2触发函数运行, 导致t2被更新查询了哪条记录就更新哪条, 一次只更新一条
清除测试数据
conn a/a drop function f_t2; drop view v2; drop table t2; drop table t1;
方法2:
使用细粒度审计(Fine-Grained Auditing)调用事件处理模块(handler module)更新表
处理函数也是作为自治事务运行的
set pages 9999 line 140 set serveroutput on size unlimited conn a/a drop table t1; drop table t2; create table t1 (a number, b number); create table t2 (a number, b number); truncate table t1; truncate table t2; insert into t1 values(1,1); insert into t1 values(2,2); commit;
建立处理函数
conn / as sysdba
create or replace procedure p_update_t2 (
p_object_schema VARCHAR2,
p_object_name VARCHAR2,
p_policy_name VARCHAR2
) is
begin
merge into a.t2
using (select a, sum(b)-avg(b) b from a.t1 group by a) t3
on (t2.a = t3.a)
when matched then
update set t2.b=t3.b
when not matched then
insert values (t3.a, t3.b);
end;
/
添加审计策略
conn / as sysdba
begin
dbms_fga.drop_policy (
object_schema => 'A'
,object_name => 'T2'
,policy_name => 'UPDATE_T2'
);
end;
/
begin
dbms_fga.add_policy (
object_schema => 'A'
,object_name => 'T2'
,policy_name => 'UPDATE_T2'
,audit_column => 'B'
,handler_schema => 'SYS'
,handler_module => 'P_UPDATE_T2'
);
end;
/
truncate table fga_log$;
select * from dba_audit_policies;
SQL> select * from dba_audit_policies; OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ------------------------------ ------------------------------ ------------------------------ POLICY_TEXT -------------------------------------------------------------------------------------------------------------------------------------------- POLICY_COLUMN PF_SCHEMA PF_PACKAGE PF_FUNCTION ENA SEL INS UPD ------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- --- DEL AUDIT_TRAIL POLICY_COLU --- ------------ ----------- A T2 UPDATE_T2 B SYS P_UPDATE_T2 YES YES NO NO NO DB+EXTENDED ANY_COLUMNS SQL>
handler_schema指定的用户好像必须要和审计策略的拥有者一致, 否则查询时报错:
SQL> select * from t2 where a=1; select * from t2 where a=1 * ERROR at line 1: ORA-06550: line 1, column 9: PLS-00302: component 'P_UPDATE_T2' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored SQL>
查询表t2
conn a/a select * from t1; select * from t2 where a=1;
SQL> select * from t1;
A B
---------- ----------
1 1
2 2
SQL> select * from t2 where a=1;
no rows selected
SQL>
这时t2啥也没查出来其实已经更新了, 也记录了审计日志
conn / as sysdba select * from a.t2; col db_user for a10 col os_user for a10 col object_schema for a10 col object_name for a10 col sql_text for a50 select to_char(timestamp,'yyyymmddhh24miss'), db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;
SQL> select * from a.t2;
A B
---------- ----------
1 0
2 0
SQL>...
SQL> select to_char(timestamp,'yyyymmddhh24miss'), db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;
TO_CHAR(TIMEST DB_USER OS_USER OBJECT_SCH OBJECT_NAM SQL_TEXT
-------------- ---------- ---------- ---------- ---------- --------------------------------------------------
20090507131441 A oracle A T2 select * from t2 where a=1
SQL>
(使用sys用户查询了表a.t2, 却没有触发审计, 好像可能是因为初始化参数AUDIT_SYS_OPERATIONS设置为false.)查询到的不是最新的,是不及时的
conn a/a insert into t1 values (1,2); insert into t1 values (2,2); commit; select * from t1; select * from t2; conn / as sysdba select * from a.t2; select to_char(timestamp,'yyyymmddhh24miss'), db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;
SQL> select * from t1;
A B
---------- ----------
1 1
2 2
1 2
2 2
SQL> select * from t2 where a=1;
A B
---------- ----------
1 0
2 0
SQL>
...
SQL> select * from a.t2;
A B
---------- ----------
1 1.5
2 2
SQL> select to_char(timestamp,'yyyymmddhh24miss'), db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;
TO_CHAR(TIMEST DB_USER OS_USER OBJECT_SCH OBJECT_NAM SQL_TEXT
-------------- ---------- ---------- ---------- ---------- --------------------------------------------------
20090507131441 A oracle A T2 select * from t2 where a=1
20090507131602 A oracle A T2 select * from t2
SQL>
有2条记录满足审计条件, 只触发了一次这也会产生不少审计日志, 需要定时清除
清除测试数据
conn / as sysdba
begin
dbms_fga.drop_policy (
object_schema => 'A'
,object_name => 'T2'
,policy_name => 'UPDATE_T2'
);
end;
/
drop procedure p_update_t2;
drop table a.t2;
drop table a.t1;
truncate table fga_log$;
外部链接:
Fine-Grained Auditing
DBMS_FGA
9i/9.2: Fine Grained Auditing
10g: Fine Grained Auditing
How to cleanup the log table FGA_LOG$ ?
用truncate或delete均可
How To Exclude Users Being Audited Through the DBMS_FGA Package
如何不审计某些用户: 在审计条件中使用SYS_CONTEXT判断当前用户, 比如audit_condition => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') <> ''TST'' '
How to Avoid Common Flaws and Errors Using Fine Grained Auditing
9i下,如果表没有被分析或没有使用CBO, 会得到意外的审计结果(比预期的要多)
-fin-
No comments:
Post a Comment