存储过程加密
其实，用了这十多年的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,REPLICATE('B',50);
go
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,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
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 AS
子句设定存储过程的安全上下文，以满足不同的安全级别需求。
(关于
EXECUTE AS
子句的详细用法，请参看MSDN:http://msdn.microsoft.com/zh-cn/library/ms188354.aspx)此处，我们需要了解的是：
sql Server 中，可以定义以下用户定义模块的执行上下文：函数（内联表值函数除外）、过程、队列和触发器。
数据库引擎使用哪一个用户帐户来验证对模块引用的对象的权限。这有助于人们更灵活、有力地管理用户定义的模块及其所引用对象所形成的对象链中的权限。必须而且只需授予用户对模块自身的权限，而无需授予用户对被引用对象的显式权限。只有运行模块的用户必须对模块访问的对象拥有权限。
针对函数、过程、队列和触发器，对应的参数也不同。存储过程对应的参数包括（
CALLER | SELF | OWNER | 'user_name
'）。
sys.sql_modules
或sys.service_queues
目录视图的execute_as_principal_id
列中。SELF 是队列的默认值。OWNER 必须映射到单独帐户，不能是角色或组。
。
调用和被调用对象的所有者必须相同。
查询。
代码如下:
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
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
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
AS
-- Deletes all rows prior to the data Feed
truncate table dbo.[tb_Demo]
GO
此时，再以TonyZhang登录，并执行存储过程，会提示：
代码如下:
CREATE PROCEDURE dbo.[CPP_SEL_CountRowsFromAnyTable]
@SchemaAndTable nvarchar(255)
AS
EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable)
GO
@SchemaAndTable nvarchar(255)
AS
EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable)
GO
授于Tonyzhang 以执行该存储过程的权限：
代码如下:
GRANT EXEC ON dbo.[CPP_SEL_CountRowsFromAnyTable] to TonyZhang
go
go
代码如下:
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
*******/
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
GO alter PROCEDURE dbo.[CPP_SEL_CountRowsFromAnyTable]
@SchemaAndTable nvarchar(255)
WITH EXECUTE AS 'JackWang'
AS
EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable)
GO
