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

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;

/