代码之家 › 专栏 › 技术社区 › Ganesh

使用触发器的Oracle审计表

audit-trail auditing triggers plsql oracle

Ganesh · 技术社区 · 7 年前

我需要在Oracle 11g中创建一个触发器来审核表。我有一个16列的表需要审计。

对于表中的每一个新插入,我需要在审计表中为插入的每一列添加一个条目,即在这种情况下,审计表中将插入16行。

对于每次更新,假设我更新第一列和第二列,那么它将在audit中创建两条记录,其中包含旧值和新值。审计表的结构为:

 id        
 mod_col_name 
 OLD VALUE 
 NEW VALUE  
 upd_time 
 mod_user_id

我的方法:

create or replace trigger my_trigger
after update or insert on temp12
for each row
declare

  TYPE tab_col_nt IS table of varchar2(30);

  v_tab_col_nt tab_col_nt;

begin

    v_tab_col_nt := tab_col_nt('id','name','salary'); --in example i have given only 3 column name

    for r in v_tab_col_nt.first..v_tab_col_nt.last

    loop

        if updating(v_tab_col_nt(r)) then

            insert into audit_table values (
                id_seq.nextval, v_tab_col_nt(r), :old.v_tab_col_nt(r),
                :new.v_tab_col_nt(r), sysdate, user
             ); --here :old & :new syntex is not working
        end if;

        if inserting then
            insert into audit_table values (
               id_seq.nextval, v_tab_col_nt(r), null, 
               :new.v_tab_col_nt(r), sysdate, user);
        end if;

    end loop;
end;

我的担忧:

如何跟踪用户登录GUI的用户id(dot net是前端)。

1 回复 | 直到 5 年前

Jair Hernandez 7 年前

原因 :old.v_tab_col_nt(r) 和 :new.v_tab_col_nt(r) :old :new 仅引用表中受影响列中的旧值和新值(duh!…),而不引用触发器内声明的用户定义类型。

您实际寻找的价值是: :old.<name of column1> :new.<name of column1> .

create or replace trigger my_trigger
after insert or update on temp12
for each row
referencing old as old new as new
begin

    /*When-Insert block. 1 record for each column in audit*/
    if (INSERTING) then -- 
        insert into audit_table values (id_seq.nextval, '<name of column1>', :old.<name of column1>, :new.<name of column1>, sysdate, user);
        insert into audit_table values (id_seq.nextval, '<name of column2>', :old.<name of column2>, :new.<name of column2>, sysdate, user);
        insert into audit_table values (id_seq.nextval, '<name of column3>', :old.<name of column3>, :new.<name of column3>, sysdate, user);
         .
         . --(same for every column)
         .
        insert into audit_table values (id_seq.nextval, '<name of column16>', :old.<name of column16>, :new.<name of column16>, sysdate, user);
    end if;
    /*end of When-Insert block*/

    /*When-Update block. A new record in audit just for the updated column(s) */
    if (UPDATING ( '<name of column1>' )) then --col 1
        insert into audit_table values (id_seq.nextval, '<name of column1>', :old.<name of column1>, :new.<name of column1>, sysdate, user);
    end if;
    if (UPDATING ( '<name of column2>' )) then --col 2
        insert into audit_table values (id_seq.nextval, '<name of column2>', :old.<name of column2>, :new.<name of column2>, sysdate, user);
    end if;
    if (UPDATING ( '<name of column3>' )) then --col 3
        insert into audit_table values (id_seq.nextval, '<name of column3>', :old.<name of column3>, :new.<name of column3>, sysdate, user);
    end if;
     . 
     . --(same for every column)
     .
    if (UPDATING ( '<name of column16>' )) then --col 16
        insert into audit_table values (id_seq.nextval, '<name of column16>', :old.<name of column16>, :new.<name of column16>, sysdate, user);
    end if;
    /*end of When-Update block*/

end;

如果整个应用程序在DB级别实现了所有用户, 执行的每个PL/SQL块或DDL中的保留字“USER”将
他们自己,通过创建自定义用户控件,那么该值必须通过参数传递(在命名PL/SQL块的情况下)或在每个表(至少每个表)中包含(非空)列您希望监视用户活动),以便每个 INSERT 声明是强制发送该信息,您可以使用 :new.<name of user monitor column> 在…内你的触发器代码,或者你可以使用

IF (:new.< name of user monitor column > is null) then raise_application_error(-20001, 'User must be specified'). END IF;