当前位置: 首页>数据库>正文

SQL aes加密 sql server加密

 SQL Server 2008中SQL应用系列--目录索引

最近对SQL Server 2008的安全入门略作小结,以作备忘。本文涉及两个应用:存储过程加密和安全上下文。

<一>存储过程加密

其实,用了这十多年的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,将您的业务逻辑乱改一通,而恰巧您五分钟前又没做备份,那会怎么样?)

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

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

插入测试表


use 
    testDb2
 
   go 
   

 
   /* 
   *********测试表**************** 
   */ 
   
 
   SET 
    ANSI_PADDING  
   ON 
   
 
   GO 
   
 
   CREATE 
     
   TABLE 
     
   [ 
   dbo 
   ] 
   . 
   [ 
   tb_demo 
   ] 
   (
 
   [ 
   id 
   ] 
     
   [ 
   int 
   ] 
     
   NOT 
     
   NULL 
   ,
 
   [ 
   submitdate 
   ] 
     
   [ 
   datetime 
   ] 
     
   NULL 
   ,
 
   [ 
   commment 
   ] 
     
   [ 
   nvarchar 
   ] 
   ( 
   200 
   )  
   NULL 
   ,
)
 
   GO 
   
 
   SET 
    ANSI_PADDING  
   OFF 
   
 
   GO 
   
 
   Insert 
     
   into 
     
   [ 
   tb_demo 
   ] 
   
 
   select 
     
   1024 
   ,  
   getdate 
   (), 
   REPLICATE 
   ( 
   ' 
   A 
   ' 
   , 
   100 
   );
 
   WAITFOR 
    DELAY  
   ' 
   00:00:04 
   ' 
   ;
 
   Insert 
     
   into 
     
   [ 
   tb_demo 
   ] 
   
 
   select 
     
   1024 
   ,  
   getdate 
   (), 
   REPLICATE 
   ( 
   ' 
   B 
   ' 
   , 
   50 
   );
 
   go


插入存储过程:




/* 
   **************创建未加密的存储过程****************** 
   */ 
   
 
   Create 
     
   Procedure 
    CPP_test_Original
 
   AS 
   
 
   select 
     
   * 
     
   from 
     
   [ 
   tb_demo 
   ] 
   
 
   go 
   
 
   /* 
   **************创建加密的存储过程****************** 
   */ 
   
 
   Create 
     
   Procedure 
    CPP_test_Encryption 
 
   with 
    encryption
 
   AS 
   
 
   -- 
   --可以换成任意的逻辑 
   
 
   execute 
    CPP_test_Original
 
   go

未加密的存储过程:


SQL aes加密 sql server加密,SQL aes加密 sql server加密_存储过程,第1张

加密的存储过程:

SQL aes加密 sql server加密,SQL aes加密 sql server加密_SQL aes加密_02,第2张

此时,至少,存储过程的内容不会被轻易看到(虽然解密也是有可能的)。应用这个,我们可以对某些关键的存储过程进行加密。但此时,存储过程仍然能被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')。


CALLERSELF         EXECUTE AS SELF 与 EXECUTE AS user_name 等价,其中指定用户是创建或更改模块的用户。创建或更改模块的用户的实际用户 ID 存储在 sys.sql_modulessys.service_queues 目录视图的 execute_as_principal_id 列中。SELF 是队列的默认值。 ■OWNER     指定模块内的语句在模块的当前所有者上下文中执行。如果模块没有指定的所有者,则使用模块架构的所有者。不能为 DDL 或登录触发器指定 OWNER。注意:OWNER 必须映射到单独帐户,不能是角色或组。'user_name' 

2、所有权链具有以下限制:

仅适用于 DML 语句:SELECT、INSERT、UPDATE 和 DELETE。

调用和被调用对象的所有者必须相同。

不适用于模块内的动态查询。

我们看一个示例:第一步、创建一个测试存储过程,用来delete表tb_Demo的所有数据


USE 
    testDb2
 
   GO 
   

 
   CREATE 
     
   PROCEDURE 
    dbo.[CPP_DEL_ALL_Tb_Demo]
 
   AS 
   
 
   -- 
    Deletes all rows prior to the data feed 
   
 
   DELETE 
    dbo. 
   [ 
   tb_Demo 
   ] 
   
 
   GO

第二步:创建一个账号TonyZhang,并赋于该账号对该存储过程的exec权限



