主頁 > 知識庫 > T-SQL問題解決集錦 數(shù)據(jù)加解密全集

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

熱門標(biāo)簽:南京電銷外呼系統(tǒng)運(yùn)營商 山西語音外呼系統(tǒng)價(jià)格 溫州語音外呼系統(tǒng)代理 重慶防封電銷機(jī)器人供應(yīng)商 智能語音外呼系統(tǒng)哪個(gè)牌子好 威海智能語音外呼系統(tǒng) 北京辦理400電話多少 西安青牛防封電銷卡 400電話申請需要開戶費(fèi)嗎
以下代碼已經(jīng)在SQLServer2008上的示例數(shù)據(jù)庫測試通過

問題一:如何為數(shù)據(jù)進(jìn)行加密與解密,避免使用者竊取機(jī)密數(shù)據(jù)?
對于一些敏感數(shù)據(jù),如密碼、卡號,一般不能使用正常數(shù)值來存儲。否則會有安全隱患。以往的加密解密都有前端應(yīng)用程序來輔助完成。而數(shù)據(jù)庫一般只能加密不能解密。

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

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

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

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

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

5、 利用密碼短語方式:
搭配EncryptBypassPhrase進(jìn)行加密,使用DecryptByPassPhrase函數(shù)來解密??梢允褂糜幸饬x的短語或其他數(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、對稱密鑰:
復(fù)制代碼 代碼如下:

a) --創(chuàng)建對稱密鑰
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ù)都會是null值。而且需要搭配Key_GUID函數(shù)來使用
h) --打開對稱密鑰
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) --檢查加密后長度,利用datalength()函數(shù)
o) SELECT DATALENGTH(ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1)))
p) FROM Person.Address
q) GO
r) --把加密后數(shù)據(jù)更新到原來另外的列上
s) UPDATE Person.Address
t) SET AddressLine2=ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(max),AddressLine1))
u) --解密:解密過程同樣需要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、非對稱密鑰:
復(fù)制代碼 代碼如下:

a) --非對稱密鑰使用兩種不同的密鑰,所以加密是是不需要輸入密碼驗(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) --添加新列存儲加密后的數(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) --解密:此過程一定要使用密碼來解密,此處的類型要與加密時(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、證書加密:
復(fù)制代碼 代碼如下:

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) --添加新列存放加密數(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、短語加密:
復(fù)制代碼 代碼如下:

a) --短語加密:該過程較為簡單,只需要使用EncryptByPassPhrase函數(shù),使用短語加密時(shí),參考的數(shù)據(jù)航不可以變動,否則解密失敗。
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ù)更新,過程中使用P@ssw0rd和AddressID數(shù)據(jù)行當(dāng)成密碼短語
i)
j) UPDATE Person.Address
k) SET AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID)
l)
m) SELECT * FROM Person.Address


問題二:如何保護(hù)數(shù)據(jù)庫對象定義,避免發(fā)生過渡暴露敏感信息?

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

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

下面舉個(gè)例子:
復(fù)制代碼 代碼如下:

--1、建立已加密的存儲過程
USE AdventureWorks
GO
CREATE PROC test
WITH ENCRYPTION
AS
SELECT SUSER_SNAME() ,
USER_NAME()
GO
--2、將上述定義內(nèi)容去除,利用短語加密搭配EncryptByPassPhrase函數(shù)加密,然后在用sys.sp_addextendedproperty存儲過程,指定一個(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、新增到指定存儲過程的擴(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'
--密碼短語

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

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'
)
)


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

Truncate在對大表全刪除操作時(shí),會明顯比Delete語句更快更有效,但是因?yàn)樗恍枰娣湃罩荆⑶乙欢ㄊ侨韯h除,所以造成數(shù)據(jù)的不可恢復(fù)性。也說明了它的危險(xiǎn)性。
但是,執(zhí)行Truncate需要有表擁有者、系統(tǒng)管理員、db_owner、db_ddladmin這些里面的其中一種高權(quán)限角色才能執(zhí)行。
對此,可以使用05之后的EXECUTE AS表達(dá)式來實(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)限來執(zhí)行作業(yè)。此步驟可以在低權(quán)限實(shí)體下執(zhí)行高權(quán)限操作,也能避免安全性漏洞。

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

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

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


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

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

在連接數(shù)據(jù)庫的session期間,都可以在master數(shù)據(jù)庫中找到session信息,但是從05開始,有了很多DMV/DMF來實(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)表。

問題五、如何避免SQL注入的攻擊?

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

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

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

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

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

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

作者: 黃釗吉

標(biāo)簽:河源 金昌 新余 貸款群呼 黃山 宜春 中衛(wèi) 濟(jì)寧

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《T-SQL問題解決集錦 數(shù)據(jù)加解密全集》,本文關(guān)鍵詞  T-SQL,問題,解決,集錦,數(shù)據(jù),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《T-SQL問題解決集錦 數(shù)據(jù)加解密全集》相關(guān)的同類信息!
  • 本頁收集關(guān)于T-SQL問題解決集錦 數(shù)據(jù)加解密全集的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章