从数据行入手保护SQL Server数据安全
2009-04-01 10:25:34 来源:WEB开发网在当今企业环境中,保证数据安全不是可有可无的工作。频繁曝光的入侵和欺骗事件、萨班斯◆奥克斯利法案、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
更多精彩
赞助商链接