一>存儲過程加密
其實，用了這十多年的SQL server，我已經(jīng)成了存儲過程的忠實擁躉。在直接使用SQL語句還是存儲過程來處理業(yè)務(wù)邏輯時，我基本會毫不猶豫地選擇后者。
理由如下：
1、使用存儲過程，至少在防非法注入(inject)方面提供更好的保護。至少，存儲過程在執(zhí)行前，首先會執(zhí)行預(yù)編譯，（如果由于非法參數(shù)的原因）編譯出錯則不會執(zhí)行，這在某種程度上提供一層天然的屏障。
我至今還記得大約八、九年前采用的一個權(quán)限控制系統(tǒng)就是通過拼湊一個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條件子句來獲取符合條件的結(jié)果集。
注意：這個參數(shù)是通過地址欄web應(yīng)用的地址欄或Winform的UI界面來輸入的，所以對惡意注入需要花費一定的成本來維護。因為一些常用的關(guān)鍵字（或敏感詞）很難區(qū)分是惡意或非惡意。
2、使用存儲過程而不是直接訪問基表，可以提供更好的安全性。你可以在行級或列級控制數(shù)據(jù)如何被修改。相對于表的訪問，你可以確認有執(zhí)行權(quán)限許可的用戶執(zhí)行相應(yīng)的存儲過程。這也是訪問數(shù)據(jù)服務(wù)器的惟一調(diào)用途徑。因此，任何偷窺者將無法看到你的SELECT語句。換句話說，每個應(yīng)用只能擁有相應(yīng)的存儲過程來訪問基表，而不是“SLEECT *”。
3、存儲過程可以加密。（這點非常實用，設(shè)想一下，您的數(shù)據(jù)庫服務(wù)器是托管的或租用的，你是否能心安理得的每天睡個安穩(wěn)覺。如果競爭對手“一不小心”登上你的SQL Server,或通過注入得到了你的存儲過程，然后相應(yīng)的注入惡意的SQL，將您的業(yè)務(wù)邏輯亂改一通，而恰巧您五分鐘前又沒做備份，那會怎么樣？）
（注意：加密存儲過程前應(yīng)該備份原始存儲過程，且加密應(yīng)該在部署到生產(chǎn)環(huán)境前完成。）
存儲過程的加密非常簡單，我們看一個例子：
插入測試表
復(fù)制代碼 代碼如下:
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
插入存儲過程：
復(fù)制代碼 代碼如下:
/***************創(chuàng)建未加密的存儲過程*******************/
Create Procedure CPP_test_Original
AS
select * from [tb_demo]
go
/***************創(chuàng)建加密的存儲過程*******************/
Create Procedure CPP_test_Encryption
with encryption
AS
----可以換成任意的邏輯
execute CPP_test_Original
go
未加密的存儲過程：
加密的存儲過程：
此時，至少，存儲過程的內(nèi)容不會被輕易看到（雖然解密也是有可能的）。應(yīng)用這個，我們可以對某些關(guān)鍵的存儲過程進行加密。但此時，存儲過程仍然能被execute、alter和drop。
二>安全上下文
除了加密sql文本的內(nèi)容，我們還可以使用EXECUTE AS 子句設(shè)定存儲過程的安全上下文，以滿足不同的安全級別需求。
如果你對這些不感興趣，請直接路過帶下劃線的段落。
(關(guān)于EXECUTE AS 子句的詳細用法，請參看MSDN:http://msdn.microsoft.com/zh-cn/library/ms188354.aspx)
此處，我們需要了解的是：
1、在 SQL Server 中，可以定義以下用戶定義模塊的執(zhí)行上下文：函數(shù)（內(nèi)聯(lián)表值函數(shù)除外）、過程、隊列和觸發(fā)器。
通過指定執(zhí)行模塊的上下文，可以控制數(shù)據(jù)庫引擎使用哪一個用戶帳戶來驗證對模塊引用的對象的權(quán)限。這有助于人們更靈活、有力地管理用戶定義的模塊及其所引用對象所形成的對象鏈中的權(quán)限。必須而且只需授予用戶對模塊自身的權(quán)限，而無需授予用戶對被引用對象的顯式權(quán)限。只有運行模塊的用戶必須對模塊訪問的對象擁有權(quán)限。
針對函數(shù)、過程、隊列和觸發(fā)器，對應(yīng)的參數(shù)也不同。存儲過程對應(yīng)的參數(shù)包括（CALLER | SELF | OWNER | 'user_name'）。
■CALLER 指定模塊內(nèi)的語句在模塊調(diào)用方的上下文中執(zhí)行。執(zhí)行模塊的用戶不僅必須對模塊本身擁有適當(dāng)?shù)臋?quán)限，還要對模塊引用的任何數(shù)據(jù)庫對象擁有適當(dāng)權(quán)限。 CALLER 是除隊列外的所有模塊的默認值，與 SQL Server 2005 行為相同。 CALLER 不能在 CREATE QUEUE 或 ALTER QUEUE 語句中指定。
■SELF EXECUTE AS SELF 與 EXECUTE AS user_name 等價，其中指定用戶是創(chuàng)建或更改模塊的用戶。創(chuàng)建或更改模塊的用戶的實際用戶 ID 存儲在 sys.sql_modules 或 sys.service_queues 目錄視圖的 execute_as_principal_id 列中。SELF 是隊列的默認值。
■OWNER 指定模塊內(nèi)的語句在模塊的當(dāng)前所有者上下文中執(zhí)行。如果模塊沒有指定的所有者，則使用模塊架構(gòu)的所有者。不能為 DDL 或登錄觸發(fā)器指定 OWNER。注意：OWNER 必須映射到單獨帳戶，不能是角色或組。
■'user_name' 指定模塊內(nèi)的語句在 user_name 指定的用戶的上下文中執(zhí)行。將根據(jù) user_name 來驗證對模塊內(nèi)任意對象的權(quán)限。不能為具有服務(wù)器作用域的 DDL 觸發(fā)器或登錄觸發(fā)器指定 user_name。請改用 login_name。user_name 必須存在于當(dāng)前數(shù)據(jù)庫中，并且必須是單獨帳戶。user_name 不能是組、角色、證書、密鑰或內(nèi)置帳戶，如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。執(zhí)行上下文的用戶 ID 存儲在元數(shù)據(jù)中，可以在 sys.sql_modules 或 sys.assembly_modules 目錄視圖的 execute_as_principal_id 列查看。
2、所有權(quán)鏈具有以下限制：
僅適用于 DML 語句：SELECT、INSERT、UPDATE 和 DELETE。
調(diào)用和被調(diào)用對象的所有者必須相同。
不適用于模塊內(nèi)的動態(tài)查詢。
我們看一個示例：
第一步、創(chuàng)建一個測試存儲過程，用來delete表tb_Demo的所有數(shù)據(jù)
復(fù)制代碼 代碼如下:
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
第二步：創(chuàng)建一個賬號TonyZhang,并賦于該賬號對該存儲過程的exec權(quán)限
復(fù)制代碼 代碼如下:
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，并執(zhí)行：
復(fù)制代碼 代碼如下:
EXECUTE dbo.CPP_DEL_ALL_Tb_Demo/**(4 row(s) affected)**/
注意：此時，雖然TonyZhang除了執(zhí)行存儲過程[CPP_DEL_ALL_Tb_Demo]之外沒有任何其他權(quán)限，但仍然執(zhí)行了存儲過程，并刪除了表記錄。
如果我們修改存儲過程為：
復(fù)制代碼 代碼如下:
Alter PROCEDURE dbo.[CPP_DEL_ALL_Tb_Demo]
AS
-- Deletes all rows prior to the data feed
truncate table dbo.[tb_Demo]
GO
此時，再以TonyZhang登錄，并執(zhí)行存儲過程，會提示：
這是因為所有者權(quán)鏈只限定在SELECT、INSERT、UPDATE 和 DELETE。而不包括Truncate，換句話說，系統(tǒng)授于的Exec只既定于SELECT、INSERT、UPDATE 和 DELETE
有人可能會問：如果在存儲過程內(nèi)部調(diào)用動態(tài)語句，而不是明確的表名，我們?nèi)绾蜗薅?quán)限呢？
第三步：我們建立一個存儲過程，功能是傳入一個參數(shù)表名，查詢該表的記錄數(shù)。
復(fù)制代碼 代碼如下:
CREATE PROCEDURE dbo.[CPP_SEL_CountRowsFromAnyTable]
@SchemaAndTable nvarchar(255)
AS
EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable)
GO
授于Tonyzhang 以執(zhí)行該存儲過程的權(quán)限：
復(fù)制代碼 代碼如下:
GRANT EXEC ON dbo.[CPP_SEL_CountRowsFromAnyTable] to TonyZhang
go
此時，以Tonyzhang登錄，執(zhí)行存儲過程，會提示：
注意，此時，tonyzhang雖然有執(zhí)行存儲過程的權(quán)限，但是沒有參數(shù)表的select權(quán)限，所以執(zhí)行失敗。
第四步：修改存儲過程的上下文
創(chuàng)建一個新賬號jackwang，賦于表tb_Demo的select權(quán)限
復(fù)制代碼 代碼如下:
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 可以執(zhí)行dbo.[tb_Demo的Select
*******/
修改存儲的執(zhí)行者
復(fù)制代碼 代碼如下:
USE Testdb2
GO
alter PROCEDURE dbo.[CPP_SEL_CountRowsFromAnyTable]
@SchemaAndTable nvarchar(255)
WITH EXECUTE AS 'JackWang'
AS
EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable)
GO
注意：這樣，我們再調(diào)用存儲過程[CPP_SEL_CountRowsFromAnyTable]時，會自動以JackWang的身份運行該存儲過程。
此時，我們?nèi)砸訲onyzhang登錄，再執(zhí)行：
小結(jié)：
本文通過簡單的兩個示例開始SQL server代碼的安全之旅，
1、存儲過程的加密，（注意：加密存儲過程前應(yīng)該備份原始存儲過程，且加密應(yīng)該在部署到生產(chǎn)環(huán)境前完成。）
2、存儲過程的安全上下文?？梢酝ㄟ^上下文設(shè)置更加嚴(yán)格的數(shù)據(jù)訪問級別。（主要是對SELECT、INSERT、UPDATE 和 DELETE語句的訪問限制）
后續(xù)部分將會涉及SQL server 2008新增的透明加密(TDE)功能。
您可能感興趣的文章:- SQL Server 2008 存儲過程示例
- 如何在SQL Server 2008下輕松調(diào)試T-SQL語句和存儲過程
- sqlserver2008查看表記錄或者修改存儲過程出現(xiàn)目錄名無效錯誤解決方法
- SQLServer2008存儲過程實現(xiàn)數(shù)據(jù)插入與更新
- sql server2008調(diào)試存儲過程的完整步驟
