T-SQL問題解決集錦——資料加解密

發糞塗牆發表於2012-06-18
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'
                  )
            )




相關文章