T-SQL問(wèn)題解決集錦數(shù)據(jù)加解密全集

更新時(shí)間：2012年06月26日 15:42:13 作者：

對(duì)于一些敏感數(shù)據(jù)，如密碼、卡號(hào)，一般不能使用正常數(shù)值來(lái)存儲(chǔ)。否則會(huì)有安全隱患。以往的加密解密都有前端應(yīng)用程序來(lái)輔助完成。而數(shù)據(jù)庫(kù)一般只能加密不能解密

以下代碼已經(jīng)在SQLServer2008上的示例數(shù)據(jù)庫(kù)測(cè)試通過(guò)

問(wèn)題一：如何為數(shù)據(jù)進(jìn)行加密與解密，避免使用者竊取機(jī)密數(shù)據(jù)？
對(duì)于一些敏感數(shù)據(jù)，如密碼、卡號(hào)，一般不能使用正常數(shù)值來(lái)存儲(chǔ)。否則會(huì)有安全隱患。以往的加密解密都有前端應(yīng)用程序來(lái)輔助完成。而數(shù)據(jù)庫(kù)一般只能加密不能解密。

從2005開(kāi)始提供了數(shù)據(jù)庫(kù)層面的數(shù)據(jù)加密與解密。其實(shí)現(xiàn)方式主要有以下：

1、利用CONVERT改變編碼方式：
利用該函數(shù)把文字或數(shù)據(jù)轉(zhuǎn)換成VARBINARY。但該方式不具備保護(hù)數(shù)據(jù)的能力，僅避免瀏覽數(shù)據(jù)的過(guò)程中能直接看到敏感數(shù)據(jù)的作用。

2、利用對(duì)稱密鑰：
搭配EncryptByKey進(jìn)行數(shù)據(jù)加密。使用DecryptByKey函數(shù)進(jìn)行解密。這種方式比較適合大數(shù)據(jù)量。因?yàn)閷?duì)稱密鑰的過(guò)程好用資源較少。

3、利用非對(duì)稱密鑰：
搭配EncryptByAsymKey進(jìn)行數(shù)據(jù)加密。使用DecryptByAsymKey函數(shù)進(jìn)行解密。用于更高安全級(jí)別的加解密數(shù)據(jù)。因?yàn)楹挠觅Y源叫多。

4、利用憑證的方式：
搭配EncryptByCert進(jìn)行加密和DecryptByCert函數(shù)進(jìn)行解密。比較類似非對(duì)稱密鑰。

5、利用密碼短語(yǔ)方式：
搭配EncryptBypassPhrase進(jìn)行加密，使用DecryptByPassPhrase函數(shù)來(lái)解密?？梢允褂糜幸饬x的短語(yǔ)或其他數(shù)據(jù)行，當(dāng)成加密、解密的關(guān)鍵字，比較適合一般的數(shù)據(jù)加解密。

案例：
1、 Convert方式：

復(fù)制代碼代碼如下:

 
a) USE tempdb 
b) GO 
c) CREATE TABLE test 
d) ( 
e) userID INT IDENTITY(1, 1) , 
f) userName VARCHAR(10) , 
g) userSalary FLOAT , 
h) cyberalary NVARCHAR(MAX) 
i) ) ; 
j) 
k) INSERT INTO TEST 
l) ( userName, userSalary ) 
m) VALUES ( 'taici', 1234 ), 
n) ( 'hailong', 3214 ), 
o) ( 'meiyuan', 1111 ) 
p) --ALTER TABLE test 
q) --ADD userNewSalary VARBINARY(512) 
r) --使用轉(zhuǎn)換函數(shù)把數(shù)據(jù)轉(zhuǎn)換成varbinary，改變編碼方式。 
s) SELECT * , 
t) CONVERT(VARBINARY(512), userSalary) 
u) FROM test 
v) --把數(shù)據(jù)轉(zhuǎn)換成int，可以恢復(fù)原有編碼方式 
w) SELECT * , 
x) CONVERT(INT, userSalary) 
y) FROM test 

2、對(duì)稱密鑰:

