Oracle Audit 審計 說明

roominess發表於2012-03-31
Oracle

一. 官網說明

1.1 Oracle 11gR2 concepts

From：

http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/ds_concepts003.htm#ADMIN12108

 

1.1.1 Database Auditing

       Databaseauditing is the monitoring and recording of selected user database actions. You can use standard auditing to audit SQL statements,privileges, schemas, objects, and network and multitier activity.Alternatively, you can use fine-grained auditingto monitor specific database activities, such as actions on a database table ortimes that activities occur. For example, you can audit a table accessed after9:00 p.m.

 

Reasons for using auditing include:

       （1）Enabling future accountability for current actions

       （2）Deterring users (or others, such as intruders) from inappropriateactions based on their accountability

       （3）Investigating, monitoring, and recording suspicious activity

       （4）Addressing auditing requirements for compliance

 

1.1.2  Oracle Audit Vault

       OracleAudit Vault enables you to consolidate, report, and configure alerts foraudited data. You can consolidate audit data generated by Oracle Database andother relational databases. You can also use Oracle Audit Vault to monitoraudit settings on target databases.

 

 

1.2 Oracle 10gR2 concept

From：

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/security.htm#i12374

 

1.2.1 Overview of Database Auditing

      Auditingis the monitoring and recording of selected user database actions. It can bebased on individual actions, such as the type of SQL statement run, or oncombinations of factors that can include name, application, time, and so on.Security policies can cause auditing when specified elements in an Oracledatabase are accessed or altered, including content.

 

Auditing is generally used to:

       （1）Enable future accountability for current actions taken in aparticular schema, table, or row, or affecting specific content

       （2）Investigate suspicious activity. For example, if an unauthorizeduser is deleting data from tables, then the security administrator could auditall connections to the database and all successful and unsuccessful deletionsof rows from all tables in the database.

       （3）Monitor and gather data about specific database activities. Forexample, the database administrator can gather statistics about which tablesare being updated, how many logical I/Os are performed, or how many concurrentusers connect at peak times.

 

       Youcan use Enterprise Manager to view and configure audit-related initializationparameters and administer audited objects for statement auditing and schemaobject auditing. For example, Enterprise Manager shows the properties forcurrent audited statements, privileges, and objects. You can view theproperties of each object, and you can search audited objects by theirproperties. You can also turn on and turn off auditing on objects, statements,and privileges.

 

1.2.2 Types and Records of Auditing

Oracle allows audit options to be focused or broad. You can audit:

       （1）Successful statement executions, unsuccessful statement executions,or both

       （2）Statement executions once in each user session or once every timethe statement is run

       （3）Activities of all users or of a specific user

 

Oracle auditing enablesthe use of several different mechanisms, with the following features:

Table 20-1 Types of Auditing

 

                         
  

Type of Auditing

  		  

Meaning/Description

  
 

Statement auditing

 		 

Audits SQL statements by type of  statement, not by the specific schema objects on which they operate.  Typically broad, statement auditing audits the use of several types of  related actions for each option. For example, AUDIT TABLE tracks several DDL  statements regardless of the table on which they are issued. You can also set  statement auditing to audit selected users or every user in the database.

 
 

Privilege auditing

 		 

Audits the use of  powerful system privileges enabling corresponding actions, such as AUDIT CREATE  TABLE. Privilege auditing is more focused than statement auditing because it  audits only the use of the target privilege. You can set privilege auditing  to audit a selected user or every user in the database.

 
 

Schema object auditing

 		 

Audits specific  statements on a particular schema object, such as AUDIT SELECT ON employees.  Schema object auditing is very focused, auditing only a specific statement on  a specific schema object. Schema object auditing always applies to all users  of the database.

 
 

Fine-grained auditing

 		 

Audits data access  and actions based on content. Using DBMS_FGA, the security administrator  creates an audit policy on the target table. If any rows returned from a DML  statement block match the audit condition, then an audit event entry is  inserted into the audit trail.

 

 

