CRM某些表加入审计

 

--更新参数文件，设置Audit等级
alter system set audit_trail=db,extended scope=spfile;
--更新参数文件，开始Audit
alter system set audit_sys_operations=TRUE scope=spfile; 
--设置需要Audit的操作（可以BY user 指定Audit的用户，可以只Audit特定操作 alter ,update,insert,select 等）
audit delete,select on SIEBEL.S_SRV_REQ  ;
--查看Audit结果，由于抓取的SQL语句有变量，所以无法确定删除的行，但可以通过SCN对比来得到数据差异

重启数据库

select username,returncode,action_name,obj_name,TIMESTAMP,SCN,sql_text from dba_audit_trail where obj_name='S_SRV_REQ';

--查差集，拿上一步的SCN来对比得到删除的行
select row_id from SIEBEL.S_SRV_REQ as of scn 42145330498 
MINUS 
select row_id from SIEBEL.S_SRV_REQ as of scn 42145330514;

 

--可以清空审计记录
truncate table aud$;

--关闭表的审计
noaudit all on SIEBEL.S_SRV_REQ;
noaudit delete on SIEBEL.S_SRV_REQ  ;
--更改参数文件，取消审计
alter system set audit_trail=none;

 

 

 SIEBEL.S_ORG_EXT BY ACCESS; AUDIT SELECT,INSERT,DELETE,UPDATE ON SIEBEL.S_LST_OF_VAL BY ACCESS; AUDIT SELECT,INSERT,DELETE,UPDATE ON SIEBEL.S_POSTN BY ACCESS; AUDIT SELECT,INSERT,DELETE,UPDATE ON SIEBEL.S_USER BY ACCESS; AUDIT SELECT,INSERT,DELETE,UPDATE ON SIEBEL.S_APP_VIEW_RESP BY ACCESS;，SIEBEL.S_SRV_REQ BY ACCESS;

 

 

create or replace trigger preventdeletedServiceReq
before delete on siebel.S_SRV_REQ
for each row
DECLARE  Cur_User varchar(50);
begin
  select user into Cur_User from dual;
  if Cur_User = 'SADMIN' then
    if  deleting then raise_application_error(-200001,'不允许删除数据!');
      dbms_output.PUT_LINE('不允许删除数据!');
      end if;
  end if;
end;