復(fù)制代碼代碼如下:

 
a) --創(chuàng)建對(duì)稱密鑰 
b) USE AdventureWorks 
c) GO 
d) CREATE SYMMETRIC KEY SymKey123 
e) WITH ALGORITHM=TRIPLE_DES ENCRYPTION BY PASSWORD='P@ssw0rd' 
f) GO 
g) --注意事項(xiàng):在啟用時(shí),需要先OPEN SYMMETRIC KEY 搭配密鑰密碼，否則所產(chǎn)生的數(shù)據(jù)都會(huì)是null值。而且需要搭配Key_GUID函數(shù)來(lái)使用 
h) --打開(kāi)對(duì)稱密鑰 
i) OPEN SYMMETRIC KEY SymKey123 DECRYPTION BY PASSWORD='P@ssw0rd'; 
j) --進(jìn)行數(shù)據(jù)加密 
k) SELECT * ,ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(max),AddressLine1)) 
l) FROM Person.Address 
m) 
n) --檢查加密后長(zhǎng)度，利用datalength()函數(shù) 
o) SELECT DATALENGTH(ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1))) 
p) FROM Person.Address 
q) GO 
r) --把加密后數(shù)據(jù)更新到原來(lái)另外的列上 
s) UPDATE Person.Address 
t) SET AddressLine2=ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(max),AddressLine1)) 
u) --解密:解密過(guò)程同樣需要OPEN SYMMETRIC KEY ,且需要利用DECRYPTBYKEY 和CONVERT函數(shù) 
v) OPEN SYMMETRIC KEY SymKey123 DECRYPTION BY PASSWORD='P@ssw0rd'; 
w) 
x) SELECT AddressID,CONVERT(VARCHAR(MAX ) ,CONVERT (VARCHAR(MAX ),DECRYPTBYKEY(AddressLine2))) 
y) FROM Person.Address 

3、非對(duì)稱密鑰：

復(fù)制代碼代碼如下:

 
a) --非對(duì)稱密鑰使用兩種不同的密鑰，所以加密是是不需要輸入密碼驗(yàn)證，但解密時(shí)就需要 
b) USE AdventureWorks 
c) GO 
d) CREATE ASYMMETRIC KEY AsymKey123 WITH ALGORITHM=RSA_2048 ENCRYPTION BY PASSWORD='P@ssw0rd'; 
e) GO 
f) 
g) --添加新列存儲(chǔ)加密后的數(shù)據(jù) 
h) ALTER TABLE Person.Address ADD AddressLine3 nvarchar(MAX) 
i) GO 
j) --進(jìn)行加密 
k) SELECT *,ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1)) 
l) FROM Person.Address 
m) GO 
n) 
o) --把數(shù)據(jù)更新到一個(gè)新列 
p) UPDATE Person.Address 
q) SET AddressLine3=ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1)) 
r) 
s) 
t) SELECT *--addressline3 
u) FROM Person.Address 
v) 
w) --解密：此過(guò)程一定要使用密碼來(lái)解密，此處的類型要與加密時(shí)相同，比如加密時(shí)用varchar,而這里用nvarchar的話是解密不了的。 
x) SELECT TOP 10 AddressID,CONVERT(VARCHAR(MAX),CONVERT (VARCHAR(MAX ),DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey123'),AddressLine3,N'P@ssw0rd'))) AS Decryptedata 
y) FROM Person.Address 

4、證書(shū)加密：

復(fù)制代碼代碼如下:

 
a) --證書(shū)加密：首先建立證書(shū)（certificate） 
b) CREATE CERTIFICATE certKey123--證書(shū)名 
c) ENCRYPTION BY PASSWORD='P@ssw0rd'--密碼 
d) WITH SUBJECT='Address Certificate',--證書(shū)描述 
e) START_DATE='2012/06/18',--證書(shū)生效日期 
f) EXPIRY_DATE='2013/06/18' ;--證書(shū)到期日 
g) GO 
h) --利用證書(shū)加密 
i) SELECT *,ENCRYPTBYCERT(CERT_ID('certKey123'),CONVERT (VARCHAR(MAX ),AddressLine1)) cyberAddress 
j) FROM Person.Address 
k) 
l) --添加新列存放加密數(shù)據(jù) 
m) ALTER TABLE Person.Address ADD AddressLine4 Nvarchar(MAX ) 
n) 
o) --把加密后數(shù)據(jù)放到新列 
p) UPDATE Person.Address 
q) SET AddressLine4=ENCRYPTBYCERT(CERT_ID('certKey123'),CONVERT (VARCHAR(MAX ),AddressLine1)) 
r) 
s) --解密 
t) SELECT AddressID,CONVERT(VARCHAR(MAX ),CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('certKey123'),AddressLine4,N'P@ssw0rd'))) DecryAddress 
u) FROM Person.Address 

