基于SQL Server策略的管理–检查审核配置

The recent articles (see below) about the SQL Server Policy Based Management and Audit features explained terms, principles, and their use in various scenarios and for different purposes. SQL Server Policy Based Management provides means to define state of SQL Server instances and their objects across an enterprise environment and verify whether their current status complies with the declared policies. Among other SQL Server objects, there is a way to evaluate declared policies against the SQL Server Audit objects too. It’s possible to check whether the configuration of the audit and audit specification objects were altered and get notified about that.

有关SQL Server基于策略的管理和审核功能的最新文章（请参见下文）介绍了术语，原理及其在各种情况下和出于不同目的的用途。 SQL Server基于策略的管理提供了在整个企业环境中定义SQL Server实例及其对象的状态，并验证其当前状态是否符合声明的策略的方法。 在其他SQL Server对象中，还有一种方法也可以根据SQL Server Audit对象评估声明的策略。 可以检查审核和审核规范对象的配置是否已更改，并得到有关此方面的通知。

In the following example, we are going to see how once created auditing can be monitored for changes. We are going to set up an auditing on SQL Server instance, create an appropriate Policy Based Management policy, and describe how to evaluate the policy and get notifications about potential policy violations. We are going to focus on SQL Server Management Studio use in the example, although T-SQL can be used as well. If needed, you can check appropriate T-SQL using the Script as options in SQL Server Management Studio for each object we are going to create.

在下面的示例中，我们将看到如何监视一旦创建的审核的更改。 我们将在SQL Server实例上进行审核，创建适当的基于策略的管理策略，并描述如何评估策略并获取有关潜在策略违规的通知。 尽管也可以使用T-SQL，但我们将在示例中重点介绍SQL Server Management Studio的用法。 如果需要，您可以在SQL Server Management Studio中使用脚本作为选项为我们要创建的每个对象检查适当的T-SQL。

To set up the auditing on a SQL Server instance, an audit object must be created first:

要在SQL Server实例上设置审核，必须首先创建审核对象：

1. Security node in 对象资源管理器中展开“ Object Explorer安全性”节点
2. New Audit context menu option of the 审计”节点的“ Audits node新建审计上下文”菜单选项

3. Use the Create Audit dialog to define the audit object. Type in the name of the audit object (e.g. LoginRolePermissionChanges), select File in the Audit destination dropdown menu, and set the desired Maximum file size value (e.g. 15 MB). For the File path value, specify the desired folder where auditing repository files will be saved (e.g. C:\AUDITs)

   使用“ 创建审核”对话框来定义审核对象。 输入审核对象的名称（例如LoginRolePermissionChanges ），在“ 审核目标”下拉菜单中选择“ 文件 ”，然后设置所需的“ 最大文件大小”值（例如15 MB ）。 对于“ 文件路径”值，指定将在其中保存审核存储库文件的所需文件夹（例如C：\ AUDITs ）

   

4. Click OK to confirm the audit object creation

   单击“ 确定”确认创建审核对象

   The next step is to create an appropriate audit specification, which will specify the target and properties we want to audit. In our example, we are going to create auditing on SQL Server logins and changes applied on them regarding role and permission changes.

   下一步是创建适当的审核规范，该规范将指定我们要审核的目标和属性。 在我们的示例中，我们将创建对SQL Server登录名以及与角色和权限更改有关的更改应用审核。

   To create the audit specification on SQL Server instance level (note that audit specifications can be declared on database level for appropriate objects and values too):

   要在SQL Server实例级别上创建审核规范（请注意，也可以在数据库级别上为适当的对象和值声明审核规范）：

5. Security node and select the 安全性节点，并选择在New Server Audit Specification context menu option in the 服务器审核规范节点的Server Audit Specification node新服务器审核规范上下文菜单选项

6. Use the Create Server Audit Specification dialog to define the new audit specification. Type in the name of the specification in the Name textbox (e.g. LoginRolePermissionChanges_Specification). Select the audit object we have created previously from the Audit dropdown. Note that the dropdown shows all existing SQL Server instance audit objects, so this is the point where the audit specification is linked to the appropriate audit object

   使用“ 创建服务器审核规范”对话框来定义新的审核规范。 在名称文本框中输入规范的名称 （例如LoginRolePermissionChanges_Specification ）。 从“审核”下拉列表中选择我们先前创建的审核对象。 请注意，下拉列表显示了所有现有SQL Server实例审核对象，因此这是审核规范链接到适当的审核对象的地方

   

7. To define the items and actions to be audited for changes (in our case SQL Server login objects) use the Actions grid. Use the Audit Action Type dropdown in the grid and select the SERVER_ROLE_MEMBER_CHANGE_GROUP item. An additional empty row will be automatically created. Similarly as for the previous row, select the SERVER_PERMISSION_CHANGE_GROUP item using the Audit Action Type dropdown

   若要定义要审核更改的项目和操作（在我们的示例中为SQL Server登录对象），请使用“ 操作”网格。 使用网格中的“ 审核操作类型”下拉列表，然后选择SERVER_ROLE_MEMBER_CHANGE_GROUP项。 将会自动创建一个附加的空行。 与上一行类似，使用“ 审核操作类型”下拉列表选择SERVER_PERMISSION_CHANGE_GROUP项

   

   The selected action types cannot be additionally tuned using the Object Class, Object Schema, Object Name, or Principal Name values (columns) in the grid, as they are tied to the SQL Server instance itself

   所选操作类型无法使用网格中的“ 对象类”，“对象架构”，“对象名称”或“ 主体名称”值（列）进行额外的调整，因为它们与SQL Server实例本身相关联

