SQLServer 2008中的代碼安全（一） 存儲(chǔ)過程加密與安全上下文
<一>存儲(chǔ)過程加密
其實(shí)，用了這十多年的SQL server，我已經(jīng)成了存儲(chǔ)過程的忠實(shí)擁躉。在直接使用SQL語句還是存儲(chǔ)過程來處理業(yè)務(wù)邏輯時(shí)，我基本會(huì)毫不猶豫地選擇后者。
理由如下：
1、使用存儲(chǔ)過程，至少在防非法注入(inject)方面提供更好的保護(hù)。至少，存儲(chǔ)過程在執(zhí)行前，首先會(huì)執(zhí)行預(yù)編譯，（如果由于非法參數(shù)的原因）編譯出錯(cuò)則不會(huì)執(zhí)行，這在某種程度上提供一層天然的屏障。
我至今還記得大約八、九年前采用的一個(gè)權(quán)限控制系統(tǒng)就是通過拼湊一個(gè)SQL語句，最終得到了一個(gè)形如“ 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é)果集。
注意：這個(gè)參數(shù)是通過地址欄web應(yīng)用的地址欄或Winform的UI界面來輸入的，所以對(duì)惡意注入需要花費(fèi)一定的成本來維護(hù)。因?yàn)橐恍┏Ｓ玫年P(guān)鍵字（或敏感詞）很難區(qū)分是惡意或非惡意。
2、使用存儲(chǔ)過程而不是直接訪問基表，可以提供更好的安全性。你可以在行級(jí)或列級(jí)控制數(shù)據(jù)如何被修改。相對(duì)于表的訪問，你可以確認(rèn)有執(zhí)行權(quán)限許可的用戶執(zhí)行相應(yīng)的存儲(chǔ)過程。這也是訪問數(shù)據(jù)服務(wù)器的惟一調(diào)用途徑。因此，任何偷窺者將無法看到你的SELECT語句。換句話說，每個(gè)應(yīng)用只能擁有相應(yīng)的存儲(chǔ)過程來訪問基表，而不是“SLEECT *”。
3、存儲(chǔ)過程可以加密。（這點(diǎn)非常實(shí)用，設(shè)想一下，您的數(shù)據(jù)庫(kù)服務(wù)器是托管的或租用的，你是否能心安理得的每天睡個(gè)安穩(wěn)覺。如果競(jìng)爭(zhēng)對(duì)手“一不小心”登上你的SQL Server,或通過注入得到了你的存儲(chǔ)過程，然后相應(yīng)的注入惡意的SQL，將您的業(yè)務(wù)邏輯亂改一通，而恰巧您五分鐘前又沒做備份，那會(huì)怎么樣？）
（注意：加密存儲(chǔ)過程前應(yīng)該備份原始存儲(chǔ)過程，且加密應(yīng)該在部署到生產(chǎn)環(huán)境前完成。）
存儲(chǔ)過程的加密非常簡(jiǎn)單，我們看一個(gè)例子：
插入測(cè)試表
use testDb2
go
/**********測(cè)試表*****************/
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
插入存儲(chǔ)過程：
/***************創(chuàng)建未加密的存儲(chǔ)過程*******************/
Create Procedure CPP_test_Original
AS
select * from [tb_demo]
go
/***************創(chuàng)建加密的存儲(chǔ)過程*******************/
Create Procedure CPP_test_Encryption
with encryption
AS
----可以換成任意的邏輯
execute CPP_test_Original
go
未加密的存儲(chǔ)過程：
加密的存儲(chǔ)過程：
此時(shí)，至少，存儲(chǔ)過程的內(nèi)容不會(huì)被輕易看到（雖然解密也是有可能的）。應(yīng)用這個(gè)，我們可以對(duì)某些關(guān)鍵的存儲(chǔ)過程進(jìn)行加密。但此時(shí)，存儲(chǔ)過程仍然能被execute、alter和drop。
<二>安全上下文
除了加密sql文本的內(nèi)容，我們還可以使用EXECUTE AS 子句設(shè)定存儲(chǔ)過程的安全上下文，以滿足不同的安全級(jí)別需求。
如果你對(duì)這些不感興趣，請(qǐng)直接路過帶下劃線的段落。
(關(guān)于EXECUTE AS 子句的詳細(xì)用法，請(qǐng)參看MSDN:http://msdn.microsoft.com/zh-cn/library/ms188354.aspx)
此處，我們需要了解的是：
1、在 SQL Server 中，可以定義以下用戶定義模塊的執(zhí)行上下文：函數(shù)（內(nèi)聯(lián)表值函數(shù)除外）、過程、隊(duì)列和觸發(fā)器。
通過指定執(zhí)行模塊的上下文，可以控制數(shù)據(jù)庫(kù)引擎使用哪一個(gè)用戶帳戶來驗(yàn)證對(duì)模塊引用的對(duì)象的權(quán)限。這有助于人們更靈活、有力地管理用戶定義的模塊及其所引用對(duì)象所形成的對(duì)象鏈中的權(quán)限。必須而且只需授予用戶對(duì)模塊自身的權(quán)限，而無需授予用戶對(duì)被引用對(duì)象的顯式權(quán)限。只有運(yùn)行模塊的用戶必須對(duì)模塊訪問的對(duì)象擁有權(quán)限。
針對(duì)函數(shù)、過程、隊(duì)列和觸發(fā)器，對(duì)應(yīng)的參數(shù)也不同。存儲(chǔ)過程對(duì)應(yīng)的參數(shù)包括（CALLER | SELF | OWNER | 'user_name'）。
僅適用于 DML 語句：SELECT、INSERT、UPDATE 和 DELETE。
調(diào)用和被調(diào)用對(duì)象的所有者必須相同。
不適用于模塊內(nèi)的動(dòng)態(tài)查詢。
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)建一個(gè)賬號(hào)TonyZhang,并賦于該賬號(hào)對(duì)該存儲(chǔ)過程的exec權(quán)限
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
以該賬號(hào)登錄SQL Server，并執(zhí)行：
EXECUTE dbo.CPP_DEL_ALL_Tb_Demo/**(4 row(s) affected)**/
注意：此時(shí)，雖然TonyZhang除了執(zhí)行存儲(chǔ)過程[CPP_DEL_ALL_Tb_Demo]之外沒有任何其他權(quán)限，但仍然執(zhí)行了存儲(chǔ)過程，并刪除了表記錄。
如果我們修改存儲(chǔ)過程為：
Alter PROCEDURE dbo.[CPP_DEL_ALL_Tb_Demo]
AS
-- Deletes all rows prior to the data feed
truncate table dbo.[tb_Demo]
GO
此時(shí)，再以TonyZhang登錄，并執(zhí)行存儲(chǔ)過程，會(huì)提示：
CREATE PROCEDURE dbo.[CPP_SEL_CountRowsFromAnyTable]
@SchemaAndTable nvarchar(255)
AS
EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable)
GO
授于Tonyzhang 以執(zhí)行該存儲(chǔ)過程的權(quán)限：
GRANT EXEC ON dbo.[CPP_SEL_CountRowsFromAnyTable] to TonyZhang
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
/*******
注意：此時(shí)，JackWang 可以執(zhí)行dbo.[tb_Demo的Select
*******/
修改存儲(chǔ)的執(zhí)行者
USE Testdb2
GO
alter PROCEDURE dbo.[CPP_SEL_CountRowsFromAnyTable]
@SchemaAndTable nvarchar(255)
WITH EXECUTE AS 'JackWang'
AS
EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable)
GO
相關(guān)文章
探討如何配置SQL2008,讓其允許C#遠(yuǎn)程外部連接的方法詳解
本篇文章是對(duì)如何配置SQL2008,讓其允許C#遠(yuǎn)程外部連接的方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
SQL Server 2008網(wǎng)絡(luò)協(xié)議深入理解
可以通過展開SQL Server 2008網(wǎng)絡(luò)配置節(jié)點(diǎn)進(jìn)行服務(wù)器協(xié)議的配置，需要了解更多的朋友可以參考本文2012-11-11
SQL Server 2008中SQL之WaitFor使用介紹
在SQL Server 2005以上版本中，在一個(gè)增強(qiáng)的WaitFor命令，其作用可以和一個(gè)job相當(dāng)。但使用更加簡(jiǎn)捷2011-05-05
本文介紹了如何利用SQL進(jìn)行推理，通過一個(gè)例子進(jìn)行實(shí)現(xiàn)推理的演示過程,需要了解的朋友可以參考下2015-07-07
SQL Server 2008 R2 應(yīng)用及多服務(wù)器管理
所謂多服務(wù)器管理 (Multiserver Administration)就是SQL Server 2008 R2提供的自動(dòng)管理多個(gè) SQL Server 實(shí)例過程的功能。在多服務(wù)器管理中，連接到主服務(wù)器并從其接收作業(yè)的服務(wù)器被稱之為目標(biāo)服務(wù)器。2014-10-10
SQL Server 2000向SQL Server 2008 R2推送數(shù)據(jù)圖文教程
這篇文章主要介紹了SQL Server 2000向SQL Server 2008 R2推送數(shù)據(jù)圖文教程,本文步驟詳細(xì),配圖清晰,需要的朋友可以參考下2014-09-09
SQLServer 2008 :error 40出現(xiàn)連接錯(cuò)誤的解決方法
在與SQLServer建立連接時(shí)出現(xiàn)與網(wǎng)絡(luò)相關(guān)的或特定與實(shí)例的錯(cuò)誤.未找到或無法訪問服務(wù)器.請(qǐng)驗(yàn)證實(shí)例名稱是否正確并且SQL SERVER已配置允許遠(yuǎn)程鏈接2013-09-09
這篇文章主要為大家詳細(xì)介紹了SQL Server 2008 R2安裝配置方法圖文教程，感興趣的小伙伴們可以參考一下2016-07-07
在與 SQL Server 建立連接時(shí)出現(xiàn)與網(wǎng)絡(luò)相關(guān)的或特定于實(shí)例的錯(cuò)誤。未找到或無法訪問服務(wù)器
在與 SQL Server 建立連接時(shí)出現(xiàn)與網(wǎng)絡(luò)相關(guān)的或特定于實(shí)例的錯(cuò)誤。未找到或無法訪問服務(wù)器。請(qǐng)驗(yàn)證實(shí)例名稱是否正確并且 SQL Server 已配置為允許遠(yuǎn)程連接。 (provider: 命名管道提供程序, error: 40 - 無法打開到 SQL Server 的連接)2015-01-01
SQL Server復(fù)制需要有實(shí)際的服務(wù)器名稱才能連接到服務(wù)器
服務(wù)器上安裝的WIN2008 R2，然后沒有在意機(jī)器名，安裝了SQL2008 R2數(shù)據(jù)庫(kù)之后，配置AD域的時(shí)候修改了機(jī)器名2013-11-11