T-SQL成績處理集錦 數據加解密選集。本站提示廣大學習愛好者：（T-SQL成績處理集錦 數據加解密選集）文章只能為提供參考，不一定能成為您想要的結果。以下是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'
)
)
成績3、若何讓指定用戶可以對數據表停止Truncate操作？
Truncate在對年夜表全刪除操作時，會顯著比Delete語句更快更有用，然則由於它不須要寄存日記，而且必定是全表刪除，所以形成數據的弗成恢復性。也解釋了它的風險性。
然則，履行Truncate須要有表具有者、體系治理員、db_owner、db_ddladmin這些外面的個中一種高權限腳色能力履行。
對此，可使用05以後的EXECUTE AS表達式來完成權限內容的切換：
1. 切換登錄：EXECUTE AS LOGIN
2. 切換用戶：EXECUTE AS USER
3. 切換履行權限：EXECUTE AS owner/'user name'，應用高用戶權限來履行功課。此步調可以在低權限實體下履行高權限操作，也能防止平安性破綻。
別的，只要EXECUTE AS Caller可以跨數據庫履行，而其他方法停止的權限切換僅限制於本數據庫。
留意：履行EXECUTE AS USER模仿應用者切換時，須要先取得被模仿用戶的受權。
可使用REVERT來復原履行內容前的原始身份。
成績4、若何獲得前端銜接的信息，如IP地址和盤算機名？
關於DBA任務或許某些特別的運用法式，須要獲得前端運用的體系信息。而這些信息假如用用戶表來存儲，價值會比直接讀取數據庫體系信息要年夜。所以建議恰當讀取體系表：
在銜接數據庫的session時代，都可以在master數據庫中找到session信息，然則從05開端，有了許多DMV/DMF來完成這些功效：
l Master.dbo.sysprocesses或許master.sys.sysprocesses：供給履行階段的SPID、盤算機名、運用法式名等。
l Sys.dm_exec_sessions：記載每一個session的根本信息，包含id、盤算機名、法式名、運用法式名等
l Sys.dm_exec_connections：記載每一個銜接到SQLServer實例的前端信息，包含收集地位、銜接時光等等。
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代替體系表。
成績5、若何防止SQL注入的進擊？
關於數據庫運用法式，不管是那種DBMS，SQL注入都是一年夜隱患。
要防止SQL注入，應當最最少做到以下幾點：
1. 檢討輸出的數據，運用法式不要信任用戶輸出的數據，必需經由磨練後能力輸出數據庫。要消除%、--等特別符號。
2. 防止果度裸露毛病信息。建議可以轉換成Windows事宜或許是轉換成運用法式外部毛病信息。
3. 應用參數化查詢或許存儲進程
留意：
靜態SQL是招致SQL注入的主凶
作者： 黃钊吉
