Oracle11g安全審計–重要帳號的DDL語句操作記錄

如果要審計資料庫中的DDL操作，那麼可以通過DDL觸發器來實現，把資料庫中的所有DDL操作都記錄下來。本例子適用於oracle 9i或更高的版本。操作方法如下：

第一步，建立表空間和相關的日誌表：

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 STAT_LOG;

第二步，建立資料庫級的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%` OR UPPER(sql_text) LIKE `RENAME%` OR UPPER(sql_text) LIKE `DROP%` OR UPPER(sql_text) LIKE `TRUNCATE%` ;
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;
/

DDL操作記錄審計效果如下：

select ddl_date,user_name,ip_addr,obj_name,ddl_type,sql_text from stat$log_ddl;
SQL> /
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 –
10 rows selected.

本文轉自vcdog 51CTO部落格，原文連結：http://blog.51cto.com/255361/1037143，如需轉載請自行聯絡原作者

相關文章