SQL Server安全（3/11）：主体和安全对象（Principals and Securables）

在保密你的服务器和数据，防备当前复杂的攻击，SQL Server有你需要的一切。但在你能有效使用这些安全功能前，你需要理解你面对的威胁和一些基本的安全概念。这篇文章提供了基础，因此你可以对SQL Server里的安全功能充分利用，不用在面对特定威胁，不能保护你数据的功能上浪费时间。

通常来说，你通过在对象上分配许可到主体来实现SQL Server里的用户和对象安全。但什么是SQL Server主体？它上面获得哪些许可？在这篇文章里，你会学到各种主体，可以通过许可授权进行SQL Server实例里进行操作和访问的安全对象。SQL Server里重要的主体是角色，你会学到相比使用用户这类主体，角色如何让安全管理更加容易。在这篇文章里你还会学到SQL Server里的安全对象，为学习许可打下基础。

授权（Authorization）

在第2篇里谈到的授权，是访问数据库服务器里所有好东西的一部分。验证就像有护照证明你是谁但是没有签证——你需要签证来进入和逗留国家。在这篇文章里你会学到授权，它如何扮演签证提供对数据库对象访问。

主体（Principal）是可以访问SQL Server或它的数据库里的一个或多个安全对象的用户或线程。安全对象（Securables）是个保护的资源，是一些只能特定人或线程才可以访问或修改的，例如表里的数据。许可（permission）让主体获得特定安全主体的访问。

继续和护照类比，主体是护照持有者，里面有所有人的照片。安全对象是主体想要访问的国家，许可是穿越国家边界并享受访问的签证。

主体（Principals）

主体，在安全上下文里，是任何用户（人类），用户组（在SQL Server里称为角色），或进程里运行的代码，它们可以清酒对安全对象的访问且被授予或禁止访问。所有的Windows和SQL Server登录都是主体，和映射到数据库里的用户一样。下面列表展示了SQL Server里较重要主体的大多数层次，从SQL Server实例权限生成的服务器级别主体，到数据库级别的主体：

Windows级别主体：

• Windows域登录
• Windows组
• Windows本地登录

SQL Server级别主体：

• SQL Server登录
• SQL Server映射到证书登录
• SQL Server映射到Windows登录的登录
• SQL Server映射到非对称匙的登录

数据库级别主体:

• 应用程序角色
• 数据库角色
• 数据库用户
• 数据库用户映射到证书
• 数据库用户映射到Windows登录
• 数据库用户映射到非对称匙
• 公共角色

理解这个层次非常重要，因为主体的范围基本上决定了可以授予它的许可范围。例如，数据库用户只能在数据库上下文里授予许可。数据库级别的主体可以有服务器的许可，Windows级别的主体可以有在SQL Server范围外，Windows本地示例里和网络上的许可，

注意刚才的列表里，一个主体除了其它方面之外，既是角色也是登录（或用户）。SQL Server里的角色类似于Windows组。在角色里拥有成员资格的用户继承分配到角色的许可。角色让安全管理更加简单，因为你不需要为各个用户管理复杂的一系列许可。SQL Server支持下列角色：

• 固定服务器角色（Fixed server roles）：进行服务器级别任务的SQL Server内建角色。
• 用户自定义服务器角色（User-defined server roles）：你创建的，分配服务器级别许可，分配登录的自定义服务器角色，因此它们在服务器对象上继承许可。
• 固定数据库角色（Fixed database roles）：进行数据库任务的内建角色，用于分配基本许可。
• 用户自定义数据库角色（User-defined database roles）：你创建的，分配许可，然后添加用户到它里面的自定义数据库角色，因此用户在数据库对象上继承许可。

你可以分配用户到多个角色。角色也可以嵌套，但不要忘乎所以——如果你嵌套的架构太复杂，你会遭受性能损失，让维护和故障排除变成噩梦。

固定服务器角色

在SQL Server里固定服务器角色是内建的角色，你不能修改它们——你只能添加登录到它们。它们只在服务器级别存在用来进行管理任务。SQL Server里固定的服务器角色列在这里，附带上它们实际的角色名：

