In the previous article of the SQL Server Audit series, SQL Server Audit Feature Components, we discussed the three main components of the SQL Server Audit feature; the SQL Server Audit, the Server Audit Specifications, and the Database Audit Specifications in details. In this article, we will show how to use the SQL Server Audit feature to track, log and review the different operations performed at both the SQL Server and the database levels.
在“ SQL Server审核”系列的上一篇文章“ SQL Server审核功能组件”中 ，我们讨论了SQL Server审核功能的三个主要组件。 有关详细信息，请参见SQL Server审核，服务器审核规范和数据库审核规范。 在本文中，我们将展示如何使用SQL Server审核功能来跟踪，记录和查看在SQL Server和数据库级别执行的不同操作。
SQL Server实例级别的操作 (SQL Server Instance Level Actions)
SQL Server provides us with a number of server-level audit action groups that can be tracked and audited using the SQL Server Audit feature. These action groups include:
SQL Server为我们提供了许多服务器级别的审核操作组，可以使用SQL Server审核功能对其进行跟踪和审核。 这些行动小组包括：
- AUDIT_CHANGE_GROUP: This event is raised when any audit or audit specification is created, modified or deleted AUDIT_CHANGE_GROUP：创建，修改或删除任何审核或审核规范时，引发此事件
- BACKUP_RESTORE_GROUP: This event is raised when a backup or restore command is issued BACKUP_RESTORE_GROUP：发出备份或还原命令时引发此事件
- DATABASE_CHANGE_GROUP: This event is raised when a database is created, altered, or dropped DATABASE_CHANGE_GROUP：创建，更改或删除数据库时引发此事件
- DATABASE_OBJECT_CHANGE_GROUP: This event is raised when a CREATE, ALTER, or DROP statement is executed on database objects, such as schemas DATABASE_OBJECT_CHANGE_GROUP：在数据库对象（例如模式）上执行CREATE，ALTER或DROP语句时，会引发此事件
- DBCC_GROUP: This event is raised when any DBCC command is executed DBCC_GROUP：执行任何DBCC命令时，都会引发此事件
- FAILED_LOGIN_GROUP: This event is raised when a principal tried to log on to SQL Server and failed FAILED_LOGIN_GROUP：当主体尝试登录到SQL Server并失败时，引发此事件
- Note:注意：Server-Level Audit Action Groups document. 服务器级审核操作组”文档。
Starting from SQL Server 2012, SQL Server provides us with the ability to create user-defined audit events, that can be integrated with ant application and allow it to write a customized event using sp_audit_write procedure. In order to track and audit the event written by an application, the USER_DEFINED_AUDIT_GROUP should be selected in either the Server or the Database Audit Specification.
从SQL Server 2012开始，SQL Server使我们能够创建用户定义的审核事件，该事件可以与ant应用程序集成，并允许其使用sp_audit_write过程编写自定义事件。 为了跟踪和审核应用程序编写的事件，应在服务器或数据库审核规范中选择USER_DEFINED_AUDIT_GROUP。
Assume that you are requested to track and audit the action groups described previously. To do that, you need to configure and enable the Server Audit Specification below, that is bind to a SQL Server Audit:
假定要求您跟踪和审核前面描述的操作组。 为此，您需要配置并启用以下与SQL Server审核绑定的服务器审核规范：
To check how the Server Audit Specification can be used to audit server-level actions, we will perform number of actions at the SQL Server instance, as below:
为了检查如何使用服务器审核规范来审核服务器级别的操作，我们将在SQL Server实例上执行许多操作，如下所示：
DBCC CHECKDB ([AdventureWorks])
GO
BACKUP DATABASE [CTAudit] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\CTAudit.bak' WITH NOFORMAT, NOINIT,
NAME = N'CTAudit-Full Database Backup'
GO
CREATE DATABASE NewTestDB
Once these operations are performed, you will see that the SQL Server Engine tracked and logged these actions to the audit target specified in the SQL Server Audit. Whatever target specified in the SQL Server Audit, you can simply view the audit logs by right-clicking on the SQL Server Audit, under the server-level Security node, and choose the View Audit Logs options, as shown below:
执行完这些操作后，您将看到SQL Server Engine跟踪了这些操作并将其记录到SQL Server审核中指定的审核目标中。 无论在SQL Server审核中指定的目标是什么，您都可以通过右键单击服务器级“安全性”节点下的“ SQL Server审核”来查看审核日志 ，然后选择“ 查看审核日志”选项，如下所示：
In the displayed Log File Viewer, you can review all the tracked actions that are performed by the server principals, caught by the SQL Server Audit feature and logged to the SQL Server Audit target, as below:
在显示的“ 日志文件查看器”中 ，您可以查看由服务器主体执行，由SQL Server审核功能捕获并记录到SQL Server审核目标的所有跟踪操作，如下所示：
For example, the previously executed DBCC command, the performed database backup operation, the newly created database and the failed login attempt are audited and logged by the SQL Server Audit built-in feature, with complete details about the action date time, the operation type, the principal name, the server name, the database name, if the operation is completed successfully or failed, the executed statement and other useful information, as shown clearly below:
例如，以前执行的DBCC命令，执行的数据库备份操作，新创建的数据库和失败的登录尝试由SQL Server Audit内置功能审核和记录，其中包含有关操作日期时间，操作类型的完整详细信息，主体名称，服务器名称，数据库名称，操作成功或失败，执行的语句和其他有用信息，如下所示：
SQL Server数据库级操作 (SQL Server Database Level Actions)
SQL Server provides us also with a number of database level audit actions and action groups that can be tracked and audited using the SQL Server Audit feature. These actions and action groups include:
SQL Server还为我们提供了许多数据库级别的审核操作和操作组，可以使用SQL Server审核功能对其进行跟踪和审核。 这些行动和行动小组包括：
- DATABASE_PRINCIPAL_CHANGE_GROUP: This event is raised when a database user is created, altered, or dropped from a database DATABASE_PRINCIPAL_CHANGE_GROUP：当从数据库创建，更改或删除数据库用户时，引发此事件
- SCHEMA_OBJECT_CHANGE_GROUP: This event is raised when a CREATE, ALTER, or DROP operation is performed on a schema SCHEMA_OBJECT_CHANGE_GROUP：在架构上执行CREATE，ALTER或DROP操作时，将引发此事件
- SELECT: This event is raised when a SELECT statement is executed SELECT：执行SELECT语句时引发此事件
- INSERT: This event is raised when an INSERT statement is executed INSERT：执行INSERT语句时引发此事件
- DELETE: This event is raised when a DELETE statement is executed DELETE：执行DELETE语句时引发此事件
- Note: For a complete list of all database-level audit actions and action groups, check the 注意 ：有关所有数据库级审核操作和操作组的完整列表，请查看“ Database-Level Audit Action Groups document. 数据库级审核操作组”文档。
Assume that we are requested to design an audit solution to track the list of database-level actions and action groups described previously. To achieve that, we need configure and enable the database Audit Specification below, that is bind to a SQL Server Audit. Recall that when you select to audit an action group, you will not be able to specify the properties of the audited object. On the other hand, choosing an action to be audited will require you to specify the class of the audited object, the name of the object(s) to be audited and the name of the principal(s) that will be audited on the selected action, as shown below:
假设要求我们设计一个审核解决方案，以跟踪先前描述的数据库级操作和操作组的列表。 为此，我们需要配置并启用下面的数据库审核规范，该规范绑定到SQL Server审核。 回想一下，当您选择审核操作组时，将无法指定被审核对象的属性。 另一方面，选择要审核的操作将需要您指定被审核对象的类别，要审核的对象的名称以及将在所选对象上审核的主体的名称。动作，如下图：
To see how the Database Audit Specification can be used to audit the database-level actions, let us perform the operations below:
要查看如何使用数据库审核规范来审核数据库级操作，让我们执行以下操作：
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [test]
GO
CREATE SCHEMA HRB
GO
SELECT * FROM [dbo].[AWBuildVersion]
GO
INSERT INTO t2 VALUES (10,102)
GO
DELETE FROM T2 WHERE C1=10
GO
From the Log File Viewer, you can see that all the performed actions will be caught by the SQL Server Audit feature and logged to the SQL Server Audit target, as below:
从“ 日志文件查看器”中 ，您可以看到所有执行的操作将被SQL Server审核功能捕获并记录到SQL Server审核目标，如下所示：
For example, the previously executed CREATE USER statement, the newly created database schema and the executed SELECT, INSERT and DELETE statements are audited and logged by the SQL Server Audit built-in tool, with useful details that describe when the action is performed, the type of that operation, the name of the principal who performed this change, the server name from which the command executed, the database name, whether this operation is completed successfully or failed, the executed statement and more useful information, as shown below:
例如，先前执行的CREATE USER语句，新创建的数据库架构以及执行的SELECT，INSERT和DELETE语句由SQL Server Audit内置工具审核和记录，其中包含一些有用的详细信息，它们描述了何时执行操作，该操作的类型，执行此更改的主体的名称，从中执行命令的服务器名称，数据库名称，该操作是成功完成还是失败，执行的语句以及更多有用的信息，如下所示：
读取审核数据文件 (Reading Audit Data File)
If the SQL Server Audit is configured to write the audit data to a flat file, with SQLAUDIT extension, the data inside that file cannot be read directly from the file. If you open the audit file using any text editing tool, you can see only useless data, as the audit data is written to the file in binary, as shown below:
如果将SQL Server审核配置为将审核数据写入扩展名为SQLAUDIT的平面文件，则无法直接从该文件读取该文件中的数据。 如果使用任何文本编辑工具打开审核文件，则只能看到无用的数据，因为审核数据是以二进制形式写入文件的，如下所示：
In order to view the audit data that is written to the target flat file, the fn_get_audit_file() table-valued function should be used. This function accepts three parameters:
为了查看写入目标平面文件的审核数据，应使用fn_get_audit_file（）表值函数。 此函数接受三个参数：
- File Pattern: that specifies the path or the path with file name for the audit file set to be read. You can read all the audit files in a specific folder by specifying the path of the folder with the asterisk (*) 文件模式 ：指定要读取的审核文件的路径或带有文件名的路径。 您可以通过使用星号（*）指定文件夹的路径来读取特定文件夹中的所有审核文件。
- Initial File Name: that specifies the path and name of a specific file in the audit file set to start reading audit records from 初始文件名 ：指定审核文件集中特定文件的路径和名称，以开始从中读取审核记录
- Audit record Offset: that specifies a known location in the specified initial_file_name. If this parameter is provided, the function will start reading at the first record of the buffer immediately following the specified offset 审核记录偏移量：在指定的initial_file_name中指定已知位置。 如果提供此参数，则函数将在指定偏移量之后立即从缓冲区的第一条记录开始读取
The fn_get_audit_file function can be used within a SELECT statement that makes it more flexible to filter the audit data retrieved from the file to limit the number of retrieved columns or rows. For example, rather than displaying all the audit file content, you can use the fn_get_audit_file function to read the actions performed during a specific period of time, a specific type of actions, the action performed by a specific principal, on a specific database and so on, as in the T-SQL script below:
可以在SELECT语句中使用fn_get_audit_file函数，该函数使筛选从文件中检索的审核数据更加灵活，以限制所检索的列或行的数量。 例如，您可以使用fn_get_audit_file函数来读取特定时间段内执行的操作，特定类型的操作，特定主体执行的操作，而不是特定数据库上的内容，而不是显示所有审核文件的内容。启用，如下面的T-SQL脚本中所示：
SELECT event_time,Action_id,succeeded,server_principal_name,database_name,object_name,Statement
FROM fn_get_audit_file('C:\Ahmad Yaseen\*',default,default)
WHERE Action_id in ('LGIF', 'CR','AL','BA','IN','DL')
And the result in our example will be like:
在我们的示例中，结果将如下所示：
重要注意事项 (Important Considerations)
- The SQL Server Audit file should be secured and protected from any unauthorized access. This can be achieved by writing the file to a secure location on a different server with limited permission to the auditors only, or simply writing the audit logs to the Windows Security log SQL Server审核文件应得到保护，并应防止未经授权的访问。 这可以通过将文件写入到另一台服务器上的安全位置（仅对审计员具有有限的权限），或者简单地将审计日志写入Windows安全日志来实现。
- When a database, with a Database Audit Specification configured on it, is attached to a new instance that has no SQL Server Audit, has SQL Server Audit that has a different GUID, or the new SQL Server instance does not support the SQL Server Audit feature, this Database Audit Specification will be orphaned and will not record any event. This situation can be corrected by binding the Database Audit Specification to an existing SQL Server Audit, or create a new SQL Server Audit and bind that Database Audit Specification to it 当数据库上配置了数据库审核规范的数据库附加到没有SQL Server审核的新实例，具有不同GUIDSQL Server审核或新SQL Server实例不支持SQL Server审核功能时，此数据库审核规范将被孤立并且不会记录任何事件。 通过将数据库审核规范绑定到现有SQL Server审核，或创建新SQL Server审核并将该数据库审核规范绑定到它，可以纠正这种情况。
- When a database, with Database Audit Specification, configured on it, participates in high availability solution such as Database Mirroring or Always On Availability Group, the mirrored and secondary replicas should contain a SQL Server Audit with the same GUID as in the Principal and the Primary servers. In addition, the SQL Server service account should have permission on the folder where the audit log files will be written 当配置了数据库审核规范的数据库参与高可用性解决方案（如数据库镜像或始终在线可用性组）时，镜像副本和辅助副本应包含一个SQL Server审核，该GUI的GUID与主体和主数据库中的相同服务器。 此外，SQL Server服务帐户应具有将写入审核日志文件的文件夹的权限
- -f flag in the startup parameters. In addition, the database administrator can fix the audit issue by starting the SQL Server instance in Single User mode using the -f标志以最小配置模式启动SQL Server实例。 另外，数据库管理员可以通过使用启动参数中的-m flag in the startup parameters -m标志以单用户模式启动SQL Server实例来解决审核问题。
- For better performance, audit only the events that you really need to track 为了获得更好的性能，请仅审核您真正需要跟踪的事件
In the next article, in this series, we will show how to audit the SQL Server using the system temporal table feature. Stay tuned!
在本系列的下一篇文章中，我们将展示如何使用系统时态表功能来审核SQL Server。 敬请关注！
目录 (Table of contents)
翻译自: https://www.sqlshack.com/using-the-sql-server-audit-feature-to-audit-different-actions/
1017
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
