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

夜鹰教程网-程序员的加油站

·设为首页·加入收藏

本站首页技术文章全套前端云课堂智慧校园

你好，欢迎来到夜鹰网！【去购买教程】

当前位置：文章中心 >> sql2016_sql2012_sql2015_sql2008

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

夜鹰教程网来源：www.yyjcw.com 日期：2016-11-19 15:25:31

在当今企业环境中，保证数据安全不是可有可无的工作。频繁曝光的入侵和欺骗事件、萨班斯?奥克斯利法案、HIPAA法案规定和爱国者法案等都要求我们能够做到，将正确数据提供给正确的用户，防止其它无权限的人访问。一般来说，“行级安全(row-level security)”的要求是：对数据库中的数据以行为单位，设定只有特定用户才可以访问。可惜的是，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)，在用户和他有权限查看的部门之间建立联系。用户访问表的每一行数据代表一个正确的用户/部门组合。如果一个用户有权限访问的部门下设许多子部门，那么这个父部门和每一个子部门在表中各占据一行数据。

让订单数据保持安全的关键是，通过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的视图将其展示给用户。该视图调用了上面定义的表值型函数，并使用参数“user”，在SQL Server中这是一个内置函数，返回当前活跃用户的ID。

以下为引用的内容：

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

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

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

实际应用思考

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

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

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

复制链接网友评论收藏本文关闭此页

上一条： Access完成累计余额下一条： SQL Server数据库中索引使用和优化（2…

夜鹰教程网成立于2008年，目前已经运营了将近 13 年，发布了大量关于 html5/css3/C#/asp.net/java/python/nodejs/mongodb/sql server/android/javascript/mysql/mvc/easyui/vue/echarts原创教程。我们一直都在坚持的是：认证负责、一丝不苟、以工匠的精神来打磨每一套教程，让读者感受到作者的用心。我们默默投入的时间，确保每一套教程都是一件作品，而不是呆板的文字和视频！目前我们推出在线辅导班试运营，模式为一对一辅导，教学工具为QQ。我们的辅导学科包括 java 、android原生开发、webapp开发、商城开发、C#和asp.net开发，winform和物联网开发、web前端开发，但不仅限于此。普通班针对的是国内学员，例如想打好基础的大学生、想转行的有志青年、想深入学习的程序员、想开发软件的初学者或者业余爱好者等。就业办针对即将毕业上岗的大四学生，或者打算转行的初级开发工程师。留学生班针对的是在欧美、加拿大、澳洲、日本、韩国、新加坡等地留学的中国学子，目的是让大家熟练地掌握编程技能，按时完成老师布置的作业，并能顺利地通过考试。详细咨询QQ：1416759661 夜鹰教程网