oracle ip on_logon_trigger,透过Trigger监控Logon和Logoff

通过Trigger监控Logon和Logoff

1,创建审计信息的Table

create table login_log

(

session_id int not null,

SID INT,

login_on_time date,

login_off_time date,

user_in_db varchar2(100),

machine varchar2(100),

os_user varchar2(100),

ip_address varchar2(20),

run_program varchar2(100)

);

2,创建登陆审计的Trigger

create or replace trigger login_on_info

after logon on database

Begin

insert into login_log(session_id,SID,login_on_time,login_off_time,user_in_db,machine,os_user,ip_address,run_program)

select AUDSID,SID,sysdate,null,sys.login_user,machine,a.OSUSER,SYS_CONTEXT('USERENV','IP_ADDRESS'),NVL(program,MODULE)

from v$session a where AUDSID = USERENV('SESSIONID');

END;

3,创建Logoff的Trigger

create or replace trigger login_off_info

before logoff on database

Begin

update login_log set login_off_time = sysdate

where session_id = USERENV('SESSIONID');

exception

when others then

null;

END;

4,可以查询审计信息

SELECT * FROM login_log;

Note:

1)需要用sys用户来创建

如不用sys用户,会报如下Error:

Thu Apr 7 14:58:00 2011

Errors in file /opt/oracle/app/oracle/admin/bks/bdump/bks_ora_7456.trc:

ORA-04098: trigger 'SYS.LOGIN_ON_INFO' is invalid and failed re-validation