5、短語(yǔ)加密：

復(fù)制代碼代碼如下:

 
a) --短語(yǔ)加密：該過(guò)程較為簡(jiǎn)單，只需要使用EncryptByPassPhrase函數(shù)，使用短語(yǔ)加密時(shí)，參考的數(shù)據(jù)航不可以變動(dòng)，否則解密失敗。 
b) SELECT *,AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID) 
c) FROM Person.Address 
d) 
e) --添加新列存放數(shù)據(jù)，注意，ENCRYPTBYPASSPHRASE函數(shù)返回的是VARBINARY類型 
f) ALTER TABLE Person.Address ADD AddressLine5 VARBINARY(256) 
g) 
h) --將數(shù)據(jù)更新，過(guò)程中使用P@ssw0rd和AddressID數(shù)據(jù)行當(dāng)成密碼短語(yǔ) 
i) 
j) UPDATE Person.Address 
k) SET AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID) 
l) 
m) SELECT * FROM Person.Address 

問(wèn)題二：如何保護(hù)數(shù)據(jù)庫(kù)對(duì)象定義，避免發(fā)生過(guò)渡暴露敏感信息？

一般的保護(hù)措施是在創(chuàng)建對(duì)象時(shí)使用WITH ENCRYPTION來(lái)把對(duì)象加密，這樣就無(wú)法查看定義。但是問(wèn)題是對(duì)于維護(hù)來(lái)說(shuō)就成了問(wèn)題，而且備份還原時(shí)這部分對(duì)象是會(huì)丟失的。

其中一個(gè)解決方法是把定義語(yǔ)句放到對(duì)象的【擴(kuò)展屬性】中保存，這樣能解決上面的問(wèn)題。

下面舉個(gè)例子:

復(fù)制代碼代碼如下:

 
--1、建立已加密的存儲(chǔ)過(guò)程 
USE AdventureWorks 
GO 
CREATE PROC test 
WITH ENCRYPTION 
AS 
SELECT SUSER_SNAME() , 
USER_NAME() 
GO 
--2、將上述定義內(nèi)容去除，利用短語(yǔ)加密搭配EncryptByPassPhrase函數(shù)加密，然后在用sys.sp_addextendedproperty存儲(chǔ)過(guò)程，指定一個(gè)擴(kuò)展名稱。 
USE AdventureWorks 
GO 
DECLARE @sql VARCHAR(MAX) 
SET @sql = 'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname(),user_name() GO' 

--3、將內(nèi)容加密后轉(zhuǎn)換成sql_variant數(shù)據(jù)類型 
DECLARE @bsql SQL_VARIANT 
SET @bsql = ( SELECT CONVERT(SQL_VARIANT, ENCRYPTBYPASSPHRASE('P@ssw0rd', 
CONVERT(VARCHAR(MAX), @sql))) 
) 

--4、新增到指定存儲(chǔ)過(guò)程的擴(kuò)展屬性中： 
EXEC sys.sp_addextendedproperty @name = N'test定義', @value = N'System.Byte[]', 
@level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'PROCEDURE', 
@level1name = N'test' 
GO 
EXEC sys.sp_addextendedproperty @name = N'代碼內(nèi)容', 
@value = N'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname(),user_name() GO', 
@level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'PROCEDURE', 
@level1name = N'test' 
GO 

--5、還原 
DECLARE @pwd VARCHAR(100)= 'P@ssw0rd' 
--密碼短語(yǔ) 

DECLARE @proc VARCHAR(100)= 'test' 
--存儲(chǔ)過(guò)程名 

DECLARE @exName NVARCHAR(100)= '代碼內(nèi)容' 
--擴(kuò)充屬性名 


--將原本結(jié)果查詢 
SELECT value 
FROM sys.all_objects AS sp 
INNER JOIN sys.extended_properties AS P ON P.major_id = sp.object_id 
AND P.minor_id = 0 
AND P.class = 1 
WHERE ( P.name = @exName ) 
AND ( ( sp.type = N'p' 
OR sp.type = N'rf' 
OR sp.type = 'pc' 
) 
AND ( sp.name = @proc 
AND SCHEMA_NAME(sp.schema_id) = N'dbo' 
) 
) 

