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

来源：互联网发布：淘宝如何导入发布宝贝编辑：程序博客网时间：2024/05/22 01:41
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]
--创建表用于存储登陆或登出的统计信息 
CREATETABLE 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)  
); 
   
--创建登陆之后的触发器 
CREATEOR REPLACE TRIGGER logon_audit_trigger  
   AFTERLOGON  
   ONDATABASE 
BEGIN 
   INSERTINTO 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; 
/ 
   
--创建登出之后的触发器 
CREATEOR REPLACE TRIGGER logoff_audit_trigger  
   BEFORE LOGOFF  
   ONDATABASE 
BEGIN 
   -- ***************************************************  
   -- Update the last action accessed  
   -- ***************************************************  
   UPDATEstats$user_log  
      SETlast_action =  
             (SELECTaction 
                FROMv$session  
               WHERESYS_CONTEXT ('USERENV','SESSIONID') = audsid)  
    WHERESYS_CONTEXT ('USERENV','SESSIONID') = session_id;  
   
   --*************************************************** 
   -- Update the last program accessed  
   -- ***************************************************  
   UPDATEstats$user_log  
      SETlast_program =  
             (SELECTprogram  
                FROMv$session  
               WHERESYS_CONTEXT ('USERENV','SESSIONID') = audsid)  
    WHERESYS_CONTEXT ('USERENV','SESSIONID') = session_id;  
   
   -- ***************************************************  
   -- Update the last module accessed  
   -- ***************************************************  
   UPDATEstats$user_log  
      SETlast_module =  
             (SELECTmodule  
                FROMv$session  
               WHERESYS_CONTEXT ('USERENV','SESSIONID') = audsid)  
    WHERESYS_CONTEXT ('USERENV','SESSIONID') = session_id;  
   
   -- ***************************************************  
   -- Update the logoff day  
   -- ***************************************************  
   UPDATEstats$user_log  
      SETlogoff_day = SYSDATE  
    WHERESYS_CONTEXT ('USERENV','SESSIONID') = session_id;  
   
   -- ***************************************************  
   -- Update the logoff time  
   -- ***************************************************  
   UPDATEstats$user_log  
      SETlogoff_time = TO_CHAR (SYSDATE, 'hh24:mi:ss') 
    WHERESYS_CONTEXT ('USERENV','SESSIONID') = session_id;  
   
   -- ***************************************************  
   -- Compute the elapsed minutes  
   -- ***************************************************  
   UPDATEstats$user_log  
      SETelapsed_minutes = ROUND ( (logoff_day - logon_day) * 1440)  
    WHERESYS_CONTEXT ('USERENV','SESSIONID') = session_id;  
END; 
/ 
2、结果样例
[sql]
--查看用户的登入登出信息 
SQL>select* fromsys.stats$user_log whererownum<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>SELECTuser_id, TRUNC (logon_day) logon_day, SUM(elapsed_minutes) total_time  
  2 FROMsys.stats$user_log  
  3 GROUPBY user_id, TRUNC (logon_day) ORDERBY 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>selecttrunc (logon_day) logon_day,substr(logon_time,1,2) hour,count(user_id)asnumber_of_logins 
  2 fromsys.stats$user_log  
  3 groupby trunc (logon_day) ,substr(logon_time,1,2)  orderby 1,2;  
   
LOGON_DAYHOUR  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  
   .............
0 0