在未开启审计的系统中,一张核心业务数据表,需要记录下非业务用户删除数据的操作。即创建一个记录其他用户删除表数据的trigger。
下面是简单测试:
t_test为测试数据表,obj_after_delete为触发器,业务用户为awen,如果是非业务用户删除表数据,trigger就将信息记录到del_rec表中。
可以扩展记录更多信息,另外,trigger对性能可能会有影响,须测试后再部署生成环境。
下面是简单测试:
t_test为测试数据表,obj_after_delete为触发器,业务用户为awen,如果是非业务用户删除表数据,trigger就将信息记录到del_rec表中。
SQL> desc del_rec
Name Null? Type
------------------------------------------------- -------- ----------------
USERNAME VARCHAR2(20)
ID NUMBER(5)
DEL_TIME DATE
SQL> desc t_test
Name Null? Type
------------------------------------------------- -------- ----------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
CREATE OR REPLACE TRIGGER obj_before_delete
AFTER DELETE ON awen.t_test
FOR EACH ROW
BEGIN
if(user!='AWEN') then
INSERT INTO awen.del_rec (id,USERNAME,DEL_TIME) VALUES (:old.object_id,user,sysdate);
end if;
END;
Name Null? Type
------------------------------------------------- -------- ----------------
USERNAME VARCHAR2(20)
ID NUMBER(5)
DEL_TIME DATE
SQL> desc t_test
Name Null? Type
------------------------------------------------- -------- ----------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
CREATE OR REPLACE TRIGGER obj_before_delete
AFTER DELETE ON awen.t_test
FOR EACH ROW
BEGIN
if(user!='AWEN') then
INSERT INTO awen.del_rec (id,USERNAME,DEL_TIME) VALUES (:old.object_id,user,sysdate);
end if;
END;
可以扩展记录更多信息,另外,trigger对性能可能会有影响,须测试后再部署生成环境。