详述SQL Server 2005对称密钥加密技术-技术开发专区

详述SQL Server 2005对称密钥加密技术

作者：Michael Coles 编辑：赵建凯 2007-05-23 14:54

    2. 证书

    2.1 创建证书

    当你配置好服务主密钥和数据库主密钥后，你就可以创建证书了。SQL Server可以创建自签名的X.509证书。使用CREATE CERTIFICATE语句来创建证书：

CREATE CERTIFICATE certificate_name [ AUTHORIZATION user_name ]
    { FROM <existing_keys> | <generate_new_keys> }
    [ ACTIVE FOR BEGIN_DIALOG =  { ON | OFF } ]

<existing_keys> ::=
    ASSEMBLY assembly_name
    | {
        [ EXECUTABLE ] FILE = 'path_to_file'
        [ WITH PRIVATE KEY ( <private_key_options> ) ]
      }

<generate_new_keys> ::=
    [ ENCRYPTION BY PASSWORD = 'password']
    WITH SUBJECT = 'certificate_subject_name'
    [ , <date_options> [ ,...n ] ]

<private_key_options> ::=
    FILE = 'path_to_private_key'
      [ , DECRYPTION BY PASSWORD = 'password' ]
      [ , ENCRYPTION BY PASSWORD = 'password' ]

<date_options> ::=
START_DATE = 'mm/dd/yyyy' | EXPIRY_DATE = 'mm/dd/yyyy'

CREATE CERTIFICATE语句有这么多的选项，幸运的是，大多数时候只用到很少的选项。下面的语句创建一个使用密码来保护的证书：

CREATE CERTIFICATE TestCertificate
   ENCRYPTION BY PASSWORD = 'thisIsAP@$$w0rd'
   WITH SUBJECT = 'This is a test certificate',
   START_DATE = '1/1/2006',
   EXPIRY_DATE = '12/31/2008';

    如果不使用ENCRYPTION BY PASSWORD子句，证书将使用数据库主密钥来保护。如果不指定START_DATE子句，将使用执行此命令的日期来填写证书的Start Date字段。

    除了CREATE CERTIFICATE，SQL Server还提供了DROP CERTIFICATE,ALTER CERTIFICATE,BACKUP CERTIFICATE语句来管理证书。

    注：不使用RESTORE语句来恢复证书。使用CREATE CERTIFICATE语句来恢复已经备份的证书。

    2.2 使用证书来加密和解密

    通过内置的函数EncryptByCert，DecryptByCert和Cert_ID，可以使用证书来加密和解密数据。Cert_ID函数得到指定名字的证书的ID。格式为：

Cert_ID ( 'cert_name' )
cert_name为证书的名字。
EncryptByCert函数需要证书ID，格式为：
EncryptByCert ( certificate_ID , { 'cleartext' | @cleartext } )

certificate_ID为通过Cert_ID函数得到的证书ID，cleartext为要加密的明文。类型为 nvarchar、char、varchar、binary、varbinary 或 nchar。EncryptByCert函数的返回值是最大大小为 8,000 个字节的 varbinary。

DecryptByCert语句用来解密先前使用证书加密的数据。格式为：

DecryptByCert (certificate_ID, 
{ 'ciphertext' | @ciphertext } 
[ , { 'cert_password' | @cert_password } ] 
)

同样，certificate_ID为通过Cert_ID函数得到的证书ID，ciphertext是加密后的数据。如果创建证书时使用了ENCRYPT BY PASSWORD，则cert_password是当时创建时设定的密码，如果没有使用ENCRYPT BY PASSWORD，那这里也不需要cert_password。

下面的脚本创建数据库主密钥，创建一个测试证书，使用证书进行加密、解密。

-- Sample T-SQL Script to demonstrate Certificate Encryption 

-- Use the AdventureWorks database 
USE AdventureWorks; 

-- Create a Database Master Key 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd'; 

-- Create a Temp Table 
CREATE TABLE Person.#Temp 
(ContactID INT PRIMARY KEY, 
FirstName NVARCHAR(200), 
MiddleName NVARCHAR(200), 
LastName NVARCHAR(200), 
eFirstName VARBINARY(200), 
eMiddleName VARBINARY(200), 
eLastName VARBINARY(200)); 

-- Create a Test Certificate, encrypted by the DMK 
CREATE CERTIFICATE TestCertificate 
WITH SUBJECT = 'Adventureworks Test Certificate', 
EXPIRY_DATE = '10/31/2009'; 

-- EncryptByCert demonstration encrypts 100 names from the Person.Contact table 
INSERT 
INTO Person.#Temp (ContactID, eFirstName, eMiddleName, eLastName) 
SELECT ContactID, EncryptByCert(Cert_ID('TestCertificate'), FirstName), 
EncryptByCert(Cert_ID('TestCertificate'), MiddleName), 
EncryptByCert(Cert_ID('TestCertificate'), LastName) 
FROM Person.Contact 
WHERE ContactID <= 100; 

-- DecryptByCert demonstration decrypts the previously encrypted data 
UPDATE Person.#Temp 
SET FirstName = DecryptByCert(Cert_ID('TestCertificate'), eFirstName), 
MiddleName = DecryptByCert(Cert_ID('TestCertificate'), eMiddleName), 
LastName = DecryptByCert(Cert_ID('TestCertificate'), eLastName); 

-- View the results 
SELECT * 
FROM Person.#Temp; 

-- Clean up work: drop temp table, test certificate and master key 
DROP TABLE Person.#Temp; 
DROP CERTIFICATE TestCertificate; 
DROP MASTER KEY;

第1页：服务主密钥和数据库主密钥第2页：证书第3页：对称密钥

关注我们