oracle通过用户登录数据库,记录Oracle用户的登录信息

为了统计哪些用户在登录使用数据库，以及其他用途，需要记录登录数据库的信息，以及尝试登录数据库失败的信息，以下几个trigger可以搞定。

创建记录用户信息的table

CREATE TABLE oram.LOGIN_LOG

(

AUDSID       NUMBER,

SID          NUMBER,

SERIAL#      NUMBER,

LOGIN_TIME   DATE,

LOGOUT_TIME  DATE,

USERNAME     VARCHAR2(30 BYTE),

MACHINE      VARCHAR2(64 BYTE),

IP           VARCHAR2(20 BYTE),

PROGRAM      VARCHAR2(48 BYTE)

);

通过trigger记录Oracle用户的登录信息

CREATE OR REPLACE TRIGGER login_on_info

/*******************************************************

author：dbabc.net

time  ：2011/12/14

功能：记录登录数据库的用户信息

说明：要以sys用户创建触发器，insert 的表可以是任意用户的

********************************************************/

AFTER LOGON

ON DATABASE

BEGIN

INSERT INTO oram.login_log

SELECT   audsid,

SID,

serial#,

SYSDATE,

NULL,

username,

machine,

SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),

program

FROM   v$session

WHERE       SID IN (SELECT   SID

FROM   v$mystat

WHERE   ROWNUM = 1)

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

AND program NOT LIKE 'JDBC%'

AND username <> 'SYSMAN'

AND TYPE <> 'BACKGROUND';

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

/

通过trigger更新用户登出数据库的时间

CREATE OR REPLACE TRIGGER login_off_info

/*******************************************************

author：dbabc.net

time  ：2011/12/14

功能：更新用户登出数据库的时间

说明：要以sys用户创建触发器

********************************************************/

BEFORE LOGOFF

ON DATABASE

BEGIN

UPDATE   oram.login_log

SET   LOGOUT_TIME = SYSDATE

WHERE       audsid = USERENV ('SESSIONID')

AND SID = (SELECT   SID

FROM   v$session s

WHERE   SID IN (SELECT   SID

FROM   v$mystat

WHERE   ROWNUM = 1))

AND serial# = (SELECT   serial#

FROM   v$session s

WHERE   SID IN (SELECT   SID

FROM   v$mystat

WHERE   ROWNUM = 1));

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

/

通过trigger记录登录数据库失败哦信息到alert日志

CREATE OR REPLACE TRIGGER logon_denied_to_alert

/*******************************************************

author：dbabc.net

time  ：2011/12/14

功能：记录登录数据库失败哦信息到alert日志中

说明：要以sys用户创建触发器

********************************************************/

AFTER SERVERERROR

ON DATABASE

DECLARE

MESSAGE     VARCHAR2 (120);

IP          VARCHAR2 (15);

v_os_user   VARCHAR2 (80);

v_module    VARCHAR2 (50);

v_action    VARCHAR2 (50);

v_pid       VARCHAR2 (10);

v_sid       NUMBER;

BEGIN

IF (ora_is_servererror (1017))

THEN

IF SYS_CONTEXT ('userenv', 'network_protocol') = 'tcp'

THEN

IP := SYS_CONTEXT ('userenv', 'ip_address');

ELSE

SELECT   DISTINCT sid INTO v_sid FROM sys.v_$mystat;

SELECT   p.SPID

INTO   v_pid

FROM   V$PROCESS p, V$SESSION v

WHERE   p.ADDR = v.PADDR AND v.sid = v_sid;

END IF;

v_os_user := SYS_CONTEXT ('userenv', 'os_user');

DBMS_APPLICATION_INFO.READ_MODULE (v_module, v_action);

MESSAGE :=

TO_CHAR (SYSDATE, 'Dy Mon dd HH24:MI:SS YYYY')

|| ' logon denied from '

|| NVL (IP, v_pid)

|| ' '

|| v_os_user

|| ' with '

|| v_module

|| ' '

|| v_action;

sys.DBMS_SYSTEM.ksdwrt (2, MESSAGE);

END IF;

END;

/

通过trigger将登录失败的信息记录到表中，先创建table，当然也可以用上面的table，这里新建了一个表跟上面区分

CREATE TABLE USER_LOGIN_AUDIT

(

SESSION_ID    NUMBER(10),

SESSION_USER  VARCHAR2(30 BYTE),

HOST          VARCHAR2(30 BYTE),

IP            VARCHAR2(30 BYTE),

CLIENT        VARCHAR2(50 BYTE),

OS_USER       VARCHAR2(50 BYTE),

STATUS        CHAR(1 BYTE),

LOGIN_TIME    DATE

);

通过trigger记录登录数据库失败哦信息到table中

CREATE OR REPLACE TRIGGER TRI_USER_LOGIN_DENIED_AUDIT

AFTER SERVERERROR

ON DATABASE

BEGIN

IF (ora_is_servererror (1017))

THEN

INSERT INTO oram.user_login_audit

VALUES   (SYS_CONTEXT ('USERENV', 'SESSIONID'),

SYS_CONTEXT ('USERENV', 'SESSION_USER'),

SYS_CONTEXT ('USERENV', 'HOST'),

SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),

SYS_CONTEXT ('USERENV', 'MODULE'),

SYS_CONTEXT ('USERENV', 'OS_USER'),

'9',

SYSDATE);

END IF;

END TRI_USER_LOGIN_DENIED_AUDIT;

/