T-SQL问题解决集锦——数据加解密

以下代码已经在SQLServer2008上的示例 数据库测试通过

问题一：如何为数据进行 加密与解密，避免使用者窃取机密数据？

对于一些敏感数据，如密码、卡号，一般不能使用正常数值来存储。否则会有安全隐患。以往的加密解密都有前端应用程序来辅助完成。而数据库一般只能加密不能解密。 www.2cto.com

从2005开始提供了数据库层面的数据加密与解密。其实现方式主要有以下：

1、 利用CONVERT改变编码方式：

利用该函数把文字或数据转换成VARBINARY。但该方式不具备保护数据的能力，仅避免浏览数据的过程中能直接看到敏感数据的作用。

2、 利用对称密钥：

搭配EncryptByKey进行数据加密。使用DecryptByKey函数进行解密。这种方式比较适合大数据量。因为对称密钥的过程好用资源较少。

3、 利用非对称密钥：

搭配EncryptByAsymKey进行数据加密。使用DecryptByAsymKey函数进行解密。用于更高安全级别的加解密数据。因为耗用资源叫多。

4、 利用凭证的方式：

搭配EncryptByCert进行加密和DecryptByCert函数进行解密。比较类似非对称密钥。

5、 利用密码短语方式：

搭配EncryptBypassPhrase进行加密，使用DecryptByPassPhrase函数来解密。可以使用有意义的短语或其他数据行，当成加密、解密的关键字，比较适合一般的数据加解密。

案例：

1、 Convert方式：

[sql]

a) USE tempdb

b) GO

c) CREATE TABLE test

d) ( www.2cto.com

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、对称密钥:

[sql]

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 www.2cto.com

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、非对称密钥：

[sql]

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)) www.2cto.com

r)

s)