1.2.3Audit Records and the Audit Trails

       Audit records includeinformation such as the operation that was audited, the user performing theoperation, and the date and time of the operation. Audit records can be storedin either a data dictionary table, called the databaseaudit trail, or in operating system files, calledan operating system audit trail.

 1.2.3.1 DatabaseAudit Trail

       The database audit trail is a single table named SYS.AUD$ in the SYS schema ofeach Oracle database's data dictionary. Several predefined views are providedto help you use the information in this table.

       Audit trail records can contain different types ofinformation, depending on the events audited and the auditing options set.The following information is always included in each audit trail record, if theinformation is meaningful to the particular audit action:

（1）User name

（2）Instance number

（3）Process identifier

（4）Session identifier

（5）Terminal identifier

（6）Name of the schema object accessed

（7）Operation performed or attempted

（8）Completion code of the operation

（9）Date and time stamp

（10）System privileges used

 1.2.3.2 Auditing in a DistributedDatabase

       Auditing is siteautonomous. An instance audits only the statements issued by directly connectedusers. A local Oracle node cannot audit actions that take place in a remotedatabase. Because remote connections are established through the user accountof a database link, statements issued through the database link's connectionare audited by the remote Oracle node.

 1.2.3.3 OperatingSystem Audit Trail

       Oracleallows audit trail records to be directed to an operating system audit trail ifthe operating system makes such an audit trail available to Oracle. If not, then audit records are written to a file outside thedatabase, with a format similar to other Oracle trace files.

       Oracleallows certain actions that are always audited to continue, even when theoperating system audit trail (or the operating system file containing auditrecords) is unable to record the audit record. The usual cause of this is thatthe operating system audit trail or the file system is full and unable toaccept new records.

       Systemadministrators configuring operating system auditing should ensure that theaudit trail or the file system does not fill completely. Most operating systemsprovide administrators with sufficient information and warning to ensure thisdoes not occur. Note, however, that configuring auditing to use the databaseaudit trail removes this vulnerability, because the Oracle database serverprevents audited events from occurring if the audit trail is unable to acceptthe database audit record for the statement.

 1.2.3.4 Operating System AuditRecords

       Theoperating system audit trail is encoded, but it is decoded in data dictionaryfiles and error messages.

       （1）Action code describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table describes thesecodes.

       （2）Privileges used describes any system privileges used to perform. theoperation. The SYSTEM_PRIVILEGE_MAP table describesall of these codes.

       （3）Completion code describes the result of the attempted operation.Successful operations return a value of zero, and unsuccessful operationsreturn the Oracle error code describing why the operation was unsuccessful.

 1.2.3.5 RecordsAlways in the Operating System Audit Trail

       Somedatabase-related actions are always recorded into the operating system audittrail regardless of whether database auditing isenabled:

       （1）At instance startup, anaudit record is generated that details the operating system user starting theinstance, the user's terminal identifier, the date and time stamp, and whetherdatabase auditing was enabled or disabled. Thisinformation is recorded into the operating system audit trail, becausethe database audit trail is not available until after startup has successfullycompleted. Recording the state of database auditing at startup also acts as anauditing flag, inhibiting an administrator from performing unaudited actions byrestarting a database with database auditing disabled.

       （2）At instance shutdown, anaudit record is generated that details the operating system user shutting downthe instance, the user's terminal identifier, the date and time stamp.

       （3）During connections with administrator privileges,an audit record is generated that details the operating system user connectingto Oracle with administrator privileges. This recordprovides accountability regarding users connected with administratorprivileges.

 

       Onoperating systems that do not make an audit trail accessible to Oracle, theseaudit trail records are placed in an Oracle audit trail file in the samedirectory as background process trace files.

 1.2.3.6 When Are Audit RecordsCreated?

       Anyauthorized database user can set his own audit options at any time, but the recording of audit information is enabled or disabled bythe security administrator.

       When auditing is enabled in the database, an audit record isgenerated during the execute phase of statement execution.

       SQL statements inside PL/SQL programunits are individually audited, as necessary, when the program unit is run.

       The generation and insertion of an audittrail record is independent of a user's transaction being committed. That is, even if a user's transaction is rolled back, theaudit trail record remains committed.

       Statement and privilegeaudit options in effect at the time a database user connects to the databaseremain in effect for the duration of the session. Setting or changing statementor privilege audit options in a session does not cause effects in that session.The modified statement or privilege audit options take effect only when thecurrent session is ended and a new session is created. In contrast, changes toschema object audit options become effective for current sessions immediately.

       Operations by the SYS user and by users connected through SYSDBAor SYSOPER can be fully audited with the AUDIT_SYS_OPERATIONS initializationparameter. Successful SQL statements from SYS are auditedindiscriminately. The audit records for sessions established by the user SYS orconnections with administrative privileges are sent to an operating systemlocation. Sending them to a location separate from the usual database audittrail in the SYS schema provides for greater auditing security.

 

