数据库审计（create/alter/drop table、user、tablespace）

一、需求：
要求审计
1、创建、修改、删除表；
2、创建、修改、删除用户；
3、创建、修改、删除表空间；

二、实现：
可以理解为对表、用户表空间的ddl操作进行审计，包括：
CREATE - 创建对象
DROP - 删除对象
ALTER - 删除对象
TRUNCATE - 清除表中所有记录

三、操作步骤
oracle@oracle[/home/oracle]> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 27 12:33:09 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@ORCL> show parameter audit

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
audit_file_dest string /oracle/admin/orcl/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
sys@ORCL> alter system set audit_trail=DB scope=spfile;

System altered.

sys@ORCL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 96471308 bytes
Database Buffers 62914560 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
sys@ORCL>
sys@ORCL> audit table, user, tablespace, alter table;

Audit succeeded.

四、检查验证
sys@ORCL> col audit_option format a20;
sys@ORCL> select audit_option,user_name from DBA_STMT_AUDIT_OPTS;

AUDIT_OPTION USER_NAME
-------------------- ------------------------------------------------------------
TABLE
TABLESPACE
USER
ALTER TABLE

sys@ORCL>
sys@ORCL> conn scott/tiger
Connected.
scott@ORCL> create table test (id int);

Table created.
scott@ORCL> alter table test add name varchar2(10);

Table altered.
scott@ORCL> truncate table test;

Table truncated.

scott@ORCL> drop table test1;

Table dropped.

scott@ORCL> create user test1 identified by test1;

User created.

scott@ORCL> alter user test1 identified by test;

User altered.

scott@ORCL> drop user test1 cascade;

User dropped.

scott@ORCL> create tablespace testtbs datafile '/oracle/oradata/orcl/testdbs01.dbf' size 10m;

Tablespace created.

scott@ORCL> alter tablespace testtbs add datafile '/oracle/oradata/orcl/testdbs02.dbf' size 10m;

Tablespace altered.

scott@ORCL> drop tablespace testtbs including contents and datafiles;

Tablespace dropped.

scott@ORCL> conn / as sysdba
Connected
sys@ORCL> col username format a10;
sys@ORCL> col action_name format a18;
sys@ORCL> select USERNAME ,ACTION_NAME,timestamp from dba_audit_trail order by timestamp;

USERNAME ACTION_NAME TIMESTAMP
---------- ------------------ ------------
SCOTT CREATE TABLE 27-MAR-09
SCOTT ALTER TABLE 27-MAR-09
SCOTT TRUNCATE TABLE 27-MAR-09
SCOTT DROP TABLE 27-MAR-09
SCOTT CREATE USER 27-MAR-09
SCOTT ALTER USER 27-MAR-09
SCOTT DROP USER 27-MAR-09
SCOTT CREATE TABLESPACE 27-MAR-09
SCOTT ALTER TABLESPACE 27-MAR-09
SCOTT DROP TABLESPACE 27-MAR-09

10 rows selected.

sys@ORCL>

五、需注意问题
sysdba的操作不审计

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22049049/viewspace-1023879/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22049049/viewspace-1023879/