记录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;
/

更多Oracle相关信息见Oracle 专题页面 http://www.bkjia.com/topicnews.aspx?tid=12

相关内容