二.  Audit說明

2.1 審計

審計（Audit)用於監視使用者所執行的資料庫操作，審計記錄可存在資料字典表（稱為審計記錄:儲存在system表空間中的 SYS.AUD$表中，可通過檢視dba_audit_trail檢視）或作業系統審計記錄中(預設位置為$ORACLE_BASE/admin/$ORACLE_SID/adump/).。預設情況下審計是沒有開啟的。

當資料庫的審計是使能的，在語句執行階段產生審計記錄。審計記錄包含有審計的操作、使用者執行的操作、操作的日期和時間等資訊。

不管你是否開啟資料庫的審計功能，以下這些作業系統會強制記錄：用管理員許可權連線Instance；啟動資料庫；關閉資料庫。

 2.1.1 Oracle審計功能

審計是對選定的使用者動作的監控和記錄，通常用於：

    審查可疑的活動。例如：資料被非授權使用者所刪除，此時安全管理員可決定對該資料庫的所有連線進行審計，以及對資料庫的所有表的成功地或不成功地刪除進行審計。

監視和收集關於指定資料庫活動的資料。例如：DBA可收集哪些被修改、執行了多少次邏輯的I/O等統計資料。

 2.1.2 ORACLE所允許的審計選擇限於下列方面：

   審計語句的成功執行、不成功執行，或者其兩者。

   對每一使用者會話審計語句執行一次或者對語句每次執行審計一次。

   對全部使用者或指定使用者的活動的審計。

 2.1.3 審計相關的表安裝

 SQLPLUS> connect / AS SYSDBA

 SQLPLUS> select * from sys.aud$;     --沒有記錄返回 

 SQLPLUS> select * from dba_audit_trail;   - 沒有記錄返回

       如果做上述查詢的時候發現表不存在，說明審計相關的表還沒有安裝，需要安裝。

 

 SQLPLUS> connect / as sysdba

 SQLPLUS> @$ORACLE_HOME/rdbms/admin/cataudit.sql

       審計表安裝在SYSTEM表空間。所以要確保SYSTEM表空間又足夠的空間存放審計資訊。

       安裝後要重啟資料庫

 2.1.4 將審計相關的表移動到其他表空間

       由於AUD$表等審計相關的表存放在SYSTEM表空間，因此為了不影響系統的效能，保護SYSTEM表空間，最好把AUD$移動到其他的表空間上。可以使用下面的語句來進行移動：

 

sql>connect / as sysdba;

sql>alter table aud$ move tablespace;

sql>alter index I_aud1 rebuild onlinetablespace ;

SQL> alter table audit$ move tablespace;

SQL> alter index i_audit rebuild onlinetablespace ;

SQL> alter table audit_actions movetablespace ;

SQL> alter index i_audit_actions rebuildonline tablespace ;

 2.1.5  truncate 或者 delete sys.aud$ 表

       在delete 之前，可以先把aud$表exp備份一下，注意，不要直接exp，先建立一張臨時表，然後將臨時表exp。

       sql>createtable audit_record tablespace users as select * from sys.aud$;

