SQL Server安全性和基于策略的管理–高级条件

The policy core consists of conditions and targets. Moreover, complex targets are defined by additional conditions, as we already showed in the SQL Server security and Policy Based Management – In practice article

政策核心包括条件和目标。 而且，复杂目标是由附加条件定义的，正如我们在《 SQL Server安全性和基于策略的管理–实践》文章中已经说明的那样

Along with predefined properties, conditions can utilize T-SQL queries too. In this article, we’ll describe and provide a step-by-step example for creating an advanced Policy Based Management policy which uses T-SQL within its condition. The example will provide the policy which verifies whether SQL SERVER Agent alerts were reset, which can be a serious security alert that requires further investigation

除了预定义的属性外，条件也可以利用T-SQL查询。 在本文中，我们将描述并提供一个分步示例，以创建高级的基于策略的管理策略，该策略在其条件下使用T-SQL。 该示例将提供用于验证是否重置了SQL SERVER Agent警报的策略，这可能是严重的安全警报，需要进一步调查。

The first thing you need to do when creating a new policy is to declare and create the condition on which the policy is based.

创建新策略时，您需要做的第一件事就是声明并创建该策略所基于的条件。

To create the condition:

要创建条件：

1. Start SQL Server Management Studio, and expand the Management node in Object Explorer
   启动SQL Server Management Studio，然后在“ 对象资源管理器”中展开“ 管理”节点
2. Expand the Policy Management node and select the New Condition option in the Conditions sub-node context menu
   展开“ 策略管理”节点，然后在“ 条件”子节点上下文菜单中选择“ 新条件”选项。
3. Type in the condition name (e.g. NumberOfResetAlerts) in the Create New Condition dialog, and select the Server item from the Facets dropdown menu
   在“ 创建新条件”对话框中输入条件名称（例如NumberOfResetAlerts ），然后从“ 构面”下拉菜单中选择“ 服务器”项

4. Click the “…” button next to the empty field in the Expression grid

   

   单击“ 表达式”网格中空白字段旁边的“…”按钮

5. This will open the Advanced Edit dialog. Scroll down the Functions and properties list, locate the ExecuteSql() function, and double-click it in order to add it to the Cell value text box

   

   The ExecuteSql(string returnType, string sqlQuery) value will be shown and ready for additional modification. The ExecuteSql() function has two parameters:

   • The result type which the function provides specified by the returnType string variable. Supported types are Numeric, String, Bool, DateTime, and GUID
   • T-SQL specified by the sqlQuery variable

   The function return value is the first column in the first row of the result set returned by the T-SQL query. Only one column should be specified in the SELECT statement. Any additional columns are ignored. The same applies to returned rows. The rows beyond the first one are not considered. In case the query result is an empty set or NULL, the condition expression built on the ExecuteSql() function will be false

   In our example, we’ll use ‘Numeric’ as a type, and ‘SELECT COUNT(*) from sdb.dbo.sysalerts where count_reset_date=0’ T-SQL as a query. The final expression in the Cell value text box will be:

   ExecuteSql(‘Numeric’, ‘SELECT COUNT(*) from msdb.dbo.sysalerts where count_reset_date=0’)

   Click Ok to save the cell expression value

   这将打开“ 高级编辑”对话框。 向下滚动“ 功能和属性”列表，找到ExecuteSql（）函数，然后双击以将其添加到“ 单元格值”文本框中

   将显示ExecuteSql（string returnType，string sqlQuery）值，并准备进行其他修改。 ExecuteSql（）函数具有两个参数：

   • 函数提供的结果类型由returnType字符串变量指定。 支持的类型为数字，字符串，布尔，日期时间和GUID
   • sqlQuery变量指定的T-SQL

   函数返回值是T-SQL查询返回的结果集第一行中的第一列。 SELECT语句中只能指定一列。 任何其他列都将被忽略。 这同样适用于返回的行。 不考虑第一行以外的行。 如果查询结果为空集或NULL，则基于ExecuteSql（）函数构建的条件表达式将为false

   在我们的示例中，我们将使用'Numeric'作为类型，并使用sdb.dbo.sysalerts中的'SELECT COUNT（*），其中count_reset_date = 0'T -SQL作为查询。 单元格值文本框中的最终表达式将是：

   ExecuteSql（'数字'，'从msdb.dbo.sysalerts中选择COUNT（*），其中count_reset_date = 0'）

   单击确定以保存单元格表达式值

6. This will close the Advanced Edit dialog and return to the New Condition dialog. Select the != operator and type in the 0 as the value.

   

   This will complete the condition expression which evaluates whether the msdb.dbo.sysalerts system table holds any records about alerts that were reset at some point in time. Note that the query within the ExecuteSql() function can be modified to meet the requirements, if needed. In addition, the Expression grid can hold multiple rows with different functions and queries in order to narrow down the required condition.

   这将关闭“高级编辑”对话框，并返回到“新条件”对话框。 选择！=运算符，然后输入0作为值。

   这将完成条件表达式，该条件表达式将评估msdb.dbo.sysalerts系统表是否保存有关在某个时间点重置的警报的任何记录。 注意，如果需要，可以修改ExecuteSql（）函数中的查询以满足要求。 另外，“ 表达式”网格可以容纳具有不同功能和查询的多行，以缩小所需条件的范围。

To create the policy that will use the NumberOfResetAlerts condition:

要创建将使用NumberOfResetAlerts条件的策略：

1. Select the New Policy option from the context menu of the Policies node. The Create New Policy Dialog will be opened
   从“ 策略”节点的上下文菜单中选择“ 新建策略”选项。 “ 创建新策略”对话框将打开。

2. Type in the policy name (e.g. ThereAreNoResetAlerts) and select the NumberOfResetAlerts condition from the Check condition dropdown list

   

   输入策略名称（例如ThereAreNoResetAlerts ），然后从“ 检查条件”下拉列表中选择NumberOfResetAlerts条件

3. Set the desired evaluation mode using the Evaluation Mode dropdown menu. Note that ExecuteSql() used in the condition supports the On Demand and On Schedule evaluation modes. On change evaluation modes are not supported
   使用评估模式下拉菜单设置所需的评估模式。 请注意，该条件中使用的ExecuteSql（）支持按需评估和按计划评估模式。 不支持变更时评估模式

Depending on the evaluation mode, consider using an appropriate SQL Server Agent alert that can automatically notify you about policy violations and help keep your SQL Server secure.

根据评估模式，请考虑使用适当的SQL Server代理警报，该警报可以自动向您通知有关策略违规的情况，并有助于确保SQL Server的安全。

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