翻译Stairway to SQL Server Security Level 3: Principals and Securables

通向SQLServer安全级别3的楼梯：主体和安全性

来自    Don Kiely, 2014/07/16

系列

本文是楼梯系列的一部分：通向SQLServer安全的阶梯

SQLServer拥有保护服务器和数据免受当今复杂攻击所需的一切。但是，在有效使用这些安全特性之前，您需要了解所面临的威胁和一些基本的安全概念。第一个阶梯级别提供了一个基础，这样您就可以充分利用SQLServer中的安全特性，而不必浪费时间在无法防止数据受到特定威胁的功能上。

通常，通过向主体分配对象的权限，可以在SQLServer中实现用户和对象安全性。但是，什么是SQLServer主体？它能得到什么许可？在这个阶梯级别上，您将了解可以通过权限在SQLServer实例中执行操作和访问安全对象的各种主体。SQL Server中的一组重要主体是角色，您将了解角色如何使安全管理比作为唯一类型主体的单个用户更容易管理。您还将了解SQLServer中的安全对象，从而为了解下一级别的权限奠定基础。

授权

身份验证，在第2级中，只是访问数据库服务器中所有优点的一部分。身份认证有点像拥有一本证明你是谁但没有签证的护照-你需要签证才能进入并在全国各地流动。在这个级别上，您将了解授权以及它作为访问数据库对象的VISA的作用。

主体是可以访问SQLServer或其数据库中的一个或多个安全对象的用户或进程。安全对象(或仅仅是安全对象)是受保护的资源，只有特定的人或进程才能查看或更改，例如表中的数据。权限使主体能够获得对安全对象的特定类型访问。

为了继续进行护照类比，主体是护照持有人，即护照上的人。安全的国家是校长想要访问的国家，获准的是通过国家边界享受访问的签证。

主体

在安全上下文中，主体是任何用户(人工类)、用户组(在SQLServer中称为角色)，或在进程中运行的代码，这些进程可以请求对安全对象的访问并授予或拒绝对其的访问。所有Windows和SQLServer登录都是主体，以及它们映射到数据库中的用户。下面的列表显示了SQLServer中大多数更重要主体的层次结构，从具有跨SQLServer实例权限的服务器范围内的主体到数据库级主体：

Windows级主体

    Windows域登录

   Windows组

   本地Windows登录

SQLServer级主体

   SQLServer登录

   映射到证书的SQLServer登录名

   映射到Windows登录名的SQLServer登录名

   映射到非对称密钥的SQLServer登录名

数据库级主体

   应用角色

   数据库角色

   数据库用户

   映射到证书的数据库用户

   映射到Windows登录的数据库用户

   映射到非对称密钥的数据库用户

   公共角色

理解这个层次结构非常重要，因为主体的作用域部分决定了授予它的权限的范围。例如，数据库用户只能在该数据库的上下文中获得授予的权限。SQLServer级主体可以具有整个服务器的权限，Windows级别的主体可以具有超出SQLServer范围、扩展到Windows的本地实例和跨网络的权限。

请注意，在前面的列表中，主体可以是登录(或用户)以及角色。SQLServer中的角色类似于Windows组。具有角色成员资格的用户继承分配给角色的权限。角色使安全管理更加容易，因为您不需要管理单个用户的复杂权限集。SQLServer支持以下类型的角色：

固定服务器角色：用于执行服务器级任务的SQLServer内置角色。

用户定义的服务器角色：您创建、分配服务器级权限和分配登录的自定义服务器角色，以便它们继承服务器对象的权限。

固定数据库角色：用于执行数据库任务和分配基本权限的内置角色。

用户定义的数据库角色：您创建、分配权限的自定义数据库角色，然后向其添加用户，以便用户继承对数据库对象的权限。

您可以将用户分配给多个角色。角色也可以嵌套，但不要太过火-如果嵌套方案太复杂，您将遭受性能损失，这可能会使维护和故障排除成为一场噩梦。

固定服务器角色

固定的服务器角色是内置的角色-在SQLServer中，您不能以任何方式更改它们-您只能向它们添加登录。它们只存在于服务器级，仅用于执行管理任务。此处列出了SQLServer中的固定服务器角色，括号中列出了实际的角色名称：