USE 
    master
 
   GO 
   
 
   CREATE 
    LOGIN TonyZhang  
   WITH 
    PASSWORD  
   = 
     
   ' 
   123b3b4 
   ' 
   
 
   USE 
    testDb2
 
   GO 
   
 
   CREATE 
     
   USER 
    TonyZhang
 
   GO 
   
 
   GRANT 
     
   EXEC 
     
   ON 
    dbo. 
   [ 
   CPP_DEL_ALL_Tb_Demo 
   ] 
     
   to 
    TonyZhang 以该账号登录SQL Server,并执行: 
  
  
EXECUTE 
    dbo.CPP_DEL_ALL_Tb_Demo
 
   /* 
   *
(4 row(s) affected)
* 
   */



注意:此时,

虽然TonyZhang除了执行存储过程[CPP_DEL_ALL_Tb_Demo]之外没有任何其他权限,但仍然执行了存储过程,并删除了表记录。如果我们修改存储过程为:



Alter PROCEDURE dbo. [ CPP_DEL_ALL_Tb_Demo ]
AS
-- Deletes all rows prior to the data feed
truncate table dbo. [ tb_Demo ]
GO

此时,再以TonyZhang登录,并执行存储过程,会提示:

 

SQL aes加密 sql server加密,SQL aes加密 sql server加密_数据库_03,第3张

这是因为所有者权链只限定

在SELECT、INSERT、UPDATE 和 DELETE。而不包括Truncate,换句话说,系统授于的Exec只既定于 SELECT、INSERT、UPDATE 和 DELETE

有人可能会问:如果在存储过程内部调用动态语句,而不是明确的表名,我们如何限定权限呢?第三步:我们建立一个存储过程,功能是传入一个参数表名,查询该表的记录数。

CREATE 
     
   PROCEDURE 
    dbo.[CPP_SEL_CountRowsFromAnyTable]
 
   @SchemaAndTable 
     
   nvarchar 
   ( 
   255 
   )
 
   AS 
   
 
   EXEC 
    ( 
   ' 
   SELECT COUNT(1) FROM  
   ' 
     
   + 
     
   @SchemaAndTable 
   )
 
   GO

授于Tonyzhang 以执行该存储过程的权限:


GRANT 
     
   EXEC 
     
   ON 
    dbo. 
   [ 
   CPP_SEL_CountRowsFromAnyTable 
   ] 
     
   to 
    TonyZhang
 
   go

此时,以Tonyzhang登录,执行存储过程,会提示:


SQL aes加密 sql server加密,SQL aes加密 sql server加密_ui_04,第4张

注意,此时,

tonyzhang虽然有执行存储过程的权限,但是没有参数表的select权限,所以执行失败。第四步:修改存储过程的上下文创建一个新账号jackwang,赋于表tb_Demo的select权限

USE 
    master
 
   GO 
   
 
   CREATE 
    LOGIN JackWang  
   WITH 
    PASSWORD  
   = 
     
   ' 
   123b3b4 
   ' 
   
 
   USE 
    Testdb2
 
   GO 
   
 
   CREATE 
     
   USER 
    JackWang
 
   GRANT 
     
   SELECT 
     
   ON 
    OBJECT::dbo. 
   [ 
   tb_Demo 
   ] 
     
   TO 
    JackWang
 
   GO 
   

 
   /* 
   ******
注意:此时,JackWang 可以执行dbo.[tb_Demo的Select 
****** 
   */

修改存储的执行者



USE 
    Testdb2
 
   GO 
   

 
   alter 
     
   PROCEDURE 
    dbo. 
   [ 
   CPP_SEL_CountRowsFromAnyTable 
   ] 
   
 
   @SchemaAndTable 
     
   nvarchar 
   ( 
   255 
   )
 
   WITH 
     
   EXECUTE AS 'JackWang' 
   
 
   AS 
   
 
   EXEC 
    ( 
   ' 
   SELECT COUNT(1) FROM  
   ' 
     
   + 
     
   @SchemaAndTable 
   )
 
   GO


注意:这样,我们再调用存储过程

[ CPP_SEL_CountRowsFromAnyTable ] 时,会自动以JackWang的身份运行该存储过程。 此时,我们仍以Tonyzhang登录,再执行:

SQL aes加密 sql server加密,SQL aes加密 sql server加密_SQL_05,第5张



小结:本文通过简单的两个示例开始SQL server代码的安全之旅,1、存储过程的加密,(注意:

加密存储过程前应该备份原始存储过程,且加密应该在部署到生产环境前完成。)2、存储过程的安全上下文。可以通过上下文设置更加严格的数据访问级别。(主要是对

SELECT、INSERT、UPDATE 和 DELETE语句的访问限制)


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



https://www.xamrdz.com/database/63s1951382.html

相关文章: