資料庫審計（create/alter/drop table、user、tablespace）

一、需求：
要求審計
1、建立、修改、刪除表；
2、建立、修改、刪除使用者；
3、建立、修改、刪除表空間；

二、實現：
可以理解為對錶、使用者表空間的ddl操作進行審計，包括：
CREATE - 建立物件
DROP - 刪除物件
ALTER - 刪除物件
TRUNCATE - 清除表中所有記錄

三、操作步驟
]> 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

> 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
> alter system set audit_trail=DB scope=spfile;

System altered.

> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
> 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.
>
> audit table, user, tablespace, alter table;

Audit succeeded.

四、檢查驗證
> col audit_option format a20;
> select audit_option,user_name from DBA_STMT_AUDIT_OPTS;

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

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

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

Table altered.
> truncate table test;

Table truncated.

> drop table test1;

Table dropped.

> create user test1 identified by test1;

User created.

> alter user test1 identified by test;

User altered.

> drop user test1 cascade;

User dropped.

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

Tablespace created.

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

Tablespace altered.

> drop tablespace testtbs including contents and datafiles;

Tablespace dropped.

> conn / as sysdba
Connected
> col username format a10;
> col action_name format a18;
> 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.

>

五、需注意問題
sysdba的操作不審計

[@more@]