• 系统管理员（sysadmin）：在SQL Server实例里进行任何活动。这个角色包含了所有其它角色——一旦用户是sysadmin成员，它们不需要其它任何角色。sysadmin成员可以做任何它们想做的事，因此限制为只需要它的，且是可以信任的成员来拥有无限制的访问，加入这个角色是个好主意。
• 大容量插入管理员（bulkadmin）：执行BULK INSERT语句来将数据快速插入数据库。
• 数据库创建者（dbcreator）：创建和修改数据库。
• 磁盘管理员（diskadmin）：管理存储数据库的各个磁盘文件。
• 进程管理员（processadmin）：管理在SQL Server里运行的进程。
• 服务器管理员（serveradmin）：配置服务器范围的配置。尽管名字和系统管理员类似，serveradmin完全不同，是限制更多的角色。
• 设置管理员（setupadmin）：安装复制和管理扩展过程。
• 安全管理员（securityadmin）：管理对于服务器的登录。

固定服务器角色通过允许你将服务器任务拆分来提供灵活性和安全性。换句话说，如果他只需要创建数据库，你不必让某人成为系统管理员。让他成为数据库创建者成员即可，它们已经拥有所有需要的许可。

你可以使用SSMS或T-SQL来分配登录到固定服务器角色。使用SSMS，按如下步骤：

提示：

来自这个系列第2篇代码创建的Tudou登录。如果你没有创建那个登录，请随意运行那个代码来创建它，或者使用第2篇里讨论的技术来创建你自己的登录。如果你稍后做，调整需要的步骤来使用那个登录。

1. 在SSMS里展开【对象浏览器】的【安全性】部分来显示登录列表。
2. 右击Tudou登录，从弹出的菜单选择【属性】。
3. 在【登录属性】对话框里，选择【服务器角色】页。这里列出了可以选择的所有可用服务器角色，单选复选框来增加角色到登录。像所有登录，Tudou，已经是公共（public）角色的成员。
4. 分配数据库创建者（dbcreator）和磁盘管理员（diskadmin）到登录。对于Tudou登录如插图3.1所示。

   

   插图3.1：分配Tudou登录到dbcreator和diskadmin固定服务器角色
5. 点击【确定】来保存修改。

或者，你可以使用对象浏览器里，服务器节点下的【服务器角色】节点来添加登录到角色。添加Tudou到securityadmin服务器角色。

1. 在对象浏览器下的【安全性】节点，展开【服务器角色】节点。
2. 在对象浏览器里右击【securityadmin】服务器角色，选择【属性】。这会打开服务器角色属性对话框。
3. 在对话框的右边点击【添加】按钮，它打开选择登录对话框。你可以输入Tudou，点击【检查名称】，或者点击【浏览】按钮来获得登录列表。一旦你输入Tudou，对话框如插图3.2所示。

   

   插图3.2：选择Tudou来添加到服务器角色
4. 点击【确定】来添加Tudou到服务器角色。服务器角色属性对话框如插图3.3所示。

   

   插图3.3：添加Tudou到securityadmin服务器角色
5. 点击【确定】保存修改。

另一个添加登录到服务器角色是T-SQL，使用sp_addsrvrolemember系统存储过程。下列代码添加现存的Tudou登录到sysadmin角色：

1 EXEC sp_addsrvrolemember 'Tudou', 'sysadmin';      

代码3.1：添加登录到服务器角色的代码

你可以通过运行2个存储过程sp_helpsrvrole 和sp_helpsrvrolemember来获得固定服务器角色的信息。如果你传入一个有效的服务器角色名称到sp_helpsrvrole ，它会显示那个角色的介绍；否则显示所有服务器角色。插图3.4显示在SSMS里2个系统存储过程的执行，显示了securityadmin的介绍和它的当前成员。

1 -- Get a list of all server roles
2 EXEC sp_helpsrvrole;
3 
4 -- Get the description of a single server role
5 EXEC sp_helpsrvrole securityadmin
6 
7 -- Get list of members of the securityadmin role
8 EXEC sp_helpsrvrolemember securityadmin      

插图3.4：使用系统存储过程获得服务器角色信息

用户自定义服务器角色

在SQL Server 2012里期待已久的安全功能是用户自定义服务器角色。对于数据库级别的许可（在这篇文章里你马上就会学到），SQL Server拥有一直灵活的用户自定义数据库角色，但使用自定义服务器角色，最终你可以获得和服务器级别一样颗粒的许可。

