亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

T-SQL問題解決集錦——數據加解密

系統 2215 0
原文: T-SQL問題解決集錦——數據加解密

以下代碼已經在SQLServer2008上的示例數據庫測試通過

問題一:如何為數據進行加密與解密,避免使用者竊取機密數據?

?????? 對于一些敏感數據,如密碼、卡號,一般不能使用正常數值來存儲。否則會有安全隱患。以往的加密解密都有前端應用程序來輔助完成。而數據庫一般只能加密不能解密。

?????? 從2005開始提供了數據庫層面的數據加密與解密。其實現方式主要有以下:

1、?利用CONVERT改變編碼方式:

利用該函數把文字或數據轉換成VARBINARY。但該方式不具備保護數據的能力,僅避免瀏覽數據的過程中能直接看到敏感數據的作用。

2、?利用對稱密鑰:

搭配EncryptByKey進行數據加密。使用DecryptByKey函數進行解密。這種方式比較適合大數據量。因為對稱密鑰的過程好用資源較少。

3、?利用非對稱密鑰:

搭配EncryptByAsymKey進行數據加密。使用DecryptByAsymKey函數進行解密。用于更高安全級別的加解密數據。因為耗用資源叫多。

4、?利用憑證的方式:

搭配EncryptByCert進行加密和DecryptByCert函數進行解密。比較類似非對稱密鑰。

5、?利用密碼短語方式:

搭配EncryptBypassPhrase進行加密,使用DecryptByPassPhrase函數來解密??梢允褂糜幸饬x的短語或其他數據行,當成加密、解密的關鍵字,比較適合一般的數據加解密。

案例:

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'

                  )

            )


          




T-SQL問題解決集錦——數據加解密


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦?。。?/p>

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 伊人狼人视频 | 男人看的网址 | 日本亚洲高清 | 亚洲国产精品一区二区不卡 | 亚洲国产精品综合久久2007 | 中文字幕最新中文字幕中文字幕 | 鲁丝一区二区三区不属 | 国产精品女仆装在线播放 | 波多野结衣与公中出中文字幕 | 中国一级特黄大片毛片 | 国产区一区二区三 | 欧美在线观看视频 | 色综合久久88色综合天天小说 | 91精品久久久久久久久久小网站 | 久久99久久99精品免观看麻豆 | 久久美剧| 亚洲国产精品高清在线一区 | 成人a视频片在线观看免费 成人a视频在线观看 | 精品欧美成人bd高清在线观看 | 青青青国产免费全部免费观看 | 国产毛片在线 | 国产啪爱视频精品免视 | 精品日韩在线视频一区二区三区 | 欧洲成人爽视频在线观看 | 97影院理论片 | 国产一区二区三区不卡免费观看 | 亚洲一区二区三区精品影院 | 亚洲精品综合一二三区在线 | 成人在线短视频 | 国产精品亚洲一区二区在线观看 | 国产精品乱码在线观看 | 污污在线网站 | 资源站在线| 精品久久香蕉国产线看观看亚洲 | 成人一区二区免费中文字幕 | 亚洲成人18 | 四虎影视永久免费观看网址 | 欧美成人另类bbw | 四虎影视永久免费观看地址 | 国产成人青青热久免费精品 | 中国毛片免费看 |