环境描述
| 描述项 | 内容 |
|---|---|
| 操作系统 | Red Hat Enterprise Linux Server release 6.5 (Santiago) |
| 数据库版本 | Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 |
1. 使用 sys 用户以 sysdba 身份登录数据库
2. 创建自动增长序列
CREATE SEQUENCE seq_dbm
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;3. 创建表空间用于存储日志记录
CREATE TABLESPACE TBS_DBM DATAFILE '/u01/app/oracle/oradata/TBS_DBM.DBF' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 30G;4. 创建表用于存储用户的登入与登出详细信息
-- DROP TABLE user_session_his;
CREATE TABLE user_session_his (recnum NUMBER PRIMARY KEY NOT NULL,
sid NUMBER, -- sessionId
serial# NUMBER, -- sid 会重用，但是同一个sid被重用时，serial#会增加，不会重复。
user_name VARCHAR2(30), -- 登入或登出用户名
program VARCHAR2(128), -- 客户端应用程序
machine VARCHAR2(64), -- 客户端machine name
terminal VARCHAR2(50), -- 客户端执行的terminal name
ipadress VARCHAR2(30), -- 客户端IP地址
osuser VARCHAR2(60), -- 客户端操作系统用户名
audsid NUMBER, -- audit session id。可以通过audsid查询当前session的sid。select sid from v$session where audsid=userenv('sessionid')
login_time DATE, -- 登入时间
logout_time DATE) -- 登出时间
TABLESPACE TBS_DBM;
CREATE INDEX idx_user_session_his1 ON user_session_his(sid,serial#);5. 创建触发器用于记录用户成功登入后的详细信息
-- DROP TRIGGER tr_logon_record;
CREATE OR REPLACE TRIGGER tr_logon_record
AFTER logon ON DATABASE
DECLARE
row_session v$session%ROWTYPE;
CURSOR cur_session(i_query_sid IN NUMBER) IS
SELECT *
FROM v$session a
WHERE NVL(a.osuser,'x') <> 'SYSTEM'
AND a.type <> 'BACKGROUND'
AND a.audsid = i_query_sid;
BEGIN
OPEN cur_session(userenv('SESSIONID'));
FETCH cur_session INTO row_session;
IF cur_session%FOUND THEN
INSERT INTO user_session_his(recnum, sid, serial#, user_name, program, machine, terminal,
ipadress, osuser, audsid, login_time, logout_time)
VALUES(seq_dbm.nextval,
row_session.SID,
row_session.SERIAL#,
sys_context('USERENV', 'SESSION_USER'),
row_session.Program,
row_session.Machine,
row_session.Terminal,
sys_context('USERENV','IP_ADDRESS'),
row_session.Osuser,
userenv('SESSIONID'),
SYSDATE,
NULL);
END IF;
CLOSE cur_session;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/6. 创建触发器用于更新用户成功登出后的登出时间
-- DROP TRIGGER tr_logoff_record;
CREATE TRIGGER tr_logoff_record
BEFORE LOGOFF ON DATABASE
DECLARE
row_session v$session%ROWTYPE;
CURSOR cur_session(i_query_sid IN NUMBER) IS
SELECT *
FROM v$session a
WHERE NVL(a.osuser,'x') <> 'SYSTEM'
AND a.type <> 'BACKGROUND'
AND a.audsid = i_query_sid;
BEGIN
OPEN cur_session(userenv('SESSIONID'));
FETCH cur_session INTO row_session;
IF cur_session%FOUND THEN
UPDATE user_session_his
SET logout_time = SYSDATE
WHERE sid = row_session.Sid
AND serial# = row_session.Serial#
AND logout_time IS NULL;
END IF;
CLOSE cur_session;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/7. 创建触发器用于将登录失败的用户信息记录到告警日志
-- DROP TRIGGER logon_denied_write_alertlog;
CREATE TRIGGER logon_denied_write_alertlog
AFTER SERVERERROR ON DATABASE
DECLARE
l_message varchar2(2000);
BEGIN
-- ORA-1017: invalid username/password; logon denied
IF (IS_SERVERERROR(1017)) THEN
SELECT 'Failed login attempt to the "' ||
sys_context('USERENV' ,'AUTHENTICATED_IDENTITY') ||'" schema' || ' using ' ||
sys_context ('USERENV', 'AUTHENTICATION_TYPE') ||' authentication' || ' at ' ||
to_char(logon_time,'dd-MON-yy hh24:mi:ss' ) || ' from ' ||
osuser ||'@'||machine ||' ['||NVL(sys_context ('USERENV', 'IP_ADDRESS'),'Unknown IP')||']' ||
' via the "' ||program||'" program.'
INTO l_message
FROM sys.v_$session
WHERE SID = to_number(SUBSTR(dbms_session.unique_session_id,1 ,4), 'xxxx')
AND serial# = to_number(SUBSTR(dbms_session.unique_session_id,5 ,4), 'xxxx');
-- write to alert log
sys.dbms_system.ksdwrt( 2,l_message );
END IF;
END;
/