在早期的SQL Server版本里，唯一授予一些许可到用户是分配它们到呢间的固定服务器角色，这会有太多的许可。让每个人都是sysadmin太可怕，却是习惯做法，问题的关键是你不能阻止sysadmin任何事情。这个严重违法了最小权限原则，但经常实际上需要。SQL Sever 2005和后续版本让这些变得更加明细，让你可以分配特定的服务器级别许可到用户，但不能分配组到服务器许可。

SQL Server 2012使用对自定义服务器角色来解决这个问题。创建新的服务器角色非常简单，使用CREATE SERVER ROLE语句：

CREATE SERVER ROLE LimitedDBA;      

代码3.2：创建新的服务器角色代码

接下来你可以授予或拒绝你想要的任何服务器级别许可。下列代码授予

CONTROL SERVER

许可到新建的角色——授予了类似sysadmin权限——然后拒绝了一些许可来缩小服务器角色的成员权限。这是非常灵活的方式来授予特定许可组成员的用户。

1 USE master;
 2 GO
 3 
 4 -- Grant the role virtual sysadmin permissions
 5 GRANT CONTROL SERVER TO LimitedDBA;
 6 
 7 -- And take some permissions away
 8 DENY ALTER ANY LOGIN TO LimitedDBA;
 9 DENY ALTER ANY SERVER AUDIT TO LimitedDBA;
10 DENY ALTER ANY SERVER ROLE TO LimitedDBA;
11 DENY CREATE SERVER ROLE TO LimitedDBA;        -- Covered by ALTER ANY SERVER ROLE
12 DENY UNSAFE ASSEMBLY TO LimitedDBA;      

代码3.3：添加或拒绝许可到服务器角色的代码

测试角色，代码3.4创建一个登录关联到Windows组，DBAs，在名为PC201602202041的机器上，添加新的登录到LimitedDBA角色。

在运行这个代码前，DBAs组应该在Windows的本地实例上，你可以通过控制面板里的【计算机管理】，展开【系统工具】和【本地用户和组】节点，添加它到【组】节点，还有修改PC201602202041为你的本地计算机名。

1 -- Create a login for DBAs Windows group
2 CREATE LOGIN [PC201602202041\DBAs] FROM WINDOWS;
3 
4 -- Add to the server role
5 ALTER SERVER ROLE LimitedDBA ADD MEMBER [PC201602202041\DBAs];      

代码3.4：创建登录添加它到服务器角色的代码

代码3.5然后创建一个SQL Sever登录carol，在SQL Server实例里没有任何权限。然后这个代码在carol的需要服务器级别许可的上下文里尝试各种操作：创建另一个登录，查看系统信息，创建另一个服务器角色。如你在插图3.5里看到的，所有这些操作都失败了，因为carol主体没有任何权限进行这些操作。

1 -- Create carol login
 2 CREATE LOGIN carol WITH PASSWORD = 'crolPWD123%%%';
 3 
 4 EXECUTE AS LOGIN = 'carol';
 5 -- Verify user context
 6 PRINT suser_sname();
 7 -- Can Carol alter logins?
 8 CREATE LOGIN donkiely WITH PASSWORD = 'G@Sm3aIKU3HA#fW^MNyA';    -- No
 9 -- Other server-level permissions?
10 SELECT * FROM sys.dm_exec_cached_plans;    -- No, requires VIEW USER STATE
11 CREATE SERVER ROLE CarolRole;                    -- No
12 REVERT;      

代码3.5：创建登录和测试它是否有特定许可的代码

提示：这个代码不检测在这个SQL Server实例里是否有carol登录存在。如果有的话，CREATE LOGIN语句会失败。在这个情况下，跳过那个语句。

代码3.5：操作失败，因为carol没有任何权限

接下来的代码添加carol到新建的LimitedDBA用户自定义服务器角色，再一次尝试同样的操作。如你在插图3.6里所见，这次carol能获得系统信息（SELECT操作），因为那个许可是通过CONTROL SERVER许可授予的。但是carol还是不能创建登录或服务器角色，因为那些许可在LimitedDBA角色里拒绝了。