然後exp：

       exptables=AUDIT_RECORD file=audit_record.dmp

最後delete 資料：

sql>delete from sys.aud$;

 或者刪除指定表的審計：

sql>delete from sys.aud$ whereobj$name='&table_nmae';
注意，delete 不會釋放system表空間。 可以使用truncate table：

sql>truncate table sys.aud$

 2.2 和審計相關的兩個主要引數

2.2.1 Audit_sys_operations

       AUDIT_SYS_OPERATIONSenables or disables the auditing of top-level operations, which are SQL statementsdirectly issued by users when connecting with SYSDBA or SYSOPER privileges.(SQL statements run from within PL/SQL procedures or functions are notconsidered top-level.) The audit records are written to the operating system'saudit trail. The audit records will be written in XML format if the AUDIT_TRAILinitialization parameter is set to xml or xml, extended.

       OnUNIX platforms, if the AUDIT_SYSLOG_LEVEL parameter has also been set, then itoverrides the AUDIT_TRAIL parameter and SYS audit records are written to thesystem audit log using the SYSLOG utility.

 http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams015.htm#REFRN10005

        預設為false，當設定為true時，所有sys使用者（包括以sysdba, sysoper身份登入的使用者）的操作都會被記錄，audit trail不會寫在aud$表中，這個很好理解，如果資料庫還未啟動aud$不可用，那麼像conn /as sysdba這樣的連線資訊，只能記錄在其它地方。如果是windows平臺，audti trail會記錄在windows的事件管理中，如果是linux/unix平臺則會記錄在audit_file_dest引數指定的檔案中。

 SYS@dave2(db2)> show parameteraudit_file_dest

NAME                      TYPE       VALUE

----------------------------------------------- ------------------------------

audit_file_dest        string     /u01/app/oracle/admin/dave2/adump

 2.2.2 Audit_trail

AUDIT_TRAIL enables or disables databaseauditing.

Values:

（1）none：Disables standard auditing. This value is thedefault if the AUDIT_TRAIL parameter was not set in the initializationparameter file or if you created the database using a method other thanDatabase Configuration Assistant. If you created the database using DatabaseConfiguration Assistant, then the default is db.

（2）os：Directs all audit records to an operating system file. Oraclerecommends that you use the os setting, particularly if you are using anultra-secure database configuration.

（3）db：Directs audit records to the database audit trail (the SYS.AUD$table), except for records that are always written to the operating systemaudit trail. Use this setting for a general database for manageability.

If the database was started in read-onlymode with AUDIT_TRAIL set to db, then Oracle Database internally sets AUDIT_TRAILto os. Check the alert log for details.

（4）db, extended：Performs all actions of AUDIT_TRAIL=db,and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$table, when available. These two columns are populated only when this parameteris specified.

If the database was started in read-onlymode with AUDIT_TRAIL set to db, extended, then Oracle Database internally setsAUDIT_TRAIL to os. Check the alert log for details.

（5）xml：Writes to the operating system audit record file in XML format.Records all elements of the AuditRecord node except Sql_Text and Sql_Bind tothe operating system XML audit file.

（6）xml, extended：Performs all actions of AUDIT_TRAIL=xml,and populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$table, wherever possible. These columns are populated only when this parameteris specified.

       Youcan use the SQL AUDIT statement to set auditing options regardless of thesetting of this parameter.

 

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams017.htm#REFRN10006

 

None：是預設值，不做審計；

DB：將audit trail 記錄在資料庫的審計相關表中，如aud$，審計的結果只有連線資訊；

DB,Extended：這樣審計結果裡面除了連線資訊還包含了當時執行的具體語句；

OS：將audit trail 記錄在作業系統檔案中，檔名由audit_file_dest引數指定；

XML：10g裡新增的。

