修改数据用户的安全性审计

根据公司安全管理办法要求，将修改数据用户的信息尽量审计详细，首先考虑记录表，后发现trace更强大，任何细小的操作都可以跟踪下来，于是就做了以下的触发器：

grant ADMINISTER DATABASE TRIGGER to moduser;
grant dba,connect,resource to moduser;
grant alter session to moduser;
grant select any dictionary to MODUSER;

 

create table LOGIN_ACTION
(
  USERNAME  VARCHAR2(20),
  SESSIONID NUMBER,
  TERMINAL      VARCHAR2(50),
  program varchar2(50),
  action varchar2(50),
  IPADD     VARCHAR2(20),
  LOGON     DATE,
  LOGOUT    DATE,
  tracename varchar2(255)
)

 

CREATE OR REPLACE TRIGGER MODUSER.capt_sql
  AFTER LOGON ON DATABASE
declare
  p_str1      varchar2(200); ---声明要执行的SQL变量
  p_str2      varchar2(200);
  p_sessionid number;
  p_username  varchar2(100);
  p_program   varchar2(100);
  p_action    varchar2(100);
  p_tracename varchar2(255);

begin
  select user into p_username from dual;
  if p_username = 'MODUSER' then
 
    execute immediate 'select distinct a.username from v$session a where a.SID=(select sid from v$mystat where rownum<2) and a.username != ''SYS'' and serial#!=1'
      into p_program;
    execute immediate 'select distinct a.username from v$session a where a.SID=(select sid from v$mystat where rownum<2) and a.username != ''SYS'' and serial#!=1'
      into p_action;
    execute immediate 'select sid from v$mystat where rownum<2'
      into p_sessionid;
 
    execute immediate '
    SELECT       d.VALUE
               || ''/''
               || LOWER (RTRIM (i.INSTANCE, CHR (0)))
               || ''_ora_''
               || p.spid
               || ''.trc'' trace_file_name
          FROM (SELECT p.spid
                  FROM v$mystat m, v$session s, v$process p
                 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
              (SELECT t.INSTANCE
                FROM v$thread t, v$parameter v
               WHERE v.NAME = ''thread''
                 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
             (SELECT VALUE
                FROM v$parameter
               WHERE NAME =''user_dump_dest'') d'
      into p_tracename;
 
    insert into login_action
      (username,
       sessionid,
       terminal,
       program,
       action,
       ipadd,
       logon,
       tracename)
    values
      (SYS_CONTEXT('USERENV', 'CURRENT_USER'),
       p_sessionid,
       SYS_CONTEXT('USERENV', 'TERMINAL'),
       p_program,
       p_action,
       SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
       sysdate,
       p_tracename);
 
    p_str1 := 'alter session set MAX_DUMP_FILE_SIZE=unlimited';
    p_str2 := 'alter session set sql_trace=true';
    execute immediate p_str1;
    execute immediate p_str2; -----启用当前SESSION跟踪
  end if;
end;

 

 

CREATE OR REPLACE TRIGGER MODUSER.capt_logout
  BEFORE LOGOFF ON DATABASE
declare
  p_str1      varchar2(200); ---声明要执行的SQL变量
  p_username  varchar2(20);
  p_sessionid number;
begin
  select user into p_username from dual;
  execute immediate 'select sid from v$mystat where rownum<2'
    into p_sessionid;

   
  if p_username = 'MODUSER' then
   
      update login_action x
         set logout = sysdate
       where x.sessionid = p_sessionid;
   
      p_str1 := 'alter session set sql_trace=true';
      execute immediate p_str1; -----禁用当前SESSION跟踪
    end if;
end;

由于后台JOB的运行也会触发，并且可能报错。所以将条件写的更加完善些，更新版本如上

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16628454/viewspace-531886/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16628454/viewspace-531886/