sql server中策略管理

sql server中策略管理

一、sql server中策略管理的位置

策略管理在SSMS的对象资源管理器数据库实例下的“管理”节点下，如图：

二、策略管理中包含三个节点：策略、条件、方面

1、方面

策略要应用的对象，包括：服务器、表、触发器、视图、存储过程……这些方面对象都是系统定义好了的，仅供瞻仰不可更改。双击具体的某一个方面可以查看该方面的属性，在定义条件时即可对这些属性进行判断，如图为存储过程方面的属性。

2、条件

是一个布尔表达式，在条件中引用方面对象的属性，用于判断策略是否为真。

3、策略

在条件为假时，根据评估模式执行相应的操作，或回滚事务，或记录异常消息。

三、策略中的评估模式

策略的评估模式有4种：按需、按计划、更改时记录和更改时禁止。

1、按需

当用户指定这种模式时，DBA可以手动调用策略来评估发面是否满足条件。

2、按计划

该模式使用 SQL Server 代理作业定期对目标对象进行策略评估，并记录违反策略的情况。

3、更改时-仅记录

SQL Server自动检查用户对方面的更新操作是否违反策略，如果违反策略则发送消息，仅仅记录违反侧露的日志消息。

4、更改时-禁止

最严格的评估模式，SQL Server自动使用 DDL 触发器来检查用户对方面的更新操作是否违反策略，如果违反策略则回滚该操作，以达到强制策略的效果。

四、实例：规范存储过程（Stored Procedure）的命名

假设现在我们要开发个业务系统，其数据库为TestDB1，使用ADO.NET 调用存储过程来实现数据操作，现在项目中规定存储过程的命名规范：以“usp_”开头。这里我们可以使用策略管理来实现对该规范的检查或强制实行。

1、在“方面”节点下右击“存储过程”，选择“新建条件”选项，系统将会弹出新建条件的窗口。

2、输入“条件”的名称：“存储过程命名规范”，然后字段列表中选择@Name，运算符为LIKE，值为'usp[_]%'。也就是判断存储过程的名字LIKE 'usp[_]%'，也就是以“usp_”开头的SQL表达。如图：

3、这里字段和值都可以使用变量和函数，如果允许“USP_”、“Usp_”等开头的存储过程，则可以将字段运用小写函数，

点击Field后面的省略号（...），弹出高级编辑器（Advanced Editor），改写为“Lower(@Name)”。

然后单击“确定”按钮，创建“条件”完成。

4、右击“策略”节点，在右键菜单中选“新建策略”选项，系统将打开新建策略窗口，输入策略名“检查存储过程命名规范”，在检查条件的下拉列表中选择刚创建的条件“存储过程命名规范”，系统将根据选择的检查条件列出针对目标，默认情况下是对每个数据库的每个存储过程进行检查，由于这里我们只希望检查TestDB1数据库，所以需要新建数据库的条件，如图：

5、单击“新建条件”后将出现与第（2）步新建条件相同的窗口，只是这里我们新建的条件方面是数据库，新建条件TestDB1，如图所示：

6、单击“确定”按钮回到新建策略窗口，针对目标变成了对TestDB1数据库的每个存储过程。这里若要强制实现这个策略，则选择评估模式为“更改：禁止”并选中“已启用”复选框表示启用该策略。

7、单击“说明”选择页，可以在其中选择策略的类别、在违反策略时给出的友好说明。最后单击“确定”按钮即可完成策略的创建工作。

8、接下来就是测试该策略是否有效了，运行如下SQL语句创建一个存储过程usp_GetDate：

USE TestDB1
GO
CREATE PROC usp_GetDate
AS
SELECT GETDATE()
GO

		

一切正常，存储过程被创建成功。

USE TestDB1
GO
CREATE PROC db1_GetDate
AS
SELECT GETDATE()
GO

		

系统抛出异常：

“SQLSERVER:\SQL\MS-ZY\DEFAULT\Databases\TestDB1\StoredProcedures\dbo.db1_GetDate”已违反策略“检查存储过程命名规范”。
此事务将回滚。
策略条件:“Lower(@Name) LIKE 'usp[_]%'”
策略说明:“项目中统一了TestDB1数据库中存储过程的命名规范，所有存储过程必须以usp_开头”
其他帮助:“存储过程必须以usp_开头”:“”
语句:“CREATE PROC db1_GetDate
AS
SELECT GETDATE()
”。
消息 3609，级别 16，状态 1，过程 sp_syspolicy_dispatch_event，第 65 行
事务在触发器中结束。批处理已中止。

		

10、再看看对象资源管理器中，该存储过程确实没有被创建。同样可以在另外的数据库中创建这两个存储过程，但是由于策略中针对的是TestDB1数据库，所以在其他数据库中这两个存储过程都将会被成功创建。

11、现在有了这个策略，大家在命名存储过程时都必须按照规范。

12、如果策略被定义为“按需”评估模式的话，则用户可以在其中创建违反策略的存储过程。

五、检查现有的数据库对象是否符合策略

1、在对象资源管理器中右击数据库对象节点，然后选择右键菜单中的“策略”下的“评估” 选项，如果要检查具体某个数据库对象的“方面”属性值的话，则选择右键菜单中的“方面”选项。

2、选择“评估”选项后系统弹出评估策略窗口，其中列出了所有存储过程方面相关的策略，选择需要验证的策略，然后单击“评估”按钮即可查看当前数据库对象是否符合策略。