注：這兩個引數是static引數，需要重新啟動資料庫才能生效。

 

2.3審計級別

當開啟審計功能後，可在三個級別對資料庫進行審計：Statement(語句)、Privilege（許可權）、object（物件）。

 2.3.1  Statement

       語句審計，對某種型別的SQL語句審計，不指定結構或物件。比如audit table 會審計資料庫中所有的create table,droptable,truncate table語句，alter session by cmy會審計cmy使用者所有的資料庫連線。

2.3.2  Privilege

       許可權審計，當使用者使用了該許可權則被審計，如執行grant selectany table to a，當執行了auditselect any table語句後，當使用者a 訪問了使用者b的表時（如select * from b.t）會用到select any table許可權，故會被審計。注意使用者是自己表的所有者，所以使用者訪問自己的表不會被審計。

2.3.3  Object

物件審計，對一特殊模式物件上的指定語句的審計. 如審計on關鍵字指定物件的相關操作，如aduitalter,delete,drop,insert on cmy.t by scott; 這裡會對cmy使用者的t表進行審計，但同時使用了by子句，所以只會對scott使用者發起的操作進行審計。

        注意：Oracle沒有提供對schema中所有物件的審計功能，只能一個一個物件審計，對於後面建立的物件，Oracle則提供on default子句來實現自動審計，比如執行audit drop on defaultby access;後，對於隨後建立的物件的drop操作都會審計。但這個default會對之後建立的所有資料庫物件有效，似乎沒辦法指定只對某個使用者建立的物件有效，想比 trigger可以對schema的DDL進行“審計”，這個功能稍顯不足。

 2.4審計的一些其他選項

2.4.1 by access / by session

by access  每一個被審計的操作都會生成一條audit trail。

by session 一個會話裡面同型別的操作只會生成一條audit trail，預設為by session。

 2.4.2 whenever [not] successful

whenever successful 操作成功(dba_audit_trail中returncode欄位為0) 才審計,

whenever not successful 反之。省略該子句的話，不管操作成功與否都會審計。

 

2.5 和審計相關的檢視

2.5.1 dba_audit_trail：儲存所有的audit trail，實際上它只是一個基於aud$的檢視。其它的檢視dba_audit_session,dba_audit_object,dba_audit_statement都只是dba_audit_trail的一個子集。

 

2.5.2 dba_stmt_audit_opts：可以用來檢視statement審計級別的audit options，即資料庫設定過哪些statement級別的審計。dba_obj_audit_opts,dba_priv_audit_opts檢視功能與之類似

 

2.5.3 all_def_audit_opts：用來檢視資料庫用on default子句設定了哪些預設物件審計。

 

2.6取消審計

將對應審計語句的audit改為noaudit即可，

如audit sessionwhenever successful

對應的取消審計語句為noauditsession whenever successful;

 

三.  Fine-grainedauditing(FGA) 細粒度審計

       細粒度審計(FGA):精細審計 ，是在 Oracle 9i 中引入的，能夠記錄 SCN 號和行級的更改以重建舊的資料，但是它們只能用於 select 語句，而不能用於 DML ，如 update 、insert 和delete 語句。因此，對於 Oracle 資料庫 10g 之前的版本，使用觸發器雖然對於以行級跟蹤使用者初始的更改是沒有吸引力的選擇，但它也是唯一可靠的方法。 10g 之後版本可以audit 所有DML。FGA的實現基於DBMS_FGA包。它屬於SYS使用者。

 

3.1 增加 FGA 策略


-- 審計表
SQL>grant resource,connect to bank identified by bank;

create table bank.accounts
(
  acct_no number primary key,
  cust_id number not null ,
  balance number(15,2) null
);
insert into bank.accounts values(1,1,10000);
insert into bank.accounts values(2,2,20000);
commit;

Begin
dbms_fga.drop_policy (
  object_schema=>'BANK',
  object_name=>'ACCOUNTS',
  policy_name=>'ACCOUNTS_ACCESS');


