oracle logon trigger一般用来审计用户登录信息或者限制用户登录,虽说不常用,但仍不失为一种好办法。
备注:不能审计dba用户登录
user_name varchar2(30),
logon_time date,
session_user varchar2(30),
ip_address varchar2(15),
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;
下面是具体的小例子
1,建审计表
user_name varchar2(30),
logon_time date,
session_user varchar2(30),
ip_address varchar2(15),
program varchar2(30));
2,建logon trigger,并限制www用户登录
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.
登录次数少