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 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,如需转载请自行联系原作者