Oracle11gr2 審計語句增強（一）

在11.2中，Oracle對於AUDIT語句進行了增強。

這一篇介紹將AUDIT的ALL STATEMENT語句。

在以前的版本中，審計只支援AUDIT ALL和AUDIT ALL PRIVILEGES，在11.2中，審計又增加了AUDIT ALL STATEMENTS，可以審計所有的頂級的SQL語句。

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> AUDIT ALL STATEMENTS;

審計已成功。

SQL> SELECT USER_NAME, AUDIT_OPTION, SUCCESS, FAILURE
  2  FROM DBA_STMT_AUDIT_OPTS;

USER_NAME                      AUDIT_OPTION                             SUCCESS    FAILURE
------------------------------ ---------------------------------------- ---------- ----------
                               ALTER SYSTEM                             BY ACCESS  BY ACCESS
                               SYSTEM AUDIT                             BY ACCESS  BY ACCESS
                               CREATE SESSION                           BY ACCESS  BY ACCESS
                               CREATE USER                              BY ACCESS  BY ACCESS
                               ALTER USER                               BY ACCESS  BY ACCESS
                               DROP USER                                BY ACCESS  BY ACCESS
                               PUBLIC SYNONYM                           BY ACCESS  BY ACCESS
                               DATABASE LINK                            BY ACCESS  BY ACCESS
                               ROLE                                     BY ACCESS  BY ACCESS
                               PROFILE                                  BY ACCESS  BY ACCESS
                               CREATE ANY TABLE                         BY ACCESS  BY ACCESS
                               ALTER ANY TABLE                          BY ACCESS  BY ACCESS
                               DROP ANY TABLE                           BY ACCESS  BY ACCESS
                               ALL STATEMENTS                           BY SESSION BY SESSION
                               CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS
                               GRANT ANY ROLE                           BY ACCESS  BY ACCESS
                               SYSTEM GRANT                             BY ACCESS  BY ACCESS
                               ALTER DATABASE                           BY ACCESS  BY ACCESS
                               CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS
                               ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS
                               DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS
                               ALTER PROFILE                            BY ACCESS  BY ACCESS
                               DROP PROFILE                             BY ACCESS  BY ACCESS
                               GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS
                               CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS
                               EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS
                               GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS
                               CREATE ANY JOB                           BY ACCESS  BY ACCESS
                               CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS

已選擇29行。

下面利用其他使用者執行一些操作：

SQL> SHOW USER
USER 為 "YANGTK"
SQL> CREATE TABLE T_TEST (ID NUMBER);

表已建立。

SQL> INSERT INTO T_TEST VALUES (1);

已建立 1 行。

SQL> SELECT * FROM T_TEST;

        ID
----------
         1

SQL> DROP TABLE T_TEST PURGE;

表已刪除。

檢查審計結果：

SQL> SELECT USERID, ACTION#, OBJ$NAME
  2  FROM SYS.AUD$
  3  WHERE OBJ$NAME = 'T_TEST';

USERID                            ACTION# OBJ$NAME
------------------------------ ---------- ----------
YANGTK                                  1 T_TEST
YANGTK                                  2 T_TEST
YANGTK                                  3 T_TEST
YANGTK                                 12 T_TEST

透過使用NOAUDIT ALL STATEMENTS語句，可以關閉所有語句的統計：

SQL> NOAUDIT ALL STATEMENTS;

審計未成功。