SQL Server 2008中的代码安全之一：存储过程安全上下文

除了加密sql文本的内容，我们还可以使用EXECUTE AS 子句设定存储过程的安全上下文，以满足不同的安全级别需求。(关于EXECUTE AS 子句的详细用法，请参看MSDN:http://msdn.microsoft.com/zh-cn/library/ms188354.aspx) 此处，我们需要了解的是：

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

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

针对函数、过程、队列和触发器，对应的参数也不同。存储过程对应的参数包括（CALLER | SELF | 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登录，并执行存储过程，会提示：  

这是因为所有者权链只限定 在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语句的访问限制）