透过Trigger监控Logon和Logoff

2012-08-11

通过Trigger监控Logon和Logoff1,创建审计信息的Tablecreate table login_log(session_id int not null,SID

通过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_infoafter logon on databaseBegin    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_infobefore logoff on databaseBegin        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 2011Errors 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

热点排行

其他数据库

透过Trigger监控Logon和Logoff