Cómo cifrar una columna de datos
En este tema se muestran dos maneras de cifrar una columna de datos por medio del cifrado simétrico. En los ejemplos siguientes, el original del texto simple permanece en la tabla sin modificar. Esto conserva la funcionalidad de la base de datos de ejemplo AdventureWorks y le permite experimentar con la funcionalidad que se demuestra en estos ejemplos. En un entorno de implementación sólo se almacenan los datos cifrados.
[!NOTA]
No se admite el cifrado que usa AES en Windows XP o Windows Server 2000. Antes de ejecutar estos ejemplos de código en un sistema que no admita AES, se debe cambiar "AES_256" a "DES".
Ejemplos
A. Cifrado simétrico simple
En el ejemplo de código siguiente se muestra cómo cifrar una columna utilizando una clave simétrica.
USE AdventureWorks;
GO
--If there is no master key, create one now.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'
GO
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
USE [AdventureWorks];
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS 'Decrypted ID Number'
FROM HumanResources.Employee;
GO
B. Cifrado simétrico con un autenticador
En el ejemplo de código siguiente se muestra cómo cifrar una columna mediante un cifrado simétrico que incluye un autenticador.
USE AdventureWorks;
--If there is no master key, create one now.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE Sales09
WITH SUBJECT = 'Customer Credit Card Numbers';
GO
CREATE SYMMETRIC KEY CreditCards_Key11
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Sales09;
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
ADD CardNumber_Encrypted varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
, CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
, CreditCardID)));
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
GO
-- Now list the original card number, the encrypted card number,
-- and the decrypted ciphertext. If the decryption worked,
-- the original number will match the decrypted number.
SELECT CardNumber, CardNumber_Encrypted
AS 'Encrypted card number', CONVERT(nvarchar,
DecryptByKey(CardNumber_Encrypted, 1 ,
HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))
AS 'Decrypted card number' FROM Sales.CreditCard;
GO
Vea también