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

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

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146

1、实现代码 [sql] --创建表用于存储登陆或登出的统计信息  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] --查看用户的登入登出信息  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.youkuaiyun.com/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      .............