记录Oracle用户的登录信息

程序员文章站 2024-02-15 08:42:28

...

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

为了统计哪些用户在登录使用数据库，以及其他用途，需要记录登录数据库的信息，以及尝试登录数据库失败的信息，以下几个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;
/