SQL Server安全（4/11）：许可（Permissions）

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

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

许可给主体访问一个对象进行在对象上或与对象的特定操作。SQL Server拥有让你思想麻木的大量许可，你可以授予它们到主体，甚至你可以禁止或撤销这些许可。这听起来有点复杂，但看完这篇文章后，你会理解SQL Server许可如何工作，你如何能在数据库和服务器对象上进行对象创建、数据访问和其它类型的操作发挥颗粒度的控制。

许可

许可就像你可以访问外国的签证，通常会有一些基本条件，例如你只能呆6个月，限制你只能在国家的七分之三区域旅游。同样，SQL Sever许可给主体访问到数据库对象，在对象上或与对象做某些事情，或进行一个操作。许可会让主体能访问表的数据或其中一部分数据，或能运行特定代码。甚至可以能授予其它的登录的许可。按理你可以授予几百种许可到不同的主体。

当授予许可时，你要遵循最小特权原则。最小特权意味着你给主体它需要来完成一个任务的精确许可——不多也不少。通过坚持这个最小特权原则，在安全管理数据库里，你已经迈出了重大一步。如果在数据库里，主体能做的唯一事情是读取一些产品信息，主体就不能——有意或无意的——删除表的内容。基本上，你要在主体能做的周围建立一个紧密的容器。

许可类型

SQL Server有多个许可类型，你可以授予到主体来控制对安全对象的访问。这里有个在SQL Server里部分最常见你会遇到和使用的清单：

CONTROL：在安全对象上授予基本的所有可能许可，标记主体为能使安全的虚拟拥有者。这包括能在能使安全的对象上授予许可到其它主体。
CREATE：授予能创建特定对象的许可，取决于授予的对象范围。例如CREATE DATABASE许可允许主体在SQL Server实例里创建新的数据库。
ALTER：授予安全对象的除了所有者的属性许可。这个许可意味着在同个范围里修改，创建，删除对象的其它许可。例如，在数据库上的ALTER许可包括修改表和架构的许可。
DELETE:允许主体删除表里存储的任何或所有数据。这的确是个非常危险的许可。
IMPERSONATE <login> or IMPERSONATE <user>：授予主体模拟另一个登录或用户的许可。模拟经常用来对于给出的对象，给主体另一个主体的许可。这个经常用来存储过程执行时，修改存储过程的执行上下文。
INSERT：允许主体插入新行到表。
SELECT：授予主体从特定表读取数据的许可。这是大多数用户使用的常见许可，因此它们可以在表上执行查询。
TAKE OWNERSHIP：授予主体占有对象所有权的许可。授予这个许可不会立即转移所有权。而是允许主体在随后可以拿到所有权。
UPDATE：允许主体在表里修改数据。
VIEW DEFINITION：允许主体查看安全对象定义的许可。这是个重要的许可，因为在攻击数据库里，结构信息非常有用。没有这个许可，攻击者在数据库或服务器实例里发现有趣目标的能力非常有限。

CREATE和ALTER许可有使用ANY关键字的各种方法：CREATE ANY <object type> 和ALTER ANY<object type>。这些许可允许创建或修改特定类型的任何安全对象。例如，在数据库级别授予ALTER ANY SCHEMA允许主体修改数据库里任何架构属性。在服务器级别，ALTER ANY LOGIN允许主体修改在服务器上的任何登录。使用ANY给你允许主体创建或修改对象的整个类，而不是单个特定对象。要注意CREATE和ALTER许可两者之间的格式有一些细微差别。

当你考虑SQL Server里的安全对象数目，对象可以拥有的潜在许可类型数目时，你已经开始考虑许可的颗粒是什么样的。对于任何用户或角色，你可以应用最小特权原则来实现许可，给用户明确的必须许可来完成工作，却不暴露其它对象的滥用。

提示：

如果你用过SQL Server的早期版本，你会发现SQL Sever 2005和后续版本完全改造了可用许可。你不再需要分配用户到拥有很多不需要许可的角色，这样违背了最小特权原则，暴露你的数据库为故意或无意的滥用。

这里主要考虑的是授予需要的权限不能有效的进行一个操作。有时候也需要其它权限，在能进行敏感操作周围提供全局的安全上下文。这个最常见的例子是CREATE TABLE许可。授予这个许可很容易犯错，理论上允许在特定数据库里创建一个表。但是主体也要能在其它地方创建表，尤其在架构里。如果主体在任何架构上没有ALTER许可，她就不能创建表。

授予许可

在SSMS里最灵活授予许可的方法是修改数据库用户或角色的属性。你也可以通过修改各个对象的属性来授予许可，但这个方法不太灵活，需要花费太多的维护工作。

下面的练习会在AdventureWorks2012里创建一个自定义的数据库角色。这个角色的成员，在Human Resources部门，需要必要的许可在数据库里，在一些人力资源相关的表里来输入和更新数据，执行一些相关的存储过程，但不能访问其它。

