站长之家- 语言 2009-04-13T10:28:40 +08:00

从数据行入手保护SQL Server数据安全

 

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

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

以下为引用的内容:

--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
INSERT  INTO [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

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

 

行级别安全代码范例

首先我们作出如下假设:

•使用SQL Server数据库(微软SQL Server 2000、2005或2008)

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

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

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

 

 

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

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

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

以下为引用的内容:

CREATE FUNCTION [dbo].[GetOrderSummary]
(
@User_Id VARCHAR(20)
)
RETURNS TABLE
AS
RETURN
(
SELECT  count(*)   as OrderCount,
   sum(OrderTotal) as Receipts
  FROM  dbo.Orders  o
  Join  dbo.UserAccess ua
  On  o.Department = ua.Department
  WHERE ua.UserID = @User_Id
)

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

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

 

 

图2 表值型函数执行示例

 

 

表1 UserAccess示例表内容

 

 

表2 Orders示例表内容

 

 

表3 Department表内容

 

显示受保护表内容

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

以下为引用的内容:

CREATE VIEW  [dbo].[OrderSummary]
AS
SELECT  OrderCount,
Receipts
FROM          dbo.GetOrderSummary(USER)

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

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

实际应用思考

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

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

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

推荐关键词

24小时热搜

查看更多内容

大家正在看