系统管理员(Sysadmin)：在SQLServer实例中执行任何活动。该角色包含所有其他角色-一旦用户是sysadmin的成员，他们就不需要任何其他角色。sysadmin的成员可以做他们喜欢做的任何事情，所以将成员资格限制在那些需要它并且可以信任有无限访问权限的人是个好主意。

BULK INSERT管理员(Bulkadmin)：执行BULK INSERT语句以快速将数据输入数据库。

数据库创建者(Dbcreator)：创建和修改数据库。

磁盘管理员(Diskadmin)：管理存储数据库的各种磁盘文件。

ProcessAdministrator(Processadmin)：管理在SQLServer中运行的进程。

服务器管理员(Serveradmin)：配置服务器范围内的设置。尽管该名称与系统管理员相似，但serveradmin是一个非常不同的角色，而且非常有限。

安装管理员(Setupadmin)：安装、复制和管理扩展过程。

安全管理员(Securityadmin)：管理服务器的登录。

固定的服务器角色通过允许将服务器任务划分为部分提供了灵活性和安全性。换句话说，如果某人只需要创建数据库，就不必让他们成为系统管理员。相反，让他们成为dbcreate的成员，并且他们拥有所需的所有权限。

您可以通过使用ManagementStudio或Transact-SQL将登录指定给固定的服务器角色。<br/>要使用ManagementStudio，请执行以下步骤：

提示：

这个阶梯的第2级代码创建了黄玉登录。如果您没有创建该登录，可以自由地运行该代码来创建它，或者使用第2级中讨论的技术创建您自己的登录。如果您使用后一种登录方式，则根据需要调整步骤以使用该登录名。

1.展开ManagementStudio中对象资源管理器的“安全性”部分，以显示登录列表。

2.右键单击Topaz登录并从弹出菜单中选择Properties。

3.在“登录属性”对话框中，选择“服务器角色”页。这列出了所有可用的服务器角色，其中包含一个复选框，用于将登录名添加到每个角色中。

4.注意，与所有登录一样，Topaz已经是公共角色的成员。将登录名分配给dbcreate和diskadmin角色。图3.1显示了登录黄玉的对话框。

图3.1.将登录黄玉分配给dbcreate和diskadmin固定服务器角色。

5.单击“确定”以保存更改。

或者，可以通过使用对象资源管理器中安全节点下的Server角色节点向角色添加登录。若要将Topaz添加到securityadmin服务器角色，请执行以下操作：

1.在对象资源管理器中的安全节点下展开“服务器角色”节点。

2.右击对象资源管理器中的securityadmin服务器角色并选择Properties。

3.这将打开“服务器角色属性”对话框。单击对话框右下角附近的“添加”按钮，该按钮将打开“选择登录”对话框。您可以键入Topaz并单击Check Name，也可以单击Browse按钮获取登录列表。输入Topaz后，对话框如图3.2所示。

图3.2。选择黄玉以添加到服务器角色。

4.单击“确定”向服务器角色添加黄玉。“服务器角色属性”对话框如图3.3所示。

图3.3.将Topaz添加到securityadmin服务器角色。

5.单击“确定”以保存更改。

向服务器角色添加登录名的另一种方法是使用Transact-SQL，方法是使用sp_addsrvrolemenger系统存储过程。下面的代码将现有的登录Topaz添加到sysadmin角色中：

EXEC sp_addsrvrolemember 'Topaz', 'sysadmin';

清单3.1：向服务器角色添加登录名的代码。

您可以通过运行两个存储过程sp_helsrvole和sp_helsrvrolemenger查找有关固定服务器角色的信息。如果将服务器角色的有效名称传递给sp_helpsrvole，它将显示该角色的描述；否则它将显示所有服务器角色。图3.4显示了在ManagementStudio中执行的系统存储过程，以显示securityadmin角色及其当前成员的描述。

Figure 3.3. Adding Topaz to the securityadmin server role.

图3.4.使用系统存储过程获取有关服务器角色的信息。

用户定义的服务器角色

