SQL Server安全性和基于策略的管理–简介

Policy Based Management allows DBAs to define the preferred state of the SQL Server system components (e.g. instances and objects) and compare the compliance status with the preferred state. Properly declared policies ensure enforcing company rules in the SQL Server environment, and are commonly a part of the SQL Server security model

基于策略的管理允许DBA定义SQL Server系统组件的首选状态（例如，实例和对象），并将合规性状态与首选状态进行比较。 正确声明的策略可确保在SQL Server环境中强制执行公司规则，并且通常是SQL Server安全模型的一部分

The Policy Based Management feature is built on top of the SQL Server Management Objects collection (objects that are designed for programming all aspects of managing Microsoft SQL Server) which supports SQL Server 2000 and later versions. Therefore Policy Based Management can be utilized on versions prior to SQL Server 2008, for instance via the PowerShell subsystem and SQL Server Agent

基于策略的管理功能建立在支持SQL Server 2000及更高版本SQL Server管理对象集合（旨在对管理Microsoft SQL Server的所有方面进行编程的对象）之上。 因此，可以在SQL Server 2008之前的版本上使用基于策略的管理，例如通过PowerShell子系统和SQL Server代理。

基于策略的管理的概念 (Concepts of Policy Based Management)

The feature has three concepts:

该功能具有三个概念：

1. Policy management – policies are created and maintained by SQL Server policy administrators
   策略管理–策略由SQL Server策略管理员创建和维护
2. Explicit administration – one or more managed targets are explicitly checked by administrators whether they comply with a specific policy, or explicitly modify the targets according to a policy
   显式管理–管理员明确检查一个或多个托管目标是否符合特定策略，或者根据策略明确修改目标
3. 1. On demand – the policy is evaluated when explicitly specified by the administrator按需 –由管理员明确指定时评估策略
   2. On change: prevent – if selected, this automated mode will utilize DDL triggers in order to prevent violations of specific policy by rolling back the changes. Note that this evaluation mode requires the nested triggers option enabled on SQL Server. If that’s not the case, the evaluation will perform with errors or fail. To enable the nested triggers, open the 更改时：阻止 –如果选中，此自动模式将使用DDL触发器，以通过回滚更改来防止违反特定策略。 请注意，此评估模式需要在SQL Server上启用嵌套触发器选项。 如果不是这种情况，评估将执行错误或失败。 若要启用嵌套触发器，请打开特定SQL Server实例的“ Properties dialog for a particular SQL Server instance, navigate to the 属性”对话框，导航到“ Advanced page, and set the 高级”页面，然后将“ Allow Triggers to Fire Others property to 允许触发器触发其他人”属性设置为TrueTrue。
   3. On change: log only – the policy is automatically evaluated on changes that may violate the policy rules and event notifications are created更改时：仅记录日志 –对可能违反策略规则的更改自动评估策略，并创建事件通知
   4. On schedule – this evaluation mode uses SQL Server Agent jobs to automatically check policy violations per scheduled time按计划进行 –此评估模式使用SQL Server代理作业来按计划的时间自动检查策略违规

Automated evaluations may affect system performance, depending on the number of objects being evaluated against the particular policy. On the other hand, automated evaluation modes ensure consistent SQL Server security

自动评估可能会影响系统性能，具体取决于根据特定策略评估的对象数量。 另一方面，自动评估模式可确保一致SQL Server安全性

基于策略的管理中使用的术语 (Terms used in Policy Based Management)

There are several terms used in the SQL Server Policy Based Management feature

SQL Server基于策略的管理功能中使用了几个术语

• Policy Based Management managed target – an entity managed by Policy Based Management, such as a SQL Server instance, database, or table
• 基于策略的管理管理的目标 –由基于策略的管理管理的实体，例如SQL Server实例，数据库或表
• Policy Based Management facet – grouped logical properties that determine specific types of managed targets with their behavior or characteristics. One target can be managed by multiple facets and one facet can manage multiple target types. For example, the Database target is managed by the Database, Database Maintenance, Database Options, Database Performance, and Database Security facets 基于策略的管理方面 –分组的逻辑属性，用于确定受管理目标的特定类型及其行为或特征。 一个目标可以由多个方面管理，而一个方面可以管理多种目标类型。 例如，数据库目标由数据库，数据库维护，数据库选项，数据库性能和数据库安全性方面管理

• Policy Based Management condition – a logical expression against which the specified target state is evaluated. For example, whether a password policy is enforced or not on login entities. One condition (expression) can consists of one or more logical sub-expressions

  基于策略的管理条件 –用来评估指定目标状态的逻辑表达式。 例如，是否对登录实体实施密码策略。 一个条件（表达式）可以包含一个或多个逻辑子表达式

  

• Policy Based Management policy – a set of Policy Based Management condition, target, evaluation mode, and schedule. For example, a set of security rules specified for login entity, such as the MustChangePassword and PasswordExpirationEnabled properties. A policy can be enabled or disabled, depending on requirements. Note that one policy is limited to one condition

  基于策略的管理策略 –一组基于策略的管理条件，目标，评估模式和时间表。 例如，为登录实体指定的一组安全规则，例如MustChangePassword和PasswordExpirationEnabled属性。 可以根据需要启用或禁用策略。 请注意，一项政策仅限于一种情况

  

• Policy Based Management policy category – SQL Server offers creation of user defined policy categories. There are two types of policy categories for the servers and for the databases. Moreover, one policy can belong to one policy category only. The purpose of policy categories is to help in managing a large number of policies in enterprise environments基于策略的管理策略类别 – SQL Server提供了用户定义策略类别的创建。 服务器和数据库有两种类型的策略类别。 而且，一个策略只能属于一个策略类别。 策略类别的目的是帮助管理企业环境中的大量策略

