SQL Server安全性和基于策略的管理–实践

introduction article we have described concepts, terms, and common Policy Based Management tasks. The SQL Server Policy Based Management feature can be used in various scenarios and for different purposes. Using once declared policies and the Export/Import feature, maintaining SQL Server security in large enterprises with a number of SQL Server instances can be an automated process 简介文章中，我们描述了概念，术语和常见的基于策略的管理任务。 SQL Server基于策略的管理功能可用于各种方案中，并用于不同的目的。 使用一次声明的策略和导出/导入功能，可以在具有多个SQL Server实例的大型企业中维护SQL Server安全性，这是一个自动化的过程。

In this article, we are going to describe a step-by-step process of creating appropriate policies for the login Password expiration enabled and database user Authentication type properties consistency evaluation as aspects of SQL Server security

在本文中，我们将描述一个逐步过程，该过程为登录启用密码过期和数据库用户身份验证类型属性一致性评估创建适当的策略，作为SQL Server安全性的各个方面

Note that the whole process of creating and evaluating policies can be done using either T-SQL or SQL Server Management Studio. To make this article more descriptive we’ll focus on SQL Server Management Studio. However, to check appropriate T-SQL, you can use the Script Policy as and Script Condition as options in the context menu of corresponding Policy Based Management objects in Object Explorer

请注意，可以使用T-SQL或SQL Server Management Studio来完成创建和评估策略的整个过程。 为了使本文更具描述性，我们将重点介绍SQL Server Management Studio。 但是，要检查适当的T-SQL，可以在“ 对象资源管理器”中相应的“基于策略的管理”对象的上下文菜单中使用“ 脚本策略 ”和“ 脚本条件”作为选项。

Each policy primary depends on the condition that will be used. Besides the condition, the policy is additionally defined against target objects

每个主要策略取决于将要使用的条件。 除了条件之外，还针对目标对象定义了策略

条件创造 (Conditions creation)

To create a condition:

要创建条件：

1. Start SQL Server Management Studio and expand the Management node. Expand the Policy Management node and open the context menu of the Conditions sub-node. Select the New Condition option
   启动SQL Server Management Studio，然后展开“ 管理”节点。 展开“ 策略管理”节点，然后打开“ 条件”子节点的上下文菜单。 选择新条件选项
2. The Create New Condition dialog will open. Type in the name of the new condition (e.g. ConditionPasswordExpirationEnabled). From the Facets dropdown menu select the one which contains a property we want to create condition for. In our case it is the Login facet. Note that each condition can use one facet only
   将打开“ 创建新条件”对话框。 输入新条件的名称（例如ConditionPasswordExpirationEnabled ）。 从“ 构面”下拉菜单中，选择一个包含我们要为其创建条件的属性的属性。 在我们的情况下，它是“ 登录”构面。 请注意，每个条件只能使用一个构面
3. In the Expression grid, set the Field, Operator, and Value dropdown options to the following values: @PasswordExpirationEnabled, =, and True. This means that the condition will check whether the Password expiration enabled property is set on particular login entities
   在“ 表达式”网格中，将“ 字段”，“运算符”和“ 值”下拉选项设置为以下值： @PasswordExpirationEnabled ，=和True 。 这意味着该条件将检查是否在特定的登录实体上设置了“启用密码过期”属性

4. Click OK to save the condition

   

   单击确定保存条件

5. Similarly, using appropriate steps (1-4), create another condition (e.g. ConditionDatabaseAuthenticationType) using the User Options facet, @LoginType field, ‘=’ operator, and WindowsUser value

   

   The condition will test whether a user’s login type is using the Windows authentication

   同样，使用适当的步骤（1-4），使用“ 用户选项”构面， @ LoginType字段，“ =”运算符和WindowsUser值创建另一个条件（例如ConditionDatabaseAuthenticationType ）

   该条件将测试用户的登录类型是否正在使用Windows身份验证

The newly created conditions will be shown under the Conditions node in Object Explorer

新创建的条件将显示在“ 对象资源管理器”中的“ 条件”节点下

政策制定 (Policies creation)

After the needed conditions are created, we can proceed with declaring and creating policies. To create a policy:

