Criptografar uma coluna de dados

Este tópico descreve como criptografar uma coluna de dados usando a criptografia simétrica no SQL Server 2012 usando o Transact-SQL.

Neste tópico

• Antes de começar:

  Segurança

• Para criptografar uma coluna de dados usando o Transact-SQL

Antes de começar

Segurança

Permissões

As permissões a seguir são necessárias para executar as etapas abaixo:

• Permissão CONTROL no banco de dados.

• Permissão CREATE CERTIFICATE no banco de dados. Somente logons do Windows, logons do SQL Server e funções de aplicativo podem possuir certificados. Grupos e funções não podem possuir certificados.

• Permissão ALTER na tabela.

• Alguma permissão na chave, e não deve ter a permissão VIEW DEFINITION negada.

[Top]

Usando Transact-SQL

Para criptografar uma coluna de dados usando uma criptografia simétrica simples

1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de Dados.

2. Na barra Padrão, clique em Nova Consulta.

3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.

   USE AdventureWorks2012;
   --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
   

Para criptografar uma coluna de dados usando a criptografia simétrica que inclui um autenticador

1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de Dados.

2. Na barra Padrão, clique em Nova Consulta.

3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.

   USE AdventureWorks2012;
   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 [AdventureWorks2012];
   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
   

Para obter mais informações, consulte o seguinte:

• CREATE CERTIFICATE (Transact-SQL)

• CREATE SYMMETRIC KEY (Transact-SQL)

• ALTER TABLE (Transact-SQL)

• OPEN SYMMETRIC KEY (Transact-SQL)

[Top]