1 ALTER SERVER ROLE LimitedDBA ADD MEMBER carol;
2 
3 -- Now does Carol have permissions?
4 EXECUTE AS LOGIN = 'carol';
5 CREATE LOGIN donkiely WITH PASSWORD = 'G@Sm3aIKU3HA#fW^MNyA';    -- Still not possible
6 SELECT * FROM sys.dm_exec_cached_plans;                            -- Yes, CONTROL SERVER covers VIEW USER STATE
7 CREATE SERVER ROLE CarolRole;                    -- Not possible
8 REVERT;      

代码3.6：再次测试服务器角色成员是否有特定许可。

插图3.6：通过LimitedDBA只有部分权限的服务器级别操作结果

为了查看你授予和觉得的服务器角色的所有可用服务器级别许可，执行下列代码。插图3.7显示了结果。

1 SELECT * FROM sys.fn_builtin_permissions('SERVER') 
2     ORDER BY permission_name;      

代码3.7：查看所有可用服务器级别许可的代码

插图3.7：服务器级别许可的部分列表

你可用创建用户自定义服务器角色来授予用户和组它们需要刚好能用来完成它们工作的特定的一系列许可。这比SQL Server的早期版本更加灵活，使用SQL Server 2012让安全管理更加简单，更容易的管理意味着更安全的服务器。

固定数据库角色

固定数据库角色存在于数据库级别，不是服务器级别，只在数据库里控制许可。每个数据库都有它自己的固定数据库角色集合，因此你可以在你的每个数据库独立配置角色。固定数据库角色和固定服务器角色一样，它们不能被删除，修改，或修改，但你可以添加数据库用户和用户自定义角色作为成员。固定数据库角色是：

• db_accessadmin：可以在数据库里添加和删除Windows登录和组，SQL Server登录。
• db_backupoperator：可以备份数据库。
• db_datareader：可以在数据库里从用户表里查看任何数据。
• db_datawriter：可以在数据库里的用户表里添加，修改或删除数据。
• db_ddladmin：可以在数据库里添加，修改或删除对象。（DDL是数据定义语言（Definition Language）的简称，对数据库做出结构化修改的T-SQL命令集）
• db_denydatareader：在数据库里不能查看任何数据。
• db_denydatawriter：在数据库里不能修改任何数据。
• db_owner：可以进行数据库角色的任何活动，包括维护和配置活动。这个角色包含素有其它角色，对于这个数据库，这个是作为管理员的基础。
• db_securityadmin：可以在数据库里管理角色成员资格和声明，还有对象许可。

在数据库里，固定数据库角色可以简单的分配许可。例如，假设你想一个用户对访问的数据库只能备份。你不想用户能读取数据——只有备份。你可以通过让用户是db_backupoperator和db_denydatareader角色的成员来实现。使用sp_helprole和sp_helprolemember系统存储过程来查看数据库角色的信息。

公用角色（Public Role）和来宾用户（Guest User）

有两个特定主体需要提下。你不见得在任何有意义的方式里使用这些主体，但它们却影响安全，因此你要知道它们是什么。

公用角色是不能删除的特殊服务器角色。每个数据库用户属于这个公用角色，因此你不需要分配用户，组或角色给它。每个SQL Server数据库包含这个公用角色，包括master，msdb，tempdb和model。但是，你可以授予或限制公用角色的许可依你安全需要。对于公用角色你要记在心上的是你授予的许可会应用到所有数据库用户。

通常你想约束到公用角色的许可，因为在安全数据库里授予的许可很少到每个人。

在每个数据库都存在来宾用户，包括像mater和model这样的系统数据库。作为用户，它从公用角色里继承许可，在特定数据库里，当服务器登录没有映射到用户时，它发挥作用。默认情况下，来宾用户没有许可，但你可以在数据库里授予访问数据库对象和进行操作的许可。你会料到，这是一件非常危险的事，对于数据库服务器，在精心设计的安全架构里，几乎没有必要，你应该避免分配许可给这个用户。尽管你不能删除这个用户，你可以通过撤销它的CONNECT许可在用户数据库里停用它，使用代码3.8。

1 USE Northwind;
2 GO
3 
4 REVOKE CONNECT FROM guest;
5 GO      

