从数据行入手保护SQL Server数据安全-sql server 多行数据合并一行

在当今企业环境中，保证数据安全不是可有可无的工作。频繁曝光的入侵和欺骗事件、萨班斯◆奥克斯利法案、HIPAA法案规定和爱国者法案等都要求我们能够做到，将正确数据提供给正确的用户，防止其它无权限的人访问。一般来说，“行级安全(row-level security)”的要求是：对数据库中的数据以行为单位，设定只有特定用户才可以访问。可惜的是，SQL Server数据库并不提供内置的行级别安全机制。

在本篇文章中，通过一个示例代码（代码清单1），来告诉大家一个在SQL Server中实现行级别安全的方法，以行为单位限定用户的访问权限，同时无需修改业务表的内容，不影响应用程序或表现层开发者，而且与用户访问数据的方式无关。该示例应用的模拟需求为：如何增加安全性到现有订单数据库中，限制经理只能访问他们管理的部门或其子部门的数据，而不管用户如何获得该表，以及针对这个数据库开发什么样的报表和查询。

代码清单1：提供了创建和加载示例表的脚本

--create table script
CREATE TABLE dbo.UserAccess
(
UserID varchar(20) NOT NULL,
Department varchar(50) NOT NULL
)
CREATE TABLE [dbo].[Orders](
[OrderID] [int] NOT NULL,
[CustomerName] [varchar](20) NOT NULL,
[OrderTotal] [money] NOT NULL,
[Department] [varchar](50) NOT NULL
) 
CREATE TABLE dbo.Departments
(
Department varchar(50) NOT NULL,
ParentDepartment varchar(50)
)
--end create table script
--script to clear then populate example tables
--clear tables
Delete from departments
Delete from orders
Delete from useraccess
--insert departments table
INSERTINTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('North America','')
INSERT INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('East','North America')
INSERT INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('Southeast','East')
INSERT INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('Northeast','East')
INSERT INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('West','North America')
INSERT INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('Southwest','West')
INSERT INTO [dbo].[Departments] ([Department],[ParentDepartment]) VALUES ('Northwest','West')
--insert orders table
INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (1,'Harris','11.00','East')
INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (2,'Corrigan','22.00','Southeast')
INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (2,'Corrigan','22.00','Southeast')
INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (3,'Baldwin','33.00','Southeast')
INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (4,'Pillow','44.00','Northeast')
INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (5,'Carpenter','55.00','Northeast')
INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (6,'Meyer','66.00','West')
INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (7,'Gonzalez','77.00','Southwest')
INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (8,'Hall','88.00','Northwest')
INSERT INTO [dbo].[Orders] ([OrderID],[CustomerName],[OrderTotal],[Department]) VALUES (9,'Patrick','99.00','Southwest')
--insert user access table
INSERT INTO [dbo].[UserAccess] ([UserID],[Department]) VALUES ('BLambert','Southwest')
INSERT INTO [dbo].[UserAccess] ([UserID],[Department]) VALUES ('MDavis','East')
INSERT INTO [dbo].[UserAccess] ([UserID],[Department]) VALUES ('MDavis','Southeast')
INSERT INTO [dbo].[UserAccess] ([UserID],[Department]) VALUES ('MDavis','Northeast')
INSERT INTO [dbo].[UserAccess] ([UserID],[Department]) VALUES ('WSimmons','Northeast')
--end script to clear then populate example tables

行级别安全代码范例

首先我们作出如下假设：

◆使用SQL Server数据库（微软SQL Server 2000、2005或2008）

◆所有表中都有一个共同的字段，使用它来决定谁可以看什么数据(本例中就是department字段)

◆通过不同的用户id来加强应用程序调用数据库的安全性，而不是通过一个单一的admin用户id。

举例来说，图1所示数据结构包含：Orders表，包含一个客户名称、订单收入和销售部门；一个部门查阅表（Departments），包含父部门字段；一个用户访问表（UserAccess），在用户和他有权限查看的部门之间建立联系。用户访问表的每一行数据代表一个正确的用户/部门组合。如果一个用户有权限访问的部门下设许多子部门，那么这个父部门和每一个子部门在表中各占据一行数据。

图1 该数据库模型图显示了本例中所用到的表

让订单数据保持安全的关键是，通过SQL Server的表值型函数来保护它，该函数要求使用当前用户的id作为参数。表值型函数与其它函数一样可以接受参数，但是返回结果为一个表，而并非一个变量。在这个函数内，通过其安全属性联合要保护的表与用户访问表，将表的结果限定在与指定用户id相关的范围之内。

示例1定义了一个表值型函数，根据UserAccess表中的限制用户可以查看的内容，返回订单数量和订单的总收入。

示例1：创建保护订单数据的表值型函数的SQL语句

CREATE FUNCTION [dbo].[GetOrderSummary] 
( 
@User_Id VARCHAR(20)
)
RETURNS TABLE 
AS
RETURN 
(
SELECTcount(*) as OrderCount, 
 sum(OrderTotal) as Receipts
FROMdbo.Orderso
Joindbo.UserAccess ua
Ono.Department = ua.Department
WHERE ua.UserID = @User_Id
)

而图2显示了该函数的执行示例，以及相关表的数据内容，如表1、表2和表3.

图2 表值型函数执行示例

表1 UserAccess示例表内容

表2 Orders示例表内容

表3 Department表内容

显示受保护表内容

通过表值型函数保护了某个表之后，你然后可以通过类似示例2的视图将其展示给用户。该视图调用了上面定义的表值型函数，并使用参数“user”，在SQL Server中这是一个内置函数，返回当前活跃用户的ID。

示例2：创建一个视图让安全用户访问受保护表的SQL

CREATE VIEW[dbo].[OrderSummary]
AS
SELECTOrderCount,
Receipts
FROMdbo.GetOrderSummary(USER)

这个视图是用户被授予访问权限的唯一对象，即使用户不具有权限使用前面的表值型函数或访问受保护表，他也可以获得被许可查看的数据。

实际应用思考

在应用程序开发实际情况中，这种方式往往需要一个以上的属性来定义用户行级别权限，而且往往需要对多个表应用安全机制。

另外，不同的数据可能具有不同的安全考虑。举例来说，一个用户可能有权限访问某个区域的销售结果，但不能访问汇总薪酬数据。在复杂的实际环境中成功运用这个战略的关键是，收集并确认来自业务的需求，然后根据这些需求来选择合适的属性来实施行级别安全。

使用活动目录的企业，可以按照活动目录组来使用这种方式获得安全性，而并一定仅限于使用用户ID，实现方法非常简单，只需要在UserAccess表中使用活动目录组替代用户ID即可。

相关阅读

萨班斯-奥克斯利法案（Sarbanes-Oxley Act）是美国立法机构根据安然有限公司、世界通讯公司等财务欺诈事件破产暴露出来的公司和证券监管问题所立的监管法规，简称《SOX法案》或《索克思法案》。

法案全称《2002年公众公司会计改革和投资者保护法案》由参议院银行委员会主席萨班斯（Paul Sarbanes）和众议院金融服务委员会（Committee on Financial Services）主席奥克斯利（Mike Oxley）联合提出，又被称作《2002年萨班斯-奥克斯利法案》。该法案对美国《1933年证券法》、《1934年证券交易法》做出大幅修订，在公司治理、会计职业监管、证券市场监管等方面作出了许多新的规定。

【编辑推荐】