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

zhanglei_itput發表於2011-07-01
資料庫觸發器
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;
/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9252210/viewspace-701116/，如需轉載，請註明出處，否則將追究法律責任。

相關文章