dbms_fga.add_policy (
  object_schema=>'BANK',
  object_name=>'ACCOUNTS',
  policy_name=>'ACCOUNTS_ACCESS');
end;
/

select * from bank.accounts;
select timestamp, db_user,os_user,object_schema,object_name,sql_text  from dba_fga_audit_trail;

-- 審計列和審計條件, 在add_policy中加入
--   audit_column => 'BALANCE'
--   audit_condition => 'BALANCE >=11000'


Begin
dbms_fga.drop_policy (
  object_schema=>'BANK',
  object_name=>'ACCOUNTS',
  policy_name=>'ACCOUNTS_ACCESS');

dbms_fga.add_policy (
  object_schema=>'BANK',
  object_name=>'ACCOUNTS',
  audit_column => 'BALANCE',
  audit_condition => 'BALANCE >=11000',
  policy_name=>'ACCOUNTS_ACCESS');
end;
/

select BALANCE from bank.accounts;
select timestamp, db_user,os_user,object_schema,object_name,sql_text  from dba_fga_audit_trail;


3.2  管理 FGA 策略


--要刪除策略，您可以使用以下語句：
begin
dbms_fga.drop_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS'
   );
end;
/

-- 對於更改策略而言，沒有隨取隨用的解決方案。要更改策略中的任何引數，必須刪除策略，再使用更改後的引數新增策略。


-- 需要臨時禁用審計收集

       例如，如果您希望將線索表移動到不同的表空間或者要刪除線索表。您可以按如下方法禁用 FGA 策略：
begin
dbms_fga.enable_policy (
object_schema => 'BANK',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_ACCESS',
enable => FALSE );
end;
/
-- 重新啟用很簡單 enable =>TRUE;

--演示何時審計操作以及何時不審計操作的各種情況 SQL 語句審計狀態:

select balance from bank.accounts;  

       進行審計。使用者選擇了在新增策略時所指定的審計列 BALANCE。


select * from  bank.accounts;     

       進行審計。即使使用者沒有明確指定列 BALANCE，* 也隱含地選擇了它。


select cust_id from bank.accounts where balance < 10000;

       進行審計。即使使用者沒有明確指定列 BALANCE，where 子句也隱含地選擇了它。


select cust_id from bank.accounts;  

       不進行審計。使用者沒有選擇列 BALANCE。
select count(*) from bank.accounts; 

       不進行審計。使用者沒有明確或隱含地選擇列 BALANCE。



3.3  處理器模組


        FGA 的功能不只是記錄審計線索中的事件；FGA 還可以任意執行過程.過程可以執行一項操作，比如當使用者從表中選擇特定行時向審計者傳送電子郵件警告，或者可以寫到不同的審計線索中。這種儲存程式碼段可以是獨立的過程或者是程式包中的過程，稱為策略的處理器模組。
       實際上由於安全性原因，它不必與基表本身處於同一模式中，您可能希望特意將它放置在不同的模式中。由於只要 SELECT 出現時過程就會執行，非常類似於 DML 語句啟動的觸發器，您還可以將其看作 SELECT 語句觸發器。


-- 以下引數指定將一個處理器模組指定給策略：
（1）handler_schema 擁有資料過程的模式
（2）handler_module 過程名稱
（3）處理器模組還可以採用程式包的名稱來代替過程名稱。在這種情況下，引數handler_module 在package.procedure 的格式中指定。



3.4  FGA 資料字典檢視


       FGA 策略的定義位於資料字典檢視 DBA_AUDIT_POLICIES 中。

       審計線索收集在 SYS 擁有的表 FGA_LOG$ 中。對於 SYS 擁有的任何原始表，此表上的某些檢視以對使用者友好的方式顯示資訊。DBA_FGA_AUDIT_TRAIL是該表上的一個檢視。
       一個重要的列是 SQL_BIND，它指定查詢中使用的繫結變數的值，這是顯著增強該工具功能的一項資訊。
       另一個重要的列是 SCN，當發生特定的查詢時，它記錄系統更改號。此資訊用於識別使用者在特定時間看到了什麼，而不是現在的值，它使用了閃回查詢，這種查詢能夠顯示在指定的 SCN 值時的資料。


