浅析SQL Server 2008中的代码安全之一:存储过程加密

转载 2012年03月22日 12:14:49

最近对SQL Server 2008的安全入门略作小结,以作备忘。本文主要是针对存储过程加密与安全来作分析。

http://database.51cto.com/art/201102/246498.htm

<一>存储过程加密

其实,用了这十多年的SQL server,我已经成了存储过程的忠实拥趸。在直接使用SQL语句还是存储过程来处理业务逻辑时,我基本会毫不犹豫地选择后者。

理由如下:

1、使用存储过程,至少在防非法注入(inject)方面提供更好的保护。因为,存储过程在执行前,首先会执行预编译,(如果由于非法参数的原因)编译出错则不会执行,这要某种程度上提供一层天然的屏障。

我至今还记得大约八、九年前采用的一个权限控制系统就是通过拼凑一个SQL语句,最终得到了一个形如“ where 1=1 and dataID in (1,2) and ModelID in (2,455) And ShopID in (111) and departID in ( 1,3) and ([Name] like %myword%) ”的where条件子句来获取符合条件的结果集。

注意:这个参数是通过地址栏web应用的地址栏或Winform的UI界面来输入的,所以对恶意注入需要花费一定的成本来维护。因为一些常用的关键字(或敏感词)很难区分是恶意或非恶意。

2、使用存储过程而不是直接访问基表,可以提供更好的安全性。你可以在行级或列级控制数据如何被修改。相对于表的访问,你可以确认有执行权限许可的用户执行相应的存储过程。这也是访问数据服务器的惟一调用途径。因此,任何偷窥者将无法看到你的SELECT语句。换句话说,每个应用只能拥有相应的存储过程来访问基表,而不是“SLEECT *”。

3、存储过程可以加密。(这点非常实用,设想一下,您的数据库服务器是托管的或租用的,你是否能心安理得的每天睡个安稳觉。如果竞争对手“一不小心”登上你的SQL Server,或通过注入得到了你的存储过程,然后相应的注入恶意的SQL,将您的业务逻辑乱改一通,而恰巧您五分钟前又没做备份,那会怎么样?)

(注意:加密存储过程前应该备份原始存储过程,且加密应该在部署到生产环境前完成。)

存储过程的加密非常简单,我们看一个例子:

插入测试表

  1. use testDb2  
  2. go  
  3. /**********测试表*****************/  
  4. SET ANSI_PADDING ON 
  5. GO  
  6. CREATE TABLE [dbo].[tb_demo](      
  7. [id] [intNOT NULL,      
  8. [submitdate] [datetime] NULL,      
  9. [commment] [nvarchar](200) NULL,  
  10. )  
  11. GO  
  12. SET ANSI_PADDING OFFGO  
  13. Insert into [tb_demo]  
  14. select 1024, getdate(),REPLICATE('A',100);  
  15. WAITFOR DELAY '00:00:04';  
  16. Insert into [tb_demo]  
  17. select 1024, getdate(),REPLICATE('B',50);  
  18. go 

插入存储过程

  1. /***************创建未加密的存储过程*******************/  
  2. Create Procedure CPP_test_Original  
  3. AS 
  4. select * from [tb_demo]  
  5. go  
  6. /***************创建加密的存储过程*******************/  
  7. Create Procedure CPP_test_Encryption   
  8. with encryption  
  9. AS 
  10. ----可以换成任意的逻辑  
  11. execute CPP_test_Original  
  12. go 

未加密的存储过程:

 

加密的存储过程:

 

此时,至少,存储过程的内容不会被轻易看到(虽然解密也是有可能的)。应用这个,我们可以对某些关键的存储过程进行加密。但此时,存储过程仍然能被execute、alter和drop

<二>安全上下文

除了加密sql文本的内容,我们还可以使用EXECUTE AS 子句设定存储过程的安全上下文,以满足不同的安全级别需求。

如果你对这些不感兴趣,请直接路过带下划线的段落。

(关于EXECUTE AS 子句的详细用法,请参看MSDN:http://msdn.microsoft.com/zh-cn/library/ms188354.aspx)

此处,我们需要了解的是:

1、在 SQL Server 中,可以定义以下用户定义模块的执行上下文:函数(内联表值函数除外)、过程、队列和触发器。

通过指定执行模块的上下文,可以控制数据库引擎使用哪一个用户帐户来验证对模块引用的对象的权限。这有助于人们更灵活、有力地管理用户定义的模块及其所引用对象所形成的对象链中的权限。必须而且只需授予用户对模块自身的权限,而无需授予用户对被引用对象的显式权限。只有运行模块的用户必须对模块访问的对象拥有权限。

针对函数、过程、队列和触发器,对应的参数也不同。存储过程对应的参数包括(CALLER | SELF | OWNER | 'user_name')。

CALLER      指定模块内的语句在模块调用方的上下文中执行。执行模块的用户不仅必须对模块本身拥有适当的权限,还要对模块引用的任何数据库对象拥有适当权限。 CALLER 是除队列外的所有模块的默认值,与 SQL Server 2005 行为相同。 CALLER 不能在 CREATE QUEUE 或 ALTER QUEUE 语句中指定。
SELF         EXECUTE AS SELF 与 EXECUTE AS user_name 等价,其中指定用户是创建或更改模块的用户。创建或更改模块的用户的实际用户 ID 存储在 sys.sql_modulessys.service_queues 目录视图的 execute_as_principal_id 列中。SELF 是队列的默认值。
OWNER     指定模块内的语句在模块的当前所有者上下文中执行。如果模块没有指定的所有者,则使用模块架构的所有者。不能为 DDL 或登录触发器指定 OWNER。注意:OWNER 必须映射到单独帐户,不能是角色或组。
'user_name'   指定模块内的语句在 user_name 指定的用户的上下文中执行。将根据 user_name 来验证对模块内任意对象的权限。不能为具有服务器作用域的 DDL 触发器或登录触发器指定 user_name。请改用 login_name。user_name 必须存在于当前数据库中,并且必须是单独帐户。user_name 不能是组、角色、证书、密钥或内置帐户,如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。执行上下文的用户 ID 存储在元数据中,可以在 sys.sql_modules 或 sys.assembly_modules 目录视图的 execute_as_principal_id 列查看。
2、所有权链具有以下限制:
仅适用于 DML 语句:SELECT、INSERT、UPDATE 和 DELETE
调用和被调用对象的所有者必须相同。
不适用于模块内的动态查询。
我们看一个示例:
第一步、创建一个测试存储过程,用来delete表tb_Demo的所有数据
  1. USE testDb2  
  2. GO  
  3. CREATE PROCEDURE dbo.[CPP_DEL_ALL_Tb_Demo]  
  4. AS 
  5. -- Deletes all rows prior to the data feed  
  6. DELETE dbo.[tb_Demo]  
  7. GO 
第二步:创建一个账号TonyZhang,并赋于该账号对该存储过程的exec权限
  1. USE master  
  2. GO  
  3. CREATE LOGIN TonyZhang WITH PASSWORD = '123b3b4' 
  4. USE testDb2  
  5. GO  
  6. CREATE USER TonyZhang  
  7. GO  
  8. GRANT EXEC ON dbo.[CPP_DEL_ALL_Tb_Demo] to TonyZhang 
以该账号登录SQL Server,并执行:
  1. EXECUTE dbo.CPP_DEL_ALL_Tb_Demo  
  2. /**  
  3. (4 row(s) affected)  
  4. **/ 
注意:此时,虽然TonyZhang除了执行存储过程[CPP_DEL_ALL_Tb_Demo]之外没有任何其他权限,但仍然执行了存储过程,并删除了表记录。
如果我们修改存储过程为:
  1. Alter PROCEDURE dbo.[CPP_DEL_ALL_Tb_Demo]  
  2. AS 
  3. -- Deletes all rows prior to the data feed  
  4. truncate table dbo.[tb_Demo]  
  5. GO 
此时,再以TonyZhang登录,并执行存储过程,会提示: 

这是因为所有者权链只限定SELECT、INSERT、UPDATE 和 DELETE。而不包括Truncate,换句话说,系统授于的Exec只既定于SELECT、INSERT、UPDATE 和 DELETE
有人可能会问:如果在存储过程内部调用动态语句,而不是明确的表名,我们如何限定权限呢?
第三步:我们建立一个存储过程,功能是传入一个参数表名,查询该表的记录数。
  1. CREATE PROCEDURE dbo.[CPP_SEL_CountRowsFromAnyTable]  
  2. @SchemaAndTable nvarchar(255)  
  3. AS 
  4. EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable)  
  5. GO 
授于Tonyzhang 以执行该存储过程的权限:
  1. GRANT EXEC ON dbo.[CPP_SEL_CountRowsFromAnyTable] to TonyZhang  
  2. go 
此时,以Tonyzhang登录,执行存储过程,会提示:

注意,此时,tonyzhang虽然有执行存储过程的权限,但是没有参数表的select权限,所以执行失败
第四步:修改存储过程的上下文
创建一个新账号jackwang,赋于表tb_Demo的select权限
  1. USE master  
  2. GO  
  3. CREATE LOGIN JackWang WITH PASSWORD = '123b3b4' 
  4. USE Testdb2  
  5. GO  
  6. CREATE USER JackWang  
  7. GRANT SELECT ON OBJECT::dbo.[tb_Demo] TO JackWang  
  8. GO  
  9. /*******  
  10. 注意:此时,JackWang 可以执行dbo.[tb_Demo的Select   
  11. *******/ 
修改存储的执行者
  1. USE Testdb2  
  2. GO  
  3. alter PROCEDURE dbo.[CPP_SEL_CountRowsFromAnyTable]  
  4. @SchemaAndTable nvarchar(255)  
  5. WITH EXECUTE AS 'JackWang' 
  6. AS 
  7. EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable)  
  8. GO 
