Oracle資料庫審計功能介紹
一：什麼是審計？ 二：為什麼使用審計？ 三：審計最佳實踐 四：強制審計 五：標準審計 六：作業系統跟蹤 七：使用AUDIT SQL語句啟用標準審計 八：審計SQL語句 九：審計許可權 十：審計模式物件 十一：審計目錄物件 十二：審計網路活動 十三：審計使用者SYS和以SYSDBA和SYSOPER身份連線的使用者 十四：使用觸發器將審計資料寫入單獨的表 十五：檢視審計記錄的
一：什麼是審計？
審計是對資料庫使用者和非資料庫使用者的選定使用者資料庫操作的監視和記錄。 您可以基於單個操作（例如執行的SQL語句型別）或包含使用者名稱、應用程式、時間等資料的組合進行審計。 您可以審計成功和失敗的活動。 要使用審計，請啟用它，然後配置必須審計的內容。 您審計的操作記錄在資料字典表或作業系統檔案中。 Oracle建議您啟用和配置審計。 審計是實施強有力的內部控制的有效方法，以便您的網站能夠滿足薩班斯-奧克斯利法案中規定的法規遵從性要求。 這使您能夠監控業務運營，並發現任何可能偏離公司政策的活動。 這樣做意味著對資料庫和應用程式軟體的訪問受到嚴格控制，確保修補程式按計劃應用並防止臨時更改。 透過預設啟用審計，您可以為審計和合規人員生成審計記錄。 對審計要有選擇性，並確保它滿足您的業務合規性需求。 What Is Auditing? Auditing is the monitoring and recording of selected user database actions, from both database users and nondatabase usersFoot 1 . You can base auditing on individual actions, such as the type of SQL statement executed, or on combinations of data that can include the user name, application, time, and so on. You can audit both successful and failed activities. To use auditing, you enable it, and then configure what must be audited. The actions that you audit are recorded in either data dictionary tables or in operating system files. Oracle recommends that you enable and configure auditing. Auditing is an effective method of enforcing strong internal controls so that your site can meet its regulatory compliance requirements, as defined in the Sarbanes-Oxley Act. This enables you to monitor business operations, and find any activities that may deviate from company policy. Doing so translates into tightly controlled access to your database and the application software, ensuring that patches are applied on schedule and preventing ad hoc changes. By enabling auditing by default, you can generate an audit record for audit and compliance personnel. Be selective with auditing and ensure that it meets your business compliance needs.
二：為什麼使用審計？
Why Is Auditing Used? 通常使用審計來執行以下活動： You typically use auditing to perform the following activities: 1.啟用行動問責制。這些操作包括在特定模式、表或行中執行的操作，或影響特定內容的操作。 Enable accountability for actions. These include actions taken in a particular schema, table, or row, or affecting specific content. 基於該責任，阻止使用者採取不當行動。 Deter users from inappropriate actions based on that accountability. 2.調查可疑活動。 Investigate suspicious activity. 例如，如果使用者正在從表中刪除資料，則安全管理員可能會決定審計到資料庫的所有連線，以及資料庫中所有表中所有成功和不成功的行刪除。 For example, if a user is deleting data from tables, then a security administrator might decide to audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database. 3.通知審計員未經授權的使用者的操作。 Notify an auditor of actions by an unauthorized user. 例如，未經授權的使用者可以更改或刪除資料，或者使用者擁有比預期更多的許可權，這可能導致重新評估使用者授權。 For example, an unauthorized user could change or delete data, or a user has more privileges than expected, which can lead to reassessing user authorizations. 4.檢測授權或訪問控制實現的問題。 Detect problems with an authorization or access control implementation. 例如，您可以建立您期望永遠不會生成審計記錄的審計策略，因為資料以其他方式受到保護。但是，如果這些策略確實生成了審計記錄，那麼您將知道其他安全控制沒有正確實施。 For example, you can create audit policies that you expect will never generate an audit record because the data is protected in other ways. However, if these policies do generate audit records, then you will know the other security controls are not properly implemented. 5.滿足合規性審計要求。 Address auditing requirements for compliance. 6.監控和收集有關特定資料庫活動的資料。 Monitor and gather data about specific database activities. 例如，資料庫管理員可以收集有關正在更新哪些表、執行了多少邏輯I/O操作或在高峰時間連線了多少併發使用者的統計資訊。 For example, the database administrator can gather statistics about which tables are being updated, how many logical I/O operations are performed, or how many concurrent users connect at peak times.
三：審計最佳實踐
遵循以下最佳實踐指南： 作為一般規則，設計審計策略以收集滿足法規遵從性要求所需的資訊量，但一定要關注引起最大安全問題的活動。 例如，審計資料庫中的每個表是不實際的，但審計包含敏感資料（如工資）的表列是可行的。 對於標準審計和細粒度審計，您可以使用一些機制來設計審計策略，這些策略側重於要審計的特定活動。 定期歸檔和清除審計跟蹤資料。 有關詳細資訊，請參閱 "Purging Audit Trail Records" 。 Best Practices for Auditing Follow these best practices guidelines: As a general rule, design your auditing strategy to collect the amount of information that you need to meet compliance requirements, but being sure to focus on activities that cause the greatest security concerns. For example, auditing every table in the database is not practical, but auditing table columns that contain sensitive data, such as salaries, is. With both standard and fine-grained auditing, there are mechanisms you can use to design audit policies that focus on specific activities to audit. Periodically archive and purge the audit trail data. See "Purging Audit Trail Records" for more information.
四：強制審計
始終針對所有平臺進行審計的活動 Oracle資料庫始終審計某些與資料庫相關的操作，並將其寫入作業系統審計檔案。 它包括使用SYSDBA或SYSOPER許可權登入的任何使用者的操作。這被稱為強制審計。 即使啟用了資料庫審計跟蹤（即，將audit_trail引數設定為DB），Oracle資料庫仍會將強制記錄寫入作業系統檔案。 預設情況下，UNIX和Windows系統的作業系統檔案都位於$ORACLE_BASE/admin/$ORACLE_SID/adump目錄中。 在Windows系統上，Oracle資料庫還將此資訊寫入Windows事件檢視器。 您可以透過設定AUDIT_FILE_DEST初始化引數來更改此目錄的位置。 強制性審計包括以下操作： 1.資料庫啟動。 將生成一條審計記錄，其中列出了啟動例項的作業系統使用者、使用者終端識別符號以及日期和時間戳。 此資料儲存在作業系統審計跟蹤中，因為資料庫審計跟蹤在成功完成啟動後才可用。 2.SYSDBA和SYSOPER登入。 Oracle資料庫記錄所有SYSDBA和SYSOPER連線。 3.資料庫關閉。 將生成一個審計記錄，其中列出了關閉例項的作業系統使用者、使用者終端識別符號以及日期和時間戳。 Activities That Are Always Audited for All Platforms Oracle Database always audits certain database-related operations and writes them to the operating system audit files. It includes the actions of any user who is logged in with the SYSDBA or SYSOPER privilege. This is called mandatory auditing. Even if you have enabled the database audit trail (that is, setting the AUDIT_TRAIL parameter to DB), Oracle Database still writes mandatory records to operating system files. By default, the operating system files are in the $ORACLE_BASE/admin/$ORACLE_SID/adump directory for both UNIX and Windows systems. On Windows systems, Oracle Database also writes this information to the Windows Event Viewer. You can change the location of this directory by setting the AUDIT_FILE_DEST initialization parameter, which is described in "Specifying a Directory for the Operating System Audit Trail". Mandatory auditing includes the following operations: Database startup. An audit record is generated that lists the operating system user starting the instance, the user terminal identifier, and the date and time stamp. This data is stored in the operating system audit trail because the database audit trail is not available until after the startup has successfully completed. SYSDBA and SYSOPER logins. Oracle Database records all SYSDBA and SYSOPER connections. Database shutdown. An audit record is generated that lists the operating system user shutting down the instance, the user terminal identifier, and the date and time stamp. 也就是說，即使AUDIT_SYS_OPERATIONS引數設定為FALSE，也會將管理員許可權連線、啟動、關閉資料庫操作記錄到audit_file_dest指定的資料夾中。
五：標準審計
用標準審計審計一般活動
本節包含：
關於標準審計
1.使用AUDIT_TRAIL初始化引數配置標準審計
2.作業系統和資料庫審計跟蹤有什麼共同點？
3.使用作業系統審計跟蹤
4.在UNIX系統上使用Syslog審計跟蹤
5.AUDIT和NOAUDIT SQL語句的工作原理
6.審計SQL語句
7.審計許可權
8.在多層環境中審計SQL語句和許可權
9.審計模式物件
10.審計目錄物件
11.審計函式、儲存過程、包和觸發器
12.審計網路活動
Auditing General Activities with Standard Auditing
This section contains:
About Standard Auditing
Configuring Standard Auditing with the AUDIT_TRAIL Initialization Parameter
What Do the Operating System and Database Audit Trails Have in Common?
Using the Operating System Audit Trail
Using the Syslog Audit Trail on UNIX Systems
How the AUDIT and NOAUDIT SQL Statements Work
Auditing SQL Statements
Auditing Privileges
Auditing SQL Statements and Privileges in a Multitier Environment
Auditing Schema Objects
Auditing Directory Objects
Auditing Functions, Procedures, Packages, and Triggers
Auditing Network Activity
什麼是標準審計？
在標準審計中，您審計SQL語句、許可權、模式物件和網路活動。
透過使用AUDITSQL語句配置標準審計和NOAUDIT語句刪除配置。
您可以將審計記錄寫入資料庫審計跟蹤或作業系統審計檔案。
What Is Standard Auditing?
In standard auditing, you audit SQL statements, privileges, schema objects, and network activity.
You configure standard auditing by using the AUDIT SQL statement and NOAUDIT to remove this configuration.
You can write the audit records to either the database audit trail or to operating system audit files.
誰可以執行標準審計？
任何使用者都可以使用AUDIT語句為自己的模式中的物件配置審計。
要撤消此物件的審計配置，使用者可以使用NOAUDIT語句。
執行此任務不需要其他許可權。無論AUDIT_TRAIL引數設定如何，使用者都可以執行AUDIT語句來設定審計選項。
如果已禁用審計，則下次啟用審計時，Oracle資料庫將記錄由AUDIT語句設定的審計活動。
"Enabling or Disabling the Standard Audit Trail"說明了如何啟用標準審計。
注意以下事項：
1.要審計另一個模式中的物件，使用者必須具有AUDIT ANY系統許可權。
2.要審計系統許可權，使用者必須具有audit system許可權。
3.如果O7_DICTIONARY_ACCESSIBILITY初始化引數設定為FALSE（預設值），則只有具有SYSDBA許可權的使用者才能對SYS.AUD$和SYS.FGA_LOG$表中的審計資料執行DML操作。
4.為了提高安全性，請將O7_DICTIONARY_ACCESSIBILITY引數設定為FALSE，以便非SYSDBA使用者無法審計SYS物件。
Who Can Perform Standard Auditing?
Any user can configure auditing for the objects in his or her own schema, by using the AUDIT statement.
To undo the audit configuration for this object, the user can use the NOAUDIT statement.
No additional privileges are needed to perform this task. Users can run AUDIT statements to set auditing options regardless of the AUDIT_TRAIL parameter setting.
If auditing has been disabled, the next time it is enabled, Oracle Database will record the auditing activities set by the AUDIT statements.
"Enabling or Disabling the Standard Audit Trail" explains how to enable standard auditing.
Note the following:
To audit objects in another schema, the user must have the AUDIT ANY system privilege.
To audit system privileges, the user must have the AUDIT SYSTEM privilege.
If the O7_DICTIONARY_ACCESSIBILITY initialization parameter has been set to FALSE (the default), then only users who have the SYSDBA privilege can perform DML actions on the audit data in the SYS.AUD$ and SYS.FGA_LOG$ tables.
For greater security, set the O7_DICTIONARY_ACCESSIBILITY parameter to FALSE so that non-SYSDBA users cannot audit SYS objects.
何時建立標準審計記錄？
作為安全管理員，您可以啟用或禁用整個資料庫的標準審計。
如果禁用，則不建立審計記錄。上一節“Who Can Perform Standard Auditing?”中介紹了配置審計選項。
如果在資料庫中啟用了審計，並且發生了配置為要審計的操作，Oracle資料庫將在SQL語句的執行階段期間或之後生成審計、計記錄。
當程式單元執行時，Oracle資料庫根據需要單獨審計PL/SQL程式單元內的SQL語句。
審計跟蹤記錄的生成和插入與提交的使用者事務無關。
也就是說，即使回滾了使用者事務，審計跟蹤記錄仍保持提交狀態。
資料庫使用者連線到資料庫時有效的語句和許可權審計選項在會話期間保持有效。
當會話已處於活動狀態時，設定或更改語句或許可權審計選項不會在該會話中生效。
修改的語句或許可權審計選項僅在當前會話結束並建立新會話時生效。
相反，對模式物件審計選項的更改對於當前會話會立即生效。
When Are Standard Audit Records Created?
You, as the security administrator, enable or disable standard auditing for the entire database.
If it is disabled, then no audit records are created. Configuring audit options is described in the previous section, "Who Can Perform Standard Auditing?"
When auditing is enabled in the database and an action configured to be audited occurs, Oracle Database generates an audit record during or after the execution phase of the SQL statement.
Oracle Database individually audits SQL statements inside PL/SQL program units, as necessary, when the program unit is run.
The generation and insertion of an audit trail record is independent of a user transaction being committed.
That is, even if a user transaction is rolled back, the audit trail record remains committed.
Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session.
When the session is already active, setting or changing statement or privilege audit options does not take effect in that session.
The modified statement or privilege audit options take effect only when the current session ends and a new session is created.
In contrast, changes to schema object audit options become immediately effective for current sessions.
使用AUDIT_TRAIL初始化引數配置標準審計
本節包含：
啟用或禁用標準審計跟蹤
AUDIT_TRAIL初始化引數的設定
啟用或禁用標準審計跟蹤
透過設定audit_trail初始化引數，可以啟用標準審計跟蹤。
此設定確定是在資料庫審計跟蹤中建立審計跟蹤、將審計活動寫入作業系統檔案還是禁用審計。
Configuring Standard Auditing with the AUDIT_TRAIL Initialization Parameter
This section contains:
Enabling or Disabling the Standard Audit Trail
Settings for the AUDIT_TRAIL Initialization Parameter
Enabling or Disabling the Standard Audit Trail
You enable the standard audit trail by setting the AUDIT_TRAIL initialization parameter.
This setting determines whether to create the audit trail in the database audit trail, write the audit activities to an operating system file, or to disable auditing.
檢查引數
Example 9-1 Checking the Current Value of the AUDIT_TRAIL Initialization Parameter
SHOW PARAMETER AUDIT_TRAIL
NAME TYPE VALUE
------------------------------------ ----------- -------
audit_trail string DB
啟用標準審計
Example 9-2 Enabling the Standard Audit Trail
CONNECT SYSTEM
ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;
CONNECT SYS/AS SYSOPER
SHUTDOWN
STARTUP
AUDIT_TRAIL引數說明
1.DB
將審計記錄定向到資料庫審計跟蹤（SYS.AUD$表），但強制和SYS審計記錄除外，這些記錄始終寫入作業系統審計跟蹤。
（“選擇審計型別”描述了每種審計型別的審計記錄的位置。）
將此設定用於常規資料庫以實現可管理性。
DB是AUDIT_TRAIL引數的預設設定。
如果資料庫以只讀模式啟動，並且AUDIT_TRAIL設定為DB，則Oracle資料庫內部將AUDIT_TRAIL設定為OS。
有關詳細資訊，請檢視警報日誌。
Directs audit records to the database audit trail (the SYS.AUD$ table), except for mandatory and SYS audit records, which are always written to the operating system audit trail.
("Selecting an Auditing Type" describes the location of the audit records for each type of auditing.)
Use this setting for a general database for manageability.
DB is the default setting for the AUDIT_TRAIL parameter.
If the database was started in read-only mode with AUDIT_TRAIL set to DB, then Oracle Database internally sets AUDIT_TRAIL to OS.
Check the alert log for details.
2.DB,EXTENDED
行為與AUDIT_TRAIL=DB相同，但也填充SYS.AUD$表的SQL繫結和SQL文字CLOB型別列（如果可用）。
DB，EXTEND使您能夠捕獲在已審計的操作中使用的SQL語句。
您可以捕獲導致審計的SQL語句和任何關聯的繫結變數。
但是，請注意，您只能從以下列資料型別捕獲資料：CHAR、NCHAR、VARCHAR、VAR CHAR2、NVARCHAR2、NUMBER、FLOAT、BINARY_FLOAT、BINARY_DOUBLE、LONG、ROWID、DATE、TIMESTAMP和TIMESTAMP WITH TIMEZONE。
還要注意，DB，EXTEND可以捕獲敏感資料，如信用卡資訊。
另請參閱“Auditing Sensitive Information”。
如果資料庫是在只讀模式下啟動的，並且AUDIT_TRAIL設定為DB、EXTEND，則Oracle資料庫內部會將AUDIT_TRAIL設定為OS。
有關詳細資訊，請檢視警報日誌。
Behaves the same as AUDIT_TRAIL=DB, but also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available.
DB,EXTENDED enables you to capture the SQL statement used in the action that was audited.
You can capture both the SQL statement that caused the audit, and any associated bind variables.
However, be aware that you only can capture data from the following column datatypes: CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2, NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE, LONG, ROWID, DATE, TIMESTAMP, and TIMESTAMP WITH TIMEZONE.
Also be aware that DB, EXTENDED can capture sensitive data, such as credit card information.
See also "Auditing Sensitive Information".
If the database was started in read-only mode with AUDIT_TRAIL set to DB, EXTENDED, then Oracle Database internally sets AUDIT_TRAIL to OS.
Check the alert log for details.
可以透過以下任一方式指定DB、EXTEND：
You can specify DB,EXTENDED in any of the following ways:
ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL='DB','EXTENDED' SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL=EXTENDED,DB SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL=EXTENDED, DB SCOPE=SPFILE;
但是，不要將DB、EXTEND括在引號中，例如：
However, do not enclose DB, EXTENDED in quotes, for example:
ALTER SYSTEM SET AUDIT_TRAIL='DB, EXTENDED' SCOPE=SPFILE;
在以前的版本中，設定為DB_EXTENDED。
In previous releases, the setting was DB_EXTENDED.
為了向後相容，保留了此設定，但在將來的版本中可能不可用。
This setting has been retained for backward compatibility but may not be available in future releases.
3.OS
將所有審計記錄定向到作業系統檔案。
Oracle建議您使用作業系統設定，特別是在使用超安全資料庫配置時。
有關更多資訊，請參閱“Advantages of the Operating System Audit Trail”。
另請參見示例9-3“Text File Operating System Audit Trail”。
如果將AUDIT_TRAIL設定為OS，則設定以下其他初始化引數：
AUDIT_FILE_DEST，指定作業系統審計記錄檔案的位置。在UNIX系統上，預設位置為$ORACLE_BASE/admin/$ORACLE_SID/adump。
為了在UNIX系統上獲得更好的效能，請將AUDIT_FILE_DEST引數設定為本地連線到執行Oracle資料庫例項的主機的磁碟上的目錄。
在Windows上，作業系統設定將審計跟蹤寫入Windows事件檢視器的應用程式區域。
AUDIT_SYS_OPERATIONS，如果您想審計由具有SYSDBA或SYSOPER許可權的使用者直接發出的頂級SQL語句。
要啟用此審計，請將AUDIT_SYS_OPERATIONS設定為TRUE。
如果將AUDIT_SYS_OPERATIONS設定為TRUE，將AUDIT_TRAIL設定為XML或XML，EXTEND，則Oracle資料庫將以XML格式寫入SYS審計記錄作業系統檔案。
AUDIT_SYSLOG_LEVEL，它使用SYSLOG實用程式將SYS和標準OS審計記錄寫入系統審計日誌。此選項僅適用於UNIX環境。
有關詳細資訊，請參閱“Configuring Syslog Auditing”。
另請參閱“Managing the Operating System Audit Trail”。
Directs all audit records to an operating system file.
Oracle recommends that you use the OS setting, particularly if you are using an ultra-secure database configuration.
See "Advantages of the Operating System Audit Trail" for more information.
See also Example 9-3, "Text File Operating System Audit Trail".
If you set AUDIT_TRAIL to OS, then set the following additional initialization parameters:
AUDIT_FILE_DEST, which specifies the location of the operating system audit record file. On UNIX systems, the default location is $ORACLE_BASE/admin/$ORACLE_SID/adump.
For better performance on UNIX systems, set the AUDIT_FILE_DEST parameter to a directory on a disk that is locally attached to the host running the Oracle Database instance.
On Windows, the OS setting writes the audit trail to the Application area of the Windows Event Viewer.
AUDIT_SYS_OPERATIONS, if you want to audit the top-level SQL statements directly issued by users who have connected with the SYSDBA or SYSOPER privilege.
To enable this auditing, set AUDIT_SYS_OPERATIONS to TRUE.
If you set AUDIT_SYS_OPERATIONS to TRUE and AUDIT_TRAIL to XML or XML,EXTENDED, then Oracle Database writes SYS audit records operating system files in XML format.
AUDIT_SYSLOG_LEVEL, which writes SYS and standard OS audit records to the system audit log using the SYSLOG utility. This option only applies to UNIX environments.
See "Configuring Syslog Auditing" for more information.
See also "Managing the Operating System Audit Trail".
4.XML
以XML格式寫入作業系統審計記錄檔案。
記錄中XML模式給定的AuditRecord節點的所有元素除了Sql_Text和Sql_Bind到作業系統XML審計檔案之外。
（此.xsd檔案表示XML審計檔案的架構定義。XML架構是用XML架構語言編寫的文件。）
如果設定了XML值，則還要設定AUDIT_FILE_DEST引數。
對於所有平臺（包括Windows），XML審計跟蹤記錄的預設位置為$ORACLE_BASE/admin/$ORACLE_SID/adump。
XML AUDIT_TRAIL值不會影響syslog審計檔案。
換句話說，如果將AUDIT_TRAIL引數設定為XML，那麼syslog審計記錄將仍然是文字格式，而不是XML檔案格式。
您可以按如下方式控制SYS和強制性審計記錄的輸出：
要將SYS和強制審計檔案以XML格式寫入作業系統檔案：請將audit_TRAIL設定為XML或XML，EXTEND，將audit_SYS_OPERATIONS設定為TRUE，但不要設定audit_SYSLOG_LEVEL引數。
要將SYS和強制審計記錄寫入syslog審計檔案，將標準審計記錄寫入XML審計檔案，請將audit_TRAIL設定為XML或XML，EXTENDED，將audit_SYS_OPERATIONS設定為TRUE，並設定audit_syslog_LEVEL引數。
Writes to the operating system audit record file in XML format.
Records all elements of the AuditRecord node given by the XML schema in except Sql_Text and Sql_Bind to operating system XML audit files.
(This .xsd file represents the schema definition of the XML audit file. An XML schema is a document written in the XML Schema language.)
See also "Advantages of the Operating System Audit Trail" and Example 9-4, "XML File Operating System Audit Trail".
If you set the XML value, then also set the AUDIT_FILE_DEST parameter.
For all platforms, including Windows, the default location for XML audit trail records is $ORACLE_BASE/admin/$ORACLE_SID/adump.
The XML AUDIT_TRAIL value does not affect syslog audit file.
In other words, if you have set the AUDIT_TRAIL parameter to XML, then the syslog audit records will still be in text format, not XML file format.
You can control the output for SYS and mandatory audit records as follows:
To write SYS and mandatory audit files to operating system files in XML format: Set AUDIT_TRAIL to XML or XML,EXTENDED, set AUDIT_SYS_OPERATIONS to TRUE, but do not set the AUDIT_SYSLOG_LEVEL parameter.
To write SYS and mandatory audit records to syslog audit files and standard audit records to XML audit files: Set AUDIT_TRAIL to XML or XML,EXTENDED, set AUDIT_SYS_OPERATIONS to TRUE, and set the AUDIT_SYSLOG_LEVEL parameter.
5.XML, EXTENDED
行為與AUDIT_TRAIL=XML相同，但也在作業系統XML審計檔案中包含SQL文字和SQL繫結資訊。
Behaves the same as AUDIT_TRAIL=XML, but also includes SQL text and SQL bind information in the operating system XML audit files.
可以透過以下任一方式指定XML、EXTEND：
You can specify XML,EXTENDED in either of the following ways:
ALTER SYSTEM SET AUDIT_TRAIL=XML, EXTENDED SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL='XML','EXTENDED' SCOPE=SPFILE;
但是，不要將XML、EXTEND括在引號中，例如：
However, do not enclose XML, EXTENDED in quotes, for example:
ALTER SYSTEM SET AUDIT_TRAIL='XML, EXTENDED' SCOPE=SPFILE;
6.NONE
禁用標準審計。
Disables standard auditing.
注意以下事項：
執行AUDIT或NOAUDIT語句後，不需要重新啟動資料庫。
如果進行了通用更改，例如更改AUDIT_TRAIL初始化引數，則需重新啟動資料庫。
您不需要設定AUDIT_TRAIL來啟用細粒度審計或SYS審計。
對於細粒度審計，您可以根據需要新增和刪除細粒度審計策略，並將它們應用於要監視的特定操作或物件。
要啟用SYS審計，請將AUDIT_SYS_OPERATIONS引數設定為TRUE。
Note the following:
You do not need to restart the database after you run the AUDIT or NOAUDIT statements.
You only need to restart the database if you made a universal change, such as changing the AUDIT_TRAIL initialization parameter.
You do not need to set AUDIT_TRAIL to enable either fine-grained auditing or SYS auditing.
For fine-grained auditing, you add and remove fine-grained audit policies as necessary, applying them to the specific operations or objects you want to monitor.
To enable SYS auditing, set the AUDIT_SYS_OPERATIONS parameter to TRUE.
六：作業系統跟蹤
關於作業系統跟蹤 作為在DBA_AUDIT_TRAIL（SYS.AUD$表）中建立標準審計記錄的替代方法，您可以在作業系統檔案中建立標準的審計記錄。 包含審計跟蹤的作業系統檔案可以包括以下任何資料： 1.資料庫審計跟蹤記錄 2.強制性審計記錄（即始終審計的資料庫操作） 3.管理使用者審計記錄（SYS） About the Operating System Trail As an alternative to creating standard audit records in the DBA_AUDIT_TRAIL (SYS.AUD$ table), you can create standard audit records in operating system files. The operating system file that contains the audit trail can include any of the following data: 1.Database audit trail records 2.Mandatory audit records (that is, database actions that are always audited) 3.Audit records for administrative users (SYS) 示例9-3文字檔案作業系統審計跟蹤 Example 9-3 Text File Operating System Audit Trail Audit trail: LENGTH: "349" SESSIONID:[5] "43464" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[6] "DBSNMP" USERHOST:[7] "SHOBEEN" TERMINAL:[3] "MAU" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[97] "Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.4)(PORT=2955))" OS$USERID:[19] "NT AUTHORITY\SYSTEM" DBID:[10] "1212547373" PRIV$USED:[1] "5" 說明： LENGTH 指此審計記錄中使用的總位元組數。 此數字包括審計記錄末尾的尾隨換行位元組（如果有）。 refers to the total number of bytes used in this audit record. This number includes the trailing newline bytes (\n), if any, at the end of the audit record. [] []括號表示每個審計條目的每個值的長度。 例如，USERID條目DBSNMP的長度為6位元組。 brackets indicate the length of each value for each audit entry. For example, the USERID entry, DBSNMP, is 6 bytes long. SESSIONID SESSIONID表示審計會話ID號。 您還可以透過查詢V$SSESSION資料字典檢視中的AUDSID列來查詢會話ID。 indicates the audit session ID number. You can also find the session ID by querying the AUDSID column in the V$SESSION data dictionary view. ENTRYID ENTRYID表示分配給每個審計跟蹤記錄的當前審計條目編號。 審計ENTRYID序列號在細粒度審計記錄和常規審計記錄之間共享。 indicates the current audit entry number, assigned to each audit trail record. The audit ENTRYID sequence number is shared between fine-grained audit records and regular audit records. STATEMENT STATEMENT是分配給使用者執行的語句的數字ID。 它出現在使用者會話期間發出的每個語句中，因為一個語句可能會導致多個審計記錄。 is a numeric ID assigned to the statement the user runs. It appears for each statement issued during the user session, because a statement can result in multiple audit records. ACTION ACTION是表示使用者執行的操作的數值。 操作型別的對應名稱在AUDIT_ACTIONS表中。 例如，動作100指的是登入。 is a numeric value representing the action the user performed. The corresponding name of the action type is in the AUDIT_ACTIONS table. For example, action 100 refers to LOGON. RETURNCODE RETURNCODE指示審計操作是否成功。 0表示成功。如果操作失敗，返回程式碼將列出Oracle資料庫錯誤號。例如，如果您嘗試刪除不存在的表，則錯誤號為ORA-00903無效的表名，這反過來又轉換為RETURNCODE設定中的903。 indicates if the audited action was successful. 0 indicates success. If the action fails, the return code lists the Oracle Database error number. For example, if you try to drop a non-existent table, the error number is ORA-00903 invalid table name, which in turn translates to 903 in the RETURNCODE setting. COMMENT$TEXT COMMENT$TEXT 表示對審計記錄的其他註釋。 例如，對於LOGON審計記錄，它可以指示身份驗證方法。 它對應於DBA_COMMON_AUDIT_TRAIL資料字典檢視的COMENT_TEXT列。 indicates additional comments about the audit record. For example, for LOGON audit records, it can indicate the authentication method. It corresponds to the COMENT_TEXT column of the DBA_COMMON_AUDIT_TRAIL data dictionary view. DBID DBID是建立資料庫時計算的資料庫識別符號。它對應於V$DATABASE資料字典檢視的DBID列。 is a database identifier calculated when the database is created. It corresponds to the DBID column of the V$DATABASE data dictionary view. ECONTEXT_ID ECONTEXT_ID指示應用程式執行上下文識別符號。 indicates the application execution context identifier. PRIVS$USED PRIVS$USED指用於執行操作的特權。要查詢特權，請查詢SYSTEM_PRIVILEGE_MAP表。 例如，特權5在此表中引用-5，這意味著CREATE SESSION。 PRIVS$USED對應於DBA_COMMON_AUDIT_TRAIL中的PRIV_USED列，該列按名稱列出特權。 refers to the privilege that was used to perform an action. To find the privilege, query the SYSTEM_PRIVILEGE_MAP table. For example, privilege 5 refers to -5 in this table, which means CREATE SESSION. PRIVS$USED corresponds to the PRIV_USED column in the DBA_COMMON_AUDIT_TRAIL, which lists the privilege by name. 其他可能的值如下： Other possible values are as follows: SCN SCN（例如，SCN:8934328925）表示系統更改號（SCN）。 如果要執行閃回查詢以查詢過去某個時間的設定值（例如列），請使用此值。 例如，要根據SCN號查詢OE.ORDERS表的ORDER_TOTAL列的值， SCN (for example, SCN:8934328925) indicates the System Change Number (SCN). Use this value if you want to perform a flashback query to find the value of a setting (for example, a column) at a time in the past. For example, to find the value of the ORDER_TOTAL column of the OE.ORDERS table based on the SCN number, use the following SELECT statement: 請使用以下SELECT語句： SELECT ORDER_TOTAL FROM OE.ORDERS AS OF SCN = 8934328925 WHERE ORDER_TOTAL = 86; SES_ACTIONS SES_ACTIONS指示會話期間發生的操作。 僅當使用BY SESSION子句審計事件時，此欄位才會出現。 由於此欄位未詳細解釋會話期間發生的操作，因此應使用BY ACCESS子句配置審計事件。 SES_ACTIONS indicates the actions that took place during the session. This field is present only if the event was audited with the BY SESSION clause. Because this field does not explain in detail the actions that occurred during the session, you should configure the audit event with the BY ACCESS clause. SES_ACTIONS欄位包含16個字元。 位置14、15和16保留供將來使用。 在前12個字元中，每個位置表示操作的結果。 它們是：ALTER、AUDIT、COMMENT、DELETE、GRANT、INDEX、INSERT、LOCK、RENAME、SELECT、UPDATE和FLASHBACK。 The SES_ACTIONS field contains 16 characters. Positions 14, 15, and 16 are reserved for future use. In the first 12 characters, each position indicates the result of an action. They are: ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, and FLASHBACK. For example, if the user had successfully run the ALTER statement, the SES_ACTIONS setting is as follows: 例如，如果使用者已成功執行ALTER語句，SES_ACTIONS設定如下： 位於第一個位置（對於ALTER）的S表示成功。 如果ALTER語句失敗，字母F將出現在它的位置。 如果行動既成功又失敗，那麼字母是B。 S--------------- The S, in the first position (for ALTER), indicates success. Had the ALTER statement failed, the letter F would have appeared in its place. If the action resulted in both a success and failure, then the letter is B. SES$TID SES$TID表示受審計操作影響的物件的ID。 SES$TID indicates the ID of the object affected by the audited action. SPARE2 SPARE2指示使用者是否修改了SYS.AUD$表。 0表示使用者修改的SYS.AUD$；否則，該值為NULL SPARE2 indicates whether the user modified SYS.AUD$ table. 0 means the user modified SYS.AUD$; otherwise, the value is NULL. 示例9-4 XML檔案作業系統審計跟蹤 Example 9-4 XML File Operating System Audit Trail <?xml version="1.0" encoding="UTF-8"?> <Audit xmlns="" xmlns:xsi=" xsi:schemaLocation=""> <Version>11.2</Version> <AuditRecord> <Audit_Type>1</Audit_Type> <Session_Id>43535</Session_Id> <StatementId>1</StatementId> <EntryId>1</EntryId> <Extended_Timestamp>2009-04-29T18:32:26.062000Z</Extended_Timestamp> <DB_User>SYSMAN</DB_User> <OS_User>SYSTEM</OS_User> <Userhost>shobeen</Userhost> <OS_Process>3164:3648</OS_Process> <Terminal>mau</Terminal> <Instance_Number>0</Instance_Number> <Action>100</Action> <TransactionId>0000000000000000</TransactionId> <Returncode>0</Returncode> <Comment_Text>Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.4)(PORT=3536))</Comment_Text> <Priv_Used>5</Priv_Used> </AuditRecord> </Audit> 在此示例中： AuditRecord元素包含整個審計記錄。（有關Audit_Record元素內元素的更多資訊，請參見示例9-3。） Audit_Type表示審計跟蹤的型別。可能的值如下： In this example: AuditRecord element contains the entire audit record. (See Example 9-3 for more information about the elements within the Audit_Record element.) Audit_Type indicates the type of audit trail. Possible values are as follows: 1：標準審計記錄 1: Standard audit record 2：細粒度審計記錄 2: Fine-grained audit record 4:SYS審計記錄 4: SYS audit record 8：強制性審計記錄 8: Mandatory audit record 此欄位僅出現在XML審計檔案中，而不是OS文字審計檔案中。 This field only appears in the XML audit files, not the OS text audit files. 作業系統審計跟蹤的優勢 Advantages of the Operating System Audit Trail 使用作業系統審計跟蹤具有以下優點： Using the operating system audit trail offers these advantages: 1.它降低了拒絕服務（DoS）攻擊的可能性。 It reduces the likelihood of a denial-of-service (DoS) attack. 2.這使得確保審計跟蹤更加容易。 如果審計員不同於資料庫管理員，則必須使用OS、XML或XML、EXTEND設定。 否則，資料庫管理員可以檢視和修改儲存在資料庫中的任何審計資訊。 It makes it easier to secure the audit trail. If the auditor is distinct from the database administrator, then you must use the OS, XML, or XML, EXTENDED setting. Otherwise, a database administrator can view and modify any auditing information that is stored in the database. 3.職責分離 由於您正在將審計跟蹤寫入特定的位置，因此您可以將其限制為特定的使用者，因此作業系統審計跟蹤強制執行職責分離概念。 Because you are writing the audit trail to a specific location that you can restrict to specific users, the operating system audit trail enforces separation of duty concepts. 3.將審計跟蹤寫入到作業系統檔案中會導致資料庫開銷最少。 因此，它非常適合於非常大的資料庫。 Writing the audit trail to an operating system file results in the least amount of overhead on the database. For this reason, it is excellent for very large databases. 4.儲存在作業系統檔案中的審計記錄可能比資料庫儲存的審計記錄更安全，因為訪問可能需要資料庫管理員不具備的檔案許可權。 更高的可用性是作業系統儲存審計記錄的另一個優勢，因為即使資料庫暫時無法訪問，這些記錄仍然可用。 Audit records stored in operating system files can be more secure than database-stored audit records because access can require file permissions that database administrators do not have. Greater availability is another advantage to operating system storage for audit records, because they remain available even if the database is temporarily inaccessible. 如果AUDIT_TRAIL初始化引數設定為XML（或XML，EXTEND），則Oracle資料庫將審計記錄作為XML檔案寫入作業系統。 您可以使用V$XML_AUDIT_TRAIL檢視，透過SQL查詢使XML審計記錄對資料庫管理員可用，從而增強了可用性。 If the AUDIT_TRAIL initialization parameter is set to XML (or XML, EXTENDED), then Oracle Database writes audit records to the operating system as XML files. You can use the V$XML_AUDIT_TRAIL view to make XML audit records available to database administrators through a SQL query, providing enhanced usability. DBA_COMMON_AUDIT_TRAIL檢視包括寫入資料庫表的標準和細粒度審計跟蹤、XML格式審計跟蹤記錄以及V$XML_AUDIT_TRAIL動態檢視的內容（標準、細粒度、SYS和強制）。 The DBA_COMMON_AUDIT_TRAIL view includes the standard and fine grained audit trails written to database tables, XML-format audit trail records, and the contents of the V$XML_AUDIT_TRAIL dynamic view (standard, fine grained, SYS and mandatory). 5.使用作業系統審計跟蹤可以使您整合來自多個源的審計記錄，包括Oracle資料庫和其他應用程式。 在一個地方檢查所有審計記錄可以更有效地檢查系統活動。 如果使用XML審計記錄，則可以使用任何標準的XML編輯工具來檢視或提取這些記錄中的資訊。 Using your operating system audit trail can enable you to consolidate audit records from multiple sources, including Oracle Database and other applications. Examining system activity can be more efficient with all audit records in one place. If you use XML audit records, then you can use of any standard XML editing tool to review or extract information from those records. 作業系統審計跟蹤的工作原理 作業系統審計跟蹤將審計資料寫入作業系統檔案。您可以透過將AUDIT_TRAIL初始化引數設定為以下值之一來啟用此功能： OS：將審計跟蹤記錄寫入UNIX系統上的文字作業系統檔案和Microsoft Windows上的應用程式事件檢視器。 XML：將審計跟蹤記錄寫入XML檔案。 XML，EXTENDED：將審計跟蹤記錄寫入XML檔案，並在作業系統XML審計檔案中包含SQL文字和SQL繫結資訊。 How the Operating System Audit Trail Works The operating system audit trail writes the audit data to an operating system file. You can enable this feature by setting the AUDIT_TRAIL initialization parameter to one of the following values: OS: Writes the audit trail records to a text operating system file on UNIX systems and to the applications Event Viewer on Microsoft Windows. XML: Writes the audit trail records to an XML file. XML, EXTENDED: Writes the audit trail records to an XML file and includes SQL text and SQL bind information in the operating system XML audit files. 為作業系統審計跟蹤指定目錄 當AUDIT_trail初始化引數設定為OS、XML或XML、EXTEND時，使用AUDIT_FILE_DEST初始化引數指定將審計跟蹤寫入的作業系統目錄。 您必須將AUDIT_FILE_DEST設定為有效目錄，其許可權僅限於Oracle軟體的所有者和DBA組。 如果指定了audit_SYS_OPERATIONS初始化引數，則強制審計資訊也會進入該目錄，使用者SYS的審計記錄也會進入其中。 您可以使用以下ALTER SYSTEM語句更改AUDIT_FILE_DEST引數，這將使新目標對所有後續會話都有效。 Specifying a Directory for the Operating System Audit Trail Use the AUDIT_FILE_DEST initialization parameter to specify an operating system directory into which the audit trail is written, when the AUDIT_TRAIL initialization parameter is set to OS, XML, or XML, EXTENDED. You must set AUDIT_FILE_DEST to a valid directory with permissions restricted to the owner of the Oracle software and the DBA group. Mandatory auditing information also goes into that directory, as do audit records for user SYS if the AUDIT_SYS_OPERATIONS initialization parameter is specified. You can change the AUDIT_FILE_DEST parameter by using the following ALTER SYSTEM statement, which enables the new destination to be effective for all subsequent sessions. 其中DEFERRED表示對以後建立的SESSION生效，仍然連線的SESSION保持原有屬性。 ALTER SYSTEM SET AUDIT_FILE_DEST = directory_path DEFERRED; 作業系統檔案的位置取決於以下內容： 如果資料庫未執行且未設定AUDIT_FILE_DEST引數，則作業系統檔案將放置在第一個預設位置$ORACLE_BASE/admin/$ORACLE_SID/adump目錄中。 如果資料庫未執行，並且第一個預設位置$ORACLE_BASE/admin/$ORACLE_SID/adump目錄不可訪問或無法寫入，或者ORACLE程式無法識別環境變數，則使用第二個預設位置，$ORACLE_HOME/rdbms/audit。 當資料庫開啟並且Oracle資料庫讀取資料庫例項的初始化檔案（initSID.ora）時，AUDIT_file_DEST引數的值將用作作業系統審計檔案目錄。 對於UNIX和Solaris系統，所有作業系統檔案都會寫入作業系統中的目錄。 對於Windows，作業系統文字記錄可從Windows事件檢視器中獲得，但作業系統XML檔案可從作業系統目錄中獲得，如前面專案符號項所述。 The location of the operating system files depends on the following: If the database is not running and you have not set the AUDIT_FILE_DEST parameter, then the operating system files are placed in the first default location $ORACLE_BASE/admin/$ORACLE_SID/adump directory. If the database is not running and the first default location, the $ORACLE_BASE/admin/$ORACLE_SID/adump directory, is inaccessible or cannot be written to, or the Oracle process cannot identify the environment variables, then the second default location, $ORACLE_HOME/rdbms/audit is used. When the database is open and Oracle Database reads the initialization file (initSID.ora) for the database instance, the value of AUDIT_FILE_DEST parameter is used as the operating system audit file directory. For UNIX and Solaris systems, all operating system files are written to a directory in the operating system. For Windows, the operating system text records are available from the Windows Event Viewer, but operating system XML files are available from an operating system directory, as explained in the preceding bulleted items. Syslog審計跟蹤看起來像什麼？ 示例9-5顯示了syslog審計跟蹤的顯示方式。 （在本例中，文字已重新格式化，以便於閱讀。實際上，文字都在一行。）與其他Oracle資料庫審計跟蹤一樣，括號表示已審計值的長度。 對於syslog審計跟蹤，LENGTH（包括LENGTH）中的文字是Oracle資料庫審計記錄。 預置文字（日期和Oracle審計[10005]行）由syslog實用程式新增。 What Does the Syslog Audit Trail Look Like? Example 9-5 shows how the syslog audit trail can appear. (For this example, the text has been reformatted for easier readability. In reality, the text is all on one line.) As with other Oracle Database audit trails, the brackets indicate the length of the value that was audited. For syslog audit trails, the text from (and including) LENGTH: is Oracle Database audit record. The prepended text (the date and Oracle Audit [10085] line) is added by the syslog utility. Example 9-5 Syslog Audit Trail for SYS User May 14 23:40:15 shobeen Oracle Audit[10085]: LENGTH : '171' ACTION :[18] 'select * from aud$' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[7] 'laurelh' CLIENT TERMINAL:[6] 'pts/12' STATUS:[1] '0' DBID:[9] '562317007' 配置Syslog審計 要啟用syslog審計，請執行以下步驟： 如“啟用或禁用標準審計跟蹤”中所述，將OS值分配給AUDIT_TRAIL初始化引數。 例如： Configuring Syslog Auditing To enable syslog auditing, follow these steps: Assign the value of OS to the AUDIT_TRAIL initialization parameter, as described in "Enabling or Disabling the Standard Audit Trail". For example: ALTER SYSTEM SET AUDIT_TRAIL=OS SCOPE=SPFILE; AUDIT和NOAUDIT SQL語句的工作原理 How the AUDIT and NOAUDIT SQL Statements Work
七：使用AUDIT SQL語句啟用標準審計
要配置標準審計選項，請使用AUDITSQL語句。 表9-3列出了可以使用AUDIT語句的類別。 表9-3標準審計級別及其影響 1.Statement 審計影響特定型別資料庫物件的特定SQL語句或語句組。 例如，AUDIT TABLE審計CREATE TABLE、TRUNCATE TABLE、COMMENT ON TABLE和DELETE[FROM]TABLE語句。 Audits specific SQL statements or groups of statements that affect a particular type of database object. For example, AUDIT TABLE audits the CREATE TABLE, TRUNCATE TABLE, COMMENT ON TABLE, and DELETE [FROM] TABLE statements. 2.Privilege 審計由指定系統許可權授權的SQL語句。 例如，AUDIT CREATE ANY TRIGGER審計使用CREATE ANY TRIPGER系統許可權發出的語句。 Audits SQL statements that are authorized by the specified system privilege. For example, AUDIT CREATE ANY TRIGGER audits statements issued using the CREATE ANY TRIGGER system privilege. 3.Object 審計特定物件上的特定語句，例如HR.EMPLOYEES表上的ALTER TABLE。 Audits specific statements on specific objects, such as ALTER TABLE on the HR.EMPLOYEES table. 4.Network 審計網路協議中的意外錯誤或網路層中的內部錯誤。 Audits unexpected errors in network protocol or internal errors in the network layer. 審計語句執行：成功、失敗或兩者都有 對於語句、許可權和模式物件審計，Oracle資料庫允許對語句的成功執行、執行語句的失敗嘗試或兩者進行選擇性審計。 這使您能夠監視操作，即使審計報表未成功完成。 監視不成功的SQL語句可能會暴露窺探或惡意行為的使用者，儘管大多數不成功的語句都不是。 這種審計方法也很有用，因為它可以減少審計跟蹤，幫助您專注於特定的操作。 這有助於保持良好的資料庫效能。 Auditing Statement Executions: Successful, Unsuccessful, or Both For statement, privilege, and schema object auditing, Oracle Database permits the selective auditing of successful executions of statements, unsuccessful attempts to execute statements, or both. This enables you to monitor actions even if the audited statements do not complete successfully. Monitoring unsuccessful SQL statement can expose users who are snooping or acting maliciously, though most unsuccessful SQL statements are neither. This method of auditing is also useful in that it reduces the audit trail, helping you to focus on specific actions. This can aid in maintaining good database performance. 選項如下： WHEEVER SUCCESSFUL子句：該子句僅審計已審計語句的成功執行。 WHEENEVER NOT SUCCESSFUL子句：該子句僅審計已審計語句的未成功執行。 只有在發出了有效的SQL語句但由於缺少適當的授權或引用了不存在的架構物件而失敗時，審計不成功的語句執行才會生成審計報告。 無法審計由於無效而無法執行的語句。 例如，設定為審計不成功的語句執行的已啟用許可權審計選項將審計使用目標系統許可權但由於其他原因而失敗的語句。 一個例子是設定了CREATE TABLE審計條件，但由於指定表空間的配額不足，某些CREATE TABLE語句失敗。 省略WHENEVER SUCCESSFUL或WHENEVER NOT SUCCESSFUL：如果省略這些子句，則Oracle資料庫將審計已審計語句的成功執行和不成功執行。 The options are as follows: WHENEVER SUCCESSFUL clause: This clause audits only successful executions of the audited statement. WHENEVER NOT SUCCESSFUL clause: This clause audits only unsuccessful executions of the audited statement. Auditing an unsuccessful statement execution generates an audit report only if a valid SQL statement is issued but fails, because it lacks proper authorization or references a nonexistent schema object. Statements that fail to execute because they were not valid cannot be audited. For example, an enabled privilege auditing option set to audit unsuccessful statement executions audits statements that use the target system privilege but failed for other reasons. One example is when a CREATE TABLE auditing condition is set, but some CREATE TABLE statements fail due to insufficient quota for the specified tablespace. Omitting WHENEVER SUCCESSFUL or WHENEVER NOT SUCCESSFUL: If you omit these clauses, then Oracle Database audits both successful and unsuccessful executions of the audited statement. 例如： AUDIT CREATE TABLE BY ACCESS WHENEVER NOT SUCCESSFUL; 如何生成標準審計記錄 Oracle資料庫為每次執行審計語句或操作生成審計記錄，如下所示： 每次執行為其配置審計的SQL語句時。這還包括PL/SQL過程中語句的執行。 每次使用配置審計的許可權時 每次對其配置審計的物件進行操作時 How Standard Audit Records Are Generated Oracle Database generates an audit record for each execution of an audited statement or operation, as follows: Each time the SQL statement for which auditing was configured is executed. This also includes the execution of the statements within PL/SQL procedures. Each time the privilege for which auditing was configured is used Each time the object for which auditing was configured is operated upon 在審計宣告中使用BY ACCESS子句的好處 預設情況下，Oracle資料庫使用By ACCESS子句功能為每個已審計事件寫入新的審計記錄。 要使用此功能，請在AUDIT語句中包含BY ACCESS，或者如果需要，可以省略它，因為它是預設值。 （從Oracle Database 11g Release 2（11.2.0.2）開始，BY ACCESS子句是預設設定。） Benefits of Using the BY ACCESS Clause in the AUDIT Statement By default, Oracle Database writes a new audit record for every audited event, using the BY ACCESS clause functionality. To use this functionality, either include BY ACCESS in the AUDIT statement, or if you want, you can omit it because it is the default. (As of Oracle Database 11g Release 2 (11.2.0.2), the BY ACCESS clause is the default setting.) Oracle建議您在audit語句中審計BY ACCESS而不是BY SESSION。 在AUDIT語句中使用BY ACCESS子句的好處如下： 1.透過BY ACCESS審計選項生成的審計記錄包含更多資訊，如執行狀態（返回程式碼）、執行日期和時間、使用的許可權、訪問的物件、SQL文字本身及其繫結值。 2.此外，BY ACCESS審計選項捕獲每次執行的SCN，這有助於閃回查詢。 3.Oracle資料庫分別記錄SQL語句的每次執行、許可權的使用以及對審計物件的訪問。 4.考慮到每次執行時記錄的返回程式碼、時間戳和SQL文字的值都是準確的，這可以幫助您確定執行了多少次操作。 5.BY ACCESS審計記錄有單獨的LOGON和LOGOFF條目，每個條目都有細粒度的時間戳。 Oracle recommends that you audit BY ACCESS and not BY SESSION in your AUDIT statements. The benefits of using the BY ACCESS clause in the AUDIT statement are as follows: The audit records generated through the BY ACCESS audit option have more information, such as execution status (return code), date and time of execution, the privileges used, the objects accessed, the SQL text itself and its bind values. In addition, the BY ACCESS audit option captures the SCN for each execution and this can help flashback queries. Oracle Database records separately each execution of a SQL statement, the use of a privilege, and access to the audited object. Given that the values for the return code, timestamp, SQL text recorded are accurate for each execution, this can help you find how many times the action was performed. The BY ACCESS audit records have separate LOGON and LOGOFF entries, each with fine-grained timestamps. 例如： AUDIT SELECT TABLE BY ACCESS; 在這種情況下： 使用者jward連線到資料庫，對名為departments的表發出五條SELECT語句，然後斷開與資料庫的連線。 使用者swillims連線到資料庫，對departments表發出三條SELECT語句，然後斷開與資料庫的連線。 審計跟蹤包含八條記錄，每個SELECT語句記錄一條記錄。 In this scenario: The user jward connects to the database and issues five SELECT statements against the table named departments and then disconnects from the database. The user swilliams connects to the database and issues three SELECT statements against the departments table and then disconnects from the database. The audit trail contains eight records, one recorded for each SELECT statement. 審計特定使用者執行的操作 語句和許可權審計選項可以審計任何使用者釋出的語句或特定使用者列表釋出的語句。 透過關注特定使用者，您可以最大限度地減少生成的審計記錄的數量。 Auditing Actions Performed by Specific Users Statement and privilege audit options can audit statements issued by any user or statements issued by a specific list of users. By focusing on specific users, you can minimize the number of audit records generated. 示例9-6顯示了使用者scott和blake在查詢或更新表或檢視時如何審計語句。 Example 9-6 shows how to audit statements by users scott and blake when they query or update a table or view. 示例9-6使用AUDIT審計使用者操作 Example 9-6 Using AUDIT to Audit User Actions AUDIT SELECT TABLE, UPDATE TABLE BY scott, blake BY ACCESS; 使用NOAUDIT SQL語句刪除審計選項 NOAUDIT語句將刪除審計選項。使用它可以重置語句和許可權審計選項以及物件審計選項。 設定語句和許可權審計計項的NOAUDIT語句可以包含BY使用者子句，以指定使用者列表來限制語句和許可權審計選項的範圍。 可以使用NOAUDIT語句使用WHEEVER子句選擇性地禁用審計選項。 如果未指定該子句，則對於成功和不成功的情況，將完全禁用審計選項。 NOAUDIT語句不支援BY ACCESS子句。您可以使用適當的NOAUDIT語句刪除審計選項，無論它們是如何開啟的。 Removing the Audit Option with the NOAUDIT SQL Statement The NOAUDIT statement removes the audit option. Use it to reset statement and privilege audit options, and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY user clause to specify a list of users to limit the scope of the statement and privilege audit options. You can use the NOAUDIT statement to disable an audit option selectively using the WHENEVER clause. If the clause is not specified, then the auditing option is disabled entirely, for both successful and unsuccessful cases. The NOAUDIT statement does not support the BY ACCESS clause. You can remove audit options, no matter how they were turned on, by using an appropriate NOAUDIT statement.
八：審計SQL語句
Auditing SQL Statements 示例： Example 9-7 Using AUDIT to Enable SQL Statement Auditing AUDIT SELECT TABLE BY ACCESS; Example 9-8 Auditing Unsuccessful Statements AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE BY ACCESS WHENEVER NOT SUCCESSFUL; 例如，要審計使用者jward和jsmith發出的所有成功宣告，請輸入以下內容： AUDIT ALL STATEMENTS BY jward, jsmith BY ACCESS WHENEVER SUCCESSFUL; 審計單個使用者執行的所有SQL語句快捷方式活動。 您可以使用ALL子句審計Oracle資料庫SQL語言參考中表13-1和表13-2中列出的所有SQL語句快捷方式 Auditing all the SQL statement shortcut activities performed by individual users. You can use the ALL clause to audit all the SQL statement shortcuts listed in Table 13-1 and Table 13-2 in Oracle Database SQL Language Reference. For example: AUDIT ALL BY jward BY ACCESS; 例如，要審計任何當前使用者會話中所有不成功的語句： AUDIT ALL STATEMENTS IN SESSION CURRENT BY ACCESS WHENEVER NOT SUCCESSFUL; 登入觸發器功能可以確定應該更全面地審計此連線。發出以下SQL命令： AUDIT ALL STATEMENTS IN SESSION CURRENT; 審計登入和登出連線和斷開連線。 AUDIT SESSION語句為每個登入和登出事件生成一個獨立的審計記錄。 這使您能夠審計與資料庫的所有成功連線和不成功連線以及與資料庫的斷開連線，無論使用者是誰。 AUDIT SESSION BY ACCESS; 也可以為單個使用者選擇性地設定此選項，如下例所示： AUDIT SESSION BY jward, jsmith BY ACCESS; 審計由於物件不存在而失敗的語句。 AUDIT語句的NOT EXISTS選項指定審計所有由於目標物件不存在而失敗的SQL語句。 AUDIT NOT EXISTS; 刪除SQL語句審計 Removing SQL Statement Auditing Example 9-9 Using NOAUDIT to Remove Session and SQL Statement Auditing NOAUDIT session; NOAUDIT session BY preston, sebastian; NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE; Example 9-10 Using NOAUDIT to Remove ALL STATEMENTS Auditing NOAUDIT ALL STATEMENTS;
九：審計許可權
Auditing Privileges 示例： Example 9-11 Using AUDIT to Configure Privilege Auditing AUDIT DELETE ANY TABLE BY ACCESS; 要審計DELETE ANY TABLE系統許可權的所有成功和不成功使用，請輸入以下語句： AUDIT DELETE ANY TABLE BY ACCESS; 刪除許可權審計 以下語句將刪除所有許可權審計選項： NOAUDIT ALL PRIVILEGES; 此示例禁用示例9-11中的審計設定： This example disables the audit settings from Example 9-11: NOAUDIT DELETE ANY TABLE;
十：審計模式物件
Auditing Schema Objects 示例： Example 9-13 Configuring Auditing for a Schema Table AUDIT DELETE ON laurel.emp BY ACCESS; Example 9-14 Auditing Successful Statements on a Schema Table AUDIT SELECT, INSERT, DELETE ON jward.dept BY ACCESS WHENEVER SUCCESSFUL; Example 9-15 Configuring Auditing for Any New Objects Using the DEFAULT Clause AUDIT SELECT ON DEFAULT BY ACCESS WHENEVER NOT SUCCESSFUL; Example 9-16 Auditing the Execution of a Procedure or Function AUDIT EXECUTE ON sec_mgr.auth_orders BY ACCESS; AUDIT INSERT TABLE BY ACCESS; AUDIT ALL ON DEFAULT BY ACCESS; AUDIT ALTER, DELETE ON DEFAULT BY ACCESS; 移除物件審計 Removing Object Auditing 示例： NOAUDIT DELETE ON emp; NOAUDIT SELECT, INSERT, DELETE ON jward.dept; NOAUDIT ALL ON emp; NOAUDIT ALL ON DEFAULT;
十一：審計目錄物件
Auditing Directory Objects Example 9-17 Auditing a Directory Object AUDIT EXECUTE ON DIRECTORY my_exec BY ACCESS; Removing Directory Object Auditing Use the NOAUDIT statement to disable directory object auditing. For example: NOAUDIT EXECUTE ON DIRECTORY my_exec; Auditing Functions, Procedures, Packages, 和 Triggers 示例 Example 9-18 Auditing All Functions, Procedures, Packages, and Triggers AUDIT EXECUTE PROCEDURE BY ACCESS; Example 9-19 Auditing a User's Execution of Functions, Procedures, Packages, and Triggers AUDIT EXECUTE PROCEDURE BY psmith BY ACCESS; Example 9-20 Auditing the Execution of a Procedure or Function within a Schema AUDIT EXECUTE ON sales_data.check_work BY ACCESS WHENEVER SUCCESSFUL; Removing the Auditing of Functions, Procedures, Packages, 和 Triggers Use the NOAUDIT statement to remove the auditing of functions, procedures, and triggers. For example: NOAUDIT EXECUTE PROCEDURE; NOAUDIT EXECUTE PROCEDURE BY psmith; NOAUDIT EXECUTE ON sales_data.checkwork;
十二：審計網路活動
Auditing Network Activity 示例： AUDIT NETWORK BY ACCESS; NOAUDIT NETWORK; 表9-4可審計網路錯誤條件 Table 9-4 Auditable Network Error Conditions TNS-02507 Encryption algorithm not installed TNS-12648 Encryption or data integrity algorithm list empty TNS-12649 Unknown encryption or data integrity algorithm TNS-12650 No common encryption or data integrity algorithm
十三：審計使用者SYS和以SYSDBA和SYSOPER身份連線的使用者
Auditing User SYS and Users Who Connect as SYSDBA and SYSOPER 您可以完全審計以SYS身份連線的使用者的會話，包括使用SYSDBA或SYSOPER許可權連線的所有使用者。 這使您能夠將管理使用者的操作寫入作業系統檔案，即使AUDIT_TRAIL引數設定為NONE、DB或DB、EXTEND。 將管理員使用者的操作寫入作業系統審計檔案比寫入SYS.AUD$表更安全，因為管理員使用者可以從此表中刪除表示其不良行為的行。 You can fully audit sessions for users who connect as SYS, including all users connecting using the SYSDBA or SYSOPER privileges. This enables you to write the actions of administrative users to an operating system file, even if the AUDIT_TRAIL parameter is set to NONE, DB, or DB, EXTENDED. Writing the actions of administrator users to an operating system audit file is safer than writing to the SYS.AUD$ table, because administrative users can remove rows from this table that indicate their bad behavior. 要配置SYSDBA和SYSOPER使用者的審計設定，請執行以下操作： To configure audit settings for SYSDBA and SYSOPER users: 將AUDIT_SYS_OPERATIONS初始化引數設定為TRUE。 Set the AUDIT_SYS_OPERATIONS initialization parameter to TRUE. ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE; 此設定記錄使用SYSDBA或SYSOPER許可權連線到資料庫的使用者直接發出的頂級操作。 它將審計記錄寫入作業系統審計跟蹤。 每個語句的SQL文字都會寫入作業系統審計跟蹤記錄中的ACTION欄位。 This setting records the top-level operations directly issued by users who have connected to the database using the SYSDBA or SYSOPER privilege. It writes the audit records to the operation system audit trail. The SQL text of every statement is written to the ACTION field in the operating system audit trail record. 如果要將系統管理員活動寫入XML檔案，請將AUDIT_TRAIL初始化引數設定為XML或XML EXTEND。 If you want to write system administrator activities to XML files, then set the AUDIT_TRAIL initialization parameter to either XML or XML, EXTENDED. 示例： For example: ALTER SYSTEM SET AUDIT_TRAIL=XML, EXTENDED SCOPE=SPFILE; 在所有作業系統中，如果將AUDIT_TRAIL設定為XML或XML EXTEND，則審計記錄將作為XML檔案寫入AUDIT_FILE_DEST初始化引數指定的目錄中。 預設情況下，Oracle資料庫將審計記錄寫入作業系統檔案。 In all operating systems, if you set AUDIT_TRAIL to either XML or XML,EXTENDED, then audit records are written as XML files in the directory specified by the AUDIT_FILE_DEST initialization parameter. By default, Oracle Database writes the audit records to operating system files. See Table 9-1, "AUDIT_TRAIL Initialization Parameter Settings" for more information about these settings. See also "Enabling or Disabling the Standard Audit Trail". 重啟資料庫 Restart the database. 重新啟動資料庫後，Oracle資料庫將審計SYSDBA和SYSOPER使用者執行的所有成功操作，並將這些審計記錄寫入作業系統審計跟蹤，而不是SYS.AUD$表。 在Windows中，如果設定了AUDIT_TRAIL初始化引數OS，則Oracle資料庫會將審計記錄作為事件寫入事件檢視器日誌檔案。 After you restart the database, Oracle Database audits all successful actions performed by SYSDBA and SYSOPER users, and writes these audit records to the operating system audit trail, and not to the SYS.AUD$ table. In Windows, if you have set the AUDIT_TRAIL initialization parameter OS, then Oracle Database writes audit records as events to the Event Viewer log file. 示例，執行如下操作： CONNECT SYS AS SYSDBA; Enter password: password ALTER SYSTEM FLUSH SHARED_POOL; UPDATE salary SET base=1000 WHERE name='laurel'; 啟用SYS審計後，ALTER SYSTEM和UPDATE語句都顯示在作業系統審計檔案中，與以下輸出類似。 （請注意，此格式可能會在不同的Oracle資料庫版本中更改。） Tue May 5 04:53:37 2009 -07:00 LENGTH : '159' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[7] 'laurelh' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[9] '561542328' Tue May 5 04:53:40 2009 -07:00 LENGTH : '183' ACTION :[30] 'ALTER SYSTEM FLUSH SHARED_POOL' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[7] 'laurelh' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[9] '561542328' Tue May 5 04:53:49 2009 -07:00 LENGTH : '200' ACTION :[47] 'UPDATE salary SET base=1000 WHERE name='laurel'' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[7] 'laurelh' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[9] '561542328'
十四：使用觸發器將審計資料寫入單獨的表
您可以使用觸發器來補充Oracle資料庫的內建審計功能。 您建立的觸發器將使用者操作記錄到單獨的資料庫表中。 當活動觸發觸發器時，觸發器將在該表中記錄該操作。 當您想要記錄自定義資訊（如表更改前後）時，觸發器非常有用。 有關建立觸發器的詳細資訊，請參閱《Oracle Database PL/SQL Language Reference》。 您不需要為觸發器啟用審計，也不需要啟用什麼型別的審計。 觸發器在資料庫審計功能之外工作。 示例如下： Example 9-26 Audit Trigger to Record Before and After Changes to a Table /* 1. Create the following table: */ CREATE TABLE emp_tab ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(8,2), deptno NUMBER(2)); /* 2. Create a table to capture the audit data. */ CREATE TABLE emp_audit_tab ( oldname VARCHAR2(10), oldjob VARCHAR2(9), oldsal NUMBER (8,2), newname VARCHAR2(10), newjob VARCHAR2(9), newsal NUMBER(8,2), user1 varchar2(10), systemdate TIMESTAMP); /* 3. Create a trigger to record the old and new values, the author of the change, and when the change took place. */ CREATE OR REPLACE TRIGGER emp_audit_trig AFTER INSERT OR DELETE OR UPDATE ON emp_tab FOR EACH ROW BEGIN INSERT INTO emp_audit_tab ( oldname, oldjob, oldsal, newname, newjob, newsal, user1, systemdate ) VALUES ( :OLD.ename, :OLD.job, :OLD.sal, :NEW.ename, :NEW.job, :NEW.sal, user, sysdate ); END; /
十五：檢視審計記錄的
列出活動報表審計選項 以下查詢返回設定的所有語句審計選項： SELECT * FROM DBA_STMT_AUDIT_OPTS; 出現類似於以下內容的輸出： USER_NAME AUDIT_OPTION SUCCESS FAILURE -------------------- ------------------- ---------- --------- JWARD DROP ANY CLUSTER BY ACCESS BY ACCESS SWILLIAMS DEBUG PROCEDURE BY ACCESS BY ACCESS MSEDLAK ALTER RESOURCE COST BY ACCESS BY ACCESS 列出活動許可權審計選項 SELECT * FROM DBA_PRIV_AUDIT_OPTS; USER_NAME PRIVILEGE SUCCESS FAILURE ------------------- -------------------- --------- ---------- PSMITH BY ACCESS BY ACCESS 列出特定物件的活動物件審計選項 SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'LAUREL' AND OBJECT_NAME LIKE 'EMP%'; OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC ... ----- ----------- --------- --- --- --- --- --- --- --- --- ... LAUREL EMP TABLE S/S -/- -/- A/- -/- S/S -/- -/- ... LAUREL EMPLOYEE VIEW -/- -/- -/- A/- -/- S/S -/- -/- ... 列出預設物件審計選項 SELECT * FROM ALL_DEF_AUDIT_OPTS; ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/- /- -/- 列出審計記錄 SELECT * FROM DBA_AUDIT_OBJECT; 列出Audit SESSION選項的審計記錄 SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK FROM DBA_AUDIT_SESSION; USERNAME LOGOFF_TI LOGOFF_LRE LOGOFF_PRE LOGOFF_LWR LOGOFF_DLO ---------- --------- ---------- ---------- ---------- ---------- JWARD 02-AUG-91 53 2 24 0 SWILLIAMS 02-AUG-91 3337 256 630 0
參考：
Home / Database / Oracle Database Online Documentation 11g, Release 2 (11.2) / Database Administration Database Security Guide 9 Verifying Security Access with Auditing
###chenjuchao 20230225 20:00###
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2937030/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 開啟mysql 資料庫審計功能。MySql資料庫
- Oracle資料庫使用者安全策略功能介紹Oracle資料庫
- ORACLE 資料庫審計詳解Oracle資料庫
- ORACLE資料庫標準審計Oracle資料庫
- 審計Oracle資料庫的使用Oracle資料庫
- 資料倉儲—資料庫—Oracle 介紹資料庫Oracle
- 安全管理：polardb資料庫審計功能資料庫
- 【實驗】【審計】【FGA】使用Oracle的審計功能監控資料庫中的可疑操作Oracle資料庫
- 資料庫安全審計在資料安全中的功能資料庫
- Oracle 審計功能Oracle
- oracle審計功能Oracle
- Oracle資料庫字符集介紹Oracle資料庫
- 【審計】標準資料庫審計資料庫
- 資料庫介紹資料庫
- ORACLE的 審計功能Oracle
- Oracle FGA審計功能Oracle
- 常用Oracle資料庫調優工具介紹Oracle資料庫
- 資料庫審計-hexorbase資料庫HexoORB
- MySQL審計外掛介紹MySql
- Oracle FGA細粒度審計——基於內容的資料庫審計（一）Oracle資料庫
- Oracle FGA細粒度審計——基於內容的資料庫審計（二）Oracle資料庫
- Oracle FGA細粒度審計——基於內容的資料庫審計（三）Oracle資料庫
- 資料庫介紹--初識資料庫資料庫
- IndexedDB資料庫介紹Index資料庫
- 關於oracle審計功能Oracle
- Teradata資料庫功能操作簡單介紹(轉載)資料庫
- 【Oracle】Oracle logminer功能介紹Oracle
- 拍拍貸資料庫審計資料庫
- 資料庫DDL操作審計資料庫
- 舉例說明Oracle資料庫審計的用法Oracle資料庫
- 【FGA】將FGA細粒度審計功能的審計結果記錄在資料庫中資料庫
- L10資料庫——資料庫介紹資料庫
- Oracle資料庫event事件與dump檔案介紹Oracle資料庫事件
- Oracle資料庫登入流程的步驟介紹Oracle資料庫
- HSQL 資料庫介紹(1)--簡介SQL資料庫
- MySQL資料庫鎖介紹MySql資料庫
- postgresql資料庫鎖介紹SQL資料庫
- Oracle資料庫的閃回查詢功能簡介Oracle資料庫