Oracle11g安全审计--重要帐号的DDL语句操作记录

 

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