ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 审计数据库登陆登出触发器

审计数据库登陆登出触发器

原创 Linux操作系统 作者:zhanglei_itput 时间:2011-07-01 11:43:14 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-10

  • 博文量
    400
  • 访问量
    1129149