从数据行入手保护SQL数据库数据安全_SQL SERVER数据库_黑客防线网安服务器维护基地--Powered by WWW.RONGSEN.COM.CN

从数据行入手保护SQL数据库数据安全[组图]

作者:黑客防线网安SQL维护基地 来源:黑客防线网安SQL维护基地 浏览次数:0

本篇关键词:安全组图入手保护
黑客防线网安网讯:  在当今企业环境中,保证数据安全不是可有可无的工作。频繁曝光的入侵和欺骗事件、萨班斯◆奥克斯利法案、HIPAA法案规定和爱国者法案等都要求我们能够做到,将正确数据提供给正确的用户,...

  在当今企业环境中保证数据安全不是可有可无的工作频繁曝光的入侵和欺骗事件、萨班斯◆奥克斯利法案、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

    黑客防线网安服务器维护方案本篇连接:http://www.rongsen.com.cn/show-9946-1.html
网站维护教程更新时间:2012-03-21 02:27:35  【打印此页】  【关闭
我要申请本站N点 | 黑客防线官网 |  
专业服务器维护及网站维护手工安全搭建环境,网站安全加固服务。黑客防线网安服务器维护基地招商进行中!QQ:29769479

footer  footer  footer  footer