191.数据安全性控制

第12章数据的安全性控制

• 12.1 SQL Server 2014安全体系结构

• 12.2 角色

• 12.3 服务器级的安全控制

• 12.4 数据库级的安全控制

• 12.5 架构级的安全控制

Ø SQL Server 2014安全体系结构主要由三部分组成：主体、权限和安全对象。要对安全对象执行某操作，主体必须获得对该对象的操作权限，否则SQL Server将禁止这种操作。主体、权限和安全对象之间的关系可示意如图12.1所示。

12.1.1 主体

主体是可以请求SQL Server资源的实体，它实际上是拥有一定权限的特定的数据库对象。每个主体都具有一个唯一的安全标识符(SID)。按影响范围的不同，主体可以分为Windows级主体、服务器级主体和数据库级主体。

1. Windows级主体

Ø Windows级主体包括Windows域登录名和Windows本地登录名。此类主体只限于服务器级操作，而不能将其他安全对象的操作权限授予给此类主体。在Windows认证模式下使用的就是这种Windows级主体。

2. 服务器级主体

Ø 服务器级主体包括SQL Server登录名以及服务器角色。

Ø SQL Server sa登录名是具有最大权限的服务器级主体。默认情况下，该登录名是在安装实例时创建的。在SQL Server 2014中，sa的默认数据库为master。利用sa登录名可以创建其他的SQL Server登录名，具有相应权限的主体也可以创建其他登录名，从而可以形成多级别、多层次的服务器主体体系。

Ø 服务器角色是一组服务器级的操作权限的集合，其作用域为服务器范围。服务器角色是“固定”在SQL Server中，用户对其不能创建或删除，因而也称为“固定服务器角色”。

Ø 角色是若干种权限的集合。当将一种角色赋给某一个主体时，该主体即享有该角色包含的所有权限。不难发现，角色的主要作用是简化权限的管理。“角色”类似于Microsoft Windows操作系统中的“组”。

Ø 表12.1列出了服务器级角色及其对应的操作权限说明。

注：public也是一种数据库角色，同时也视为一种服务器角色。每个SQL Server登录名都属于public服务器角色。如果未向某个服务器主体授予或拒绝对某个安全对象的特定权限，该用户将继承授予该对象的public角色的权限。

3. 数据库级主体

Ø 数据库级主体包括数据库用户、数据角色和应用程序角色。

Ø 当创建数据库时，会默认创建一个名为guest的数据库用户，并且每个数据库用户都自动成为public角色的成员。

Ø 数据库角色是一组作用域为数据库范围的若干操作权限的集合。当一个数据库用户或数据库角色成为某一数据库角色的成员时，该数据库用户或数据库角色就拥有该数据库角色的所有操作权限。数据库角色又可以分为固定的数据库角色和用户自定义角色。固定的数据库角色及其权限说明如表12.2所示，用户自定义角色是由用户定义的数据库角色。

注：角色db_ddladmin包含下列权限：ALTER ANY ASSEMBLY、ALTER ANY ASYMMETRIC KEY、ALTER ANY CERTIFICATE、ALTER ANY CONTRACT、ALTER ANY DATABASE DDL TRIGGER、ALTER ANY DATABASE EVENT、NOTIFICATION、ALTER ANY DATASPACE、ALTER ANY FULLTEXT CATALOG、ALTER ANY MESSAGE TYPE、ALTER ANY REMOTE SERVICE BINDING、ALTER ANY ROUTE、ALTER ANY SCHEMA、ALTER ANY SERVICE、ALTER ANY SYMMETRIC KEY、CHECKPOINT、CREATE AGGREGATE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE QUEUE、CREATE RULE、CREATE SYNONYM、CREATE TABLE、CREATE TYPE、CREATE VIEW、CREATE XML SCHEMA COLLECTION、REFERENCES。

应用程序角色的作用是，只允许通过特定的应用程序连接的用户访问特定数据。在默认情况下，应用程序角色不包含任何成员，且是非活动的，这是与数据库角色的主要区别。

12.1.2 安全对象

Ø 安全对象是SQL Server数据库引擎授权系统控制对其进行访问的资源。狭义上，可将数据库中能够访问的数据库对象视为安全对象，

Ø 表、视图、存储过程等都是安全对象。一个主体只有拥有对一个安全对象的操作权限时才能对其进行相应的操作。对安全对象的操作权限可以授给一个主体或添加到一个角色中。

Ø 按照作用范围分类，安全对象可以分为服务器级安全对象、数据库级安全对象和架构级安全对象。

1. 服务器级安全对象

ü 服务器级安全对象是指作用范围为服务器的安全对象，它包括端点、登录用户和数据库。服务器级安全对象的操作权限只能赋给服务器级主体（如SQL Server登录用户），而不能赋给数据库级主体（如数据库用户）。

2. 数据库级安全对象

ü 数据库级安全对象是指作用范围为数据库的安全对象，它包括用户、角色、应用程序角色、程序集、消息类型、路由、服务、远程服务绑定、全文目录、证书、非对称密钥、对称密钥、约定、架构等。这些对象的操作权限可以赋给数据库级主体（如数据库用户等）。

3. 架构级安全对象

ü 架构是自SQL Server 2008开始提供的一种对象管理机制，它是形成单个命名空间的数据库对象的集合。架构级安全对象是指作用范围为架构的安全对象，它包括数据类型、XML架构集合和对象类，其中对象类又包括聚合、约束、函数、过程、队列、统计信息、同义词、表、视图等。

12.1.3 权限

Ø 权限是指出用户对特定数据库对象拥有的操作权力，也可以将权限理解为这些操作的集合。如果某一个用户某一个权限，且该权限包含了某一个操作，则该用户可以执行该操作。权限是SQL Server采用的主要安全机制。SQL Server通过授予主体的权限或收回已授予的权限来控制主体对安全对象的操作，从而避免越权非法操作，保证数据库的安全性。

Ø 表12.3列出了主要的权限类别以及可应用这些权限的安全对象的种类。

第12章数据的安全性控制

• 12.1 SQL Server 2014安全体系结构

• 12.2 角色

• 12.3 服务器级的安全控制

• 12.4 数据库级的安全控制

• 12.5 架构级的安全控制

角色是数据安全控制中涉及的一个十分重要的概念。简单而言，角色是相关操作权限的集合。根据作为对象的不同，角色可以分为服务器角色、数据库角色和应用程序角色等。当我们将一个主体添加到一个角色中，该主体则拥有该角色所包含的全部权限，从而达到简化权限管理之目的。我们也可以进行角色的创建、查看、修改和删除等操作。

12.2.1 服务器角色

Ø 服务器角色是执行服务器级管理的若干权限的集合。当我们将有关服务器级主体添加到服务器角色中而使它们成为服务器角色的成员时，这些主体就拥有了该服务器角色所包含的所有权限。显然，服务器角色简化了对服务器级主体的权限管理。但服务器角色已经被SQL Server“固化”了（因而又称固定服务器角色），用户不能添加、删除或修改服务器角色的任何属性。

Ø 服务器级角色一共有九种：sysadmin、serveradmin、securityadmin、processadmin、setupadmin、bulkadmin、diskadmin、dbcreator和public。其权限简要说明如下：

• sysadmin：系统管理员角色，拥有最大最多权限的服务器角色，利用这些权限可以完成任何的服务器级操作。一般是系统管理员才能被赋予这样的角色权限。

• serveradmin：服务器管理员角色，角色成员具有对服务器进行设置和关闭的权限。

• securityadmin：安全管理员角色，该角色成员可以对登录名及其属性进行管理，包括授予、拒绝、撤销服务器级或数据库级的权限，可以重置登录名和密码等。

• processadmin：进程管理员角色，该角色成员具有终止SQL Server实例中运行进程的权限。

• setupadmin：设置管理员角色，该角色成员可以添加和删除链接服务器。

• bulkadmin：该角色成员可以执行BULK INSERT 语句。

• diskadmin：磁盘管理角色，该角色具有管理磁盘文件的权限。

• dbcreator：数据库创建角色，该角色可以创建、更改、删除和还原任何数据库。

• public：其角色成员可以查看任何数据库。

12.2.2 数据库角色

Ø 数据库角色是数据库级的相关操作权限的集合。数据库角色分为两类，一类是数据库创建后自动产生的数据库角色，用户不能更改或删除这些角色，这些角色称为固定数据库角色，可以用系统存储过程sp_helpdbfixedrole来查看这类数据库角色，如图12.2所示。可见，固定数据库角色一共有九种。