SQLServer 2012中一个期待已久的安全功能是用户定义的服务器角色。SQLServer长期以来一直为数据库级权限提供灵活的用户定义的数据库角色(稍后您将在这个级别了解这些角色)，但是使用自定义服务器角色，您最终可以获得服务器级权限的粒度。

在SQLServer的旧版本中，向用户授予某些权限的唯一方法就是将它们分配给内置的固定服务器角色，该角色通常具有太多的权限。让每个人都成为系统管理员是一种可怕但普遍的做法，尤其是有问题的，因为您不能否认系统管理员的任何事情。这在很大程度上违反了最低特权原则，但往往是一种实际的必要性。SQLServer 2005和以后的版本使这一切变得更加细化，允许您将几乎任何特定的服务器级权限分配给用户，但缺乏将这些权限分组为服务器角色的能力。

SQLServer 2012通过支持用户定义的服务器角色解决了这个问题。创建一个新的服务器角色就像使用CREATESERVER Role语句一样简单：

CREATE SERVER ROLE LimitedDBA;

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

然后，您可以授予和拒绝角色任何服务器级别的权限。下面的代码将控制服务器权限授予新角色-类似于授予sysadmin权限-然后拒绝一些权限以缩小服务器角色成员的权限。这是一种非常灵活的方法，可以授予属于组特定权限的用户。

USE master;
GO

-- Grant the role virtual sysadmin permissions
GRANT CONTROL SERVER TO LimitedDBA;

-- And take some permissions away
DENY ALTER ANY LOGIN TO LimitedDBA;
DENY ALTER ANY SERVER AUDIT TO LimitedDBA;
DENY ALTER ANY SERVER ROLE TO LimitedDBA;
DENY CREATE SERVER ROLE TO LimitedDBA;        -- Covered by ALTER ANY SERVER ROLE
DENY UNSAFE ASSEMBLY TO LimitedDBA;

清单3.3：添加和拒绝服务器角色权限的代码。

为了测试角色，清单3-4中的代码在名为Marathon的机器上创建一个与Windows组DBA关联的登录名，并将新登录添加到LimitedDBA角色中。

提示：

在运行此代码之前，DBA组必须存在于Windows的本地实例中。您可以通过进入控制面板的计算机管理小程序，展开系统工具以及本地用户和组节点，并将其添加到组节点中来创建它。另外，将机器名从马拉松更改为本地机器。

-- Create a login for DBAs Windows group
CREATE LOGIN [Marathon\DBAs] FROM WINDOWS;

-- Add to the server role
ALTER SERVER ROLE LimitedDBA ADD MEMBER [Marathon\DBAs];

清单3.4：创建登录并将其添加到服务器角色的代码。

清单3.5中的代码随后创建了一个SQLServer登录CALL，在SQLServer实例中没有任何权限。然后，代码在carol的安全上下文下尝试各种需要服务器级权限的操作：创建另一个登录、查看系统信息和创建另一个服务器角色。所有这些操作都失败，如图3.5所示，因为carol主体没有执行这些操作的权限。

-- Create carol login
CREATE LOGIN carol WITH PASSWORD = 'crolPWD123%%%';

EXECUTE AS LOGIN = 'carol';
-- Verify user context
PRINT suser_sname();
-- Can Carol alter logins?
CREATE LOGIN donkiely WITH PASSWORD = 'G@Sm3aIKU3HA#fW^MNyA';    -- No
-- Other server-level permissions?
SELECT * FROM sys.dm_exec_cached_plans;    -- No, requires VIEW USER STATE
CREATE SERVER ROLE CarolRole;                    -- No
REVERT;

清单3.5：创建登录名的代码，并测试它是否具有特定权限。

提示：

此代码不检查此SQLServer实例中是否存在现有的carol登录名。如果存在，则CREATELOGIN语句将失败。那样的话，就跳过那句话吧。

Figure 3.3. Adding Topaz to the securityadmin server role.

图3.5.失败的操作，因为carol没有权限。

接下来，代码将carol添加到新的LimitedDBA用户定义服务器角色，并再次尝试执行相同的操作。如图3.6所示，这次carol能够获得系统信息(SELECT操作)，因为该权限是通过控制服务器权限授予的。但是carol仍然不能创建登录或服务器角色，因为这些权限被从LimitedDBA角色显式拒绝。

