1. 登陸觸發器

CREATE OR REPLACE TRIGGER DB_LOGON
AFTER LOGON ON DATABASE
BEGIN
insert into user_logon values(sys_context('userenv','host'),
sys_context('userenv','session_user'),
sys_context('userenv','ip_address'),
sys_context('userenv','os_user'),
sys_context('userenv','bg_job_id'),
sys_context('userenv','fg_job_id'),
sysdate);
commit;

exception
when others then
rollback;

END;
/

2.登出觸發器
CREATE OR REPLACE TRIGGER DB_LOGOFF
BEFORE LOGOFF ON DATABASE
BEGIN
insert into user_logoff values(sys_context('userenv','host'),
sys_context('userenv','session_user'),
sys_context('userenv','ip_address'),
sys_context('userenv','os_user'),
sys_context('userenv','bg_job_id'),
sys_context('userenv','fg_job_id'),
sysdate);
commit;

exception
when others then
rollback;

END;
/

3.記錄登陸使用者表
create table user_logon(
hostname varchar2(100),
username varchar2(40),
ipaddress varchar2(30),
osuser varchar2(30),
bg_job_id varchar2(20),
fg_job_id varchar2(20),
time date);

4. 記錄登出使用者表
create table user_logoff(
hostname varchar2(100),
username varchar2(30),
ipaddress varchar2(30),
osuser varchar2(30),
bg_job_id varchar2(20),
fg_job_id varchar2(20),
time date);

col hostname for a30
col USERNAME for a10
col IPADDRESS for a15
col OSUSER for a10
col bg_job_id for a10
col fg_job_id for a10
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from user_logon;

col hostname for a30
col USERNAME for a10
col IPADDRESS for a15
col OSUSER for a10
col bg_job_id for a10
col fg_job_id for a10
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from user_logoff;

5. 建測試用的JOB
variable jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,'INSERT_T1;',to_date('2011-06-30 15:03:00','yyyy-mm-dd hh24:mi:ss'),'trunc(sysdate+1/1440,''MI'')');
commit;
end;
/

create or replace procedure insert_t1
as
begin
insert into t1 values(7369,'SMITH','CLERK',7902,to_date('1980-12-17 00:00:00','yyyy-mm-dd hh24:mi:ss'),800,0,20);
commit;
end;
/

審計資料庫登陸登出觸發器

相關文章