数据加密

--示例一,使用证书加密数据. 

--建立测试数据表 
CREATE TABLE tb(ID int IDENTITY(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) 
  SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo1'),N'这是证书1加密的内容-liangCK');  --使用证书1加密 

INSERT tb(data) 
  SELECT ENCRYPTBYCERT(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 

--删除测试证书与数据表 
DROP CERTIFICATE Cert_Demo1; 
DROP CERTIFICATE Cert_Demo2; 
DROP TABLE tb; 
GO 

--示例二,使用对称密钥加密数据, 
--对称密钥又使用证书来加密. 

--创建测试数据表tb 
CREATE TABLE tb(ID int IDENTITY(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 BY CERTIFICATE Cert_Demo --使用Cert_Demo证书加密 
GO  

--要使用Sym_Demo对称密钥.必需使用OPEN SYMMETRIC KEY来打开它 

OPEN SYMMETRIC KEY Sym_Demo 
   DECRYPTION BY CERTIFICATE Cert_Demo 
      WITH PASSWORD=N'liangCK.123' 

--插入加密数据 

INSERT tb(data) 
  SELECT ENCRYPTBYKEY(KEY_GUID(N'Sym_Demo'),N'这是加密的数据,能显示出来吗?') 
   
--关闭密钥 
CLOSE SYMMETRIC KEY Sym_Demo 

--插入完加密数据,现在使用SELECT来查询一下数据 

SELECT * FROM tb  

GO 

--现在来解密此数据 

--同样,还是要先打开对称密钥 

OPEN SYMMETRIC KEY Sym_Demo 
   DECRYPTION BY CERTIFICATE Cert_Demo 
      WITH PASSWORD=N'liangCK.123' 

SELECT CONVERT(NVARCHAR(50),DECRYPTBYKEY(data)) --这里可见,数据已经解密出来了. 
FROM tb  

CLOSE SYMMETRIC KEY Sym_Demo 
GO 

--删除测试 
DROP SYMMETRIC KEY Sym_Demo 
DROP CERTIFICATE Cert_Demo 
DROP TABLE tb 

--示例三,还有一种方法加密数据更简单 
--就是使用EncryptByPassPhrase 

--建立测试数据表tb 

CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000)); 
GO 

INSERT tb(data) 
  SELECT EncryptByPassPhrase(N'这是密码,用来加密的',N'这是要加密的内容'); 
   
--解密 

SELECT CONVERT(NVARCHAR(50),DECRYPTBYPASSPHRASE(N'这是密码,用来加密的',data)) 
FROM tb  

GO 
DROP TABLE tb