Ø 另一类是用户根据实际需要而创建起来的数据库角色，称为用户自定义数据库角色。在Transact-SQL中，创建自定义数据库用户可用CREATE ROLE语句来完成。该语句的语法如下：

CREATE ROLE role_name [ AUTHORIZATION owner_name ]

其中，role_name表示待创建角色的名称；owner_name表示将拥有新角色的数据库用户或角色的名称，如果未指定owner_name，则执行CREATE ROLE的用户将拥有该角色。

【例12.1】创建自定义数据库角色，其拥有者为数据库用户。

Ø 下列代码创建了自定义数据库角色MyRole1，该角色为数据库MyDatabase的用户user1所拥有：

USE MyDatabase;

CREATE ROLE MyRole1 AUTHORIZATION user1

如果省略了AUTHORIZATION子句，则MyRole1为当前数据库用户所拥有。

Ø 角色是若干操作权限的集合，但是刚创建的角色是“空的”，没有包含任何权限。为使角色形成权限的集合，需要利用GRANT等语句对空角色“填充”权限。

Ø 删除自定义数据库角色可用语句DROP ROLE来完成。该语句的语法如下：

DROP ROLE role_name

其中，role_name为要删除的角色的名称。

【例12.2】自定义数据库角色删除实例。

Ø 下列语句用于删除自定义数据库角色MyRole2：

DROP ROLE MyRole2;

Ø 无法从数据库中删除拥有安全对象的角色。若要删除拥有安全对象的数据库角色，必须首先转移这些安全对象的所有权，或从数据库删除它们。无法从数据库中删除拥有成员的角色。若要删除有成员的角色，必须首先删除角色的成员。

Ø 不能使用DROP ROLE删除固定数据库角色。

12.2.3 应用程序角色

Ø 应用程序角色是用于给应用程序（而不是数据库角色或用户）分配权限的一种数据库级角色。当应用程序连接到数据库、激活应用程序角色，该应用程序将拥有应用程序角色所具有的所有权限，但这些权限只在连接期间有效。应用程序角色使用两种身份验证模式，可是使用sp_setapprole来激活，激活时需要密码（由应用程序提供）。

Ø 应用程序角色具有以下特点：

• 应用程序角色不包含成员，这与数据库角色不同。

• 当客户端应用程序向系统存储过程sp_setapprole提供应用程序角色名称和密码时，可激活应用程序角色。

• 密码必须存储在客户端计算机上，并且在运行时提供；应用程序角色无法从SQL Server内激活。

• 密码不加密。从SQL Server 2005开始，参数密码作为单向散列存储。

• 一旦激活，通过应用程序角色获取的权限在连接期间保持有效。

• 应用程序角色继承授予public角色的权限。

• 如果固定服务器角色sysadmin的成员激活某一应用程序角色，则安全上下文在连接期间切换为应用程序角色的上下文。

第12章数据的安全性控制

• 12.1 SQL Server 2014安全体系结构

• 12.2 角色

• 12.3 服务器级的安全控制

• 12.4 数据库级的安全控制

• 12.5 架构级的安全控制

12.3.1 身份验证模式

Ø 身份验证模式是指SQL Server确认用户的方式。SQL Server用户有两种来源：一种是Windows授权的用户（简称Windows用户，此处的Windows是指Windows NT或Windows 2000及其以上的版本），即这种用户的账号和密码是由Windows操作系统建立、维护和管理的，对SQL Server而言它们是来自Windows操作系统，只不过是由SQL Server确认为SQL Server用户而已；另一种是SQL Server授权的用户，这种用户的账号和密码是由SQL Server服务器创建、维护和管理，与Windows操作系统无关。

Ø SQL Server为这两种不同类型的用户提供了不同的身份验证模式。

1. Windows身份验证模式

Ø 在这种认证模式下，SQL Server允许Windows用户连接到SQL Server服务器，即这种用户只要能够登录Windows，再登录SQL Server时就不需要进行身份认证了。登录界面如图12.3所示。

2. SQL Server验证模式

Ø 在这种验证模式下，当用户要登录SQL Server时，SQL Server服务器要对登录的用户进行身份认证，即必须提供有效的登录名和密码，这些登录名和密码是保存在SQL Server数据库中，与Windows无关。SQL Server验证模式登录界面如图12.4所示。

Ø 注意，登录服务器时，“服务器类型”下拉框中要选择“数据库引擎”一项，表示要登录到数据库服务器。

12.3.2 创建登录

1. CREATE LOGIN的基本语法

创建登录服务器帐号的SQL语句是CREATE LOGIN，其语法如下：

CREATE LOGIN loginName { WITH <option_list1> | FROM <sources> }

其中：

<option_list1> ::=

PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]

[ , <option_list2> [ ,... ] ]

<option_list2> ::=

SID = sid

| DEFAULT_DATABASE = database

| DEFAULT_LANGUAGE = language

| CHECK_EXPIRATION = { ON | OFF}

| CHECK_POLICY = { ON | OFF}

| CREDENTIAL = credential_name

u 对涉及的参数说明如下：

Ø loginName

指定创建的登录名。有四种类型的登录名：SQL Server登录名、Windows登录名、证书映射登录名和非对称密钥映射登录名。如果从Windows 域帐户映射loginName，则loginName必须用方括号([ ])括起来。

Ø PASSWORD = 'password‘

指定正在创建的登录名的密码，仅适用于SQL Server登录名。密码应保持一定的长度，最好是各种字符的组合，不能使用如生日之类的、别人容易猜测的密码。

Ø PASSWORD = hashed_password

指定要创建的登录名的密码的哈希值，仅适用于HASHED关键字。

Ø HASHED

指定在PASSWORD参数后输入的密码已经过哈希运算，仅适用于SQL Server 登录名。如果未选择此选项，则在将作为密码输入的字符串存储到数据库之前，对其进行哈希运算。

Ø MUST_CHANGE

如果选择此选项，则SQL Server将在首次使用新登录名时提示用户输入新密码，即强迫用户更改密码。仅适用于SQL Server登录名。

Ø CREDENTIAL = credential_name

指定映射到新SQL Server登录名的凭据的名称。该凭据必须已存在于服务器中。当前此选项只将凭据链接到登录名。在未来的SQL Server 版本中可能会扩展此选项的功能。

Ø SID = sid

指定新SQL Server登录名的GUID。如果未选择此选项，则SQL Server自动指派GUID。仅适用于SQL Server登录名。

Ø DEFAULT_DATABASE = database

指定将指派给登录名的默认数据库。如果未包括此选项，则默认数据库将设置为master。

Ø DEFAULT_LANGUAGE = language

指定将指派给登录名的默认语言。如果未包括此选项，则默认语言将设置为服务器的当前默认语言。即使将来服务器的默认语言发生更改，登录名的默认语言也仍保持不变。

Ø CHECK_EXPIRATION = { ON | OFF }

指定是否对此登录帐户强制实施密码过期策略。默认值为OFF，表示不强制。此选项仅适用于SQL Server登录名。

Ø CHECK_POLICY = { ON | OFF }

指定应对此登录名强制实施运行SQL Server的计算机的Windows密码策略。默认值为ON。仅适用于SQL Server 登录名。

只有CHECK_POLICY设置为ON时，才能指定MUST_CHANGE选项，CHECK_EXPIRATION才能设置为ON。

Ø WINDOWS

指定将登录名映射到Windows登录名。

Ø CERTIFICATE certname

指定将与此登录名关联的证书名称。此证书必须已存在于master数据库中。

Ø ASYMMETRIC KEY asym_key_name

指定将与此登录名关联的非对称密钥的名称。此密钥必须已存在于master数据库中。

2. 创建SQL Server登录

Ø SQL Server sa是在创建数据库实例时设置的登录名，它具有最高的权限，可以在服务器上执行任何操作。

Ø 除了修改密码以外，用户不能对sa进行删除或任何其他修改操作。通过利用sa登录服务器，用户可以创建具有不同权限的各级登录帐号。

Ø 在本小节中将介绍SQL Server 2014服务器登录名及其密码等的创建、修改和删除方法。

【例12.3】以最简洁方式创建SQL Server登录名并设置密码。

Ø 这是较简单也是最常用登录名创建方法，代码如下：

CREATE LOGIN myLogin1 WITH PASSWORD = '123456';

其中，myLogin1为登录名，密码为123456。执行该语句后即可用myLogin1登录服务器了。但该语句没有显式指定默认数据库，SQL Server会自动将master设置为默认数据库，因此登录myLogin1指定打开master数据库。

Ø 如果在密码设置项后面再加上选项MUST_CHANGE，则在第一次用myLogin1登录服务器时会强制用户更改密码。

