oracle審計簡單設定

資料庫審計

引數：audit_trail
SQL> show parameter audit
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /data/admin/oral/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string     
audit_trail                          string      NONE

引數查詢：
可看是否靜態引數v$system_parameter
檢視audit_trail可設定的值：v$parameter_valid_values
 
 
配置審計需要重啟例項，配置之前，需要備份好spfile;
create pfile='/oracle/app/product/10.2/dbs/pfile826.ora' from spfile;
alter system set AUDIT_TRAIL =DB_EXTENDED scope=spfile;
alter system set audit_sys_operations=true scope=spfile;
shutdown immediate
startup

如需關閉審計
alter system set audit_trail=none scope=spfile;
重啟資料庫

如果審計表aud$不存在,需要手工建立
SQL> conn / as sysdba
 SQL> @?/rdbms/admin/cataudit.sql

將審計記錄表移動到新的表空間
select * from aud$ ;
create tablespace aud datafile '/data/oradata/oral/aud01.dbf' size 10m autoextend on;
alter table aud$ move tablespace aud;
alter table aud$ move lob(sqlbind) store as (tablespace aud);
alter table aud$ move lob(sqltext) store as (tablespace aud);
alter index I_AUD1 rebuild tablespace aud;

如需刪除審計記錄，則將sys.aud$表中記錄刪除，將釋放表空間；

開通的審計策略
如何檢測潛在的登入攻擊
audit create session by access whenever not successful;
audit connect by access whenever not successful;

對使用者的所有行為進行審計
audit all by scott;

可檢視到審計策略；
select * from dba_stmt_audit_opts ;--語句審計：statement
select * from dba_priv_audit_opts ; ---系統許可權審計狀態
select * from dba_obj_audit_opts ;--物件許可權審計狀態，中S:SESSION, A:ACCESS；按成功次數進行審計：audit select on t by access；SESSION 意思就是隻記錄一次成功；是預設值；

查詢審計結果：
select * from dba_audit_trail ;   ---審計結果存放的表
select * from dba_fga_audit_trail;
select * from dba_common_audit_trail;

DBA_AUDIT_EXISTS;列出audit not exists和audit exists產生的審計跟蹤,我們預設的都是audit exists.
DBA_AUDIT_OBJECT;可以查詢所有物件跟蹤資訊.(例如,對grant,revoke等記錄),資訊完全包含於dba_audit_trail
DBA_AUDIT_SESSION;所得到的資料都是有關logon或者logoff的資訊.
DBA_AUDIT_STATEMENT;列出grant ,revoke ,audit ,noaudit ,alter system語句的審計跟蹤資訊.
ALL_DEF_AUDIT_OPTS

檢視審計內容，主要欄位：os_username, userhost, timestamp, owner，sql_bind, sql_text
SQL> select * from dba_audit_trail where  owner = 'AUDIT_TEST' order by timestamp;
注意：owner的值必須大寫，例如 owner = 'AUDIT_TEST'

輔助檢視
1.SYS.AUD$
審計功能的底層檢視,如果需要對資料進行刪除,只需要對aud$檢視進行刪除既可,其他檢視裡的資料都是由aud$所得.
select * from sys.audit_actions;---審計的可用命令動作，可以查詢出在aud$等檢視中actions列的含義
select * from SYSTEM_PRIVILEGE_MAP;--可以查詢出aud$等檢視中priv$used列的含義(注意前面加'-')

審計策略示例：
對某使用者某動作開啟審計：audit create session by a;
停止審計：noaudit create session;
對錶開啟查詢審計：audit select on t whenever successful;
對使用者a執行的語句中有table的進行審計： audit table by a;
audit CREATE TABLE by scott;
 audit CREATE TABLE, CREATE VIEW, ALTER USER;
 audit INDEX;  --包括CREATE INDEX, DROP INDEX, ALTER INDEX and ANALYZE INDEX
 audit INDEX by scott;
 audit ALL whenever SUCCESSFUL;
 AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;
 audit select any table;
 audit select any table, delete any table by scott, system;
 audit select on SCOTT.EMP whenever successful;
 audit delete on SCOTT.EMP by access;
 audit ALL on SCOTT.EMP;
 audit select on DEFAULT;
 AUDIT NETWORK;
 AUDIT ROLE WHENEVER NOT SUCCESSFUL;
 AUDIT CREATE ANY DIRECTORY;
 

審計速查
Quick Reference to Auditing Information

 Database Audit mode
 ~~~~~~~~~~~~~~~~~~~
 show parameter audit

 AUDIT_TRAIL   --&gt DB, DB_EXTENDED, OS, XML, XML_EXTENDED, FALSE or NONE
 AUDIT_FILE_DEST --&gt Audit File location
 AUDIT_SYS_OPERATIONS --&gt Controls whether the activities of SYSDBA are audited or not.
 AUDIT_SYSLOG_LEVEL    --&gt specifies a SYSLOG facility that will receive the audit information

 What Statements are being audited ?
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 To set audit:

 AUDIT [option] [BY user|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]

 select * from dba_stmt_audit_opts where USER_NAME='...';

 Columns are:
 AUDIT_OPTION from STMT_AUDIT_OPTION_MAP
 SUCCESS 'BY SESSION', 'BY ACCESS' or 'NOT SET'
 FAILURE ""

 What Privileges are being audited ?
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 To set audit:
 AUDIT [option] [BY user|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]

 select * from dba_priv_audit_opts where USER_NAME='...';

 Columns are:
 PRIVILEGE from SYSTEM_PRIVILEGE_MAP
 SUCCESS 'BY SESSION', 'BY ACCESS' or 'NOT SET'
 FAILURE ""

 What Objects are being audited ?
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 To set Auditing:
 AUDIT [object_option] ON [schema].object|DEFAULT [BY SESSION|ACCESS]
 [WHENEVER {NOT} SUCCESSFUL]

 select * from dba_obj_audit_opts where owner='..' and OBJECT_NAME='...';
 select * from all_def_audit_opts;

 Columns are:
 ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA

 X/Y - is no option set
 X is when successful
 Y is when Unsuccessful
 S set by session
 A set by access

 Audit results
 ~~~~~~~~~~~~~
 Raw results can go to various places depending on the value of parameter AUDIT_TRAIL:

 - when audit_trail is DB or DB_EXTENDED the audit data will go to AUD$ (DBA_AUDIT_TRAIL is a view on top of this table ).
 Main where columns are: USERNAME, TIMESTAMP, OWNER
 - when audit_trail is OS or XML or XML_EXTENDED the audit data will be written to files located in the AUDIT_FILE_DEST directory
 - when AUDIT_SYSLOG_LEVEL is defined and audit_trail is set to OS the audit data will be sent to SYSLOG

 For underlying results see:

 Select STATEMENT, TIMESTAMP, ACTION, USERID from AUD$;

 Auditing administrative connections
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 The administrative user connections (CONNECT / AS SYSDBA or CONNECT / AS SYSOPER) are always logged regardless of audit setting.
 On UNIX platforms these are logged to *.aud files in $ORACLE_HOME/rdbms/audit when the instance is stopped and to AUDIT_FILE_DEST
 when the instance is started regardless of any init.ora parameter settings. See Note 103964.1 for more details.
 ---------------------------------------------------