基于DBA及普通用户使用触发器对用户的表审计

长平狐 发布于 2013/09/17 15:11
阅读 68
收藏 0

一、DBA用户使用触发器审计普通用户所属表的某个字段——可以实现。

注意一些输出为了节约篇幅,已经删除。比如查询后的提示---1 row created.等等
会话一:在DBA用户下创建触发器,来审计 scott下的EMP表sal,empno列的DML操作。

SCOTT@ bys001>conn bys/bys
BYS@ bys001>create table emp_audit(oldempno number(4),newempno number(4),oldsal number(7,2),newsal number(7,2),luser varchar2(100),sdate date);
创建触发器:
create or replace trigger trg_emp_audit
after  insert  or delete or update  of  sal,empno on scott.emp for each row
declare
pragma autonomous_transaction;
begin
insert into  emp_audit values (old.empno,:new.empno,old.sal,:new.sal,user,sysdate);
commit;
 end;
/

Trigger created.
BYS@ bys001>select * from emp_audit;
no rows selected
会话二:新建SCOTT用户的会话,更新一条记录
SCOTT@ bys001>update emp set sal=9999,empno=9998 where empno=7369;
1 row updated.
会话一:查询审计信息已经生成--和会话1 的操作是否提交无关
BYS@ bys001>col luser for a10
BYS@ bys001>select * from emp_audit;
  OLDEMPNO   NEWEMPNO     OLDSAL     NEWSAL LUSER      SDATE
---------- ---------- ---------- ---------- ---------- -------------------
      7369       9998        800       9999 SCOTT      2013/07/25 20:21:21


删除触发器及相关的表 --为文件下一步实验
BYS@ bys001>drop trigger trg_emp_audit;
Trigger dropped.
BYS@ bys001>drop table emp_audit purge;
Table dropped.
####################################################################

二、普通用户使用触发器审计自己所属表的某个字段——可以实现。

SCOTT@ bys001>create table emp_audit(oldempno number(4),newempno number(4),oldsal number(7,2),newsal number(7,2),luser varchar2(100),sdate date);

create or replace trigger trg_emp_audit
after  insert  or delete or update  of  sal,empno on scott.emp for each row
declare
pragma autonomous_transaction;
begin
insert into  emp_audit values (old.empno,:new.empno,old.sal,:new.sal,user,sysdate);
commit;
end;
/

Trigger created.

SCOTT@ bys001>col luser for a10
SCOTT@ bys001>select * from emp_audit;
no rows selected

只更新sal字段
SCOTT@ bys001>update emp set sal=9999 where empno=7369;
1 row updated.

查询审计表里的信息
SCOTT@ bys001>select * from emp_audit;
  OLDEMPNO   NEWEMPNO     OLDSAL     NEWSAL LUSER      SDATE
---------- ---------- ---------- ---------- ---------- -------------------
      7369       7369        800       9999 SCOTT      2013/07/25 20:05:48
#####################################################################

三、普通用户使用触发器审计普通用户所属表的某个字段——无法实现

会话一:给TEST用户在EMP上的查询权限
20:37:13 SCOTT@ bys001>grant select on emp to test;
Grant succeeded.
会话二:

BYS@ bys001>conn test/test
Connected.
TEST@ bys001>create table emp_audit(oldempno number(4),newempno number(4),oldsal number(7,2),newsal number(7,2),luser varchar2(100),sdate date);
Table created.

create or replace trigger trg_emp_audit
after  insert  or delete or update  of  sal,empno on scott.emp for each row
declare
pragma autonomous_transaction;
begin
insert into  emp_audit values (:old.empno,:new.empno,:old.sal,:new.sal,user,sysdate);
commit;
end;
/
after  insert  or delete or update  of  sal,empno on scott.emp for each row
                                                           *
ERROR at line 2:
ORA-00942: table or view does not exist

会话一:给TEST用户在EMP上的增删改权限,查询权限上一步已经给过。

20:37:26 SCOTT@ bys001>grant insert,update,delete on emp to test;
Grant succeeded.
会话二:
create or replace trigger trg_emp_audit
after  insert  or delete or update  of  sal,empno on scott.emp for each row
declare
pragma autonomous_transaction;
begin
insert into  emp_audit values (:old.empno,:new.empno,:old.sal,:new.sal,user,sysdate);
commit;
end;
/
after  insert  or delete or update  of  sal,empno on scott.emp for each row
                                                           *
ERROR at line 2:
ORA-01031: insufficient privileges


原文链接:http://blog.csdn.net/q947817003/article/details/11212055
加载中
返回顶部
顶部