SQL 2005加密数据方法
--Author: liangCK 小梁
--示例一,使用证书加密数据.
--建立测试数据表
CREATE TABLE tb(ID intIDENTITY(1,1),data varbinary(8000));
GO
--建立证书一,该证书使用数据库主密钥来加密
CREATE CERTIFICATE Cert_Demo1
WITH
SUBJECT=N'cert1 encryption by database master key',
START_DATE='2008-01-01',
EXPIRY_DATE='2008-12-31'
GO
--建立证书二,该证书使用密码来加密
CREATE CERTIFICATE Cert_Demo2
ENCRYPTION BY PASSWORD='liangCK.123'
WITH
SUBJECT=N'cert1 encrption by password',
START_DATE='2008-01-01',
EXPIRY_DATE='2008-12-31'
GO
--此时,两个证书已经建立完,现在可以用这两个证书来对数据加密
--在对表tb做INSERT时,使用ENCRYPTBYCERT加密
INSERT tb(data)
SELECTENCRYPTBYCERT(CERT_ID(N'Cert_Demo1'),N'这是证书1加密的内容-liangCK'); --使用证书1加密
INSERT tb(data)
SELECTENCRYPTBYCERT(CERT_ID(N'Cert_Demo2'),N'这是证书2加密的内容-liangCK'); --使用证书2加密
--ok.现在已经对数据加密保证了.现在我们SELECT看看
SELECT * FROM tb ;
--现在对内容进行解密显示.
--解密时,使用DECRYPTBYCERT
SELECT证书1解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo1'),data)),
--使用证书2解密时,要指定DECRYPTBYCERT的第三个参数,
--因为在创建时,指定了ENCRYPTION BY PASSWORD.
--所以这里要通过这个密码来解密.否则解密失败
证书2解密
=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo2'),data,N'liangCK.123'))
FROM tb ;
--我们可以看到,因为第2条记录是证书2加密的.所以使用证书1将无法解密.所以返回NULL
/*
证书1解密 证书2解密
-------------------------------------------------- --------------------------------------------------
这是证书1加密的内容-liangCK NULL
NULL 这是证书2加密的内容-liangCK
(2 行受影响)
*/
GO
--删除测试证书与数据表
DROPCERTIFICATE Cert_Demo1;
DROPCERTIFICATE Cert_Demo2;
DROP TABLE tb;
GO
--示例二,使用对称密钥加密数据,
--对称密钥又使用证书来加密.
--创建测试数据表tb
CREATE TABLE tb(ID intIDENTITY(1,1),data varbinary(8000));
GO
--建立证书,该证书用于加密对称密钥.
CREATE CERTIFICATE Cert_Demo
ENCRYPTION BY PASSWORD=N'liangCK.123'
WITH
SUBJECT=N'cert encryption by password',
START_DATE='2008-01-01',
EXPIRY_DATE='2008-12-31'
GO
--建立对称密钥
CREATE SYMMETRIC KEY Sym_Demo
WITH
ALGORITHM=DES --使用DES加密算法
ENCRYPTION BYCERTIFICATE Cert_Demo --使用Cert_Demo证书加密
GO
--要使用Sym_Demo对称密钥.必需使用OPEN SYMMETRIC KEY来打开它
OPEN SYMMETRIC KEY Sym_Demo
DECRYPTION BYCERTIFICATE Cert_Demo
WITH PASSWORD=N'liangCK.123'
--插入加密数据
INSERT tb(data)
SELECTENCRYPTBYKEY(KEY_GUID(N'Sym_Demo'),N'这是加密的数据,能显示出来吗?')
--关闭密钥
CLOSE SYMMETRIC KEY Sym_Demo
--插入完加密数据,现在使用SELECT来查询一下数据
SELECT * FROM tb
GO
--现在来解密此数据
--同样,还是要先打开对称密钥
OPEN SYMMETRICKEY Sym_Demo
DECRYPTION BYCERTIFICATE Cert_Demo
WITH PASSWORD=N'liangCK.123'
SELECTCONVERT(NVARCHAR(50),DECRYPTBYKEY(data)) --这里可见,数据已经解密出来了.
FROM tb
CLOSE SYMMETRICKEY Sym_Demo
GO
--删除测试
DROP SYMMETRICKEY Sym_Demo
DROPCERTIFICATE Cert_Demo
DROP TABLE tb
--示例三,还有一种方法加密数据更简单
--就是使用EncryptByPassPhrase
--建立测试数据表tb
CREATE TABLE tb(ID intIDENTITY(1,1),data varbinary(8000));
GO
INSERT tb(data)
SELECT EncryptByPassPhrase(N'这是密码,用来加密的',N'这是要加密的内容');
--解密
SELECTCONVERT(NVARCHAR(50),DECRYPTBYPASSPHRASE(N'这是密码,用来加密的',data))
FROM tb
GO
DROP TABLE tb