提示：

一旦你为用户配置许可，然后为另一个用户做同样的操作，授予和第一个用户同样的许可，很明显，分配许可到到角色，然后添加用户到角色更容易。

你会使用在第2篇里，在AdventureWorks2012数据库里创建的用户Tudou。如果你那个时候没有创建这个用户，在SSMS里执行下列代码创建登录和用户。

USE master;
GO CREATE LOGIN Tudou WITH PASSWORD = 'yBqyZIPT8}b]b[{5al0v';
GO USE AdventureWorks2012;
GO CREATE USER TudouZ FOR LOGIN Tudou
WITH DEFAULT_SCHEMA = HumanResources;
GO

代码4.1:创建一个登录并映射到数据库用户

在AdventureWorks2012数据库里，你需要一个DataEntry的用户自定义数据库角色。按这些步骤来创建它。

在SSMS里，连接到数据库服务器实例，切换到安装的AdventureWorks2012数据库。在对象浏览器里，展开【数据库】，展开【AdventureWorks2012】节点，然后展开【安全性】【角色】。
右击【数据库角色】，从弹出的菜单选择【新建数据库角色】打开【数据库角色】对话框。
在【角色名称】里输入DataEntry，所有者输入dbo。
点击【添加】按钮，添加用户TudouZ到角色（如果不存在请在AdventureWorks2012数据库里创建这个用户）。在你关掉选择数据库用户或角色对话框后，【数据库角色-新建】如插图4.1所示。

插图4.1:创建DataEntry数据库角色，添加用户TudouZ
点击【确定】保存你的修改，创建DataEntry角色。

在这个时候DataEntry角色不允许TudouZ和其它你添加的角色做任何事情，因为你还没设置它的任何许可。DataEntry角色成员需要能在AdventureWorks2012数据库里的Employee, Address,和JobCandidate表，添加和修改数据。它们也需要能执行uspUpdateEmployeeHireInfo和uspUpdateEmployeePersonaInfo存储过程。但这些成员不能查看存储过程的定义。

使用下列步骤来添加和拒绝合适的许可到DataEntry角色。

在SSMS的对象浏览器里，展开 AdventureWorks2012数据库的【安全性】【角色】【数据库角色】节点。
右击【DataEntry】节点，从弹出菜单里选择【属性】。这会打开【数据库角色属性】对话框。
在对话框的左边列表里选择【安全对象】页。这页会让你选择这个角色已经授予的许可的安全对象，指定你想要授予角色的具体许可。
点击【搜索】来添加安全对象。这会打开【添加对象】对话框，提供选择特定对象的选项，特定对象，特定类型的所有对象和属于该架构的所有的所有对象。在这个练习里，因为你想为表和存储过程同时添加许可，保持默认【特定对象】选择如插图4.2所示，点击确定。

插图4.2：选择要授予许可的对象类型
【选择对象】对话框打开了。点击【对象类型】按钮来打开【选择对象类型】对话框，从列表里选择【存储过程】和【表】，如插图4.3所示。点击【确定】关闭对话框，回到【选择对象】对话框，如插图4.4所示。在对象类型里你可以看到存储过程和表。

插图4.3：需要授予许可的对象类型

插图4.4：选择存储过程和表后的【选择对象】对话框
点击【浏览】按钮来查看数据库里的存储过程和表列表。这会打开【查找对象】对话框，划下找到并选择这些对象：点击【确定】关闭【查找对象】对话框。注意你选择的对象现在列在【选择对象】对话框里，在分号隔开的列表里，如插图4.6所示。点击【确定】关闭这个对话框来保存你的修改。
- HumanResources.Employee 表
- HumanResources.JobCandidate 表
- HumanResources.uspUpdateEmployeeHireInfo 存储过程
- HumanResources.uspUpdateEmployeePersonalInfo 存储过程
- Person.Address 表
  当你完成表和存储过程选择时，对话框应该如插图4.5所示。（为了显示所有选择项目，我把对话框的高度调高了）。
  
  插图4.5：选择分配权限的存储过程和表
插图4.6：用来分配许可的选择对象结果
选择对于DataEntry数据库橘色属性对话框列出了你选择的安全对象和每个可用许可。DataEntry角色成员需要能这些表里插入和修改数据，因此一次选择一个表，在对话框的底部，为这些在许可选择授予列点击复选框。对于HumanResources.Employee表，选择的情况如插图4.7所示。

插图4.7：在表上选择插入和更新许可
提示：
不要和【具有授予权限】弄混。【授予】允许特定许可，【具有授予权限】允许用户或角色授予许可到其它主体。小心错发了【具有授予权限】许可。
对于每个存储过程，授予【执行】许可和拒绝【查看定义】许可。插图4.8显示了对于useUpdateEmployeeHireInfo存储过程的选择情况。

