在此练习当中,您将通过定义策略来阻止在DBO架构下创建对象。
1.在AdventureWorks 的dbo 架构下新建对象
(1)依次点击File, Open, File… 然后打开C:\Manageability Labs\Policy-Based Management 目录下的ObjectOwnerCreationPoliciesTestScript.sql 脚本,确保在CHICAGO\SQLDev01 实例下打开该脚本
(2)打开行号。点击Tools, Options… 在Options 对话框中,展开Text Editor 部分然后选择All Languages.勾选Line numbers 选项,然后点击OK 并关闭该对话框
(3)执行第27-40行:
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> USE AdventureWorks
go
CREATE TABLE TestTable
(
Col1 int
)
go
SELECT SCHEMA_NAME(schema_id) AS SchemaName, *
FROM sys.objects
WHERE [name] = 'TestTable'
-- note that it is in the default schema of dbo
go
(4)查看输出结果显示架构名称为dbo.
(5)保持该脚本处于打开状态,然后继续后面的任务
2.创建一个策略可以强制执行的条件
(1)在CHICAGO\ConfigServer 的Object Explorer 中,依次展开Management, Policy Management, Conditions.
(2)右键点击Conditions 然后点击New Condition…
(3)在General 选项卡中,输入“DBO is not a valid user-defined Schema Name” 作为该条件的名称
(4)在Facet 下拉列表中,点击Multipart Name.
(5)在Expression 中首先点击Field 下拉列表,然后点击Field 列中的单元格,此时下拉菜单将会出现。针对Multipart Name 有两个选项,分别是@Name (针对对象名称) 和@Schema (针对创建此对象的架构名称). 选择@Schema.
(6)在Expression 中点击Operator 下拉列表。在此实验中我们希望@Schema != ‘dbo’ ,因此在Operator 下拉列表中选择!=
(7)最后,在Value 列中输入‘dbo’ (包括上引号).
(8)点击下面的空行,从而完成当前行的输入。然后点击OK 退出
(9)在Description 选项卡中,输入如下信息:“DBO is not a valid user-defined Schema Name as object/schema separation is enforced. All user-defined objects should be designed/defined in schemas that reflect the granularity required for security and object access.”.
(10)点击OK 从而完成此条件的添加过程
3.创建一个策略来强制执行用户定义的条件
(1)在Object Explorer 中,依次展开Management/Policy Management/Policies, 然后右键点击Policies 并点击New Policy…
(2)在General 选项卡中,输入: “DBO is not a valid user-defined Schema Name - Base Tables, Views & SPs” 作为策略的名称
(3)在Check condition 下拉列表中,找到Multipart Name 组并选择“DBO is not a valid user-defined Schema Name”
(4)完成后你将可以设置该策略都要应用到那些对象上。在Against Targets 中选择Every StoredProcedure, Table 以及View,但只应用到“Development Databases.” 为了创建我们所要定义的“Development Databases”,点击? Database 然后点击New Condition…
(5)在General 选项卡中,输入:“Development Databases (explicitly named)” 作为条件的名称
(6)在Facet 下拉列表中,选择Database.
(7)在Expression 中,首先点击Field 下拉列表,然后选择@Name.
(8)在Expression 中,点击Operator 下拉列表,此时我们希望@Name IN (‘AdventureWorks’, ‘AdventureWorksDW’) ,因此我们在Operator 下拉列表中选择IN
(9)最后在Value 列中输入‘AdventureWorks2008’, ‘AdventureWorksDW2008’, AdventureWorksLT2008’ (包括上引号和逗号,另外不要直接从此实验手册中复制粘贴,否则引号格式将发生改变)
(10)点击下面的空行,从而完成当前行的输入。然后点击OK 退出
(11)此时OK 按钮依然无法点击。目前这是IN 操作符的一个bug,因此对上述内容进行修改,只针对AdventureWorks200(8) 最终的条件将变为@Name = ‘AdventureWorks2008’. 点击OK 并创建此条件
(12)在Create New Policy 对话框中,为每一个对象类型(StoredProcedure, Table, 和View)勾选? , 然后选择“Development Databases (explicitly named)”:
(13)当设置完目标以后,我们将设置Execution Mode 为On Change – Prevent.
(14)在Server restriction 选项中,保留默认设置None.
(15)在Description 选项卡中,设置Category 为“Database Best Practice: Security ”.
(16)然后输入描述信息“Corporate standard (as of September 2008), is to use schemas for better security and granularity. Objects should not be owned by the dbo - especially if cross-database ownership chaining were to become enabled.”
(17)此外在Additional Help Hyperlink 中添加一些辅助信息,如下所示:
Text to display: Please see our Security Guidelines and Best Practices here
Address: http://InternalWebsite/Policies/SQLServerSecurity.htm
(18)返回到General 选项卡,并勾选? Enabled 复选框来启用该策略
(19)点击OK
尝试在DBO 架构下新建一个对象,并对比着在非DBO 架构下新建对象
(20)返回到查询窗口(ObjectOwnerCreationPoliciesTestScript.sql 脚本应当处于打开状态). 如果没有打开,请从C:\Manageability Labs\Policy-Based Management 目录将其打开
(21)执行第42-43行:
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> DROP TABLE TestTable
go
(22)删除数据表以后,尝试再次执行第27-40行:
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> USE AdventureWorks
go
CREATE TABLE TestTable
(
Col1 int
)
go
SELECT SCHEMA_NAME(schema_id) AS SchemaName, *
FROM sys.objects
WHERE [name] = 'TestTable'
-- note that it is in the default schema of dbo
go
(23)这次将会看到如下信息:
CHICAGO(CHICAGO\Administrator):
Policy 'DBO is not a valid user-defined Schema Name - Base Tables, Views & SPs' has been violated by 'Server/Database[@Name='AdventureWorks2008']/Table[@Name='TestTable' and @Schema='dbo']'.
This transaction will be rolled back.
Policy description: Corporate standard (as of September 2008), is to use schemas for better security and granularity. Objects should not be owned by the dbo - especially if cross-database ownership chaining were to become enabled.'
Additional help: 'Please see our Security Guidelines and Best Practices' : 'http://InternalWebsite/Policies/SQLServerSecurity.htm'.
CHICAGO(CHICAGO\Administrator): Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 50
The transaction ended in the trigger. The batch has been aborted.
CHICAGO(CHICAGO\Administrator): (0 row(s) affected)
(24)现在执行第47-55行:
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
--> CREATE TABLE Person.TestTable
(
Col1 int
)
Go
SELECT SCHEMA_NAME(schema_id) AS SchemaName, *
FROM sys.objects
WHERE [name] = 'TestTable'
(25)对象(Person.TestTable) 将可以成功创建,因为它不在dbo 架构中
(26)退出SQL Server Management Studio,本实验结束