代码3.8：在用户数据库里通过撤销它的CONNECT许可来停用来宾用户的代码。

不要在系统数据库里停用来宾用户，这会带来你不想处理的问题！这些数据库需要来宾用户做各种各样的功能。

dbo用户和架构

在每个数据库里dbo是个特殊的用户账号，它映射到sysadmin固定服务器角色。这就是说，如果你是sysadmin角色的成员，你在任何数据里创建了一个对象，那么那个对象的拥有者是dbo，不是你。你不能删除dbo用户，它只映射到sysadmin，不是数据库拥有者（db_owner）。这是令人迷惑的，因为dbo用户真的和db_owner角色毫无关系。

每个数据库也有属于dbo用户的dbo架构，这是dbo用户的默认架构。因此，当你作为sysadmin访问数据库时，不指定任何架构创建一个对象，它的两部分名称会是dbo.对象名称。当任何其他用户访问数据时，如果没有指定架构名称的话，dbo架构也是默认的次要架构。如果用用户joe尝试访问名为sales的表，SQL Server首先会检查对于joe用户，在默认架构里是否有sales表，如果没有的话，它会检查在dbo架构里是否有sales表。仅当2个架构里都没有sales表存在，才会有生成找不到对象的错误。因此对于每个访问的对象，最好的做法是指定它的架构名。

用户定义数据库角色

数据库角色不限于预定义的角色——你可以创建你自己的角色。一个用户可以创建2类数据库角色：

• 标准角色（Standard Role）：使用这个角色可以建东分配到用户组的许可。你可以嵌套固定数据库角色或其他用户自定义角色，分配用户到角色，在这个情况下，它们从角色里继承许可。
• 应用程序角色（Application Role）：应用程序使用这个角色来运行应用程序或连接，通过提供角色名和密码来登录到数据库，并激活应用程序角色。你不能对其它角色的方式添加用户到应用程序角色，一旦激活，应用程序角色的许可应用到连接的持续时间。任何个人权限的用户会被挂起，只会应用程序角色的许可会被检查。

你可以用添加用户到固定数据库角色的方式，添加用户定义角色到固定数据库角色：通过固定数据库角色的属性对话框。

可安全对象（Securable Objects）

安全对象是你可以控制访问的保护资源。通常它是物理上的东西，或者它至少物理上可以是数字对象的东西！但可安全的（securable）可以是一个行为，能对数据库或SQL Server实例做出修改的能力。例如，管理员可以授予主体获得对象拥有权的能力。授予这个许可不能立即改变对象的所有权；它只是主体以后可以做个的能力。

插图3.8显示了在SQL Server实例里的大多数的可安全对象。服务器级别拥有最广的范围，围绕了整个SQL Server，包括可以影响主体对数据库做出改变的能力许可。数据库范围围绕了特定数据库里的所有对象，例如用来管理用户和创建加密匙。架构范围包括架构里的所有对象——数据库的基本数据结构，包括表和它们的数据。一个数据库可以包含很多架构，每个可以包含数据库对象完整集合的子集。架构强大的地方是你可以在架构上分配和拒绝许可，这些许可会应用到架构里包含的所有对象。

插图3.8：SQL Server里的可安全对象。箭头显示的是在对象层次里一个范围如何包含一个小的范围

在服务器级别授予许可意味这也授权更小范围的许可，理解这个非常重要。例如，授予服务器级别许可会意味着在一个或所有数据库的架构里的主体都有这个许可。

小结

在这篇文章里，你学到了授权的第一部分，在SQL Server实例和它的数据库里的主体和安全对象。在下篇文章里，你会学到许可，当在安全对象上授予主体时，给予或拿走主体能在对象上做一些事情的能力。有了这个理解，你能在SQL Server里用好验证和许可的颗粒性，当允许许可的用户或进程完成它们的工作时，保持整个数据库财产的严格管控。

原文链接：

http://www.sqlservercentral.com/articles/stairway/113155/

注：此文章为

WoodyTu

学习MS SQL技术，收集整理相关文档撰写，欢迎转载，请在文章页面明显位置给出此文链接！

若您觉得这篇文章还不错请点击下右下角的推荐，有了您的支持才能激发作者更大的写作热情，非常感谢！