Ø 需要说明的是，在实际应用中密码应由字母、数字等多种字符构成，过于简单的密码容易被破解。这里设置得比较简单是为了方便说明问题，实际应用中一定不能这么设置。

【例12.4】创建一个指定默认数据库的SQL Server登录名。

Ø 利用例12.3创建的登录myLogin1登录服务器后发现，我们仅能访问master数据库。原因在于，创建该登录时并没有显式指定默认数据库，因此master自动被设置为默认数据库。

Ø 在本例中，创建名为myLogin2的登录，其密码亦为123456，但其默认数据库指定为MyDatabase。

CREATE LOGIN myLogin2

WITH PASSWORD = '123456',

DEFAULT_DATABASE = MyDatabase; -- 指定默认数据库

Ø 由于指定了master数据库以外的数据库——MyDatabase作为默认数据库，因此必须创建一个基于此登录的数据库用户，否则myLogin2不能正常登录服务器。以下为数据库MyDatabase添加数据库用户myLogin2（与登录名同名）

USE MyDatabase;

EXEC sp_grantdbaccess 'myLogin2'; -- 创建同名的数据库用户名

Ø 也可以利用CREATE USER语句创建一个与myLogin2不同名的数据库用户名User_myLogin2，但必须指定将该用户映射到myLogin2：

USE MyDatabase;

CREATE USER User_myLogin2 FOR LOGIN myLogin2;

Ø 执行上述代码，创建登录myLogin2，然后用该登录名登录服务器。

3. 创建Windows登录

Ø 创建Windows登录名就是将已有的Windows用户名设置为SQL Server服务器的登录名。因此，待设置的Windows用户名必须是已经存在的，然后利用CREATE LOGIN语句将之设置为服务器的登录名。

【例12.5】创建Windows登录名。

首先用Windows系统的控制面板创建一个名为sql2014的Windows用户，然后利用下列代码将之设置为SQL Server服务器的登录名：

CREATE LOGIN [MZQ\sql2014]

FROM WINDOWS

WITH DEFAULT_DATABASE = MyDatabase; -- 指定默认数据库

USE MyDatabase;

EXEC sp_grantdbaccess 'MZQ\sql2014'; -- 创建同名的数据库用户名（必须）

其中，MZQ为笔者机器的机器名（即计算机名），“[MZQ\sql2014]”中的方括号不能省略。

执行上述代码，创建Windows登录名sql2014，然后切换到Windows用户sql2014，登录数据库时选择Windows认证验证方式并选择用户sql2014即可登录数据库。

12.3.3 查看登录

1. 查看所有的登录

Ø 服务器级主体的信息保存在系统目录视图sys.server_principals中，因此，通过查询系统目录视图sys.server_principals可以获得登录名的相关信息。

【例12.6】查看服务器登录名的基本信息。

SQL Server登录名和Windows登录名的type列值分别为‘S’和‘U’，因此可以利用下列SELECT语句来查询服务器登录名的基本信息：

SELECT name 登录名,type_desc 类型说明,is_disabled '禁用/启用',create_date 创建时间,

modify_date 最近修改时间, default_database_name 默认数据库,default_language_name

默认语言

FROM sys.server_principals

WHERE type = 'S' OR type = 'U’;

执行该SELECT即可看到当前服务器上所有登录的基本信息。

2. 查看当前登录

Ø 函数SYSTEM_USER可用于返回当前的登录的名称。如果当前用户使用Windows身份验证登录到SQL Server，则SYSTEM_USER返回如下形式的Windows登录标识名称：

DOMAIN\user_login_name

Ø 如果当前用户使用SQL Server身份验证登录到SQL Server，则SYSTEM_USER返回SQL Server登录标识名称，例如为以myLogin1登录的用户返回myLogin1。

【例12.7】查看当前登录名。

PRINT SYSTEM_USER;

笔者使用sa登录，故执行上述语句后输出：sa。

12.3.4 登录的权限管理

1. 对登录授予权限

Ø 对登录授权是指将对服务器级安全对象（包括端点、登录用户和数据库）的操作权限赋给登录用户，使得该登录用户可以对此服务器级安全对象执行相应的操作。每一个刚创建的登录，会自动成为角色public的成员。但这种成员仅仅拥有公众访问权，而没有任何的操作权。所以，对刚创建的登录（如myLogin1），虽然可以连接服务器和打开其默认的数据库，但它几乎不能做任何事情。为此，需要对它们授权，这样它们才能具有执行相关操作的权力。我们可以从两种途径对登录用户授权：一种是利用GRANT语句，另一种是利用服务器角色。

（1）利用GRANT语句

利用GRANT语句可以对登录用户授予对服务器级主体的操作权限，其语法如下：

GRANT permission [ ,...n ] }

ON LOGIN :: SQL_Server_login

TO <server_principal> [ ,...n ]

[ WITH GRANT OPTION ]

[ AS SQL_Server_login ]

其中：

<server_principal> ::=

SQL_Server_login

| SQL_Server_login_from_Windows_login

| SQL_Server_login_from_certificate

| SQL_Server_login_from_AsymKey

对涉及的参数说明如下：

ü permission

指定可对SQL Server登录用户授予的权限。这些权限可用下列SELECT语句查看：

SELECT DISTINCT permission_name

FROM sys.fn_builtin_permissions('SERVER');

ü LOGIN :: SQL_Server_login

指定要对其授予权限的登录名，::为作用域限定符，必须使用。

ü TO <server_principal>

指定要向其授予权限的服务器级主体的名称。

ü SQL_Server_login

指定SQL Server登录用户的名称。

ü SQL_Server_login_from_Windows_login

指定通过Windows登录帐户创建的SQL Server登录用户的名称。

ü SQL_Server_login_from_certificate

指定映射到证书的SQL Server登录用户的名称。

ü SQL_Server_login_from_AsymKey

指定映射到非对称密钥的SQL Server登录用户的名称。

ü WITH GRANT OPTION

指示该主体还可以向其他主体授予所指定的权限。

ü AS SQL_Server_login

指定执行此查询的主体要从哪个SQL Server登录用户派生其授予该权限的权限。

注意：只有在当前数据库为master时，才可授予其服务器作用域内的权限。

【例12.8】对登录用户授权。

Ø 在下列代码中，将对登录用户myLogin1的IMPERSONATE操作权限赋给Windows用户MZQ\sql2014：

USE master;

GRANT IMPERSONATE ON LOGIN::myLogin1 to [MZQ\sql2014];

Ø 此后，MZQ\sql2014用户可以对myLogin1用户执行IMPERSONATE操作。

Ø 如果还希望MZQ\sql2014用户具有创建数据库的权限，则可通过下列语句将CREATE ANY DATABASE权限赋给它：

USE master;

GRANT CREATE ANY DATABASE to [MZQ\sql2014];

（2）利用服务器角色

Ø 角色的成员拥有该角色所包含的所有权限。因此，通过将一个登录用户添加为一个服务器角色的方法，可以到达对登录用户授权的目的。

Ø 向服务器角色添加成员可利用系统存储过程sp_addsrvrolemember来完成。该存储过程的语法如下：

sp_addsrvrolemember [ @loginame= ] 'login'

, [ @rolename = ] 'role‘

其中，login为要添加到固定服务器角色中的登录名，role为要添加登录的固定服务器角色的名称。添加成功时sp_addsrvrolemember返回0，否则返回1。

【例12.9】创建登录，并对它授予超级权限。

Ø 角色sysadmin拥有所有的操作权限，即为所谓的超级权限。以下先创建名为myLogin3的登录，然后将之添加成为服务器角色sysadmin的成员，从而拥有超级权限。

CREATE LOGIN myLogin3

WITH PASSWORD = '123456',

DEFAULT_DATABASE = MyDatabase; -- 指定默认数据库

USE MyDatabase;

EXEC sp_grantdbaccess 'myLogin3';

EXEC sp_addsrvrolemember 'myLogin3', 'sysadmin'; -- 将myLogin3添加为sysadmin的成员

执行上述代码后，创建的myLogin3将与sa具有同样的操作权限。

2. 对登录收回权限

（1）利用REVOKE语句

Ø 对于利用GRANT语句向登录授予的权限，可以利用REVOKE语句对其收回。针对服务器权限的收回，REVOKE语句的语法如下：

REVOKE [ GRANT OPTION FOR ] permission [ ,...n ] }

ON LOGIN :: SQL_Server_login

{ FROM | TO } <server_principal> [ ,...n ]

[ CASCADE ]

[ AS SQL_Server_login ]

其中：

<server_principal> ::=

SQL_Server_login

