审计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语句都记录了下来。

 

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