在MIS系统中,系统审计功能是很重要的一部分,审计的一部分就是记录数据修改日志。记录数据修改日志有很多种实现方案,有通过后台程序实现的,在修改程序中增加日志代码,也有通过数据库实现的,使用触发器来记录修改日志。本方案采用第二种方案。这种方案的优点是无论你通过什么方式修改数据,都会记录下来,极少需要修改代码;缺点是需要应用程序配合,以便能知道是哪个应用系统的账号做的修改。
本方案的核心是,通过一个配置表,配置哪些表需要记录日志、修改哪些列的时候记录日志。然后根据配置信息,为每个表生成用于记录日志的触发器。
一、日志配置表
日志配置表用于配置:
1、需要记录日志的表(table_name)
2、修改哪些列时,需要记录日志(log_cols),列名用逗号分隔。
3、该表的id列(id_col)。用于在日志中唯一标识一行数据。
4、记录日志时需要额外记录的一些关键列,列名逗号分隔(key_cols)。
下面是个例子:
二、日志表
日志表用来记录对数据的修改。
本方案中,使用数据库序列生成日志表的id。使用前,要先创建一个序列SEQ_DATA_LOG:
1 -- Create sequence
2 create sequence SEQ_DATA_LOG
3 minvalue 1
4 maxvalue 9999999999999999999999999999
5 start with 31
6 increment by 1
7 cache 10;
三、触发器
触发器是方案的核心。
触发器里可以使用:new.列名和:old.列名来得到修改的列的旧值和新值。但是列名不能用变量,换句话说,:new不能用在动态sql中,因此就不能读取配置表,来动态取每个需要记录日志的列的旧值和新值。一句话说,你不能使用如下的语句:
vsql := 'select :new.name from dual';
excute immediate vssql into vName ;
只能在触发器中这样写
select :new.name into vName from dual;
在写触发器的时候就要知道要记录哪些列的日志,要写死了。
没有办法,职能采取一种折中的方式:通过配置信息,自动创建触发器。
1、创建触发器的存储过程
1 create or replace procedure proc_create_log_trigger(
2 p$table_name in varchar2, p$tri_script out varchar2
3 )
4 is
5 v$id_col varchar2(30);
6 v$logid_exp varchar2(100);
7 v$log_cols varchar2(1000);
8 v$key_cols varchar2(1000);
9 v$tri_sql varchar2(4000);
10 v$idcol_exp_ins nvarchar2(1000);
11 v$idcol_exp nvarchar2(1000);
12 v$kv1_exp nvarchar2(1000);
13 v$kv2_exp nvarchar2(1000);
14 v$oth_kv_exp nvarchar2(1000);
15 v$kv1_exp_ins nvarchar2(1000);
16 v$kv2_exp_ins nvarchar2(1000);
17 v$oth_kv_exp_ins nvarchar2(1000);
18 v$opuser_exp nvarchar2(1000);
19 v$count int ;
20 begin
21 v$id_col := null ;
22 select cfg.id_col, cfg.log_cols, cfg.key_cols into v$id_col,v$log_cols,v$key_cols
23 from phs_data_log_cfg cfg
24 where cfg.table_name = p$table_name;
25 if v$id_col is null then
26 return ;
27 end if ;
28
29 v$tri_sql := 'create or replace trigger tr_' ||p$table_name || '_log ' ||
30 'before delete or insert or update '||
31 'on ' || p$table_name || ' '||
32 'for each row '||
33 'declare '||
34 'v$opuser varchar2(100);'||
35 'begin ';
36
37 v$logid_exp :='seq_data_log.nextval';
38 v$idcol_exp_ins := ':new.'||v$id_col;
39 v$idcol_exp := ':old.'||v$id_col;
40 v$count := 1;
41 v$kv1_exp := ' ';
42 v$kv2_exp := ' ';
43 v$oth_kv_exp :=' ';
44 v$kv1_exp_ins := ' ';
45 v$kv2_exp_ins := ' ';
46 v$oth_kv_exp_ins :=' ';
47 v$opuser_exp := 'v$opuser';
48 v$tri_sql := v$tri_sql || 'select sys_context(''hcm_context'', ''hcm_user'') into '||v$opuser_exp||' from dual;';
49 for r in(SELECT REGEXP_SUBSTR (replace(v$key_cols,' ',''), '[^,]+', 1,rownum) as v from dual connect by rownum<=LENGTH(v$key_cols) - LENGTH(regexp_replace(v$key_cols, ',', ''))+1) loop
50 if v$count = 1 then
51 v$kv1_exp := ':old.'||r.v ;
52 v$kv1_exp_ins := ':new.'||r.v ;
53 end if ;
54 if v$count = 2 then
55 v$kv2_exp := ':old.'||r.v ;
56 v$kv2_exp_ins := ':new.'||r.v ;
57 end if ;
58 if v$count > 2 then
59 v$oth_kv_exp := v$oth_kv_exp ||''''||r.v||':''||:old.'||r.v||'||'';''';
60 v$oth_kv_exp_ins := v$oth_kv_exp_ins ||''''||r.v||':''||:new.'||r.v||'||'';''';
61 end if ;
62 v$count := v$count + 1;
63 end loop;
64
65 if v$kv1_exp = ' ' then v$kv1_exp := 'NULL'; end if;
66 if v$kv2_exp = ' ' then v$kv2_exp := 'NULL'; end if;
67 if v$oth_kv_exp = ' ' then v$oth_kv_exp := 'NULL'; end if;
68 if v$kv1_exp_ins = ' ' then v$kv1_exp_ins := 'NULL'; end if;
69 if v$kv2_exp_ins = ' ' then v$kv2_exp_ins := 'NULL'; end if;
70 if v$oth_kv_exp_ins = ' ' then v$oth_kv_exp_ins := 'NULL'; end if;
71
72 v$tri_sql := v$tri_sql ||
73 'if inserting then '||
74 'insert into phs_data_upd_log( op_code,id, data_id, table_name, kv1, kv2, oth_kv,op_user, op_time )'||
75 ' values( ''insert'','||v$logid_exp||','||v$idcol_exp_ins||','''||p$table_name||''','||v$kv1_exp_ins||','||v$kv2_exp_ins ||','||v$oth_kv_exp_ins||','||v$opuser_exp||', sysdate);'||
76 'elsif deleting then '||
77 'insert into phs_data_upd_log( op_code, id, data_id, table_name, kv1, kv2, oth_kv,op_user, op_time )'||
78 ' values( ''delete'','||v$logid_exp||','||v$idcol_exp||','''||p$table_name||''','||v$kv1_exp||','||v$kv2_exp ||','||v$oth_kv_exp||','||v$opuser_exp||', sysdate);';
79
80 v$tri_sql := v$tri_sql ||'else ';
81 for r in(SELECT REGEXP_SUBSTR (replace(v$log_cols,' ',''), '[^,]+', 1,rownum) as v from dual connect by rownum<=LENGTH(v$log_cols) - LENGTH(regexp_replace(v$log_cols, ',', ''))+1) loop
82 v$tri_sql := v$tri_sql ||
83 'if updating('''||r.v||''') and (:new.'||r.v||' is not null or :old.'||r.v||' is not null) and :new.'||r.v||'<>:old.'||r.v||' then '||
84 'insert into phs_data_upd_log( op_code, id, data_id, table_name, col, old_value, new_value, kv1, kv2, oth_kv,op_user, op_time )'||
85 ' values( ''update'','||v$logid_exp||','||v$idcol_exp||','''||p$table_name||''','''||r.v||''',:old.'||r.v||',:new.'||r.v||','||v$kv1_exp||','||v$kv2_exp ||','||v$oth_kv_exp||','||v$opuser_exp||', sysdate);'||
86 'end if;';
87 end loop;
88
89 v$tri_sql := v$tri_sql || 'end if;';
90 v$tri_sql := v$tri_sql || 'end;';
91 p$tri_script := v$tri_sql;
92 end ;
这个存储过程的核心就是,读取某个表的日志配置数据,形成创建用于记录日志的触发器的脚本。
执行以下语句,就可以创建触发器:
declare
tri_script varchar2(4000);
begin
-- Call the procedure
proc_create_log_trigger( p$table_name =>'phd_pe_sjxmlr', p$trig_script =>tri_script );
execute immediate tri_script ;
end;
下面是自动创建的一个触发器的脚本:
1 create or replace trigger tr_phd_pe_sjxmlr_log
2 before delete or insert or update on phd_pe_sjxmlr
3 for each row
4 declare
5 v$opuser varchar2(100);
6 begin
7 select sys_context('hcm_context', 'hcm_user') into v$opuser from dual;
8 if inserting then
9 insert into phs_data_upd_log
10 (op_code,
11 id,
12 data_id,
13 table_name,
14 kv1,
15 kv2,
16 oth_kv,
17 op_user,
18 op_time)
19 values
20 ('insert',
21 seq_data_log.nextval,
22 :new.h_guid,
23 'phd_pe_sjxmlr',
24 :new.col_id,
25 :new.h_qjbh,
26 'h_khdxbh:' || :new.h_khdxbh || ';',
27 v$opuser,
28 sysdate);
29 elsif deleting then
30 insert into phs_data_upd_log
31 (op_code,
32 id,
33 data_id,
34 table_name,
35 kv1,
36 kv2,
37 oth_kv,
38 op_user,
39 op_time)
40 values
41 ('delete',
42 seq_data_log.nextval,
43 :old.h_guid,
44 'phd_pe_sjxmlr',
45 :old.col_id,
46 :old.h_qjbh,
47 'h_khdxbh:' || :old.h_khdxbh || ';',
48 v$opuser,
49 sysdate);
50 else
51 if updating('col_id') and
52 (:new.col_id is not null or :old.col_id is not null) and
53 :new.col_id <> :old.col_id then
54 insert into phs_data_upd_log
55 (op_code,
56 id,
57 data_id,
58 table_name,
59 col,
60 old_value,
61 new_value,
62 kv1,
63 kv2,
64 oth_kv,
65 op_user,
66 op_time)
67 values
68 ('update',
69 seq_data_log.nextval,
70 :old.h_guid,
71 'phd_pe_sjxmlr',
72 'col_id',
73 :old.col_id,
74 :new.col_id,
75 :old.col_id,
76 :old.h_qjbh,
77 'h_khdxbh:' || :old.h_khdxbh || ';',
78 v$opuser,
79 sysdate);
80 end if;
81 if updating('h_value') and
82 (:new.h_value is not null or :old.h_value is not null) and
83 :new.h_value <> :old.h_value then
84 insert into phs_data_upd_log
85 (op_code,
86 id,
87 data_id,
88 table_name,
89 col,
90 old_value,
91 new_value,
92 kv1,
93 kv2,
94 oth_kv,
95 op_user,
96 op_time)
97 values
98 ('update',
99 seq_data_log.nextval,
100 :old.h_guid,
101 'phd_pe_sjxmlr',
102 'h_value',
103 :old.h_value,
104 :new.h_value,
105 :old.col_id,
106 :old.h_qjbh,
107 'h_khdxbh:' || :old.h_khdxbh || ';',
108 v$opuser,
109 sysdate);
110 end if;
111 if updating('h_qjbh') and
112 (:new.h_qjbh is not null or :old.h_qjbh is not null) and
113 :new.h_qjbh <> :old.h_qjbh then
114 insert into phs_data_upd_log
115 (op_code,
116 id,
117 data_id,
118 table_name,
119 col,
120 old_value,
121 new_value,
122 kv1,
123 kv2,
124 oth_kv,
125 op_user,
126 op_time)
127 values
128 ('update',
129 seq_data_log.nextval,
130 :old.h_guid,
131 'phd_pe_sjxmlr',
132 'h_qjbh',
133 :old.h_qjbh,
134 :new.h_qjbh,
135 :old.col_id,
136 :old.h_qjbh,
137 'h_khdxbh:' || :old.h_khdxbh || ';',
138 v$opuser,
139 sysdate);
140 end if;
141 if updating('h_khdxbh') and
142 (:new.h_khdxbh is not null or :old.h_khdxbh is not null) and
143 :new.h_khdxbh <> :old.h_khdxbh then
144 insert into phs_data_upd_log
145 (op_code,
146 id,
147 data_id,
148 table_name,
149 col,
150 old_value,
151 new_value,
152 kv1,
153 kv2,
154 oth_kv,
155 op_user,
156 op_time)
157 values
158 ('update',
159 seq_data_log.nextval,
160 :old.h_guid,
161 'phd_pe_sjxmlr',
162 'h_khdxbh',
163 :old.h_khdxbh,
164 :new.h_khdxbh,
165 :old.col_id,
166 :old.h_qjbh,
167 'h_khdxbh:' || :old.h_khdxbh || ';',
168 v$opuser,
169 sysdate);
170 end if;
171 end if;
172 end;
2、在触发器中得到mis系统的账号
在日志中,需要记录操作用户,这个用户是业务系统的用户而不是数库的账号。在触发器中如何能得到应用系统的用户呢。
可以参见另一篇文章:利用oracle context 向 oracle 传值
上面的触发器使用如下代码得到业务系统的用户账号:
1 select sys_context('hcm_context', 'hcm_user') into v$opuser from dual;
前提是,业务系统在执行修改代码时,要先执行以下语句,将账号写入到数据库session中:
1 proc_hcm_context( 'hcm_context', 'hcm_user', '9999');
proc_hcm_context 是个存储过程。
四、日志的例子
我们执行以下sql语句,然后查看日志记录的数据:
1 update phd_pe_sjxmlr set h_value=0.000001 where h_guid = '37E10088B5C0459C10EAF12F34962D17';
查看日志:
1 SELECT * FROM phs_data_upd_log ;
五、总结
以上给出了一个通过触发器记录数据修改日志的一种通用解决方案。
核心是,可配置,并且根据配置信息,自动创建触发器,通过触发器记录修改日志。业务系统的账号通过oracle session context 从业务系统传到oracle服务器,在触发器拿到它。
这种方案有点是对原有系统改动比较小,很容易扩展,使用面很广。