创建所需条件后，我们可以继续声明和创建策略。 创建策略：

1. Select the New Policy option in the context menu of the Policies node
   在“ 策略”节点的上下文菜单中选择“ 新建策略”选项。
2. This will open the Create New Policy dialog. Type in the name for the policy (e.g. PolicyLoginPasswordExpiration)
   这将打开“ 创建新策略”对话框。 输入策略名称（例如PolicyLoginPasswordExpiration ）

3. Select previously created ConditionPasswordExpirationEnabled condition using the Check condition dropdown menu. Note that the dropdown menu provides conditions sorted per used facets during their creation. We have used the Login and User Options facets, and our conditions are listed under these facets. This way you can to quickly locate needed condition

   

   使用检查条件下拉菜单选择先前创建的ConditionPasswordExpirationEnabled条件。 请注意，下拉菜单提供了在创建时按使用面分类的条件。 我们使用了“ 登录”和“ 用户选项”构面，我们的条件在这些构面下列出。 这样您可以快速找到所需条件

4. The Against Targets box will automatically show the Every Login target. The target can be additionally customized, for instance to check only the logins with the name that matches specific pattern. We’ll explain the customization in further text in context of another policy

   

   “ 针对目标”框将自动显示“ 每次登录”目标。 可以另外自定义目标，例如，仅检查名称与特定模式匹配的登录。 我们将在其他政策的背景下进一步说明自定义

5. The next step is to define the policy evaluation mode. Default is the On demand mode. However, we will choose the On schedule mode via the Evaluation Mode dropdown menu. Click the New button to open a common SQL Server Agent job schedule dialog. After you define the schedule, it will be shown under its name in the Schedule box (e.g. MorningCheck). You can also use the Pick option to open the list of available schedules that already exist on the SQL Server instance

   Using the Server restriction option, you can specify, for instance, that the policy is applicable to specific SQL Server instances (e.g. SQL Server 2012 and newer versions)

   下一步是定义策略评估模式。 默认为按需模式。 但是，我们将通过评估模式下拉菜单选择按计划模式。 单击“ 新建”按钮以打开常见SQL Server代理作业计划对话框。 定义时间表后，它将显示在“ 时间表”框中的名称下方（例如MorningCheck ）。 您还可以使用“ 选择”选项打开SQL Server实例上已经存在的可用计划列表。

   使用“ 服务器限制”选项，您可以例如指定该策略适用于特定SQL Server实例（例如SQL Server 2012和更高版本）

6. Click the OK button to save the policy
   单击确定按钮以保存策略

Similarly, using the same steps (1-3), create another policy (e.g. PolicyDatabaseAuthenticationType) with the ConditionDatabaseAuthenticationType condition. Note that there are two targets in the Against Targets box now. There are the Every User and Every Database targets since user entities exist in multiple SQL Server database objects. This time, we are going to customize targets and narrow down their scope by specifying a match pattern for database names (e.g. database name starts with ACME):

类似地，使用相同的步骤（1-3），使用ConditionDatabaseAuthenticationType条件创建另一个策略（例如PolicyDatabaseAuthenticationType ）。 请注意，“ 针对目标”框中现在有两个目标。 由于用户实体存在于多个SQL Server数据库对象中，因此存在“ 每个用户”和“ 每个数据库”目标。 这次，我们将通过为数据库名称指定匹配模式来定制目标并缩小目标范围（例如，数据库名称以ACME开头）：

1. Open the Every dropdown menu and select the New condition option, as shown below

   

   打开“ 每个”下拉菜单，然后选择“ 新条件”选项，如下所示

2. This will open the Create New Condition dialog we have previously described. However, this time we’ll use it to declare the condition which will narrow down the targets in the policy
   这将打开我们之前描述的“ 创建新条件”对话框。 但是，这次我们将使用它来声明条件，以缩小策略中的目标范围

3. Type in the condition name (e.g. OnlyACMEDBs), leave the Database facet value (it’s set by default since we are creating the condition on databases), and set the expression to @Name LIKE ‘ACME%’ using the Expression grid, as shown below

   

   输入条件名称（例如OnlyACMEDBs ），保留数据库构面值（由于我们正在数据库上创建条件，因此默认设置），然后使用表达式网格将表达式设置为@Name LIKE'ACME％' ，如下所示