常见的基于策略的管理任务 (Common Policy Based Management tasks)

There are several common tasks that need to be performed prior to evaluation of a SQL Server policy. First, you need to create the condition which will be used within the policy

在评估SQL Server策略之前，需要执行几个常见任务。 首先，您需要创建将在策略中使用的条件

Creating and modifying conditions

创建和修改条件

To create a new condition via SQL Server Management Studio, navigate to the Policy Management node in Object Explorer, expand the node and select the New Condition option from the Conditions sub-node context menu. This will open the Create New Condition dialog

要通过SQL Server Management Studio创建新条件，请导航至“ 对象资源 管理器”中的“ 策略管理”节点，展开该节点，然后从“ 条件”子节点上下文菜单中选择“ 新建条件”选项。 这将打开“ 创建新条件”对话框

The dialog provides following properties:

该对话框提供以下属性：

• Condition name – the one we’ll use later in the specific policy
  条件名称-我们稍后将在特定策略中使用的名称
• Facet dropdown menu – a list of all available facets to choose from. A condition is limited to one facet. For example, a condition cannot be used to specify properties both from the Login and Server Audit facets. However, there are shared properties between multiple facets such as the MustChangePassword property is shared with the Login and Login Options facets
  构面下拉菜单–所有可用构面的列表供您选择。 条件仅限于一个方面。 例如，不能同时使用条件从“登录”和“服务器审核”方面指定属性。 但是，多个构面之间存在共享属性，例如MustChangePassword属性与“登录”和“登录选项”构面共享
• Expression – the grid used to define a logical expression against which policy target state will be evaluated
  表达式–用于定义逻辑表达式的网格，将根据该逻辑表达式评估策略目标状态

Once created, the condition will be shown under the Conditions node and can be additionally modified if needed. The next step is to create policies with appropriate conditions

创建后，该条件将显示在“ 条件”节点下，并且可以根据需要进行其他修改。 下一步是创建具有适当条件的策略

Creating and modifying policies

创建和修改策略

To create a new policy, navigate to the Policy Management node in Object Explorer, expand the node and select the New Policy option from the Policies sub-node context menu. This will open the Create New Policy dialog where you can specify the name of the policy, select one of existing conditions, and specify the targets

要创建新策略，请导航至“ 对象资源 管理器”中的“ 策略管理”节点，展开该节点，然后从“ 策略”子节点上下文菜单中选择“ 新建策略”选项。 这将打开“ 创建新策略”对话框，您可以在其中指定策略名称，选择现有条件之一并指定目标

Note that some conditions can only be applied to certain target types. For example, you cannot apply a condition based on the Database facet to login entities as targets. If no targets appear in the dialog, the check condition is defined at the server level

请注意，某些条件只能应用于某些目标类型。 例如，您不能将基于数据库构面的条件应用于登录实体作为目标。 如果对话框中没有目标，则检查条件在服务器级别定义

Finally, the dialog provides the evaluation mode selection. Choose one of the previously described evaluation modes appropriate to the created policy, e.g. the On demand evaluation mode if the policy will be evaluated manually

最后，对话框提供评估模式选择。 选择适合于所创建策略的前述评估模式之一，例如，如果将手动评估策略，则按需评估模式

Once created policies are located in the Policies node and can be additionally edited

创建的策略一旦位于“ 策略”节点中，即可进行其他编辑

Exporting and importing policy

进出口政策

The exporting and importing policy features makes Policy Based Management transferable between SQL Server instances throughout enterprise environment. Each policy can be exported via its context menu (the Export Policy option) into an xml file. To import policy, select the appropriate option from the Policies node context menu. This will open the Import dialog that is used to specify one or more xml files, specify whether to replace duplicates, and set the state of imported items (Enabled, Disabled, or Preserve policy state)

导出和导入策略功能使基于策略的管理可以在整个企业环境中SQL Server实例之间转移。 每个策略都可以通过其上下文菜单（“导出策略”选项）导出到xml文件中。 要导入策略，请从“策略”节点上下文菜单中选择适当的选项。 这将打开“导入”对话框，该对话框用于指定一个或多个xml文件，指定是否替换重复项以及设置导入项目的状态（“启用”，“禁用”或“保留”策略状态）

Evaluating policies

评估政策

Policies can be evaluated based on their evaluation mode as we previously described. To manually evaluate the policy, or enforce scheduled evaluation, select the Evaluate option in the context menu of the policy

如前所述，可以根据策略的评估模式来评估策略。 要手动评估策略或强制执行计划的评估，请在策略的上下文菜单中选择“评估”选项

This will open the results of the evaluation indicating all target objects that do not comply with the policy

这将打开评估结果，指示所有不符合策略的目标对象

Additionally, you can select the View option and check the exact cause of the evaluation failure

此外，您可以选择“ 查看”选项并检查评估失败的确切原因

The policy evaluation feature provides a possibility to fix non-complying target property, but we’ll discuss that in the next article where we’ll provide a step-by-step Policy Based Management example that will ensure the login PasswordExpirationEnabled and database user AuthentiationType properties consistency as aspects of SQL Server security

策略评估功能提供了修复不符合目标的属性的可能性，但是我们将在下一篇文章中讨论，我们将提供一个分步基于策略的示例，该示例将确保登录PasswordExpirationEnabled和数据库用户AuthentiationType属性一致性作为SQL Server安全性的方面

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