Oracle 審計 audit

--審計 AUDIT
1）審查可疑活動
如：所有表的刪除
2）監視和收集關於指定資料庫活動的資料
如：哪些表被經常修改

--開啟和禁用審計
audit/noaudit

--審計的型別
語句審計 如：audit create table
許可權審計
物件審計 如：audit select on scott.dept

--audit_trail引數
none：禁用資料庫審計。此引數為預設值。
os：把審計記錄寫到一個作業系統檔案（作業系統審計跟蹤）中。
db：把審計記錄寫入資料庫審計跟蹤（儲存在SYS.AUD$表中），dba_audit_trail。
db_bextended：把所有審計記錄傳送到資料庫審計跟蹤（SYS.AUD$），此外，填充SQLBIND和SQLTEXT CLOB列。
xml：指定資料庫審計，進入OS檔案的是XML格式的審計記錄。
xml_extended：與XML設定相同，另外還記錄所有審計跟蹤列，包括SQLTEXT和SQLBIND。

audit_file_dest 指定審計檔案放置目錄。
 
alter system set audit_trail=OS scope=spfile           (需要重啟資料庫)
alter system set audit_trail=db_extended scope=spfile  (需要重啟資料庫)可以檢視詳細語句的話

sys.aud$表和作業系統檔案儲存審計記錄
select USERID,USERHOST,SQLTEXT from sys.aud$ where userid='HR';

--審計命令
audit session whenever successful
audit session whenever not sucessful

--例子
audit create table by scott;
(noaudit create table by scott; 關閉審計）

create table audit_test (c1 int);
SYS@dbtest> audit insert,update on scott.audit_test by access whenever successful;
Audit succeeded.

SYS@dbtest

> select object_name,object_type,alt,del,ins,upd,sel
  2   from dba_obj_audit_opts;
OBJECT_NAME                    OBJECT_TYPE             ALT   DEL   INS   UPD   SEL
------------------------------ ----------------------- ----- ----- ----- ----- -----
AUDIT_TEST                     TABLE                   -/-   -/-   A/-   A/-   -/-

--相關檢視
dba_audit_trail
user_audit_trail
dba_audit_object

--例子
SYS@ test11g> show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/test11g/
                                                 adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB

SYS@ test11g> alter system set audit_trail=db_extended scope=spfile;
System altered.

SYS@ test11g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ test11g> startup
ORACLE instance started.

Total System Global Area  627732480 bytes
Fixed Size                  1338336 bytes
Variable Size             444597280 bytes
Database Buffers          176160768 bytes
Redo Buffers                5636096 bytes
Database mounted.
Database opened.
SYS@ test11g> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/test11g/
                                                 adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB_EXTENDED

SYS@ test11g> audit create session by ikki;
Audit succeeded.

SYS@ test11g> audit resource by ikki;
Audit succeeded.

[oracle@serv11 app]$ sqlplus ikki/ikki

IKKI@ test11g> create table t1(c1 int, c2 int, c3 int);
Table created.

SYS@ test11g> audit insert, update on ikki.t1 by access whenever successful;
Audit succeeded.

IKKI@ test11g> insert into t1 values(1,2,3);
1 row created.

IKKI@ test11g> commit;
Commit complete.

IKKI@ test11g> select * from t1;
        C1         C2         C3
---------- ---------- ----------
         1          2          3

IKKI@ test11g> update t1 set c3=2
  2  where c1=1;
1 row updated.

IKKI@ test11g> commit;
Commit complete.

SYS@ test11g> set linesize 100
SYS@ test11g> col username for a8
SYS@ test11g> col action_name for a12
SYS@ test11g> col priv_used for a12
SYS@ test11g> col extended_timestamp for a18
SYS@ test11g> col sql_text for a30

SYS@ test11g> select username, action_name, priv_used, extended_timestamp, sql_text
  2  from dba_audit_object   
  3  where username='IKKI';

USERNAME ACTION_NAME  PRIV_USED    EXTENDED_TIMESTAMP SQL_TEXT
-------- ------------ ------------ ------------------ ------------------------------
IKKI     UPDATE                    27-NOV-13 11.03.13 update t1 set c3=2
                                   .905728 AM +08:00  where c1=1

IKKI     INSERT                    27-NOV-13 11.02.13 insert into t1 values(1,2,3)
                                   .252535 AM +08:00

IKKI     CREATE TABLE CREATE TABLE 27-NOV-13 11.00.28 create table t1(c1 int, c2 int
                                   .661674 AM +08:00  , c3 int)

SYS@ test11g> noaudit create session by ikki;
Noaudit succeeded.

SYS@ test11g> noaudit resource by ikki;
Noaudit succeeded.

SYS@ test11g> noaudit insert, update on ikki.t1;
Noaudit succeeded.

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27633655/viewspace-1081552/，如需轉載，請註明出處，否則將追究法律責任。