4. To save the condition and return to the PolicyDatabaseAuthenticationType policy creation dialog click OK
   要保存条件并返回到PolicyDatabaseAuthenticationType策略创建对话框，请单击“ 确定”。

5. The Create New Policy (PolicyDatabaseAuthenticationType) dialog will look like this:

   

   This time, we’ll leave the Evaluation mode option with the default On demand value. To save the policy click OK

   创建新策略 （ PolicyDatabaseAuthenticationType ）对话框将如下所示：

   这次，我们将保留默认的On demand value的评估模式选项。 要保存该策略，请单击“ 确定”。

Once the policies are created, they are shown under the Policies node in Object Explorer. Note that the OnlyACMEDBs condition is also available for further use

创建策略后，它们将显示在“ 对象资源管理器”中 “ 策略”节点下。 请注意， OnlyACMEDBs条件也可以进一步使用

If needed, each condition or policy can be modified by selecting the Properties context menu option. Also, policies can be exported/imported from/to SQL Server instances (via context menu in Object Explorer) and instantly applied ensuring security consistency in the enterprise

如果需要，可以通过选择“ 属性”上下文菜单选项来修改每个条件或策略。 此外，可以从SQL Server实例中导入/导出策略到SQL Server实例（通过对象资源管理器中的上下文菜单），也可以立即应用策略以确保企业中的安全性

政策评估 (Policy evaluation)

Policies can be evaluated per their evaluation mode (as described in the previous article). In our example we have created two policies. One is set to be automatically evaluated based on the schedule, and the other one is set to be evaluated on user’s demand

可以按照策略的评估模式对策略进行评估（如上一篇文章中所述 ）。 在我们的示例中，我们创建了两个策略。 一项设置为根据计划自动评估，另一项设置为根据用户需求进行评估

Whatever the policy evaluation mode is used, the policy can always be evaluated manually. To do that select the Evaluate context menu option of the specific policy (e.g. PolicyLoginPasswordExpiration). The Evaluate Policies dialog will open

无论使用哪种策略评估模式，都可以始终手动评估策略。 为此，请选择特定策略的“ 评估上下文”菜单选项（例如PolicyLoginPasswordExpiration ）。 将打开“ 评估策略”对话框

The dialog will provide the evaluation result for each target object in the Target details grid. In our example one login complies with the policy rule, while the other one does not

该对话框将在“ 目标详细信息”网格中提供每个目标对象的评估结果。 在我们的示例中，一次登录符合策略规则，而另一次则不符合

To apply the policy to the target that doesn’t comply the rule, check the box next to the target item in the grid and click the Apply button. The policy will modify the target login according to the rule (in our case it will set the login’s Password expiration enabled property). The dialog will automatically re-evaluate the policy:

要将策略应用于不符合规则的目标，请选中网格中目标项目旁边的框，然后单击“ 应用”按钮。 该策略将根据规则修改目标登录名（在我们的示例中，它将设置登录名的Password expiration enabled属性 ）。 该对话框将自动重新评估策略：

In this article, we have described the necessary steps to create policy conditions, policies that use the conditions, and how to evaluate the policies against specific target objects. Finally, we described how the particular target object that does not comply with the policy can be automatically fixed. Policy Based Management is a powerful SQL Server feature that helps ensuring security across enterprise by applying declared values to SQL Server security related properties, both on SQL Server instances and their objects. Moreover, it provides ways to evaluate policies on target objects and apply the policies where required

在本文中，我们描述了创建策略条件的必要步骤，使用这些条件的策略以及如何针对特定目标对象评估策略。 最后，我们描述了如何自动修复不符合策略的特定目标对象。 基于策略的管理是一项强大SQL Server功能，可通过将声明的值应用于SQL Server实例及其对象上与SQL Server安全相关的属性来帮助确保整个企业的安全性。 此外，它提供了评估目标对象策略并在需要时应用策略的方法。

翻译自: https://www.sqlshack.com/sql-server-security-policy-based-management-practice/