DECRYPTBYKEYAUTOCERT (Transact-SQL)
SQL Server 2008
Permet le déchiffrement à l'aide d'une clé symétrique qui est automatiquement déchiffrée avec un certificat.
L'exemple suivant montre comment utiliser DecryptByKeyAutoCert pour simplifier le code qui effectue le déchiffrement. Ce code doit être exécuté sur une copie nouvellement installée de la base de données AdventureWorks.
--Create the keys and certificate.
USE AdventureWorks;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Sammamish HR',
EXPIRY_DATE = '10/31/2009';
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
----Add a column of encrypted data.
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037 ;
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber
= EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO
--
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--There are two ways to decrypt the stored data.
--
--OPTION ONE, using DecryptByKey()
--1. Open the symmetric key
--2. Decrypt the data
--3. Close the symmetric key
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS 'Decrypted ID Number'
FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--OPTION TWO, using DecryptByKeyAutoCert()
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKeyAutoCert ( cert_ID('HumanResources037') , NULL ,EncryptedNationalIDNumber))
AS 'Decrypted ID Number'
FROM HumanResources.Employee;