ALTER SERVER ROLE LimitedDBA ADD MEMBER carol;

-- Now does Carol have permissions?
EXECUTE AS LOGIN = 'carol';
CREATE LOGIN donkiely WITH PASSWORD = 'G@Sm3aIKU3HA#fW^MNyA';    -- Still not possible
SELECT * FROM sys.dm_exec_cached_plans;                            -- Yes, CONTROL SERVER covers VIEW USER STATE
CREATE SERVER ROLE CarolRole;                    -- Not possible
REVERT;

清单3.6：再次测试服务器角色的成员是否具有特定权限的代码。

图3.6.通过LimitedDBA具有部分权限的服务器级操作的结果。

为了查看可以授予和拒绝服务器角色的所有可用服务器级权限，请执行以下代码。图3.7显示了结果

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

清单3.7：查看所有可用服务器级权限的代码。

图3.7.服务器级权限的部分列表。

您可以创建用户定义的服务器角色，以授予用户和组执行其工作所需的非常特定的权限集，并且不再提供更多权限。这比早期版本的SQLServer灵活得多，使用SQLServer 2012的安全性管理更加容易，而更容易的管理不可避免地意味着更安全的服务器。

固定数据库角色

固定的数据库角色存在于数据库级别，而不是服务器级别，并且仅在数据库中控制授权。每个数据库都有自己的固定数据库角色集合，因此可以在每个数据库中单独配置这些角色。固定数据库角色与固定服务器角色类似，因为它们不能被删除、修改或更改，但您可以将数据库用户和用户定义的角色添加为成员。固定的数据库角色是：

DB_accessadmin：可以在数据库中添加或删除Windows登录名和组以及SQLServer登录。DB_backupOperator：可以备份数据库。

DB_datareader：可以查看数据库中所有用户表中的任何数据。

DB_DataWriter：可以在数据库中的所有用户表中添加、更改或删除数据。

DB_ddladmin：可以在数据库中添加、修改或删除对象。(DDL代表数据定义语言，即对数据库进行结构更改的Transact-SQL命令集。)

DB_denydatareader：无法查看数据库中的任何数据。

DB_denydataWriter：无法更改数据库中的任何数据。

DB_Owner：可以执行所有数据库角色的活动以及维护和配置活动。此角色包括所有其他角色，因此它本质上是此数据库的管理员。

DB_securityadmin：可以管理数据库中的角色成员资格、语句和对象权限。

固定数据库角色可以简化数据库中的权限分配。例如，假设您希望使用户能够访问特定的数据库，但只能对其进行备份。您不希望用户能够读取数据-只是备份它。您可以通过让用户成为db_backupOperator和db_denydatareader角色的成员来轻松完成这一任务。使用sp_helprole和sp_helprolemenger系统存储过程查看有关数据库角色的信息。

公众角色与嘉宾用户

有几个特殊的原则需要提及。您不太可能以任何有意义的方式使用这些主体，但它们确实会影响安全性，因此您需要知道它们是什么。

公共角色是不能删除的特殊服务器角色。每个数据库用户都属于这个公共角色，因此不需要为其分配用户、组或角色。每个SQLServer数据库都包含公共角色，包括主角色、msdb、temdb和模型。但是，您可以根据安全需要授予或限制公共角色的权限。关于公共角色，要记住的重要一点是，授予public的权限适用于所有数据库用户。

提示：

通常，您希望限制授予公共角色的权限，因为向每个人授予权限很少会导致安全数据库。

客户用户存在于每个数据库中，包括主数据库和模型数据库。作为用户，它继承公共角色的权限。当服务器登录没有映射到特定数据库中的用户时，它就会发挥作用。默认情况下，来宾用户没有权限，但可以授予在数据库中访问数据库对象和执行操作的权限。正如您可能预期的那样，在精心设计的数据库服务器安全方案中，这样做是非常危险的，而且您应该避免将权限分配给该用户。虽然您不能删除这个用户，但是您应该在用户数据库中禁用它，方法是使用清单3.8中的代码撤销它的连接权限。

