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

最新推荐文章于 2024-11-13 08:23:13 发布

cr147369

最新推荐文章于 2024-11-13 08:23:13 发布

阅读量181

点赞数

文章标签：数据库

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;

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9252210/viewspace-701116/