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

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

1、实现代码

--创建表用于存储登陆或登出的统计信息

CREATETABLEstats$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_dayDATE,

logon_time VARCHAR2 (10),

logoff_dayDATE,

logoff_time VARCHAR2 (10),

elapsed_minutes NUMBER (8)

);

--创建登陆之后的触发器

CREATEORREPLACETRIGGERlogon_audit_trigger

AFTERLOGON

ONDATABASE

BEGIN

INSERTINTOstats$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;

/

--创建登出之后的触发器

CREATEORREPLACETRIGGERlogoff_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;

/

--创建表用于存储登陆或登出的统计信息

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*fromsys.stats$user_logwhererownum<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

2FROMsys.stats$user_log

3GROUPBYuser_id, TRUNC (logon_day)ORDERBY2;

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

2fromsys.stats$user_log

3groupbytrunc (logon_day) ,substr(logon_time,1,2)orderby1,2;

LOGON_DAYHOURNUMBER_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

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