通过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