3.5 檢視和 FGA


       到目前為止已經討論了在表上應用 FGA；現在讓我們來看如何在檢視上使用 FGA。假定在 ACCOUNTS 表上定義檢視 VW_ACCOUNTS 如下：

create view bank.vw_accounts as select * from bank.accounts;

select * from bank.vw_accounts;
select timestamp, db_user,os_user,object_schema,object_name,sql_text  from dba_fga_audit_trail;

       如果您只希望審計對檢視的查詢而不是對錶的查詢，可以對檢視本身建立策略。通過將檢視名稱而不是表的名稱傳遞給打包的過程dbms_fga.add_policy 中的引數 object_name，可以完成這項工作。
       隨後 DBA_FGA_AUDIT_TRAIL 中的 OBJECT_NAME 列將顯示檢視的名稱，並且不會出現有關表訪問的附加記錄。


3.6 其它用途


       除了記錄對錶的選擇訪問，FGA 還可用於某些其它情況：
       （1）可以對資料倉儲使用 FGA，以捕獲特定的表、檢視或物化檢視上發生的所有語句，這有助於計劃索引。不需要到 V$SQL 檢視去獲取這些資訊。即使 SQL 語句已經超出了 V$SQL 的期限，在 FGA 審計線索中將會始終提供它。
       （2）由於 FGA 捕獲繫結變數，它可以幫助您瞭解繫結變數值的模式，這有助於設計直方圖集合等。
       （3）處理器模組可以向審計者或DBA 傳送警告，這有助於跟蹤惡意應用程式。
       （4）由於 FGA 可以作為 SELECT 語句的觸發器，您可以在需要這種功能的任何時候使用它。

3.7 檢視部分欄位說明

3.7.1 DBA_AUDIT_POLICIES

-----------------------------------------------------------------------
OBJECT_SCHEMA   對其定義了 FGA 策略的表或檢視的所有者
OBJECT_NAME     表或檢視的名稱
POLICY_NAME     策略的名稱 — 例如，ACCOUNTS_ACCESS
POLICY_TEXT     在新增策略時指定的審計條件 — 例如，BALANCE >;= 11000
POLICY_COLUMN   審計列 — 例如，BALANCE
ENABLED         如果啟用則為 YES，否則為 NO  
PF_SCHEMA       擁有策略處理器模組的模式（如果存在）
PF_PACKAGE      處理器模組的程式包名稱（如果存在）
PF_FUNCTION     處理器模組的過程名稱（如果存在）



3.7.2  DBA_FGA_AUDIT_TRAIL

-----------------------------------------------------------------------
SESSION_ID      審計會話識別符號；與 V$SESSION 檢視中的會話識別符號不同
TIMESTAMP       審計記錄生成時的時間標記
DB_USER         發出查詢的資料庫使用者
OS_USER         作業系統使用者
USERHOST        使用者連線的機器的主機名
CLIENT_ID       客戶識別符號（如果由對打包過程dbms_session.set_identifier 的呼叫所設定）
EXT_NAME        外部認證的客戶名稱，如 LDAP 使用者
OBJECT_SCHEMA   對該表的訪問觸發了審計的表所有者
OBJECT_NAME     對該表的 SELECT 操作觸發了審計的表名稱
POLICY_NAME     觸發審計的策略名稱（如果對錶定義了多個策略，則每個策略將插入一條記錄。在此情況下，該列顯示哪些行是由哪個策略插入的。)
SCN             記錄了審計的 Oracle 系統更改號
SQL_TEXT        由使用者提交的 SQL 語句 
SQL_BIND        由 SQL 語句使用的繫結變數（如果存在）

 

