oracle11g安全审计-凯发app官方网站

create tablespace stat_log
logging
datafile
'/apps/oracle/oradata/statlog.dbf' size 2048m autoextend on next 128m maxsize 8g
extent management local segment space management auto;
create table stat$log_ddl
(
ddl_date date,
user_name varchar2(30),
ip_addr varchar2(30),
obj_name varchar2(50),
ddl_type varchar2(30),
object_type varchar2(18),
owner varchar2(30),
sql_text varchar2(1000)
) tablespace sta_tlog;

第二步，创建数据库级的ddl触发器，把所有的ddl操作都记录下来

点击(此处)折叠或打开

create or replace trigger ddl_audit after create or alter or drop or truncate or
grant or revoke or rename
on database
declare
ipaddr varchar2(20);
stext varchar2(1000);
begin
begin
select sys_context('userenv', 'ip_address') into ipaddr from dual;
exception when others then
ipaddr:='-';
end;
begin
select sql_text into stext from v$open_cursor where upper(sql_text) like 'alter%';
exception when others then
stext:='-';
end;
insert into sys.stat$log_ddl values
(sysdate,
user,
nvl (ipaddr,'-'),
nvl(ora_dict_obj_name,'-'),
nvl(ora_sysevent,'-'),
nvl(ora_dict_obj_type,'-'),
nvl(ora_dict_obj_owner,'-'),
stext
);
exception when others then
null;
end;
/

审计记录效果如下：

点击(此处)折叠或打开

sql> select ddl_date,user_name,ip_addr,obj_name,ddl_type,sql_text from stat$log_ddl;
ddl_date user_name ip_addr obj_name ddl_type sql_text
------------------- ---------- -------------------- ---------- ---------- --------------------------------------------------
2012-10-25 23:31:40 dba_user - t1 create -
2012-10-25 23:32:32 dba_user - n_test drop -
2012-10-25 23:36:04 dba_user 172.18.130.114 t1 drop -
2012-10-25 23:42:49 dba_user 172.18.130.114 test alter alter table test drop(name)
2012-10-25 23:43:08 dba_user 172.18.130.114 test alter alter table test add(name varchar2(20))
2012-10-25 23:44:10 dba_user 172.18.130.114 test alter alter table test rename to test01
2012-10-25 23:44:44 dba_user 172.18.130.114 test01 rename -
2012-10-25 23:51:31 dba_user 172.18.130.114 test alter alter table test add(addr varchar2(10))
2012-10-25 23:52:12 dba_user 172.18.130.114 test alter alter table test rename column addr to ipaddr
2012-10-26 00:22:10 dba_user 172.18.130.114 byte_test truncate -

阅读(7822) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们