| SQL_Server_login_from_Windows_login

| SQL_Server_login_from_certificate

| SQL_Server_login_from_AsymKey

【例12.10】对登录收回指定的收权。

Ø 下列代码用于对登录用户MZQ\sql2014收回对IMPERSONATE权限（对登录myLogin1）以及CREATE ANY DATABASE权限：

USE master;

REVOKE IMPERSONATE ON LOGIN::myLogin1 FROM [MZQ\sql2014];

REVOKE CREATE ANY DATABASE FROM [MZQ\sql2014];

（2）利用服务器角色

Ø 对于已是某个服务器角色成员的登录，如果取消它的成员身份，那么它原来拥有的该角色权限也将自动被取消，从而达到收回权限的目的。

Ø 从服务器角色中删除成员的操作可用系统存储过程sp_dropsrvrolemember来完成。该存储过程的语法如下：

sp_dropsrvrolemember [ @loginame = ] 'login' , [ @rolename = ] 'role‘;

其中，login为要从服务器角色中删除的登录的名称，role为从其中删除成员的服务器角色的名称。添加成功时sp_dropsrvrolemember返回0，否则返回1。

【例12.11】删除服务器角色的成员。

将登录myLogin3从服务器角色sysadmin中删除：

EXEC sp_dropsrvrolemember ‘myLogin3’, ‘sysadmin’

执行上述代码后，登录myLogin3几乎失去所有的操作权限（除非它还拥有其他角色权限）。

3. 对登录拒绝权限

Ø 对登录拒绝权限是指拒绝已对登录用户授予的权限，这可利用DENY语句来实现。被拒绝的权限可能是由GRANT语句授予的，也可能是通过服务器角色成员资格继承的权限。与DENY语句不同的是，REVOKE语句不能收回通过服务器角色成员资格继承的权限，但DENY语句可以“收回”这些权限。

用于拒绝服务器权限的DENY语句的语法如下：

DENY permission [ ,...n ] }

ON LOGIN :: SQL_Server_login

TO <server_principal> [ ,...n ]

[ CASCADE ]

[ AS SQL_Server_login ]

其中：

<server_principal> ::=

SQL_Server_login

| SQL_Server_login_from_Windows_login

| SQL_Server_login_from_certificate

| SQL_Server_login_from_AsymKey

该语法中涉及的参数的意义与上小节中介绍的REVOKE语句一样，故不再作说明。

【例12.12】拒绝对登录名的IMPERSONATE 权限。

Ø 拒绝登录MZQ\sql2014对myLogin1的IMPERSONATE权限，代码如下：

USE master;

DENY IMPERSONATE ON LOGIN::myLogin1 TO [MZQ\sql2014];

【例12.13】拒绝登录用户拥有服务器角色中指定的权限。

Ø 服务器角色serveradmin包含了SHUTDOWN等服务器级权限，当登录MZQ\sql2014被添加成为该服务器角色成员时，它自动拥有该角色包含的所有权限。如果拒绝登录MZQ\sql2014拥有SHUTDOWN权限，但让它拥有serveradmin中的其他权限时，可利用下列语句来实现：

USE master;

EXEC sp_addsrvrolemember [MZQ\sql2014], ‘serveradmin’;

--将MZQ\sql2014添加为serveradmin的成员

DENY SHUTDOWN TO [MZQ\sql2014]; -- 拒绝serveradmin中的SHUTDOWN权限

12.3.5 删除登录

Ø 登录的删除可利用SQL的DROP LOGIN语句来实现，该语句的语法如下：

DROP LOGIN login_name

其中，login_name为要删除的登录名。

【例12.14】删除已有的登录。

对于已存在的登录myLogin2，可用下列语句删除：

DROP LOGIN myLogin2;

注意：不能删除SQL Server sa登录，不能删除正在使用的登录，也不能删除拥有任何安全对象、服务器级对象或SQL Server 代理作业的登录。此外，不能同时删除多个登录。

第12章数据的安全性控制

• 12.1 SQL Server 2014安全体系结构

• 12.2 角色

• 12.3 服务器级的安全控制

• 12.4 数据库级的安全控制

• 12.5 架构级的安全控制

12.4.1 数据库用户的管理

Ø 服务器登录名用于连接SQL Server服务器，但它不能访问数据库，只有数据库用户才能访问数据库。因此，在创建服务器登录名以后，还需要创建相应的数据库用户。一个数据库可以拥有多个数据库用户，一个数据库用户也可以访问多个数据库（在有权限的前提下）。但有一点是共同的，即所有数据库用户总是与某一个登录名相关联（依赖于一个登录名），且只能关联（依赖）一个登录名。因此，对一个登录名，可以创建多个与它相关联的其他数据库用户，从而可以通过一个登录名访问多个数据库。

1. 两个特殊的数据库用户——dbo和guest

在SQL Server 2014中，创建数据库时会自动生成两个特殊的数据库用户——dbo和guest。dbo在数据库范围内拥有最高的权限，可以执行一切操作。固定服务器角色sysadmin的任何成员都映射到每个数据库内的用户dbo上。

【例12.15】创建一个超级登录用户。

Ø 先创建登录AdminUser，然后将之添加到固定服务器角色sysadmin中，AdminUser即可形成一个超级登录用户，它可以管理一切事务。代码如下：

CREATE LOGIN AdminUser WITH PASSWORD = '123456',

DEFAULT_DATABASE = MyDatabase; -- 指定默认数据库

EXEC sp_addsrvrolemember 'AdminUser', 'sysadmin'; -- 将AdminUser添加为sysadmin的成员

Ø 执行上述代码后即可形成登录AdminUser，它自动映射到所有数据库内的用户dbo上，因此就不需要为之创建数据库用户了。

Ø guest用户允许任何已经登录到SQL Server服务器的用户都可以访问数据库，但访问的前提是需要对该用户授予CONNECT权限。

【例12.16】创建一个guest登录用户。

Ø 下面创建一个guest登录用户，用户名为GusetUser，使之可以访问数据库MyDatabase和DB_test，但没有任何操作权限（包括SELECT等）。代码如下：

CREATE LOGIN GusetUser WITH PASSWORD = '123',

DEFAULT_DATABASE = MyDatabase; -- 指定默认数据库

USE MyDatabase;

GRANT CONNECT TO GUEST;

USE DB_test;

GRANT CONNECT TO GUEST;

Ø 执行上述代码后，创建登录GusetUser。如果要禁用GUEST用户，可通过执行下列语句来收回SELECT权限，从而使GUEST失效：

REVOKE CONNECT TO GUEST;

注意：用户dbo和guest都不能被删除。另外，如果要查看当前在使用的登录名和数据库用户名，则可利用函数SYSTEM_USER和USER_NAME()来完成：

PRINT '当前登录名：'+SYSTEM_USER;

PRINT '当前数据库用户名：'+USER_NAME();

2. 创建数据库用户

Ø 除了两个特殊的数据库用户——dbo和guest以外，我们也可以使用CREATE USER语句创建数据库用户。CREATE USER语句的语法如下：

CREATE USER user_name