USE Northwind;
GO

REVOKE CONNECT FROM guest;
GO

清单3.8：通过撤销用户数据库的CONNECT权限，禁用用户数据库中的来宾用户的代码。

提示：

不要禁用系统数据库中的来宾用户，这可能会导致您不想处理的问题！这些数据库需要来宾用户提供各种功能。

DBO用户与架构

DBO是每个数据库中映射到sysadmin固定服务器角色的特殊用户帐户。这意味着，如果您是sysadmin角色的成员，并且在任何数据库中创建对象，那么该对象的所有者将是DBO，而不是您。不能删除DBO用户，它只能映射到sysadmin，而不是数据库所有者(Db_Owner)。这可能会让人感到困惑，因为DBO用户实际上与db_Owner角色没有任何关系。

每个数据库还拥有一个DBO用户拥有的DBO模式，并且是DBO用户的默认架构。因此，当您以sysadmin的形式访问数据库并在不指定模式的情况下创建对象时，它的两部分名称将是dbo.objectname。如果没有指定架构名称，则DBO架构也是任何其他用户访问数据时的次要默认架构。如果用户Joe试图访问一个名为Sales的表，SQL Server将首先检查用户Joe的默认架构中是否有一个Sales表，如果没有，它将检查DBO模式中是否有一个Sales表。只有在两个架构中都不存在Sales时，才会产生一个错误，无法找到该对象。最佳实践是始终为每个访问的对象指定架构名称。

用户定义的数据库角色

数据库角色并不局限于预定义的角色-您可以创建自己的角色。用户可以定义两种类型的数据库角色：

标准角色：使用此角色简化向用户组分配权限。您可以嵌套固定的数据库角色或其他用户定义的角色，并将用户分配给角色，在这种情况下，用户继承角色的权限。

应用程序角色：应用程序使用此角色允许应用程序或连接登录到数据库，并通过提供角色名称和密码激活应用程序角色。不能像对其他角色那样将用户添加到应用程序角色，一旦激活，应用程序角色的权限将在连接的持续时间内应用。用户可能拥有的任何单独权限都将被暂停，并且只检查应用程序角色的权限。

提示：

您可以向固定数据库角色添加用户定义的角色，就像通过固定数据库角色的“属性”对话框将用户添加到固定数据库角色一样。

安全对象

安全对象是可以控制访问的受保护资源。通常它是一个物理的东西，或者至少像一个数字对象一样的物理的东西！但是，安全也可以是一种操作，即对数据库或SQLServer实例进行某种更改的能力。例如，管理员可以授予主体获取对象所有权的能力。授予该权限并不会立即改变对象的所有权；它只是赋予主体在未来某个时间执行该权限的能力。

图3.8显示了SQLServer实例中的大多数安全对象。服务器级安全对象具有最广泛的范围，包括所有SQLServer，包括影响主体对所有数据库进行更改的权限。数据库作用域包含特定数据库中的所有对象，例如用于管理用户和创建加密密钥的对象。模式作用域包括模式中的所有对象-本质上是数据库的数据结构，包括表及其数据。数据库可以包含许多模式，而且每个模式都可以包含完整数据库对象集的子集。架构的强大之处在于，您可以分配和拒绝架构的权限，这些权限适用于架构包含的所有对象。

 

图3.8.SQLServer中的安全对象。箭头显示了一个作用域如何包含对象层次结构中较小的作用域。

重要的是要理解，在服务器级别授予权限通常意味着权限在较小的范围内。例如，授予数据库级权限可能意味着主体对一个或所有数据库架构中的对象具有隐含的权限。

摘要

在SQLServer安全性阶梯的这个级别上，您了解了授权的第一部分、SQLServer及其数据库实例中可用的主体和安全对象。在下一个级别中，您将了解权限，当将权限授予安全对象上的主体时，授予或取消了主体对象执行某些操作的能力。有了这种理解，您将能够有效地利用SQL Server中身份验证和授权的细粒度特性，在允许授权用户和进程完成其工作的同时，对数据库资产保持严格控制。

Figure 3.3. Adding Topaz to the securityadmin server role.