插图4.8：对于useUpdateEmployeeHireInfo存储过程授予【执行】，拒绝【查看定义】许可。
在【数据库角色属性】对话框里点击【确定】保存你的修改，提交它们到数据库。取决于对象个数和你选择的许可，这会花上一会。

当然，你同样可以使用T-SQL代码来创建对象和分配许可。代码4.2创建了DataEntry角色，分配用户TudouZ给它，授予和拒绝了你使用对话框一样的许可。

-- Create the DataEntry role and assign Topaz to it
CREATE ROLE [DataEntry] AUTHORIZATION [dbo];
ALTER ROLE [DataEntry] ADD MEMBER [TudouZ]; -- Assign permissions to the DataEntry role
GRANT INSERT ON [HumanResources].[Employee] TO [DataEntry];
GRANT UPDATE ON [HumanResources].[Employee] TO [DataEntry];
GRANT INSERT ON [HumanResources].[JobCandidate] TO [DataEntry];
GRANT UPDATE ON [HumanResources].[JobCandidate] TO [DataEntry];
GRANT INSERT ON [Person].[Address] TO [DataEntry];
GRANT UPDATE ON [Person].[Address] TO [DataEntry];
GRANT EXECUTE ON [HumanResources].[uspUpdateEmployeeHireInfo] TO [DataEntry];
DENY VIEW DEFINITION ON [HumanResources].[uspUpdateEmployeeHireInfo] TO [DataEntry];
GRANT EXECUTE ON [HumanResources].[uspUpdateEmployeePersonalInfo] TO [DataEntry]
DENY VIEW DEFINITION ON [HumanResources].[uspUpdateEmployeePersonalInfo] TO [DataEntry];
GO

代码4.2：分配新的角色和分配许可的代码

检查和测试许可

如果你想检查下DataEntry角色拥有的许可，你可以使用SSMS里的图形工具或执行访问数据库对象元数据的T-SQL代码。使用SSMS，在对象浏览器里展开AdventureWorks2012数据库，展开【安全性】【角色】【数据库角色】。找到DataEntry角色（没看到的话刷新下），右击角色，从弹出菜单里选择【属性】。这个打开和用来创建角色一样的【数据库角色属性】对话框，你可以使用【安全对象】页来查看角色的许可。

或者你可以使用代码4.3的代码来查看DataEntry角色的许可，使用sys.database_permissions，sys.database_principals安全类别视图和sys.objects类别视图。

SELECT DB_NAME() AS 'Database', p.name, p.type_desc, dbp.state_desc,
dbp.permission_name, so.name, so.type_desc
FROM sys.database_permissions dbp
LEFT JOIN sys.objects so ON dbp.major_id = so.object_id
LEFT JOIN sys.database_principals p ON dbp.grantee_principal_id = p.principal_id
WHERE p.name = 'DataEntry'
ORDER BY so.name, dbp.permission_name;

代码4.3：对DataEntry角色查看许可元数据的代码

当你运行这个代码，你会看到如插图4.9所示的结果。

插图4.9：对于DataEntry角色的许可元数据

你可以打开新的SSMS实例里的图形界面测试这些许可，以TudouZ登录（如果你使用这篇或第2篇的代码，密码是yBqyZIPT8}b]b[{5al0v)）。然后尝试在HumanResources.Employee, HumanResources.JobCandidate, 或Person.Address表里插入新的记录或更新现有的记录，运行下你分配执行许可的2个存储过程之一。这些操作应该会成功。然后在别的表尝试插入或更新数据，这些操作会失败。你应该只能进行你授权的操作。

当然你也可以在T-SQL代码里进行同样的测试，使用如清单4.4的代码。这个代码首先设置运行的上下文为TudouZ，它是DataEntry角色的成员。然后它在Person.Address表里插入新行。这个操作会成功，因为DataEntry有INSERT许可。然后代码尝试在HumanResources.Department表里插入新行，这个操作会失败，因为DataEntry在那个表上没有INSERT许可。接下来这个代码成功执行了HumanResources.uspUpdateEmployeePersonalInfo存储过程，因为在那个存储过程上DataEntry有执行许可。执行dbo.uspGetManagerEmployees失败，因为DataEntry没有运行那个代码的许可。最后代码返回执行的上下文为你登录SSMS数据库的样子。

EXECUTE AS USER = 'TudouZ'; -- Succeeds
INSERT INTO Person.Address
(AddressLine1, City, StateProvinceID, PostalCode)
VALUES
('8 Hazelnut', 'Irvine', 9, '');
GO -- Fails
INSERT INTO HumanResources.Department
(Name, GroupName)
VALUES
('Advertising', 'Sales and Marketing');
GO -- Succeeds (doesn't actually change any data)
DECLARE @RC INT;
EXECUTE @RC = HumanResources.uspUpdateEmployeePersonalInfo
1, '', '1963-03-02', 'S', 'M';
GO -- Fails
EXECUTE dbo.uspGetManagerEmployees 1;
GO REVERT;

代码4.4：测试DataEntry角色许可的代码