oracle数据库审计(四)

Thu Dec 5 16:22:00 2013 +08:00
LENGTH : '160'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[10] '2745484551'
注意到其他用户信息则无法审计。那么就需要看一下另一个参数：audit_trail
该参数具有如下值：
AUDIT_TRAIL = { none | os | db [, extended] | xml [, extended] }
none 表示不启用审计
os 表示审计文件信息在操作系统存储，这是oracle建议的
db 表示审计信息记录在sys.aud$视图中
db，extended 表示审计信息记录在sys.aud$视图中，其中包含sql text和sql bind
xml 表示在操作系统以xml格式记录审计日志
xml 表示在操作系统以xml格式记录审计日志，其中包含sql text和sql bind
其中这些审计信息可以通过查看dba_audit_trait以及aud$。
EG:
SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/app/oracle/admin/RHYS/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
SQL> show user
USER is "SYS"
SQL> select os_username,username,timestamp,obj_name,ACTION_NAME,audit_option,sql_text,sql_bind from dba_audit_trail where owner='SCOTT' ORDER BY TIMESTAMP DESC;

no rows selected

SQL> audit select table,update table,delete table,insert table by scott by access;

Audit succeeded.

SQL> grant select on dba_audit_trail to scott
2 ;

Grant succeeded.

SQL> conn scott/root
Connected.

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL>
SQL> select os_username,username,timestamp,obj_name,ACTION_NAME,audit_option,sql_text,sql_bind from dba_audit_trail where owner='SCOTT' ORDER BY TIMESTAMP DESC;

OS_USERNAME USERNAME TIMESTAMP OBJ_NAME ACTION_NAME AUDIT_OPTION SQL_TEXT SQL_BIND
-------------------- ---------- --------- --------------- ---------------------------- -------------------- ------------------------------ ------------------------------
oracle SCOTT 15-DEC-13 EMP SELECT
oracle SCOTT 15-DEC-13 DEPT SELECT

SQL>
本次是采用语句审计，还可以采用会话审计。http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4007.htm#SQLRF53735
语句审计：

SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/app/oracle/admin/RHYS/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
SQL>
SQL>
SQL> alter system set audit_trail=db,extended scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 440402984 bytes
Database Buffers 180355072 bytes
Redo Buffers 3313664 bytes
Database mounted.
Database opened.
SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/app/oracle/admin/RHYS/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB, EXTENDED
SQL>
SQL> conn scott/root
Connected.
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select os_username,username,timestamp,obj_name,ACTION_NAME,audit_option,sql_text,sql_bind from dba_au