使用触发器记录oracle用户登陆信息-阿里云开发者社区

使用触发器记录oracle用户登陆信息

2013-10-28 1064

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： Oracle 提供了强大的审计功能，可以针对用户级，系统级范围，以及标准审计，细粒度审计等多种方式来审计各种数据库层面上的操作。然很多中小型数据库需要记录用户的登陆登出信息，而又不希望牺牲太多的性能。

Oracle 提供了强大的审计功能，可以针对用户级，系统级范围，以及标准审计，细粒度审计等多种方式来审计各种数据库层面上的操作。然很多中小型数据库需要记录用户的登陆登出信息，而又不希望牺牲太多的性能。基于这种情形，使用基于数据库级别的触发器可以简单的实现这个需求。

1、实现代码

--创建表用于存储登陆或登出的统计信息
CREATE TABLE stats$user_log
(
   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)
);

--创建登陆之后的触发器
CREATE OR REPLACE TRIGGER logon_audit_trigger
   AFTER LOGON
   ON DATABASE
BEGIN
   INSERT INTO stats$user_log
        VALUES (USER,
                SYS_CONTEXT ('USERENV', 'SESSIONID'),
                SYS_CONTEXT ('USERENV', 'HOST'),
                NULL,
                NULL,
                NULL,
                SYSDATE,
                TO_CHAR (SYSDATE, 'hh24:mi:ss'),
                NULL,
                NULL,
                NULL);
END;
/

--创建登出之后的触发器
CREATE OR REPLACE TRIGGER logoff_audit_trigger
   BEFORE LOGOFF
   ON DATABASE
BEGIN
   -- ***************************************************
   -- Update the last action accessed
   -- ***************************************************
   UPDATE stats$user_log
      SET last_action =
             (SELECT action
                FROM v$session
               WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid)
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

   --***************************************************
   -- Update the last program accessed
   -- ***************************************************
   UPDATE stats$user_log
      SET last_program =
             (SELECT program
                FROM v$session
               WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid)
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

   -- ***************************************************
   -- Update the last module accessed
   -- ***************************************************
   UPDATE stats$user_log
      SET last_module =
             (SELECT module
                FROM v$session
               WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid)
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

   -- ***************************************************
   -- Update the logoff day
   -- ***************************************************
   UPDATE stats$user_log
      SET logoff_day = SYSDATE
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

   -- ***************************************************
   -- Update the logoff time
   -- ***************************************************
   UPDATE stats$user_log
      SET logoff_time = TO_CHAR (SYSDATE, 'hh24:mi:ss')
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;

   -- ***************************************************
   -- Compute the elapsed minutes
   -- ***************************************************
   UPDATE stats$user_log
      SET elapsed_minutes = ROUND ( (logoff_day - logon_day) * 1440)
    WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id;
END;
/

2、结果样例

--查看用户的登入登出信息
SQL> select * from sys.stats$user_log where rownum<3;

USER_ID    SESSION_ID HOST            LAST_PROGRAM     LAST_MODULE     LOGON_DAY LOGON_TIME LOGOFF_DA LOGOFF_TIM  ELP_MINS
---------- ---------- --------------- ---------------- ---------------- --------- ---------- --------- ---------- --------
GX_ADMIN    5409517   v2012DB01u      JDBC Thin Client JDBC Thin Client 24-OCT-13 12:20:30   24-OCT-13 16:20:30   240
GX_ADMIN    5409518   v2013DB01u      JDBC Thin Client JDBC Thin Client 24-OCT-13 12:22:23   24-OCT-13 16:22:30   240

--汇总用户登陆时间     
SQL> SELECT user_id, TRUNC (logon_day) logon_day, SUM (elapsed_minutes) total_time
  2  FROM sys.stats$user_log
  3  GROUP BY user_id, TRUNC (logon_day) ORDER BY 2;

USER_ID                        LOGON_DAY TOTAL_TIME
------------------------------ --------- ----------
GX_ADMIN                       24-OCT-13        960
SYS                            24-OCT-13
GX_ADMIN                       25-OCT-13       2891
GX_WEBUSER                     25-OCT-13
SYS                            25-OCT-13
GX_WEBUSER                     26-OCT-13
GX_ADMIN                       26-OCT-13       2880
SYS                            26-OCT-13
GX_WEBUSER                     27-OCT-13
GX_ADMIN                       27-OCT-13       2640
GX_WEBUSER                     28-OCT-13

--Author : Leshami
--Blog   : http://blog.csdn.net/leshami

--基于日期时间段的用户登陆数
SQL> select trunc (logon_day) logon_day,substr(logon_time,1,2) hour,count(user_id) as number_of_logins
  2  from sys.stats$user_log
  3  group by trunc (logon_day) ,substr(logon_time,1,2)  order by 1,2;

LOGON_DAY HOUR   NUMBER_OF_LOGINS
--------- ------ ----------------
24-OCT-13 12                    2
24-OCT-13 16                    3
24-OCT-13 20                    2
24-OCT-13 22                    2
24-OCT-13 23                    1
25-OCT-13 00                    2
25-OCT-13 03                  104
25-OCT-13 04                    2
25-OCT-13 06                    2
25-OCT-13 10                    2
25-OCT-13 14                    2
   .............

3、原文代码及参考

Oracle audit logon trigger - auditing user activity

Oracle 审计失败的用户登陆(Oracle audit)