原文:
T-SQL問題解決集錦——數據加解密
以下代碼已經在SQLServer2008上的示例數據庫測試通過
問題一：如何為數據進行加密與解密，避免使用者竊取機密數據？
?????? 對于一些敏感數據，如密碼、卡號，一般不能使用正常數值來存儲。否則會有安全隱患。以往的加密解密都有前端應用程序來輔助完成。而數據庫一般只能加密不能解密。
?????? 從2005開始提供了數據庫層面的數據加密與解密。其實現方式主要有以下：
1、?利用CONVERT改變編碼方式：
利用該函數把文字或數據轉換成VARBINARY。但該方式不具備保護數據的能力，僅避免瀏覽數據的過程中能直接看到敏感數據的作用。
2、?利用對稱密鑰：
搭配EncryptByKey進行數據加密。使用DecryptByKey函數進行解密。這種方式比較適合大數據量。因為對稱密鑰的過程好用資源較少。
3、?利用非對稱密鑰：
搭配EncryptByAsymKey進行數據加密。使用DecryptByAsymKey函數進行解密。用于更高安全級別的加解密數據。因為耗用資源叫多。
4、?利用憑證的方式：
搭配EncryptByCert進行加密和DecryptByCert函數進行解密。比較類似非對稱密鑰。
5、?利用密碼短語方式：
搭配EncryptBypassPhrase進行加密，使用DecryptByPassPhrase函數來解密。可以使用有意義的短語或其他數據行，當成加密、解密的關鍵字，比較適合一般的數據加解密。
案例：
1、?Convert方式：
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) --使用轉換函數把數據轉換成varbinary，改變編碼方式。
s) SELECT * ,
t) CONVERT(VARBINARY(512), userSalary)
u) FROM test
v) --把數據轉換成int，可以恢復原有編碼方式
w) SELECT * ,
x) CONVERT(INT, userSalary)
y) FROM test
2、對稱密鑰:
a) --創建對稱密鑰
b) USE AdventureWorks
c) GO
d) CREATE SYMMETRIC KEY SymKey123
e) WITH ALGORITHM=TRIPLE_DES ENCRYPTION BY PASSWORD='P@ssw0rd'
f) GO
g) --注意事項:在啟用時,需要先OPEN SYMMETRIC KEY 搭配密鑰密碼，否則所產生的數據都會是null值。而且需要搭配Key_GUID函數來使用
h) --打開對稱密鑰
i) OPEN SYMMETRIC KEY SymKey123 DECRYPTION BY PASSWORD='P@ssw0rd';
j) --進行數據加密
k) SELECT * ,ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(max),AddressLine1))
l) FROM Person.Address
m)
n) --檢查加密后長度，利用datalength()函數
o) SELECT DATALENGTH(ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1)))
p) FROM Person.Address
q) GO
r) --把加密后數據更新到原來另外的列上
s) UPDATE Person.Address
t) SET AddressLine2=ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(max),AddressLine1))
u) --解密:解密過程同樣需要OPEN SYMMETRIC KEY ,且需要利用DECRYPTBYKEY 和CONVERT函數
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、非對稱密鑰：
a) --非對稱密鑰使用兩種不同的密鑰，所以加密是是不需要輸入密碼驗證，但解密時就需要
b) USE AdventureWorks
c) GO
d) CREATE ASYMMETRIC KEY AsymKey123 WITH ALGORITHM=RSA_2048 ENCRYPTION BY PASSWORD='P@ssw0rd';
e) GO
f)
g) --添加新列存儲加密后的數據
h) ALTER TABLE Person.Address ADD AddressLine3 nvarchar(MAX)
i) GO
j) --進行加密
k) SELECT *,ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1))
l) FROM Person.Address
m) GO
n)
o) --把數據更新到一個新列
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) --解密：此過程一定要使用密碼來解密，此處的類型要與加密時相同，比如加密時用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、證書加密：
a) --證書加密：首先建立證書（certificate）
b) CREATE CERTIFICATE certKey123--證書名
c) ENCRYPTION BY PASSWORD='P@ssw0rd'--密碼
d) WITH SUBJECT='Address Certificate',--證書描述
e) START_DATE='2012/06/18',--證書生效日期
f) EXPIRY_DATE='2013/06/18' ;--證書到期日
g) GO
h) --利用證書加密
i) SELECT *,ENCRYPTBYCERT(CERT_ID('certKey123'),CONVERT (VARCHAR(MAX ),AddressLine1)) cyberAddress
j) FROM Person.Address
k)
l) --添加新列存放加密數據
m) ALTER TABLE Person.Address ADD AddressLine4 Nvarchar(MAX )
n)
o) --把加密后數據放到新列
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、短語加密：
a) --短語加密：該過程較為簡單，只需要使用EncryptByPassPhrase函數，使用短語加密時，參考的數據航不可以變動，否則解密失敗。
b) SELECT *,AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID)
c) FROM Person.Address
d)
e) --添加新列存放數據，注意，ENCRYPTBYPASSPHRASE函數返回的是VARBINARY類型
f) ALTER TABLE Person.Address ADD AddressLine5 VARBINARY(256)
g)
h) --將數據更新，過程中使用P@ssw0rd和AddressID數據行當成密碼短語
i)
j) UPDATE Person.Address
k) SET AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID)
l)
m) SELECT * FROM Person.Address
問題二：如何保護數據庫對象定義，避免發生過渡暴露敏感信息？
?????? 一般的保護措施是在創建對象時使用WITH ENCRYPTION來把對象加密，這樣就無法查看定義。但是問題是對于維護來說就成了問題，而且備份還原時這部分對象是會丟失的。
?????? 其中一個解決方法是把定義語句放到對象的【擴展屬性】中保存，這樣能解決上面的問題。
下面舉個例子:
--1、建立已加密的存儲過程
USE AdventureWorks
GO
CREATE PROC test
WITH ENCRYPTION
AS
SELECT SUSER_SNAME() ,
USER_NAME()
GO
--2、將上述定義內容去除，利用短語加密搭配EncryptByPassPhrase函數加密，然后在用sys.sp_addextendedproperty存儲過程，指定一個擴展名稱。
USE AdventureWorks
GO
DECLARE @sql VARCHAR(MAX)
SET @sql = 'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname(),user_name() GO'
--3、將內容加密后轉換成sql_variant數據類型
DECLARE @bsql SQL_VARIANT
SET @bsql = ( SELECT CONVERT(SQL_VARIANT, ENCRYPTBYPASSPHRASE('P@ssw0rd',
CONVERT(VARCHAR(MAX), @sql)))
)
--4、新增到指定存儲過程的擴展屬性中：
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'代碼內容',
@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'
--密碼短語
DECLARE @proc VARCHAR(100)= 'test'
--存儲過程名
DECLARE @exName NVARCHAR(100)= '代碼內容'
--擴充屬性名
--將原本結果查詢
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'
)
)
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
