最近对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文本的内容，我们还可以使用execute as 子句设定存储过程的安全上下文，以满足不同的安全级别需求。
(关于execute as 子句的详细用法，请参看msdn:
此处，我们需要了解的是：
针对函数、过程、队列和触发器，对应的参数也不同。存储过程对应的参数包括（caller | self | owner | 'user_name'）。
指定模块内的语句在模块调用方的上下文中执行。执行模块的用户不仅必须对模块本身拥有适当的权限，还要对模块引用的任何数据库对象拥有适当权限。 caller 是除队列外的所有模块的默认值，与 sql server 2005 行为相同。 caller 不能在 create queue 或 alter queue 语句中指定。
execute as self 与 execute as 等价，其中指定用户是创建或更改模块的用户。创建或更改模块的用户的实际用户 id 存储在 sys.sql_modules 或 sys.service_queues 目录视图的 execute_as_principal_id 列中。self 是队列的默认值。 owner 必须映射到单独帐户，不能是角色或组。 指定模块内的语句在 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 列查看。
。
我们看一个示例： 第一步、创建一个测试存储过程，用来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只既定于 有人可能会问：如果在存储过程内部调用动态语句，而不是明确的表名，我们如何限定权限呢？ 第三步：我们建立一个存储过程，功能是传入一个参数表名，查询该表的记录数。
代码如下:
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登录，执行存储过程，会提示：
注意，此时，。 第四步：修改存储过程的上下文 创建一个新账号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
注意：这样，我们再调用存储过程
小结： 本文通过简单的两个示例开始sql server代码的安全之旅， 1、存储过程的加密，（注意：） 2、存储过程的安全上下文。可以通过上下文设置更加严格的数据访问级别。（主要是对）
后续部分将会涉及sql server 2008新增的透明加密(tde)功能。