注意:这样,我们再调用存储过程[CPP_SEL_CountRowsFromAnyTable]时,会自动以JackWang的身份运行该存储过程。此时,我们仍以Tonyzhang登录,再执行:

小结:
本文通过简单的两个示例开始SQL server代码的安全之旅,
1、存储过程的加密,(注意:加密存储过程前应该备份原始存储过程,且加密应该在部署到生产环境前完成。
2、存储过程的安全上下文。可以通过上下文设置更加严格的数据访问级别。(主要是对SELECT、INSERT、UPDATE 和 DELETE语句的访问限制

后续部分将会涉及SQL server 2008新增的透明加密(TDE)功能。

SQL 2008 r2 存储过程 加密解密

第一步:打开DAC连接功能 第二步:在MASTER数据库创建一个解密存储过程 1 USE master 2 GO 3 CREATE PROCEDURE [dbo].[sp__win...
  • jindou910101
  • jindou910101
  • 2014年10月13日 18:15
  • 6084

sql server 2008存储过程参数

--使用存储过程参数 --包括输入参数和输出参数,以及参数的默认值 --指定参数名称和数据类型 --输入参数允许用户将数据值传递到存储过程或者函数 --输出参数允许存储过程将数据值或者游标变量传递给用...
  • hephec
  • hephec
  • 2014年11月27日 16:21
  • 1444

Sql server 2008 查询某个时间段被修改过的存储过程

SELECT * FROM sys.objects WHERE type = 'P' AND modify_date BETWEEN '2011-11-01' AND '2011-11-09' ORD...
  • CsethCRM
  • CsethCRM
  • 2011年11月08日 15:41
  • 3263

C#调用Sql Server 2008的存储过程流程

前言 最近用到了sql server 2008的存储过程,特此整理成文 流程步骤 1.获取链接 2.建立可以执行SQL语句的SqlCommand 3.指明调用的是存储过程 4.设置数据适配器 5.执行...
  • u011446177
  • u011446177
  • 2016年11月15日 22:37
  • 243

sqlServer存储过程的加密和解密方法

 ★加密存储过程: IF EXISTS (SELECT name FROM sysobjects WHERE name = 'encrypt_this' AND type = 'P'...
  • u013597888
  • u013597888
  • 2015年08月02日 14:25
  • 2487

SQLserver2008,存储过程,游标的使用。

当我们需要用到 存储过程的时候,你会发现,有时候不仅仅是对数据的增删改查,经常需要对中间表的结果集进行操作,下面我先讲一下我这里的业务逻辑,然后再贴代码块, 我们微信上面由客户经理发放企业红包,需要粉...
  • zhangxc0310
  • zhangxc0310
  • 2016年05月30日 09:48
  • 2130

SQL server 2008 分页存储过程

GO /****** Object: StoredProcedure [dbo].[T_GetPager] Script Date: 03/23/2015 14:35:01 ******/ S...
  • xundh
  • xundh
  • 2015年03月23日 14:36
  • 1190

SQL Server 2008 检索数据库、表、视图、存储过程等

1、列出系统所有数据库信息       select * from sys.databases 2、判断数据库是否存在,若存在则删除       if exists (select * from...
  • XueminXu
  • XueminXu
  • 2013年03月12日 15:46
  • 1208

sqlserver获取存储过程返回值

1.OUPUT参数返回值 CREATE PROCEDURE [dbo].[nb_order_insert]( @o_buyerid int , @o_id bigint OUTPUT ) AS ...
  • liangweiwei130
  • liangweiwei130
  • 2011年08月16日 11:43
  • 14577

使用SQL Server存储过程中的返回值的两种方法

//-----方法1CREATE PROC TEST1  @PA1 INT,  @PA2 INTAS  RETURN @PA1+@PA2GODECLARE @PA3 INTEXEC @PA3=TEST...
  • dracularking
  • dracularking
  • 2008年04月13日 09:23
  • 10042
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:浅析SQL Server 2008中的代码安全之一:存储过程加密
举报原因:
原因补充:

(最多只允许输入30个字)