8. OK to save the audit specification确定”保存审核规范

Note that once created audit object and audit specification are disabled by default and they need to be enabled. To enable them, use the appropriate context menu options (Enable Audit and Enable Audit Server Specification)

请注意，一旦创建审核对象和审核规范，默认情况下将其禁用，并且需要将其启用。 要启用它们，请使用适当的上下文菜单选项（“ 启用审核”和“ 启用审核服务器规范” ）

Next, create appropriate Policy Based Management policies that will be used to evaluate the declared auditing state with the state at the moment of evaluation. In other words, create policies based on the current auditing configuration and use them as a sort of snapshot when evaluating

接下来，创建适当的基于策略的管理策略，该策略将用于评估声明的审核状态以及评估时的状态。 换句话说，根据当前的审核配置创建策略，并在评估时将其用作快照

To create a policy tied up to the current state of the audit object:

要创建与审计对象的当前状态相关联的策略，请执行以下操作：

1. Right click the LoginRolePermissionChanges audit object and select the Facets option. The View Facets dialog will open and provide the current properties of the audit object that can be evaluated using the SQL Server Policy Based Management feature

   右键单击LoginRolePermissionChanges审核对象，然后选择“ 构 面”选项。 “ 查看构面”对话框将打开并提供审核对象的当前属性，可以使用“基于SQL Server策略的管理”功能对其进行评估

   

2. Click the Export Current State as Policy button to open the Export as Policy dialog. Type in the name for the new policy (e.g. Policy_LoginRolePermissionChanges) and the name for the corresponding policy condition (e.g. Condition_LoginRolePermissionChanges)

   单击“ 将当前状态导出为策略”按钮以打开“ 导出为策略”对话框。 输入新策略的名称（例如Policy_LoginRolePermissionChanges ）和相应策略条件的名称（例如Condition_LoginRolePermissionChanges ）

   

   The policy can be saved either to the current SQL Server instance (the To local server option), or to an XML file and imported for later use on the same or another SQL Server instance

   该策略可以保存到当前SQL Server实例（“ 到本地服务器”选项），也可以保存到XML文件，然后导入以供以后在相同或另一个SQL Server实例上使用。

3. Click OK to create the policy and its condition. The newly created policy and condition will show up under the appropriate Object Explorer nodes

   单击“ 确定”创建策略及其条件。 新创建的策略和条件将显示在适当的“ 对象资源管理器”节点下

   

The created policy and condition can be modified additionally, if needed. Let’s say the policy is created so that it is evaluated against all audit objects on the SQL Server instance. That is far from convenient, as it’s not likely that other audit objects comply with the current state of the LoginRolePermissionChanges audit object (e.g. the Create Date value). To change the policy target to the specific audit target instead of Every:

如果需要，可以另外修改创建的策略和条件。 假设创建了该策略，以便针对SQL Server实例上的所有审核对象对其进行评估。 这很不方便，因为其他审核对象不太可能符合LoginRolePermissionChanges审核对象的当前状态（例如， 创建日期值）。 要将策略目标更改为特定的审核目标，而不是将Every更改为：

1. Select the Properties option in the Policy_LoginRolePermissionChanges policy context menu
2. 在Policy_LoginRolePermissionChanges策略上下文菜单中选择“ 属性”选项

3. Click the New condition option in the Every dropdown list in the Against targets box

   在“ 针对目标”框中的“ 每个”下拉列表中，单击“ 新条件”选项。

   

4. Define the appropriate condition (@Name = ‘LoginRolePermissionChanges’) and save the condition

   定义适当的条件（ @Name ='LoginRolePermissionChanges' ）并保存条件

   

5. Policy_LoginRolePermissionChanges policy will now be declared against the specific audit object onlyPolicy_LoginRolePermissionChanges策略

Also, the Condition_LoginRolePermissionChanges condition we previously created by exporting the current audit object state can to be adjusted if needed. To do that:

同样，如果需要，我们可以通过导出当前审核对象状态来先前创建的Condition_LoginRolePermissionChanges条件。 要做到这一点：

1. Select the Properties option of the Condition_LoginRolePermissionChanges context menu
2. 选择Condition_LoginRolePermissionChanges上下文菜单的“ 属性”选项

3. The Expression grid will provide all the condition items created by default

   表达式网格将提供默认情况下创建的所有条件项

   

   Adjust existing rows by changing their values, add additional or remove the ones which are not required. Click OK to save condition changes

   通过更改现有行的值来调整现有行，添加其他行或删除不需要的行。 单击确定以保存条件更改

To evaluate the policy against the audit object, select the Evaluate option from the policy context menu. Since the audit object was not modified in the meantime, the audit object will comply with the declared policy:

要针对审核对象评估策略，请从策略上下文菜单中选择“ 评估”选项。 由于同时未修改审核对象，因此审核对象将符合声明的策略：

If we change any property of the audit object and then evaluate the policy again, the result will show that the audit object does not comply with the declared state and that it was altered. Note that in order to be modified, an audit object must be previously disabled

如果我们更改审核对象的任何属性，然后再次评估策略，结果将显示审核对象不符合声明的状态，并且已更改。 请注意，为了进行修改，必须事先禁用审核对象

We have used the manual policy evaluation in the example. However, as we have previously described in the SQL Server security and Policy Based Management – Alerting article, evaluation can be scheduled with an appropriate alert notification

在示例中，我们使用了手动策略评估。 但是，正如我们先前在“ SQL Server安全和基于策略的管理–警报”文章中所描述的那样，可以使用适当的警报通知来计划评估

翻译自: https://www.sqlshack.com/sql-server-policy-based-management-check-audit-configuration/