[ { { FOR | FROM }

{

| CERTIFICATE cert_name

| ASYMMETRIC KEY asym_key_name

}

| WITHOUT LOGIN

]

[ WITH DEFAULT_SCHEMA = schema_name ]

Ø 在CREATE USER语句中，如果省略FOR LOGIN子句，则必须数据库用户名user_name必须与其所依赖的登录名同名（否则不能创建该数据库用户），表示将创建的数据库用户映射到同名的服务器登录。

u 对涉及参数说明如下：

Ø user_name

指定在此数据库用户的名称。

Ø LOGIN login_name

指定所依赖的服务器登录名。login_name必须是服务器中已创建的有效的登录名。当此SQL Server登录名进入数据库时，它将获取正在创建的数据库用户的名称和ID。

Ø CERTIFICATE cert_name

指定要创建数据库用户的证书。

Ø ASYMMETRIC KEY asym_key_name

指定要创建数据库用户的非对称密钥。

Ø WITH DEFAULT_SCHEMA = schema_name

指定服务器为此数据库用户解析对象名时将搜索的第一个架构。如果不指定schema_name，则数据库用户将使用dbo作为默认架构。注意，在创建映射到Windows组、证书或非对称密钥的用户时，不能指定schema_name。

Ø WITHOUT LOGIN

指定不应将用户映射到现有登录名。

【例12.17】利用服务器登录创建数据库用户。

Ø 首先创建名为myLogin4的服务器登录，其密码为'123456'，然后基于myLogin4创建数据库MyDatabase的用户（登录名与数据库用户名同名）。代码如下：

CREATE LOGIN myLogin4 WITH PASSWORD = '123456';

USE MyDatabase; -- 必须指定数据库

CREATE USER myLogin4; -- 基于myLogin4创建同名的数据库用户

Ø 这是最简单的数据库用户创建方法，因为在CREATE USER语句中只指定了数据库用户名myLogin4，并不显式指定所依赖的登录名（省略了FOR LOGIN子句）。但在这种最简单的方式中，要求所使用的数据库用户名必须与其依赖的登录名同名。

【例12.18】基于一个登录创建多个数据库用户。

本实例展示如何利用一个登录来创建多个数据库的用户。

（1）首先创建登录myLogin5：

CREATE LOGIN myLogin5 WITH PASSWORD = '123456';

（2）创建三个数据库DB_test1、DB_test2、DB_test3：

CREATE DATABASE DB_test1;

CREATE DATABASE DB_test2;

CREATE DATABASE DB_test3;

（3）基于登录myLogin5创建数据库DB_test1的用户DB_user1：

USE DB_test1; -- 指定数据库（必须）

CREATE USER DB_user1 FOR LOGIN myLogin5;

同样，创建数据库DB_test2、DB_test3的用户，分别为DB_user2和DB_user3：

USE DB_test2; -- 指定数据库（必须）

CREATE USER DB_user2 FOR LOGIN myLogin5;

USE DB_test3; -- 指定数据库（必须）

CREATE USER DB_user3 FOR LOGIN myLogin5;

u 读者可能注意到，虽然对数据库DB_test1、DB_test2和DB_test3分别创建了用户DB_user1、DB_user2和DB_user3，但在登录服务器时好像只使用了登录名和登录密码就可以进入这三个数据库，而并没有用到用户名DB_user1、DB_user2和DB_user3。那么，数据库用户DB_user1、DB_user2和DB_user3在登录数据库时起什么作用呢？

答：实际上，在利用登录名成功连接服务器后，试图进入某个数据库时，SQL Server登录名将获取该数据库用户的名称和ID，然后作相应的验证才能进入数据库。可见，登录名隐式使用数据库用户。如果没有这些数据库用户，登录名是不能访问数据库的。

3. 查看数据库用户

系统存储过程sp_helpuser可用于查看当前数据库中数据库级主体的信息，其语法如下：

sp_helpuser [ [ @name_in_db = ] 'security_account‘ ]

其中，security_account为当前数据库中数据库用户或数据库角色的名称。security_account必须存在于当前数据库中。如果未指定security_account，则sp_helpuser返回有关所有数据库用户的信息，其结构如表12.4所示。

【例12.19】利用sp_helpuser查看当前数据库的所有数据库用户。

下列代码用于查询数据库MyDatabase中的所有数据库用户及其相关信息：

USE MyDatabase; -- 指定数据库

sp_helpuser;

在笔者机器上执行上述代码，结果如图12.5所示。

从图12.5容易看出，哪一用户拥有哪个角色以及依赖于哪个登录等，一目了然。

Ø 在编写SQL代码的时候，可能需要获得当前数据库用户或指定数据库用户的名称，这时可以利用函数USER_NAME来完成。该函数的语法如下：

USER_NAME ( [ id ] )

其中，id表示指定的数据库用户的id。id是可选的，如果没有指定id，则返回当前数据库用户的名称。

【例12.20】查看当前数据库用户的名称。

笔者在本次登录时使用了sa登录。由于该登录自动映射到用户dbo，所以下列语句在笔者机器上将返回dbo：

print USER_NAME();

4. 修改数据库用户

Ø 数据库用户的修改由ALTER USER语句来实现，其语法如下：

ALTER USER userName WITH <set_item> [ ,...n ]

其中：

<set_item> ::=

NAME = newUserName

| DEFAULT_SCHEMA = schemaName

| LOGIN = loginName

其中，userName为待修改的数据库用户名，newUserName为修改后新的数据用户名，loginName表示修改后的用户要映射到的登录名，schemaName为新的架构名。

Ø 可见，利用ALTER USER语句可以修改数据库用户的三个属性值：数据库用户的名称、架构和所依赖的登录名。

【例12.21】修改数据库用户的架构和所依赖的登录名。

在本例中，先创建两个登录：myLogin6和myLogin7，然后为数据库MyDatabase创建用户user1，并映射到myLogin6，最后将user1的架构和依赖的登录分别改为Purchasing和myLogin7。代码如下：

CREATE LOGIN myLogin6 WITH PASSWORD = 'akh8#Udfgsr7mAcxo3Bfe';

CREATE LOGIN myLogin7 WITH PASSWORD = 'akh8#Udfgsr7mAcxo3Bfe';

USE MyDatabase;

CREATE USER user1 FOR LOGIN myLogin6;

ALTER USER user1 WITH DEFAULT_SCHEMA = Purchasing, LOGIN = myLogin7; --修改用户

5. 删除数据库用户

Ø 删除数据库用户由DROP USER语句来完成，其语法如下：

DROP USER user_name

其中，user_name为删除的数据库用户的名称。

【例12.22】删除数据库用户。

将数据库MyDatabase的用户user1删除，代码如下：

DROP USER user1;

12.4.2 安全对象的权限管理

Ø 数据库级主体可以对数据库级安全对象执行相关操作，但这种操作是有前提的，即数据库级主体必须拥有与数据库级安全对象相关的权限。也就是说，只有拥有相关的权限后，数据库级主体才能对数据库级安全对象执行对应的操作。

Ø 数据库级安全对象包括用户、角色、应用程序角色、程序集、消息类型、路由、服务、远程服务绑定、全文目录、证书、非对称密钥、对称密钥、约定、架构等。数据库级主体包括数据库用户、数据角色和应用程序角色。

u 给数据库级主体授权——GRANT

• 给数据库级主体授权是指将数据库级安全对象的有关操作权限授予数据库级主体，使该主体可以对对应的安全对象执行相应的操作。当然，也可以将其他安全对象的操作权限赋给数据库级主体。

• 由于数据库级安全对象包含的对象很多，限于篇幅，我们不能一一介绍。下面主要通过举例说明如何将对一个数据库级安全对象的操作权限赋给另一个数据库级安全对象，以及将对数据库的操作权限（如CREATE TABLE、CREATE VIEW等）赋给另一个数据库级安全对象。

• 也可以通过利用角色来对主体授权，即使主体成为角色的成员。

（1）将对数据库级主体的操作权限赋给（另一个）数据库级主体

利用GRANT语句可以将对一个数据库级主体的操作权限赋给另一个数据库级主体，语法如下：

GRANT permission [ ,...n ]

{ [ USER :: database_user ]

| [ ROLE :: database_role ]

| [ APPLICATION ROLE :: application_role ]

}

TO <database_principal> [ ,...n ]

[ WITH GRANT OPTION ]

[ AS <database_principal> ]

对涉及的参数说明如下：

Ø permission

该参数表示可对数据库主体授予的权限。

Ø USER :: database_user

指定要对其授予权限的用户的类和名称。::为作用域限定符，不能省略（下同）。

Ø ROLE :: database_role

指定要对其授予权限的角色的类和名称。

Ø APPLICATION ROLE :: application_role

指定要对其授予权限的应用程序角色的类和名称。

Ø WITH GRANT OPTION

指示该主体还可以向其他主体授予所指定的权限。

Ø AS <database_principal>

指定执行此查询的主体要从哪个主体派生其授予该权限的权限。

Ø Database_user

指定数据库用户。

Ø Database_role

指定数据库角色。

Ø Application_role

指定应用程序角色。

Ø Database_user_mapped_to_Windows_User

指定映射到Windows用户的数据库用户。

Ø Database_user_mapped_to_Windows_Group

指定映射到Windows组的数据库用户。

Ø Database_user_mapped_to_certificate

指定映射到证书的数据库用户。

Ø Database_user_mapped_to_asymmetric_key

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

Ø Database_user_with_no_login

指定无相应服务器级主体的数据库用户。

【例12.23】将对一个用户的CONTROL权限赋给另一个用户。

Ø 作为例子，下面先创建登录myLogin8_1和myLogin8_2，然后基于这两个登录，分别创建数据库MyDatabase的用户User1和User2，最后将对用户User2的CONTROL操作权限赋给用户User1。代码如下：

CREATE LOGIN myLogin8_1 WITH PASSWORD = '123456'; --创建登录

CREATE LOGIN myLogin8_2 WITH PASSWORD = '123456';

USE MyDatabase; -- 指定数据库（必须）

CREATE USER User1 FOR LOGIN myLogin8_1; --创建数据库用户

CREATE USER User2 FOR LOGIN myLogin8_2;

USE MyDatabase;

GRANT CONTROL ON USER::User2 TO User1; -- 授权

u 对数据库用户的操作权限主要包括：CONTROL、IMPERSONATE、ALTER、VIEW DEFINITION；

u 对数据库角色的操作权限主要包括：CONTROL、TAKE OWNERSHIP、ALTER、VIEW DEFINITION；

u 对应用程序角色的操作权限主要包括：CONTROL、ALTER、VIEW DEFINITION。

读者可以模仿例12.23，将对一个数据库级主体的操作权限赋给另一个数据库级主体。

1. 将对数据库的操作权限赋给（一个）数据库级主体

下列的GRANT语句的语法用于将对当前数据库的操作权限赋给一个数据库级主体：

GRANT <permission> [ ,...n ]

TO <database_principal> [ ,...n ] [ WITH GRANT OPTION ]

[ AS <database_principal> ]

其中：

<permission>::=

permission | ALL [ PRIVILEGES ]

<database_principal> ::=

Database_user

| Database_role

| Application_role

| Database_user_mapped_to_Windows_User

| Database_user_mapped_to_Windows_Group

| Database_user_mapped_to_certificate

| Database_user_mapped_to_asymmetric_key

| Database_user_with_no_login

Ø 可以看出，此处的参数与前面介绍的GRANT语句语法中的参数绝大部分参数相同，不同的是以下三个参数：

p permission

指定可授予的对数据库的操作权限。这些权限可用下列SELECT语句来查看：

SELECT permission_name

FROM sys.fn_builtin_permissions('DATABASE');

p ALL

该项表示同时授予下列权限：BACKUP DATABASE、BACKUP LOG、CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE和CREATE VIEW。但注意，它并不表示授予所有可能的权限。

p PRIVILEGES

包含此参数是为了符合ISO标准。

【例12.24】将对数据库的CREATE TABLE权限等赋给数据库用户。

Ø 下面代码用于将对数据库MyDatabase的CREATE TABLE、CREATE VIEW和CREATE PROCEDURE操作权限（分别表示对数据库MyDatabase的创建表、创建视图和创建存储过程的权限）赋给数据库用户User1，代码如下：

USE MyDatabase;

GRANT CREATE TABLE,CREATE VIEW,CREATE PROCEDURE TO User1;

Ø 执行上述代码后，在利用User1登录数据库MyDatabase;时，就可以在其中创建表、视图和存储过程。

【例12.25】让一个数据库用户具有授权的全向。

ü 经授权后，有的用户可能拥有很大的权限，但该用户本身也许不能将其拥有的大量权限赋给别的用户或角色。如果需要让用户能够将其拥有的权限赋给别的用户或角色，则必须在对用户授权时使用WITH GRANT OPTION选项。

ü 下面代码用于将对角色MyRole1的VIEW DEFINITION操作权限赋给用户user1，同时允许用户user1将此权限赋给其他的数据库级主体（即用户user1具有将对角色MyRole1的VIEW DEFINITION操作权限赋给其他数据库级主体的能力）：

USE MyDatabase;

GRANT VIEW DEFINITION ON ROLE:: MyRole1 TO user1 WITH GRANT OPTION;

2. 查看数据库级安全对象上的权限——sp_helprotect

系统存储过程sp_helprotect可用于查看数据库级安全对象上的所有权限。sp_helprotect的语法如下：

sp_helprotect [ [ @name = ] 'object_statement' ]

[ , [ @username = ] 'security_account' ]

[ , [ @grantorname = ] 'grantor' ]

[ , [ @permissionarea = ] 'type' ]

对涉及的参数说明如下：

Ø [ @name = ] 'object_statement‘

指定当前数据库或语句中具有报告权限的对象的名称。默认值为NULL，表示将返回所有的对象权限和语句权限。如果值为一个对象（表、视图、存储过程或扩展存储过程），则该对象必须是当前数据库中的有效对象。

如果object_statement是一个语句，则该语句可以是下列语句之一：

• CREATE DATABASE

• CREATE DEFAULT

• CREATE FUNCTION

• CREATE PROCEDURE

• CREATE RULE

• CREATE TABLE

• CREATE VIEW

• BACKUP DATABASE

• BACKUP LOG

Ø [ @username = ] 'security_account’

security_account表示为其返回权限的主体的名称。默认值为NULL，表示将返回当前数据库中的所有主体的权限。security_account必须存在于当前数据库中。

Ø [ @grantorname = ] 'grantor‘

给其他主体赋予权限的主体的名称。默认值为NULL，表示将返回数据库中任意主体授予的权限的全部信息。

Ø [ @permissionarea = ] 'type‘

指示是显示对象权限（字符串o）、语句权限（字符串s）还是同时显示两者(os) 的字符串。默认值为os。type 可以是o和s的任意组合，o和s之间可以有也可以没有逗号和空格。

【例12.26】查看指定用户所拥有的权限。

Ø 根据上面系统存储过程sp_helprotect的语法，查看数据库用户User1所拥有的权限可以利用下列语句来实现：

EXEC sp_helprotect @username = 'User1’;

Ø 或

EXEC sp_helprotect NULL,'User1‘;

如果sp_helprotect不带参数，则将返回所有对象的权限信息。

3. 对数据库级主体收回权限——REVOKE

在给某个数据库级主体授予某些操作权限后，如何认为没有必要或不应该给它授予这些权限时，可以利用REVOKE语句收回已授予的权限。

（1）收回已授予的对数据库级主体的操作权限

将对数据库级主体的操作权限赋给另一个数据库级主体后，如果想收回已赋予的权限，则可用REVOKE语句的下列语法：

REVOKE [ GRANT OPTION FOR ] permission [ ,...n ]

{ [ USER :: database_user ]

| [ ROLE :: database_role ]

| [ APPLICATION ROLE :: application_role ]

}

{ FROM | TO } <database_principal> [ ,...n ]

[ CASCADE ]

[ AS <database_principal> ]

u 上述语法中涉及的参数与前面介绍的GRANT语句语法中涉及的参数的意义基本相同。需要特别说明的是下列两个参数：

Ø GRANT OPTION

指示要撤消向其他主体授予指定权限的权限，不会撤消该权限本身。但如果主体具有不带GRANT选项的指定权限，则将撤消该权限本身。

Ø CASCADE

指示要撤消的权限也会从此主体授予或拒绝该权限的其他主体中撤消。

【例12.27】收回一个用户拥有的对另一个用户的CONTROL操作权限。

在例12.23中，将对用户User2的CONTROL权限赋给用户User1，现用REVOKE语句对用户User1收回该权限，代码如下：

USE MyDatabase;

REVOKE CONTROL ON USER:: User2 FROM User1;

【例12.28】收回一个用户拥有的对其他主体授权的权限。

例12.25中，对用户User1授予了对角色MyRole1的VIEW DEFINITION操作权限，并允许用户User1将该权限赋给其他主体。如果要收回用户User1拥有的将对角色MyRole1的VIEW DEFINITION操作权限赋给其他主体的权限（但不收回User1拥有的将对角色MyRole1的VIEW DEFINITION操作权限），则可用下列语句来实现：

USE MyDatabase;

REVOKE GRANT OPTION FOR VIEW DEFINITION ON ROLE:: MyRole1 FROM User1;

注意：执行上述语句后，User1仍然可以执行对角色MyRole1的VIEW DEFINITION操作，只是不能将该操作权限赋给其他主体而已。如果需要同时收回User1拥有的对角色MyRole1的VIEW DEFINITION操作权限，则可用下列语句实现：

USE MyDatabase;

REVOKE VIEW DEFINITION ON ROLE:: MyRole1 FROM User1 CASCADE;

（2）收回已授予的对数据库的操作权限

将对当前数据库的操作权限赋给一个数据库级主体后，如果需要收回该操作权限，则可用REVOKE语句的下列语法：

REVOKE [ GRANT OPTION FOR ] <permission> [ ,...n ]

{ TO | FROM } <database_principal> [ ,...n ]

[ CASCADE ]

[ AS <database_principal> ]

u 该语法涉及的参数与前面介绍的REVOKE语句语法涉及的参数的意义基本相同。需要注意的是以下两个参数：

Ø ALL

指定该选项时，表示同时收回下列权限：BACKUP DATABASE、BACKUP LOG、CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE 和CREATE VIEW。但不是所有的数据库权限。

Ø PRIVILEGES

包含此参数是为了符合ISO 标准。请不要更改ALL 的行为。

【例12.29】收回数据库用户拥有的对数据库的有关操作权限。

Ø 例12.24中，对用户User1授予了对数据库MyDatabase的CREATE TABLE、CREATE VIEW和CREATE PROCEDURE操作权限。如果需要将后面的两个操作权限（CREATE VIEW和CREATE PROCEDURE），则可用下列语句实现：

USE MyDatabase;

REVOKE CREATE VIEW,CREATE PROCEDURE FROM User1;

Ø 执行上述语句后，用下列语句来查看用户User1所拥有的数据库权限，如图12.6所示。这表明，CREATE VIEW和CREATE PROCEDURE权限确实已经被收回。

USE MyDatabase;

EXEC sp_helprotect @username = 'User1';

4. 对数据库级主体拒绝权限——DENY

Ø 主体主要是通过两种途径获得对安全对象的操作权限，一种是利用GRANT语句对其授权，另一个是通过组或角色成员资格继承权限。利用REVOKE语句可以收回由GRANT语句授予的权限，但不能收回通过组或角色成员资格继承权限。因此，要彻底地不允许主体拥有某一种操作权限，则最好使用DENY语句来实现。

Ø DENY语句语句的简化语法如下：

DENY { ALL [ PRIVILEGES ] }

| permission [ ( column [ ,...n ] ) ] [ ,...n ]

[ ON [ class :: ] securable ] TO principal [ ,...n ]

[ CASCADE] [ AS principal ]

【例12.30】拒绝对一个用户授予对另一个用户的CONTROL权限。

Ø 下列代码用于拒绝用户User1 对MyDatabase用户User2的CONTROL 权限：

USE MyDatabase;

DENY CONTROL ON USER::User2 TO User1;

此后，用户User1不能执行对用户User2的CONTROL操作。

【例12.31】拒绝一个用户拥有对数据库的CREATE TABLE权限。

Ø 下列代码用于拒绝用户User1拥有对数据库MyDatabase的CREATE TABLE权限：

USE MyDatabase;

DENY CREATE TABLE TO User1;

Ø 此后，用户User1不能执行对数据库MyDatabase的CREATE TABLE操作，即User1不能在数据库MyDatabase中创建数据表。除非执行下列语句，以将CREATE TABLE权限赋给用户User1（解除拒绝）：

USE MyDatabase;

GRANT CREATE TABLE TO User1; -- 授权

第12章数据的安全性控制

• 12.1 SQL Server 2014安全体系结构

• 12.2 角色

• 12.3 服务器级的安全控制

• 12.4 数据库级的安全控制

• 12.5 架构级的安全控制

12.5.1 架构及其管理

1. 架构的概念

Ø 架构（SCHEMA）是形成单个命名空间的数据库对象的集合，它包括数据类型、XML架构集合和对象类，其中对象类又包括聚合、约束、函数、过程、队列、统计信息、同义词、表、视图等。在同一个架构中不能存在重名的数据库对象。

Ø 在一个架构中不允许存在同名的两个表，只有在位于不同架构中的两个表才能重名。从管理的角度看，架构是数据对象管理的逻辑单位。

Ø 在SQL Server 2000中没有架构的概念，架构是自SQL Server 2005开始在SQL Server中出现的。实际上，在SQL Server 2000中架构和数据库用户是同一个概念，两者是合一的。自SQL Server 2005开始，架构与数据库用户分离了。具体讲，在SQL Server 2000 中，数据库用户和架构是隐式连接在一起的。每个数据库用户都是与该用户同名的架构的所有者。因而，SQL Server 2000 中的架构也就是数据库中的用户。

Ø 架构和数据库用户分离是SQL Server 2005对SQL Server 2000的一个重要改进。这种改进的好处体现在：

• 多个用户可以通过角色成员身份或Windows 组成员身份拥有一个架构。

• 有效简化了删除数据库用户的操作。

• 删除数据库用户时，不需要对该用户架构所包含的对象进行重命名。因此，在删除创建架构所含对象的用户后，不再需要修改和测试显式引用这些对象的应用程序。从而可以有效较少系统开发的总工作量。

• 通过默认架构的共享，可以实现统一名称解析；而且通过架构的共享，开发人员可以将共享对象存储在为特定应用程序专门创建的架构中，而不是DBO 架构中。

• 可以用比SQL Server 2000中更大的粒度管理架构和架构包含的对象的权限。

Ø 在SQL Server 2014中，架构分为两种类型：一种是系统内置的架构，称为系统架构；另一种是由用户定义的架构，称为用户自定义架构。

Ø 在创建数据库用户时，必须指定一个默认架构，即每个用户都有一个默认架构。如果不指定，则使用系统架构dbo作为用户的默认架构。服务器在解析对象的名称时，第一个要搜索的架构就是用户的默认架构。

2. 创建架构——CREATE SCHEMA

创建架构的语法如下：

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]

其中：

<schema_name_clause> ::=

{

schema_name

| AUTHORIZATION owner_name

| schema_name AUTHORIZATION owner_name

}

<schema_element> ::=

{

table_definition | view_definition | grant_statement

revoke_statement | deny_statement

}

对涉及参数说明如下：

Ø schema_name

指定待创建的架构的名称，在数据库内是唯一的。

Ø AUTHORIZATION owner_name

指定将拥有架构的数据库级主体（数据库用户或角色）的名称。该主体可以拥有包含当前架构在内的多个架构，并且可以不使用当前架构作为其默认架构。

Ø table_definition

指定在架构内创建表的CREATE TABLE语句。执行此语句的主体必须对当前数据库具有CREATE TABLE 权限。

Ø view_definition

指定在架构内创建视图的CREATE VIEW语句。执行此语句的主体必须对当前数据库具有CREATE VIEW权限。

Ø grant_statement

指定可对除新架构外的任何安全对象授予权限的GRANT语句。

Ø revoke_statement

指定可对除新架构外的任何安全对象收回权限的REVOKE语句。

Ø deny_statement

指定可对除新架构外的任何安全对象拒绝授予权限的DENY语句。

【例12.32】创建架构实例。

以下代码将创建名为mySchema1的架构，其拥有者为数据库用户User1，同时创建数据表T1（其所属架构自动设置为mySchema1）。此外，该架构创建语句还向User2授予SELECT权限，对用户User2拒绝授予INSERT权限。

USE MyDatabase;

CREATE SCHEMA mySchema1 AUTHORIZATION User1

CREATE TABLE T1(c1 int, c2 int, c3 int)

GRANT SELECT TO User2

DENY INSERT TO User2;

3. 修改架构——ALTER SCHEMA

Ø 架构的修改主要包括在架构之间传输安全对象以及修改架构的拥有者。前者用ALTER SCHEMA语句来实现，后者则用ALTER AUTHORIZATION语句来完成。以下分别举例说明。

【例12.33】在架构之间传输安全对象。

有时候需要将一个架构中的安全对象传输到另一个架构中去，例如，将架构mySchema1中的安全对象——数据表T1传输到架构mySchema2中去，则可利用下列的ALTER SCHEMA语句来实现：

ALTER SCHEMA mySchema2 TRANSFER mySchema1.T1;

【例12.34】修改架构的拥有者。

将架构mySchema1的拥有者由原来的User1改为User2（即将mySchema1的所有权传递给User2），代码如下：

ALTER AUTHORIZATION ON SCHEMA::mySchema1 TO User2;

4. 查看架构

【例12.35】查看当前数据库中的所有架构。

系统目录视图sys.schemas保存了当前数据库中的所有架构信息，这些信息包含架构的名称（name）、架构id（schema_id）和其拥有者的id（principal_id）。因此，利用下列语句可以返回当前数据库中的所有架构：

USE MyDatabase; -- 指定当前数据库

SELECT name 架构名

FROM sys.schemas;

【例12.36】查看架构的拥有者。

架构的拥有者为数据库主体，这些主体信息包含在sys.database_principals中。因此，通过对sys.schemas和sys.database_principals的连接查询即可获得架构的拥有者。代码如下：

USE MyDatabase;

SELECT a.name 架构, b.name 拥有者

FROM sys.schemas a

JOIN sys.database_principals b

ON a.principal_id = b.principal_id;

【例12.37】查看指定架构包含的对象。

为了观看效果，下面代码先在数据库MyDatabase中创建架构mySchema3（其拥有者为User1），然后在该架构内创建三个对象——数据表：T3_1、T3_2和T3_3，最后查询架构mySchema3包含的对象。代码如下：

USE MyDatabase;

CREATE SCHEMA mySchema3 AUTHORIZATION User1; -- 创建架构

CREATE TABLE mySchema3.T3_1(c1 int, c2 int); -- 创建架构内的对象

CREATE TABLE mySchema3.T3_2(c1 int, c2 int);

CREATE TABLE mySchema3.T3_3(c1 int, c2 int);

SELECT b.name 架构名, a.name 包含的对象名, a.type 对象类型 -- 查询架构包含的对象

FROM sys.objects a

JOIN sys.schemas b

ON a.schema_id = b.schema_id

WHERE b.name = 'mySchema3';

5. 删除架构——DROP SCHEMA

Ø 从数据库中删除架构可用DROP SCHEMA语句来实现，其语法如下：

DROP SCHEMA schema_name;

schema_name表示待删除的架构的名称。但需要注意的是，要删除的架构不能包含任何对象，否则删除操作将失败。

【例12.38】删除架构实例。

删除在例12.37中创建的架构mySchema3，但该架构包含三个对象：表T3_1、T3_2和T3_3（可用前面介绍的方法查看一个架构包含的所有对象），因此需要删除者三张数据表：

USE MyDatabase;

DROP TABLE mySchema3.T3_1, mySchema3.T3_2, mySchema3.T3_3;

或者将利用ALTER SCHEMA... TRANSFER语句将架构mySchema3下的对象传递到其他架构（如mySchema2）下：

USE MyDatabase;

ALTER SCHEMA mySchema2 TRANSFER mySchema3.T3_1

ALTER SCHEMA mySchema2 TRANSFER mySchema3.T3_2

ALTER SCHEMA mySchema2 TRANSFER mySchema3.T3_3

然后才能删除架构mySchema3：

DROP SCHEMA mySchema3; -- 删除架构

6. 架构权限的管理

Ø 对架构的操作权限可以赋给一个或多个数据库级主体，也可以对已授予的权限进行收回或禁用等操作，这些操作也分别使用GRANT、DENY、REVOKE语句来实现。

Ø 针对对架构的操作权限，其赋权操作由GRANT语句的下列语法完成：

GRANT permission [ ,...n ] ON SCHEMA :: schema_name

TO database_principal [ ,...n ]

[ WITH GRANT OPTION ]

[ AS granting_principal ]

其中，对涉及的参数说明如下：

(1) permission

指定可授予的、对架构的操作权限。这些权限可以利用下列语句查询：

SELECT permission_name 权限名

FROM sys.fn_builtin_permissions('SCHEMA’);

(2) ON SCHEMA :: schema_name

设置此项时，表示将对其操作的权限赋给database_principal的架构，schema_name则表示该架构的名称，需要范围限定符“::”。

(3) database_principal

指定要向其授予权限的主体，为以下类型之一：

ü 数据库用户

ü 数据库角色

ü 应用程序角色

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

ü 映射到Windows 组的数据库用户

ü 映射到证书的数据库用户

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

ü 未映射到服务器主体的数据库用户

(4) GRANT OPTION

指示该主体还可以向其他主体授予所指定的权限。

(5) AS granting_principal

指定一个主体，执行该查询的主体从该主体获得授予该权限的权利。该主体是以下类型之一：

ü 数据库用户

ü 数据库角色

ü 应用程序角色

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

ü 映射到Windows 组的数据库用户

ü 映射到证书的数据库用户

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

ü 未映射到服务器主体的数据库用户

【例12.39】将对架构的操作权限赋给指定的数据库主体。

Ø 下列代码将对架构mySchema1的INSERT、UPDATE和DELETE权限赋给数据库用户User1：

USE MyDatabase;

GRANT INSERT,UPDATE,DELETE ON SCHEMA :: mySchema1 TO User1;

Ø REVOKE、DENY语句GRANT语句的使用方法类似。例如，收回或拒绝User1对架构mySchema1的INSERT权限，可分别利用下面的语句来实现：

REVOKE INSERT ON SCHEMA :: mySchema1 TO User1;

DENY INSERT ON SCHEMA :: mySchema1 TO User1;

12.5.2 安全对象的权限管理

Ø 架构级安全对象包括数据类型、XML架构集合和对象类，其中对象类又包括聚合、约束、函数、过程、队列、统计信息、同义词、表、视图等。本节将重点介绍如何管理对对象类的操作权限。

Ø 可以这样形象地理解：服务器级和数据库级的安全控制分别是对数据的最外层和次外层保护，而架构级的安全控制则是对数据最内层的保护，是数据的“贴身侍卫”。架构是安全对象在逻辑上的集合，如果一个主体拥有了对架构的访问权限，那么它对该架构内的所有安全对象都具有相应的访问权限。如果觉得一个主体仅从架构那里继承来的权限还“不够用”，我们可以单独给它“开小灶”——将对对象的操作权限逐一地给它赋权，从而使该主体拥有足够的权限。

Ø 下面将介绍如何将对架构级安全对象的操作权限赋给一个数据库级主体，以及对这些权限的收回和拒绝等。

Ø 给一个主体授予对架构级安全对象（表、视图、表值函数、存储过程、扩展存储过程、标量函数、聚合函数、服务队列或同义词）的操作权限，也可以使用GRANT语句，相应的语法如下：

GRANT <permission> [ ,...n ] ON

[ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ]

TO <database_principal> [ ,...n ]

[ WITH GRANT OPTION ]

[ AS <database_principal> ]

该语法涉及的参数说明如下：

Ø permission

指定可以授予的对架构包含的对象的权限。这些权限可以利用下列SELECT语句查看：

SELECT permission_name

FROM sys.fn_builtin_permissions('OBJECT');

Ø ALL

选择该项表示授予适用于指定对象的所有ANSI-92权限。对于不同权限，ALL 的含义有所不同：

ü 标量函数权限：EXECUTE、REFERENCES

ü 表值函数权限：DELETE、INSERT、REFERENCES、SELECT、UPDATE

ü 存储过程权限：EXECUTE

ü 表权限：DELETE、INSERT、REFERENCES、SELECT、UPDATE

ü 视图权限：DELETE、INSERT、REFERENCES、SELECT、UPDATE

Ø PRIVILEGES

选择此参数是为了符合ANSI-92标准，不会更改ALL的行为。

Ø column

指定表、视图或表值函数中要授予对其权限的列的名称。括号( )是必须的。只能授予对列的SELECT、REFERENCES及UPDATE权限。column可以在权限子句中指定，也可以在安全对象名之后指定。

Ø ON [ OBJECT :: ] [ schema_name ].object_name

指定要授予对其权限的对象。如果指定了schema_name，则OBJECT短语是可选的，否则是必选的。如果使用了OBJECT短语，则需要作用域限定符(::)。如果未指定schema_name，则使用默认架构。如果指定了schema_name，则需要架构作用域限定符\(.)。

Ø TO <database_principal>

指定要向其授予权限的主体。

Ø WITH GRANT OPTION

选择该选项表示该主体还可以向其他主体授予所指定的权限。

Ø AS <database_principal>

指定执行此查询的主体要从哪个主体派生其授予该权限的权限。

Ø Database_user

指定数据库用户。

Ø Database_role

指定数据库角色。

Ø Application_role

指定应用程序角色。

Ø Database_user_mapped_to_Windows_User

指定映射到Windows 用户的数据库用户。

Ø Database_user_mapped_to_Windows_Group

指定映射到Windows 组的数据库用户。

Ø Database_user_mapped_to_certificate

指定映射到证书的数据库用户。

Ø Database_user_mapped_to_asymmetric_key

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

Ø Database_user_with_no_login

指定无相应服务器级主体的数据库用户。

【例12.40】授予对表的SELECT权限

本例中，将对架构dbo内表student的SELECT权限赋给数据库用户User1，代码如下：

GRANT SELECT ON OBJECT::dbo.student TO User1;

【例12.41】授予对存储过程的EXECUTE权限。

本例中，将对存储过程MyPro1的EXECUTE权限赋给数据库角色MyRole1，代码如下：

GRANT EXECUTE ON OBJECT::dbo.MyPro1 TO MyRole1;

这样，角色MyRole1就包含了对存储过程MyPro1的执行权限。

对于架构级安全对象，权限的收回（REVOKE）、拒绝（DENY）与权限的授予（GRANT）的方法是一样的，具体使用时只需将GRANT改为REVOKE或DENY即可。

【例子】对于例12.25中给MyRole1授予的对dbo.MyPro1的EXECUTE权限，可用下列语句将其收回：

REVOKE EXECUTE ON OBJECT::dbo.MyPro1 TO MyRole1;

posted @ 2019-06-22 23:53 Zander_Zhao 阅读(603) 评论(0) 编辑收藏举报

刷新页面返回顶部

Zander Zhao

To the time to life, rather than to life in time .

191.数据安全性控制

公告