一：什么是审计？ 二：为什么使用审计？ 三：审计最佳实践 四：强制审计 五：标准审计 六：操作系统跟踪 七：使用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###