ORACLE 11g 实现审计用户登入登出<登陆退出>的详细记录

需求就是需要记录用户登陆登出的记录，包括时间以及连接ip地址以及连接方式等等，这样有助于跟踪分析问题，特别是一些人为无意识的dml操作导致数据丢失、数据混乱的问题追踪，还是非常有效的。

大家知道oracle库开启了审计audit后，肯定有办法查到问题，但是审计比较消耗资源，所以可以通过触发器之类的来间接实现这个功能。

1、建记录表

-- Create table
createtable UC_logoN_OFF
(
  user_id         VARCHAR2(30),session_id      NUMBER(8),host            VARCHAR2(30),last_program    VARCHAR2(48),last_action     VARCHAR2(32),last_module     VARCHAR2(32),logon_day       DATE,logon_time      VARCHAR2(10),logoff_day      DATE,logoff_time     VARCHAR2(10),elapsed_minutes NUMBER(8),sid             NUMBER(8),serial          NUMBER(8)
)
tablespaceUSERS
  pctfree10
  initrans1
  maxtrans255;

PS：后续的索引问题，可以根据数据来随时添加，提高查询效率。

2、建立登陆触发器

CREATEORREPLACETRIGGER trig_logon_audit
  AFTER logoN
   ONDATABASE
BEGIN
   INSERTINTO timdba.uc_logon_off
        selectUSER,SYS_CONTEXT ('USERENV','SESSIONID'),'HOST'),NULL,SYSDATE,TO_CHAR (SYSDATE,'yyyy-mm-dd hh24:mi:ss'),nullfrom dual;
END;

3，建立登出的触发器

登出这里也采用了insert语句，是考虑到因为用户如果用了update的话，在登陆记录里面做update操作，如果登陆用户恶意的话，它是可以修改记录的。它下次登陆后，直接修改上次登陆的记录的ip地址等等信息，这样就会给我们误导了，当然我们也可以根据归档日志来分析，但是会比较麻烦一些，那么同理这张记录表uc_logon_off表就失去记录的功效了，因为每次我们都会怀疑这里的记录是否被认为修过过了，而不是真实的原始记录。

那现在改成了insert，再加上后面禁止delete、update操作，那么就确保uc_logon_off表的数据的真实性，虽然有可能被人恶意insert新记录的可能，但是每次它连接后最后一条记录都是真实的，这样我们就只根据uc_logon_off表进行分析就可以得出用户的操作行为记录。

CREATE OR REPLACE TRIGGER trig_logoff_audit
 BEFORE logoFF
   ON DATABASE
DECLARE
    v_date date;
BEGIN
  
    SELECt t.logon_day INTO v_date from (select logon_day FROM timdba.uc_logon_off where session_id= SYS_CONTEXT ('USERENV','SESSIONID') AND logon_day IS NOT NULL order by logon_day asc) t where rownum<2;
    INSERT INTO timdba.uc_logon_off
        select USER,SYS_CONTEXT ('userenv','host'),(SELECT action FROM v$session WHERE SYS_CONTEXT ('USERENV','SESSIONID') = audsid),(SELECT program FROM v$session WHERE SYS_CONTEXT ('USERENV',(SELECT module  FROM v$session WHERE SYS_CONTEXT ('USERENV',v_date,TO_CHAR(v_date,'hh24:mi:ss'),sysdate,TO_CHAR(sysdate,TRUNC(TO_NUMBER(sysdate - v_date) * 1440,2),ROUND(TO_NUMBER(sysdate - v_date) * 24 * 60*60 ),'ip_address') from dual;
               
                commit; 
          /*
          UPDATE timdba.uc_logon_off
            SET last_action = 'v_tag logoff: '||(SELECT action FROM v$session WHERE SYS_CONTEXT ('USERENV',last_program = (SELECT program FROM v$session WHERE SYS_CONTEXT ('USERENV',last_module = (SELECT module  FROM v$session WHERE SYS_CONTEXT ('USERENV',logoff_day = SYSDATE,logoff_time = TO_CHAR (SYSDATE,elapsed_minutes = 'v_tag logoff:'||TRUNC(TO_NUMBER(sysdate - logon_day) * 1440,elapsed_seconds = ROUND(TO_NUMBER(sysdate - logon_day) * 24 * 60*60 )       
          WHERE SYS_CONTEXT ('USERENV','SESSIONID') = session_id;
          */
 
   COMMIT;
               
 
END;

4，禁止删改登陆登出操作记录

create or replace trigger timdba.trig_uc_logonoff
  before update or delete on timdba.uc_logon_off
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF deleting THEN
    RAISE_APPLICATION_ERROR(-20001,'can not delete ');
 
  ELSIF updating then
    RAISE_APPLICATION_ERROR(-20001,'can not update ');
   END IF;
END;

5，给所有的用户授权记录操作

因为触发器里面需要查询v$sql以及v$session，还要对timdba.uc_logon_off表进行操作，所以需要赋予这些操作权限，那么如果要记录所有用户的，就需要把所有用户都赋予这样的操作权限。

--（1）       统计需要授权的语句
grant create session,connect to dw;
grant select on v_$sql to dw;
grant select on v_$session to dw;
grant select,insert on timdba.uc_logon_off to dw;
 
--（2）创建临时表，记录所有用户
create table timdba.Z_USERS as select distinct owner from all_objects;
 
--（3）生成授权的sql语句
select 'grant create session,connect to ' || owner ||'; ' from timdba.Z_USERS t where t.owner not in('TIMDBA','SYS','SYSTEM')
union all
select              'grant select on v_$sql to  ' || owner ||'; ' from timdba.Z_USERS t where t.owner not in('TIMDBA','SYSTEM')
union all
select              'grant select on v_$session to  ' || owner ||'; '   from timdba.Z_USERS t where t.owner not in('TIMDBA','SYSTEM')
union all
select 'grant select,insert on timdba.uc_logon_off to ' || owner ||'; ' from timdba.Z_USERS t where t.owner not in('TIMDBA','SYSTEM');
 
--（4）将sql结果记录copy到一个文件grant_logonoff.sql，放到/home/oracle目录，然后sqlplus登陆执行sql文件授权。
sql> @/home/oracle/grant_logonoff.sql;
 
Grant succeeded.
 
 
Grant succeeded.
 
 
Grant succeeded.
……s

6，查看部分记录

查询 sql：

select  t.elapsed_minutes,t.elapsed_seconds,t.ip,t.*
 from  uc_logon_off t where t.user_id in('DW','LOGMINER','YS','TIMDBA','PLCRM') and t.elapsed_minutes is not null order by t.logon_day desc,t.session_id desc;

记录如下图所示：

C:\pic\oracle\20170330_01.png