小結：
       FGA 在 Oracle 資料庫中支援隱私和職能策略。因為審計發生在資料庫內部而不是應用程式中，所以無論使用者使用的訪問方法是什麼（通過諸如 SQL*Plus 等工具或者應用程式），都對操作進行審計，允許進行非常簡單的設定。

 

 

四. 相關示例

4.1  審計功能的引數控制

audit_trail 引數的值可以設定為以下幾種

1. NONE：不開啟

2. DB：開啟審計功能

3. OS：審計記錄寫入一個作業系統檔案。

4. TRUE：與引數DB一樣

5. FALSE：不開啟審計功能。

這個引數是寫道spfile裡面的，需要重啟資料庫

 

4.2  檢視是否審計功能是否啟動

SQL> show parameter audit

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

audit_file_dest                      string      /u01/app/oracle/admin/ORCL/adump

audit_sys_operations                 boolean     FALSE

audit_syslog_level                   string

audit_trail                          string      NONE

 

4.3 開啟審計

SQL> conn /as sysdba

SQL> show parameter audit

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

audit_file_dest                      string      /u01/app/oracle/admin/ORCL/adump

audit_sys_operations                 boolean     FALSE

audit_syslog_level                   string

audit_trail                          string      NONE

 

SQL> alter system setaudit_sys_operations=TRUE scope=spfile;   

--審計管理使用者(以sysdba/sysoper角色登陸)

SQL> alter system setaudit_trail=db,extended scope=spfile;

開啟審計要重啟例項

 

SQL> show parameter audit

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

audit_file_dest                      string      /u01/app/oracle/admin/ORCL/adump

audit_sys_operations                 boolean     TRUE

audit_syslog_level                   string

audit_trail                          string      DB, EXTENDED     

 

4.4  關閉審計

SQL> conn /as sysdba

SQL> show parameter audit

SQL> alter system set audit_trail=none;

關閉審計也需要重啟例項

 

4.5 審計例項

4.5.1 啟用審計

SQL> conn sys/admin as sysdba

已連線。

SQL> show parameter audit

NAME               TYPE        VALUE

------------------------------------ -----------------------------------------

audit_file_dest         string     D:\ORACLE\ADMIN\DBA\ADUMP

audit_sys_operations                 boolean     FALSE

audit_trail                          string      NONE

SQL> alter system set audit_sys_operations=TRUEscope=spfile;

--審計管理使用者(以sysdba/sysoper角色登陸)

SQL> alter system setaudit_trail=db,extended scope=spfile;

SQL> startup force;

SQL> show parameter audit

 

NAME                            TYPE        VALUE

------------------------------------         -----------------------------------------

audit_file_dest                      string      D:\ORACLE\ADMIN\DBA\ADUMP

audit_sys_operations                 boolean     TRUE

audit_trail                          string      DB, EXTENDED

 

4.5.2 開始審計

注意：無法對 SYS 使用者操作執行 audit 或noaudit 命令

SQL> conn  system/admin

SQL> audit all on test;

SQL> commit;

SQL> delete from test;

SQL> commit;

SQL> selectos_username,username,userhost,terminal,timestamp,owner,obj_name,action_name,sessionid,os_process,sql_textfrom dba_audit_trail;

os_user username userhost   terminal timestamp      owner

-------   -------- --------------- ----------------------------- ----------------

user   system   workgroup\hfcc-hfcc-kf-3068    22-10月-09 system

SQL> audit select table by test byaccess;

 

如果在命令後面新增by user則只對user的操作進行審計,如果省去by使用者,則對系統中所有的使用者進行審計(不包含sys使用者).

 

例：

audit delete any table; --審計刪除表的操作

audit delete any table whenever notsuccessful; --只審計刪除失敗的情況

audit delete any table whenever successful;--只審計刪除成功的情況

audit delete,update,insert on user.table bysystem; --審計system使用者對錶user.table的delete,update,insert操作

 

4.5.3 撤銷審計

SQL> noaudit all on t_test;

 

 


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

相關文章