SQL Server安全（10/11）：行级别安全（Row-Level Security）

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

---

不像其它一些工业强度的数据库服务器。SQL Server对于单个数据记录，缺少内建机制，称作行级别安全（Row-Level Security）。这篇文章会探寻为什么你可能想使用这样的行级别颗粒的数据访问安全和你如何能实施行级别安全。

行级别安全

在SQL Server 2000和SQL Server 2005发布之间，微软认真对待安全。比尔盖茨早在2002年给公司写了它的“可信赖计算机”备忘录，因此SQL Server 2005收到大量的安全修正。一个新的安全战略是比早起版本做出更多颗粒度的授权架构，让你可以颗粒度的完全控制谁可以用哪个对象。

对增加安全粒度的一个明显遗漏是它没有扩展到行级别。在表级别你有很多控制访问选项，当SQL Server在表里没有内建访问控制机制，通常称为“行级别安全”。在很多场景里这是个问题，尤其是有管控要求保护特定数据的时候。例如，在医疗数据库里的病人表有所有就诊病人的数据，但只有医疗管理员有所有病人的访问，医生和护士应该只有它们直接照顾病人的访问。有很多其它场景需要允许访问表的子集数据，或甚至到单元格级别。当SQL Server没有实现行级别安全的内建方式。

提示：

表包含行和列，因此微软使用单元格（cell）术语来指定位于表里一行的一列里的数据。它是必须要的区分，因为有些场景，你需要安全在表里的各个单元格（单个数据项）。在接下来的文章你会学到更多。

行级别安全在数据库里提供单条记录上的细粒度访问控制。理想结果是通过查询的执行上下文筛选的记录，因此用户只需访问有许可的记录。这个筛选使用SQL Server里同样的安全功能，控制到其它数据库、服务器对象和在这些对象上操作的访问。

由于SQL Server缺少内建的行级别安全，开发人员和管理员需要设计出各种聪明和创造性的技术来解决这个缺点。尽管这些技术常需要不少额外工作来配置，为了实施颗粒度的控制，不是在表里所有记录的控制，只授予表里子集记录的访问，这样做是值得的。这个技术可以相对简单，也可以和你需要的一样复杂，使用安全标签和访问控制的层级目录。安全标签描述数据项目的敏感性，在这篇文章里你会学习到这个技术。

这里我们假设行级别安全是在服务器级别的最佳实施。你可以在应用程序级别控制数据库对象的访问，但你必须在使用数据库的每个应用程序里实现安全层。在数据库里的行级别安全意味着安全对应用程序是透明的，应用程序开发者不需要，甚至留意访问控制所在的地方。

微软最终开始宣布在SQL Server里行级别安全的缺少，受云主机数据库需要驱动。它们首次给SQL Azure数据库增加了行级别安全，也会是SQL Server 2016的一部分。

提示：

通过各个用户或组的，几乎都需要行级别安全技术控制访问，这就是说应用程序需要为每个用户使用特定的标识连接到受保护的数据库，这个标识通常是Windows或SQL Server登录。这就是说，通常你不能应用程序实现行级别安全，应用程序使用中间层连接池，它必须使用单个标识来访问数据库。但这个通常不是问题，因为通过行级别安全的数据库保护通常有其它需要终端用户的需求，例如审计。

用视图实现行级别安全

稍后你将学到，SQL Server 2016实现了内建的安全。但是直到它的发布，你迁移数据库到它里面，我们需要自己实现保护。我们来看一个使用常见技术实现行级别安全的简单实现。尽管它很简单，它可以作为更为复杂方法实现的基础。假设公司有包含敏感信息的客户记录，例如信用额度和重罪定罪（felony convictions）。（是的，我知道，这有点做作，但我们开始吧）。只有系统管理员（或董事会）和分配到客户的公司代表才可以看到客户数据，所有这些都是存储在单个Customer表。

代码10.1展示了创建样本数据库和单个Customer表的代码，并插入一些数据。一切都相当简单，除了在表了加了UserAccess字段。这是数据库代码用来限制访问的字段。记住这只是个简单的例子，有很多其它方法来实现这个。

IF DB_ID('RowLevelSecurityDB') IS NOT NULL DROP DATABASE RowLevelSecurityDB;
CREATE DATABASE RowLevelSecurityDB;
GO
USE RowLevelSecurityDB;
GO

-- Create the sample table that we want to protect with row-level security
CREATE TABLE Customer (
    CustId INT, 
    Name NVARCHAR(30), 
    City NVARCHAR(20), 
    CreditLimit MONEY,
    SocialSecurityNumber NCHAR(11), 
    FelonyConvictions INT,
    UserAccess NVARCHAR(50)
);
GO

-- Add some data to the Customer table
INSERT INTO dbo.Customer
       (CustId, Name, City, CreditLimit, SocialSecurityNumber, FelonyConvictions, UserAccess)
