基于值审计(以触发器为核心)
一、示例
create or replace trigger system.hrsalary_audit
after update of salary on hr.employees
referencing new as new old as old
for each row
begin
if :old.salary != :new.salary then
insert into system.audit_employees
values
(sys_context('userenv', 'os_user'), sysdate,
sys_context('userenv', 'ip_address'),
:new.employee_id || ' salary changed from ' || :old.salary || ' to '||:new.salary);
end if;
end;
/
触发器是创建在 system 用户下的,针对 hr 用户的 employees 表的 salary 字段进行基于值的审计,如果对表 employees 表的 salary
字段进行了更新,那么就会触发 hrsalary_audit,如果更新后的 salary值与原来的值不同,那么就把做更新这个动作的操作系统用户名及IP
地址都记录到表system.audit_employees中,同时将 salary 的值也记录下来。
二、实际应用测试
1) 创建日志表 db_ddl_log
SYS@ORA11GR2>create table db_ddl_log(logdate date default sysdate,username varchar2(200),ddltype varchar2(200),objecttype varchar2(200),objectname varchar2(200),objectownner varchar2(200),ipaddress varchar2(18)) tablespace users;
Table created.
——解释:【
comment on column DB_DDL_LOG.LOGDATE is '记录日期';
comment on column DB_DDL_LOG.USERNAME is '用户名';
comment on column DB_DDL_LOG.DDLTYPE is 'DDL 类型三种: alter,drop,create;
comment on column DB_DDL_LOG.OBJECTTYPE is '操作对象类型';
comment on column DB_DDL_LOG.OBJECTNAME is '操作对象名称';
comment on column DB_DDL_LOG.OBJECTOWNNER is '操作对象拥有者';
comment on column DB_DDL_LOG.IPADDRESS is 'IP 地址';
】
2) 创建日志表 eventlog
SYS@ORA11GR2>create table eventlog (
2 oper_time date default sysdate,
3 eventname varchar2(20),
4 obj_type varchar2(20),
5 obj_name varchar2(20),
6 obj_owner varchar2(20),
7 ip_addr varchar2(18),
8 os_user varchar2(200),
9 terminal varchar2(200),
10 host_name varchar2(200),
11 user_name varchar2(200),
12 session_id number
13 ) tablespace users;
Table created.
——解释:【
comment on column EVENTLOG.OPER_TIME is 'DDL 时间';
comment on column EVENTLOG.EVENTNAME is '事件类型: create alter drop';
comment on column EVENTLOG.OBJ_TYPE is '目标类型: table procedure function 等';
comment on column EVENTLOG.OBJ_NAME is '目标名称';
comment on column EVENTLOG.OBJ_OWNER is '目标所在用户';
comment on column EVENTLOG.IP_ADDR is '终端 IP';
comment on column EVENTLOG.OS_USER is '终端操作系统用户名';
comment on column EVENTLOG.TERMINAL is '终端名称';
comment on column EVENTLOG.HOST_NAME is '终端主机名';
comment on column EVENTLOG.USER_NAME is '执行 DDL 的 oracle 用户名';
comment on column EVENTLOG.SESSION_ID is '会话 ID';
】
1) 创建写入日志的过程包
SYS@ORA11GR2>create or replace package pkg_ddl
2 is
3 /*
4 * author:wangxuebing
5 * created date:2016-09-22
6 */
7 procedure sp_trg_ddlcreate;
8 procedure sp_trg_ddlalter;
9 procedure sp_trg_ddldrop;
10 procedure sp_ddlall;
11 end pkg_ddl;
12 /
Package created.
SYS@ORA11GR2>create or replace package body pkg_ddl
2 is
3 /*
4 * author:wangxuebing
5 * created date:2016-09-22
6 */
7 procedure sp_trg_ddlcreate
8 is
9 begin
10 insert into db_ddl_log
11 values (sysdate, user, 'create', sys.dictionary_obj_type,
12 sys.dictionary_obj_name, sys.dictionary_obj_owner,
13 sys_context ('userenv', 'ip_address'));
14 end sp_trg_ddlcreate;
15 procedure sp_trg_ddlalter
16 is
17 begin
18 insert into db_ddl_log
19 values (sysdate, user, 'alter', sys.dictionary_obj_type,
20 sys.dictionary_obj_name, sys.dictionary_obj_owner,
21 sys_context ('userenv', 'ip_address'));
22 end sp_trg_ddlalter;
23 procedure sp_trg_ddldrop
24 is
25 begin
26 insert into db_ddl_log
27 values (sysdate, user, 'drop', sys.dictionary_obj_type,
28 sys.dictionary_obj_name, sys.dictionary_obj_owner,
29 sys_context ('userenv', 'ip_address'));
30 end sp_trg_ddldrop;
31 procedure sp_ddlall
32 is
33 begin
34 insert into eventlog
35 (eventname, obj_type, obj_name, obj_owner, ip_addr,
36 os_user, terminal, host_name, user_name, session_id)
37 select sys.sysevent, sys.dictionary_obj_type,
38 sys.dictionary_obj_name, sys.dictionary_obj_owner,
39 sys_context ('userenv', 'ip_address'),
40 sys_context ('userenv', 'os_user'),
41 sys_context ('userenv', 'terminal'),
42 sys_context ('userenv', 'host'), ora_login_user,
43 sys_context ('userenv', 'sessionid')
44 from dual;
45 end sp_ddlall;
46 end pkg_ddl;
47 /
Package body created.
2) 创建基于值审计的触发器(触发器的处理动作都源于 pkg_ddl 包,请仔细阅读此包)
SYS@ORA11GR2>create or replace trigger trg_dbddl_alter_log
2 after alter on database
3 begin
4 pkg_ddl.sp_trg_ddlalter;
5 end trg_dbddl_alter_log;
6 /
Trigger created.
SYS@ORA11GR2>create or replace trigger trg_dbddl_create_log
2 after create on database
3 begin
4 pkg_ddl.sp_trg_ddlcreate;
5 end trg_dbddl_create_log;
6 /
Trigger created.
SYS@ORA11GR2>create or replace trigger trg_dbddl_drop_log
2 after drop on database
3 begin
4 pkg_ddl.sp_trg_ddldrop;
5 end trg_dbddl_drop_log;
6 /
trigger created.
--注,下面这个触发器约等同于上面三个触发器
SYS@ORA11GR2>create or replace trigger trg_dbddl_log
2 after ddl on database
3 begin
4 pkg_ddl.sp_ddlall;
5 end trg_dbddl_alter_log;
6 /
Trigger created.
3) 测试:(自行测试)
创建、修改、删除一张表,测试触发器的结果
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31397003/viewspace-2126851/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2126851/