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(20),
machine varchar2(20),
os_user varchar2(20),
ip_address varchar2(20),
run_program varchar2(200)
);
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') and program !='JDBC Thin Client';
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,可以查询审计信息
1.SELECT * FROM login_log;