問(wèn)題三、如何讓指定用戶可以對(duì)數(shù)據(jù)表進(jìn)行Truncate操作？

Truncate在對(duì)大表全刪除操作時(shí)，會(huì)明顯比Delete語(yǔ)句更快更有效，但是因?yàn)樗恍枰娣湃罩?，并且一定是全表刪除，所以造成數(shù)據(jù)的不可恢復(fù)性。也說(shuō)明了它的危險(xiǎn)性。
但是，執(zhí)行Truncate需要有表?yè)碛姓?、系統(tǒng)管理員、db_owner、db_ddladmin這些里面的其中一種高權(quán)限角色才能執(zhí)行。
對(duì)此，可以使用05之后的EXECUTE AS表達(dá)式來(lái)實(shí)現(xiàn)權(quán)限內(nèi)容的切換：

1. 切換登錄：EXECUTE AS LOGIN

2. 切換用戶：EXECUTE AS USER

3. 切換執(zhí)行權(quán)限：EXECUTE AS owner/'user name'，利用高用戶權(quán)限來(lái)執(zhí)行作業(yè)。此步驟可以在低權(quán)限實(shí)體下執(zhí)行高權(quán)限操作，也能避免安全性漏洞。

另外，只有EXECUTE AS Caller可以跨數(shù)據(jù)庫(kù)執(zhí)行，而其他方式進(jìn)行的權(quán)限切換僅限制于本數(shù)據(jù)庫(kù)。

注意：執(zhí)行EXECUTE AS USER模擬使用者切換時(shí)，需要先獲得被模擬用戶的授權(quán)。

可以使用REVERT來(lái)還原執(zhí)行內(nèi)容前的原始身份。

問(wèn)題四、如何獲取前端連接的信息，如IP地址和計(jì)算機(jī)名？

對(duì)于DBA工作或者某些特殊的應(yīng)用程序，需要獲取前端應(yīng)用的系統(tǒng)信息。而這些信息如果用用戶表來(lái)存儲(chǔ)，代價(jià)會(huì)比直接讀取數(shù)據(jù)庫(kù)系統(tǒng)信息要大。所以建議適當(dāng)讀取系統(tǒng)表：

在連接數(shù)據(jù)庫(kù)的session期間，都可以在master數(shù)據(jù)庫(kù)中找到session信息，但是從05開(kāi)始，有了很多DMV/DMF來(lái)實(shí)現(xiàn)這些功能：

l Master.dbo.sysprocesses或者master.sys.sysprocesses：提供執(zhí)行階段的SPID、計(jì)算機(jī)名、應(yīng)用程序名等。

l Sys.dm_exec_sessions：記錄每個(gè)session的基本信息，包括id、計(jì)算機(jī)名、程序名、應(yīng)用程序名等

l Sys.dm_exec_connections：記錄每個(gè)連接到SQLServer實(shí)例的前端信息，包括網(wǎng)絡(luò)位置、連接時(shí)間等等。

l select client_net_address 'Client IP Address',local_net_address 'SQL ServerIP Address',*

l from sys.dm_exec_connections

l where session_id=@@spid

在2005以后，建議使用DMV取代系統(tǒng)表。

問(wèn)題五、如何避免SQL注入的攻擊？

對(duì)于數(shù)據(jù)庫(kù)應(yīng)用程序，無(wú)論是那種DBMS，SQL注入都是一大隱患。

要避免SQL注入，應(yīng)該最起碼做到以下幾點(diǎn)：

1. 檢查輸入的數(shù)據(jù)，應(yīng)用程序不要相信用戶輸入的數(shù)據(jù)，必須經(jīng)過(guò)檢驗(yàn)后才能輸入數(shù)據(jù)庫(kù)。要排除%、--等特殊符號(hào)。

2. 避免果度暴露錯(cuò)誤信息。建議可以轉(zhuǎn)換成Windows事件或者是轉(zhuǎn)換成應(yīng)用程序內(nèi)部錯(cuò)誤信息。

3. 使用參數(shù)化查詢或者存儲(chǔ)過(guò)程

注意：
動(dòng)態(tài)SQL是導(dǎo)致SQL注入的主兇

作者：黃釗吉