審計DBA使用者操作

審計對於普通dba使用者而言，沒有什麼特別之處，如果是dba使用者觸發了審計條件，一樣會被記錄到審計日誌中。

但是SYS使用者是不同的，因為普通審計對於SYS使用者無效。

 

 

看一個簡單的例子：

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB
SQL> audit create table;

Audit succeeded.

SQL> conn test/test
Connected.
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA

17 rows selected.

SQL> create table t_audit (id number);

Table created.

SQL> select owner, obj_name, action_name, to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss')
  2  from dba_audit_trail
  3  where obj_name = 'T_AUDIT';

OWNER           OBJ_NAME             ACTION_NAME                  TO_CHAR(TIMESTAMP,'
--------------- -------------------- ---------------------------- -------------------
TEST            T_AUDIT              CREATE TABLE                 2010-11-15 21:59:23

如果是SYS執行CREATE TABLE，則不會留下audit記錄：

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> create table t_sys_audit (id number);

Table created.

SQL> select owner, obj_name, action_name, to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss')
  2  from dba_audit_trail
  3  where obj_name = 'T_SYS_AUDIT';

no rows selected

Oracle為了解決SYS使用者的審計問題，引入了初始化引數AUDIT_SYS_OPERATIONS，如果這個引數設定為TRUE，所有SYS操作都會寫入到作業系統的AUDIT記錄中：

SQL> show parameter audit_sys_operations

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE
SQL> alter system set audit_sys_operations = true scope = spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2835349504 bytes
Fixed Size                  2075432 bytes
Variable Size             671089880 bytes
Database Buffers         2147483648 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.
SQL> drop table t_sys_audit;

Table dropped.

SQL> select * from dual;

D
-
X

SQL> show parameter audit_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /opt/ora10g/admin/test08/adump
SQL> select spid from v$process
  2  where addr in (select paddr from v$session
  3  where sid in (select sid from v$mystat where rownum = 1));

SPID
------------
3021

下面檢查對應的audit記錄：

SQL> host more /opt/ora10g/admin/test08/adump/ora_3021.aud
Audit file /opt/ora10g/admin/test08/adump/ora_3021.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1
System name:    Linux
Node name:      yans1
Release:        2.6.9-42.0.0.0.1.ELsmp
Version:        #1 SMP Sun Oct 15 15:13:57 PDT 2006
Machine:        x86_64
Instance name: test08
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 3021, image: oracle@yans1 (TNS V1-V3)

Mon Nov 15 22:38:41 2010
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/1
STATUS: 0

Mon Nov 15 22:38:44 2010
ACTION : 'ALTER DATABASE OPEN'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/1
STATUS: 0

Mon Nov 15 22:43:38 2010
ACTION : 'drop table t_sys_audit'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/1
STATUS: 0

Mon Nov 15 22:43:42 2010
ACTION : 'select * from dual'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/1
STATUS: 0

Mon Nov 15 22:43:50 2010
ACTION : 'SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number',        6,'big int
eger', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER('%audit_file_dest%') OR
DER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/1
STATUS: 0

Mon Nov 15 22:44:48 2010
ACTION : 'select spid from v$process
where addr in (select paddr from v$session
where sid in (select sid from v$mystat where rownum = 1))'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/1
STATUS: 0

可以看到，從以SYS身份登陸開始，到開啟資料庫，執行drop table語句，執行select語句都記錄到audit記錄中，甚至連show parameter引發的sqlplus內部執行的sql語句都記錄了下來。