oracle logon trigger

oracle logon trigger一般用来审计用户登录信息或者限制用户登录，虽说不常用，但仍不失为一种好办法。备注：不能审计dba用户登录

下面是具体的小例子

1，建审计表

CREATE TABLE LOGONLOG(os_user varchar2(30),
user_name varchar2(30),
logon_time date,
session_user varchar2(30),
ip_address varchar2(15),

program varchar2(30));

2，建logon trigger，并限制www用户登录

create or replace trigger 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 sys.logonlog values
(os_user, user_name, sysdate,v_su, v_ip, v_program);
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;
end;

/

3，什么时候适合使用

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.

登录次数少

参考资料

How To Create A Trigger To Capture User Information On Logon [ID 454088.1]