SQL SERVER2005加密解密数据

一．数据库配置

１．为数据库创建数据库主密钥

脚本代码：

USE Northwind 
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO

　　数据库名    Northwind

    密码        P@ssw0rd

 

２．对数据库主密钥进行备份

脚本代码：

      USE Northwind
GO
BACKUP MASTER KEY 
    TO FILE = 'C:/DBFile/DMK.bak'
    ENCRYPTION BY PASSWORD = 'P@ssw0rd!@'
GO

数据库名    Northwind

    出力文件    C:/DBFile/DMK.bak

 

３．让SQL2005创建自签名的证书

脚本代码：

USE Northwind
GO
CREATE CERTIFICATE cert_TestCert1 
    ENCRYPTION BY PASSWORD = 'P@ssw0rd'
    WITH SUBJECT = 'TestCert1',
    START_DATE = '1/31/2008',
    EXPIRY_DATE = '1/31/2009'
GO

    证书名      cert_TestCert1

    密码        P@ssw0rd

    有效期间    1/31/2008 ～ 1/31/2009

 

４．备份导出证书和私钥

脚本代码：

BACKUP CERTIFICATE cert_TestCert1 
    TO FILE = 'c:/DBFile/TestCert1.cer' 
    WITH PRIVATE KEY 
        (DECRYPTION BY PASSWORD = 'P@ssw0rd' , 
         FILE = 'c:/DBFile/TestCert1_pvt' , 
         ENCRYPTION BY PASSWORD = 'Pa$w0rd')

    证书出力文件       c:/DBFile/TestCert1.cer

    证书密码           P@ssw0rd

    私钥密码           Pa$w0rd

 

５．使用证书加密、解密数据（测试是否配置成功）

脚本代码：

DECLARE @cleartext varbinary(200)
DECLARE @cipher varbinary(200)
SET @cleartext = CONVERT(varbinary(200), 'Test text string')
SET @cipher = EncryptByCert(Cert_ID('cert_TestCert1'), @cleartext)
SELECT @cipher
SELECT CONVERT(varchar(200), DecryptByCert(Cert_ID('cert_TestCert1'), @cipher, N'P@ssw0rd')) AS [ClearText]

    执行结果下图所视：

    

※ 第一行为加密后的数据，第二行为解密后的数据

 

６．对称密钥做成

    脚本代码

CREATE SYMMETRIC KEY TestSymmetricKey

      WITH ALGORITHM = TRIPLE_DES

      ENCRYPTION BY CERTIFICATE cert_TestCert22

    用证书对对称密钥加密

    对称密钥名   TestSymmetricKey

    证书名       cert_TestCert22

二．对加密数据的操作（证书加密）

1．测试表的做成

脚本代码：

      USE Northwind
CREATE TABLE dbo.EmpSalary(
    EmpID int, 
    Title nvarchar(50),
    Salary varbinary(500)
)
GO

   注：字段Salary为要加密的字段，要加密的字段必须为varbinary类型（二进制类型）

 

２．插入数据

    SQL文：

　　　　Insert into 

EmpSalary

values

　 ( 1,'CEO',

EncryptByCert(Cert_ID('cert_TestCert1'),

CONVERT(varbinary(200), '6000'))

            )

    注：要把字符串类型的数据先转换成二进制类型后再加密

 

３．更新数据

    SQL文：

        Update

EmpSalary

Set

Salary = 

EncryptByCert(Cert_ID('cert_TestCert1'),

CONVERT(varbinary(200), '6980'))

 

４．删除数据

SQL文：

        delete from EmpSalary

注：无特殊处理

 

５．抽出数据

SQL文：

       Select 

  EmpID,

  Title,

CONVERT(varchar(200), DecryptByCert(Cert_ID('cert_TestCert22'), Salary, N'P@ssw0rd')) Salary 

from EmpSalary

where

 CONVERT(varchar(200), DecryptByCert(Cert_ID('cert_TestCert22'), Salary, N'P@ssw0rd')) = '6000'

 

三．对加密数据的操作（对称密钥加密）

１．打开和关闭对称密钥

脚本代码

   OPEN SYMMETRIC KEY TestSymmetricKey DECRYPTION BY CERTIFICATE cert_TestCert22 with PASSWORD = 'P@ssw0rd'

   CLOSE SYMMETRIC KEY TestSymmetricKey

注：在使用对称密钥前打开密钥，使用结束后关闭

 

２．抽出数据

　　SQL文：

　　　　OPEN SYMMETRIC KEY TestSymmetricKey DECRYPTION BY CERTIFICATE cert_TestCert22 with PASSWORD = 'P@ssw0rd'

Select EmpID,Title, CONVERT(varchar(200), 

DecryptByKey(Salary)) Salary from EmpSalary

　　　　CLOSE SYMMETRIC KEY TestSymmetricKey

 

３．插入数据

　　SQL文：

        OPEN SYMMETRIC KEY TestSymmetricKey DECRYPTION BY CERTIFICATE cert_TestCert22 with PASSWORD = 'P@ssw0rd'

Insert into EmpSalary values (  1 ,'CEO',

     EncryptByKey(Key_GUID('TestSymmetricKey'), '8741'))

        CLOSE SYMMETRIC KEY TestSymmetricKey