VALUES (1, N'Don Kiely', N'Fairbanks', 5.00, N'123-45-6789', 17, N'UserOne'),
       (2, N'Kalen Delaney', N'Seattle', 500000.00, N'987-65-4321', 0, N'UserOne'),
       (3, N'Tony Davis', N'Cambridge', 5000.00, N'', 0, N'UserTwo'),
       (4, N'E.T. Barnette', N'Fairbanks', 0.00, N'555-66-7777', 47, N'UserOne'),
       (5, N'Soapy Smith', N'Sitka', 0.00, N'222-33-4444', 32, N'UserTwo'); 

代码10.1：创建样本数据库和表，并插入样本数据

提示：

自定义行级别安全架构通常需要数据库架构的一些层级改变，额外的UserAccess字段就其中方法之一。另一个处理更复杂情景的常见做法是创建多对多的表来连接巷道用户或角色。SQL Server有这个工具，按你的需要的想法来解决这个问题。

所有到表的数据访问会通过视图；代码10.2创建MyCustomersView视图，基于用户执行上下文筛选数据。CREATE VIEW代码有趣的部分是WHERE子句。它使用USER_NAME函数获得执行上下文的用户名。然后SELECT语句返回在UserAccess字段里用户名匹配的所有行。或者，如果用户是db_owner数据库成员或服务器sysadmin角色成员，视图会返回表里的所有行。

IF object_id(N'dbo.MyCustomers', 'V') IS NOT NULL
    DROP VIEW dbo.MyCustomers;
GO

CREATE VIEW dbo.MyCustomersView AS
    SELECT CustId, Name, City, CreditLimit, SocialSecurityNumber, FelonyConvictions, UserAccess FROM dbo.Customer 
    WHERE UserAccess = USER_NAME() OR
        IS_ROLEMEMBER('db_owner') = 1 OR
        IS_SRVROLEMEMBER('sysadmin') = 1;
GO

代码10.2：创建MyCustomersView视图，基于执行上下文在表里筛选数据

这是配置保存数据和提供对它访问所有需要的代码。代码10.3创建2个数据库用户。在这个例子里用户不需要映射到服务器层级登录，但在大多数情况下需要这样做。然后代码拒绝所有到这个Customer表的用户访问，在视图上授予select许可。这个拒绝了直接从表的直接访问，但允许通过视图的访问。如果你想用户通过视图修改数据，你也可以授予这些许可。

CREATE USER UserOne WITHOUT LOGIN;
CREATE USER UserTwo WITHOUT LOGIN;
GO

-- Set permissions
DENY SELECT, INSERT, UPDATE, DELETE ON dbo.Customer TO UserOne, UserTwo;
GRANT SELECT ON dbo.MyCustomersView TO UserOne, UserTwo;
GO

代码10.3：创建2个数据库用户，限制它们到Customer表的访问，在视图上授予select许可。

现在你可以测试行级别安全架构，使用代码10.4。第一个SELECT语句作为sysadmin测试视图。你会看到如插图10.1的结果集，这个语句返回表里的所有行。

-- Test as admin
SELECT * FROM dbo.MyCustomersView;    -- Should succeed and return all rows

-- Test as regular users
EXECUTE AS USER = 'UserOne';
SELECT * FROM dbo.Customer;            -- Should fail
SELECT * FROM dbo.MyCustomersView;    -- Should succeed and return 3 rows
REVERT;
GO

EXECUTE AS USER = 'UserTwo';
SELECT * FROM dbo.Customer;            -- Should fail
SELECT * FROM dbo.MyCustomersView;    -- Should succeed and return 2 rows
REVERT;
GO

代码10.4：测试简单的行级别安全，第一次以sysadmin，然后以数据库的每个用户。

 

插图10.1：以sysadmin使用视图，选择数据的结果：所有行返回。

代码10.4里的第2段测试在UserOne的执行上下文的里的行级别安全视图。在这个例子里，有两个SELECT语句。第一个直接从表读取数据，测试DENY许可。第二个测试视图使用。如预料的一样，第一个语句失败（插图10.2），第二个语句返回3条数据（插图10.3），UserAccess等于UserOne的行。我们有行级别安全！

插图10.2：当以UserOne测试表和视图时，失败和成功信息。

插图10.3：当以UserOne执行时，从MyCustomersView视图返回的行

在代码10.4里最后一段代码再次直接从表获取任何数据失败，但这次视图返回UserTwo访问Customer表的2条数据，如插图10.4所示。

插图10.4：以UserTwo执行时，从MyCustomersView视图返回行。

对于实施行级别安全，你有非常简单的架构。它是简单的，但对于理解基本概念很有用。但这样做的方法有一些问题：

• 如果你需要保护多个表，你会用不同程度的复杂逻辑来实现视图来允许不同的用户和角色访问保护的数据。当架构修改时，表对应的视图也要随着多次改变。
• 这个例子是基于用户名称的，并假定每一行只有一个访问用户。很可能你也需要通过角色筛选的方法，当没有唯一用户名时，你也要处理。
• 当用户名修改时，你要在一个或多个表里修改数据。

