SQLServer 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

未加密的存储过程：

加密的存储过程：

此时，至少，存储过程的内容不会被轻易看到（虽然解密也是有可能的）。应用这个，我们可以对某些关键的存储过程进行加密。但此时，存储过程仍然能被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_modules 或 sys.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的所有数据


代码如下:

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登录，并执行存储过程，会提示：


这是因为所有者权链只限定在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登录，执行存储过程，会提示：


注意，此时，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 server代码的安全之旅，
1、存储过程的加密，（注意：加密存储过程前应该备份原始存储过程，且加密应该在部署到生产环境前完成。）
2、存储过程的安全上下文。可以通过上下文设置更加严格的数据访问级别。（主要是对SELECT、INSERT、UPDATE 和 DELETE语句的访问限制）



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

赞 (0)

相关推荐

  • SQL server 2008 数据安全(备份和恢复数据库)

    SQL server 2008 数据安全(备份和恢复数据库)

    下边是我对部分内容的总结,里边偏向了T-SQL语句实现的总结,对于SQL Server Management Studio中对象管理器的操作并没有太多的总结,因为这些都有一些向导,而且,大部分都是在对应的节点,右击找相应的操作,相应的对象,然后根基向导去操作! 首先是大概知识点的总结: 下边是一些T-SQL语句对应的总结,1,管理备份设备的语句: 2,备份的语句: 3,数据恢复的对应语句: 最后, 上边讲到了备份有完全备份,差异备份,事务日志备份和文件组和数据文件备份,恢复有简单恢复,简单恢复,

  • SQL Server 数据库安全管理介绍

    SQL Server 数据库安全管理介绍

    对于数据库应用程序,数据库的安全是至关重要的.SQL Server的安全功能可以保护数据免受未经授权的泄漏和篡改!下边是对安全一章的总结! 首先是安全对象的一些总结!大家看这张图: 在这里我把固定服务器角色和固定数据库角色的详细内容给大家! 1, 固定服务器角色的: 2,固定数据库角色: 当然有了这些知识基础,我们如何管理这些安全项目呢? 1,先看看我们验证模式的设置, 2,用户登录名的管理设置: 3.1角色的设置(对象资源管理器): 3.2,角色管理设置(T-SQL语句): 4.管理权限的设置

  • SQL Server 2012 安全概述

    SQL Server 2012 安全概述

    在保密你的服务器和数据,防备当前复杂的攻击,SQL Server有你需要的一切.但在你能有效使用这些安全功能前,你需要理解你面对的威胁和一些基本的安全概念.这篇文章提供了基础,因此你可以对SQL Server里的安全功能充分利用,不用在面对特定威胁,不能保护你数据的功能上浪费时间. 从让人眼花缭乱的客户端使用连接,通过到处分布的网络,尤其是互联网,关系数据库在各种应用程序里广泛使用.这使数据对任何人,在任何地方都可访问.数据库可以保存人类知识的很大部分,包括高度敏感的个人信息和让国际商务工作的关

  • SQL Server 2008中的代码安全（八）透明加密(TDE)

    SQL Server 2008中的代码安全（八）透明加密(TDE)

    当一个用户数据库可用且已启用TDE时,在写入到磁盘时在页级实现加密.在数据页读入内存时解密.如果数据库文件或数据库备份被盗,没有用来加密的原始证书将无法访问.这几乎是SQL Server2008安全选项中最激动人心的功能了,有了它,我们至少可以将一些初级的恶意窥视拒之见外. 下面的两个例子将展示如何启用和维护透明数据加密. 示例一.启用透明加密(TDE) /********************TDE**************** 3w@live.cn ****************/ U

  • 创建安全的个人Web服务器(winserver2003、sql2000)

    一.Windows Server2003的安装   1.最小2个分区,分区格式都采用NTFS格式 2.在断开网络的情况安装好2003系统 3.安装IIS,仅安装必要的 IIS 组件.(禁用不需要的如FTP 和 SMTP 服务)   默认情况下,IIS服务没有安装,在添加/删除Win组件中选择"应用程序服务器",然后点击"详细信息",   双击Internet信息服务(iis),勾选以下选项:   Internet 信息服务管理器:公用文件:后台智能传输服务 (BIT

  • SQLServer 2008中的代码安全（一） 存储过程加密与安全上下文

    SQLServer 2008中的代码安全（一） 存储过程加密与安全上下文

    <一>存储过程加密 其实,用了这十多年的SQL server,我已经成了存储过程的忠实拥趸.在直接使用SQL语句还是存储过程来处理业务逻辑时,我基本会毫不犹豫地选择后者. 理由如下: 1.使用存储过程,至少在防非法注入(inject)方面提供更好的保护.至少,存储过程在执行前,首先会执行预编译,(如果由于非法参数的原因)编译出错则不会执行,这在某种程度上提供一层天然的屏障. 我至今还记得大约八.九年前采用的一个权限控制系统就是通过拼凑一个SQL语句,最终得到了一个形如" where

  • SQLServer 2008中的代码安全（五） 非对称密钥加密

    SQLServer 2008中的代码安全（五） 非对称密钥加密

    非对称密钥包含数据库级的内部公钥和私钥,它可以用来加密和解密SQL Server数据库中的数据,它可以从外部文件或程序集中导入,也可以在SQL Server数据库中生成.它不像证书,不可以备份到文件.这意味着一旦在SQL Server中创建了它,没有非常简单的方法在其他用户数据库中重用相同的密钥.非对称密钥对于数据库加密属于高安全选项,因而需要更多的SQL Server资源. 我们看一组例子: 示例一.创建非对称密钥 创建非对称密钥使用如下命令: CREATE ASYMMETRIC KEY  创

  • SQLServer 2008中的代码安全（四） 主密钥

    SQLServer 2008中的代码安全（四） 主密钥

    如下图: 1.服务器主密钥(Service Master Key),位于层次结构的最顶端,并且在安装SQL Server时自动创建,用于加密系统数据.链接的服务器登录名以及数据库主密钥.在第一次通过SQL Server使用服务主密钥来加密证书.数据库主密钥或链接的服务器主密码时,服务主密钥会自动生成,并且使用SQL Server服务账户的Windows证书来生成它.如果必须改变SQL Server服务账号,微软建议使用SQL Server配置管理器,因为这个工具将执行生成新服务主密钥需要的合适的

  • SQLServer 2008中的代码安全（七） 证书加密

    SQLServer 2008中的代码安全（七） 证书加密

    证书同时包含公钥和密钥,前者用来加密,后者解密.SQL Server可以生成它自己的证书,也可以从外部文件或程序集载入.因为可以备份然后从文件中载入它们,证书比非对称密钥更易于移植,而非对称密钥却做不到.这意味着可以在数据库中方便地重用同一个证书. 注意:证书和非对称密钥同样的消耗资源. 我们看一组例子: 示例一.创建数据库证书 创建数据库证书:CREATE SYMMETRIC KEY  (http://msdn.microsoft.com/en-us/library/ms187798.aspx

  • SQL Server 2008中的代码安全（三） 通过PassPhrase加密

    SQL Server 2008中的代码安全（三） 通过PassPhrase加密

    前言: 在SQL Server 2005和SQL Server 2008之前.如果希望加密敏感数据,如财务信息.工资或身份证号,必须借助外部应用程序或算法.SQL Server 2005引入内建数据加密的能力,使用证书.密钥和系统函数的组合来完成. 与数字证书类似.SQL Server 证书包括公钥和私钥这一对密钥,它们都用来加密和解密数据.SQL Server也拥有创建非对称密钥和对称密钥对象的能力.非对称密钥(asymmetric key)与证书相似,公钥用来加密数据库,私钥用来解密数据.非

  • SQLServer 2008中SQL增强之二 Top新用途

    一.TOP替代Set RowCount 在SQL Server 2005之前的传统SQL语句中,top语句是不支持局部变量的.见http://www.jb51.net/article/27089.htm此时可以使用Set RowCount,但是在SQL Server 2005/2008中,TOP通常执行得更快,所以应该用TOP关键字来取代Set RowCount. 复制代码 代码如下: /***************创建测试表********************* ************

  • SQL Server 2008中的代码安全（六） 对称密钥加密

    SQL Server 2008中的代码安全（六） 对称密钥加密

    证书和非对称密钥使用数据库级的内部公钥加密数据,并且使用数据库级内部私钥解密数据.而对称密钥相对简单,它们包含一个同时用来加密和解密的密钥.困此,使用对称密钥加密数据更快,并且用在大数据时更加合适.尽管复杂度是考虑使用它的因素,但它仍然是一个很好的加密数据的选择. 我们看一组例子: 示例一.创建对称密钥 对称密钥的特性是:在数据库会话中使用它对数据进行加密和解密前必须首先打开. 创建对称密钥使用如下命令:CREATE SYMMETRIC KEY  创建对称密钥.(http://msdn.micr

  • SQL Server 2008中的代码安全（二） DDL触发器与登录触发器

    SQL Server 2008中的代码安全（二） DDL触发器与登录触发器

    本文主要 涉及DDL触发器和登录触发器的应用实例. MicrosoftSQL Server 提供两种主要机制来强制使用业务规则和数据完整性:约束和触发器.触发器为特殊类型的存储过程,可在执行语言事件时自动生效.SQL Server 包括三种常规类型的触发器:DML 触发器.DDL 触发器和登录触发器. 1.当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器.DML 事件包括在指定表或视图中修改数据的 INSERT 语句.UPDATE 语句或 DELETE 语句.DML 触发器可

  • SQLServer 2008中SQL增强之三 Merge(在一条语句中使用Insert,Update,Delete)

    SQL Server 2008提供了一个增强的SQL命令Merge,用法参看MSDN:http://msdn.microsoft.com/zh-cn/library/bb510625.aspx 功能:根据与源表联接的结果,对目标表执行插入.更新或删除操作.例如,根据在另一个表中找到的差异在一个表中插入.更新或删除行,可以对两个表进行同步. 我们看一个例子,假如,有一总产品列表,一个分店产品列表,需要从分店添加产品时更新总产品列表. 总产品表,分店产品表结构完全一致: 复制代码 代码如下: if

  • SQLServer 2008中SQL增强之一：Values新用途

    复制代码 代码如下: Create table Demo_Values (PKID int not null identity(1,1) primary key ,DName Nvarchar(20) null ,DCode NVarchar(30) null ,DDate datetime null ) go --this SQL is only for SQL Server 2008 Insert into Demo_Values (DName,DCode,DDate) values ('D

随机推荐