20150609 Created By BaoXinjian
一、摘要
oracle logon trigger一般用来审计用户登录信息或者限制用户登录，虽说不常用，但仍不失为一种好办法。
1. 不能审计dba用户登录
2. 什么时候适合使用
It is advised you use this trigger only when
(1) not using archive logging on the database or
非归档模式
(2) there are few logons to the database.
登录次数少
二、锁定统计信息
Step1. 创建审计表
CREATE TABLE gavin.bxj_logonlog(
os_user varchar2(30),
user_name varchar2(30),
logon_time date,
session_user varchar2(30),
ip_address varchar2(15),
program varchar2(30)
);
Step2. 创建Logon Trigger
CREATE OR REPLACE TRIGGER gavin.bxj_on_logon
AFTER logon ON DATABASE
DECLARE
user_name varchar2(30);
os_user varchar2(30);
v_sid number;
v_su varchar2(15);
v_program varchar2(30);
v_ip varchar2(15);
BEGIN
EXECUTE IMMEDIATE 'select distinct sid from sys.v_$mystat'
INTO v_sid;
EXECUTE IMMEDIATE 'select osuser, username, program from sys.v_$session where sid = :b1'
INTO os_user, user_name, v_program
USING v_sid;
SELECT sys_context('userenv', 'SESSION_USER') INTO v_su from dual;
SELECT sys_context('userenv', 'IP_ADDRESS') INTO v_ip from dual;
INSERT INTO gavin.bxj_logonlog
VALUES
(os_user, user_name, sysdate, v_su, v_ip, v_program);
--禁止WWW账号登陆
IF (user_name = 'WWW') THEN
DBMS_SESSION.SET_IDENTIFIER('about to raise app_error..');
RAISE_APPLICATION_ERROR(-20003, 'You are not allowed to connect to the database');
END IF;
--启用GAVINTEST账号的所有操作的trace记录
IF user = 'GAVINTEST' THEN
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set tracefile_identifier='''||user||'_10046''';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
END;
/
Step3. 使用普通账号登陆，用以测试查看BXJ_LOGONLOG审计表记录
Step4. 使用WWW账号登陆，用以测试是否允许登录
Step5. 使用GAVINTEST账号登陆，用以而是是否产生trace file
Thanks and Regards
参考：Matelink - How To Create A Trigger To Capture User Information On Logon [ID 454088.1]
技术交流，技术讨论，欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