还有其它一个问题，取决于你的特殊情景。

但你可以容易创建这个情景，增加你任何需要或想要的复杂层级。想法的伟大源泉是来自微软的行级别安全白皮书，在下一部分会谈到，它介绍了复杂行级别安全的实现方法，刚才的例子是简单的。

来自微软的最佳行级别安全实践

一个行级别安全更复杂，现实世界在用的已经超出了这系列文章的范围。但微软已经生成这个样本作为白皮书的一部分，在分类数据库里实施行和单元级别安全。

提示：

上述链接的行级别安全白皮书是原先是为SQL Server 2005写的，但微软为它保留了多年。最后一次更新在2012年1月，即使现在的SQL Server 2014也是同样适用。

在这个白皮书里介绍的方法，是为公共部分的安全需要和有最高级别的安全需要的分类数据库所设计。它适用安全标签和视图来提供颗粒访问控制，和超过行级别对各个单元格的访问保护，如果施行完整架构。白皮书介绍的安全标签如下：

安全标签是一条信息，它描述了数据条目（一个对象）的敏感度。它是包含从一个或多个目录标记的字符。用户（提交者）有同样标记的许可描述。每个提交者的许可可以通过它们自己的标签。提交这的标签和对象的标签进行比较决定是否可以访问这个对象。

使用的例子保护层级的架构，使用类似米国政府喜欢的SECRET，TOP SECRET和UNCLASSIFIED分类。架构允许多个访问标准，例如这些安全分类受与不同项目或部门联合的进一步限制。例如，有最高机密类别的在NSA里的人不能访问FBI的最高机密文件。

实施这种，对于行和单元级别的安全的跨领域，分等级的安全架构需要更多代码，包括创建数据库对象，用来数据保存和提供数据访问——主要是表和视图——还有维护系统的代码。白皮书里包含了很多代码！另外在白皮书里的代码，微软创建了SQL Server标签安全工具箱，放在CodePlex上，包括上面的引用白皮书。工具箱包括标签策略设计器应用程序，文档和使用

描述在不同方法里实现的例子。SQL Server标签策略设计器的例子如插图10.5所示。这个应用程序提供让你定义你分类的漂亮界面。然后工具会生成SQL代码来创建所有需要的对象，在你选择的数据库里直接运行代码，或者保存为脚本文件，用于后期修改或执行。

插图10.5：SQL Server标签策略设计器用户界面，用白皮书里样本架构类别。

即使你不需要白皮书里介绍的这类灵活和非常颗粒度的安全架构，你可以通过阅读白皮书学到更多，探寻工具箱的内容。在你自己数据库和应用程序里用于实现行级别安全的选项仅限于在SQL Server里你用各个工具的想法。

SQL Server 2016里的行级别安全

对于创造性，自定义行级别安全架构的需求已经过时，因为有SQL Server 2016了。微软宣布这个版本包含内建的行级别安全，这是像其它数据库引擎Oracle已经拥有多时，期待已久的功能。

这个功能目前还在开发（现在已经发布），因此这篇文章不会太深入讨论。但这个方法肯定有效，你定义安全谓语筛选作为内联，表值函数，当用户或应用访问受保护的数据时，它成为安全策略的一部分触发。代码10.5展示了一些在SQL Server 2016的CTP版本里使用的行级别安全代码，摘自在线帮助里行级别安全话题预告。当表里的SalesRep列和执行查询的用户，或是管理员用户，这个安全策略包含单个返回1的筛选谓语。这个和刚才使用UserAccess列和视图来筛选数据的代码类似。

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result 
        WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO

CREATE SECURITY POLICY SalesFilter
    ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep) ON dbo.Sales
    WITH (STATE = ON);
GO

代码10.5：创建安全策略来实施内建的行级别安全的SQL Server 2016代码

当为行级别安全实现商务逻辑，不需要在像在分类数据库白皮书里介绍方法对象的昂贵支持，以内联表值函数，创建安全谓语筛选的能力提供了巨大的灵活性。对应用访问数据透明是件好事：应用程序不需要知道数据库里的任何行级别安全。

微软在SQL Server 2016里的行级别安全的设计目标是性能和自定义实现。这个拭目以待，但我确认会有一些性能低效的自定义代码降低它们！发布后，SQL Server 2016的新功能会大大减少行级别安全需要的工作。

小结

在SQL Server 2014和先前版本里缺少对行级别安全的支持是在内建在SQL Server 2005和后续版本里的其它颗粒度安全架构的一个明显遗漏。但聪明的开发人员几年来发明很多聪明架构来让你下至表里的行和单元格级别，保持数据安全，有不同级别的复杂度和灵活度。然而，还是值得努力去创建自定义架构，基于其它发布的架构或你自己创建的，这样的话，你可以更精细的保护你的数据。SQL Server 2016通过数据库引擎内建对行级别安全的支持让这些变得简单。

原文链接

http://www.sqlservercentral.com/articles/Stairway+Series/128866/