使用觸發器記錄oracle使用者登陸資訊

 Oracle 提供了強大的審計功能，可以針對使用者級，系統級範圍，以及标準審計，細粒度審計等多種方式來審計各種資料庫層面上的操作。然很多中小型資料庫需要記錄使用者的登陸登出資訊，而又不希望犧牲太多的性能。基于這種情形，使用基于資料庫級别的觸發器可以簡單的實作這個需求。

1、實作代碼

--建立表用于存儲登陸或登出的統計資訊

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) 

); 

--建立登陸之後的觸發器

CREATEORREPLACETRIGGER logon_audit_trigger 

AFTER LOGON 

ONDATABASE

BEGIN

INSERTINTO stats$user_log 

VALUES (USER, 

SYS_CONTEXT ('USERENV', 'SESSIONID'), 

SYS_CONTEXT ('USERENV', 'HOST'), 

NULL, 

SYSDATE, 

TO_CHAR (SYSDATE, 'hh24:mi:ss'), 

NULL); 

END; 

/ 

--建立登出之後的觸發器

CREATEORREPLACETRIGGER logoff_audit_trigger 

BEFORE LOGOFF 

-- ***************************************************

-- Update the last action accessed

UPDATE stats$user_log 

SET last_action = 

(SELECTaction

FROM v$session 

WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = audsid) 

WHERE SYS_CONTEXT ('USERENV', 'SESSIONID') = session_id; 

--***************************************************

-- Update the last program accessed

SET last_program = 

(SELECT program 

-- Update the last module accessed

SET last_module = 

(SELECT module 

-- Update the logoff day

SET logoff_day = SYSDATE 

-- Update the logoff time

SET logoff_time = TO_CHAR (SYSDATE, 'hh24:mi:ss') 

-- Compute the elapsed minutes

SET elapsed_minutes = ROUND ( (logoff_day - logon_day) * 1440) 

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 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> 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 groupby trunc (logon_day) ,substr(logon_time,1,2) orderby 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 

............. 

本文轉自東方之子736651CTO部落格，原文連結：http://blog.51cto.com/ecloud/1336726 ，如需轉載請自行聯系原作者