Criptografar uma coluna de dadosEncrypt a Column of Data

  • 2 minutos para ler

APLICA-SE A: SimSQL Server SimBanco de Dados SQL do Azure NãoAzure Synapse Analytics (SQL DW) NãoParallel Data Warehouse APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

Este artigo descreve como criptografar uma coluna de dados usando uma criptografia simétrica no SQL Server 2019 (15.x)SQL Server 2019 (15.x) usando Transact-SQLTransact-SQL.This article describes how to encrypt a column of data by using symmetric encryption in SQL Server 2019 (15.x)SQL Server 2019 (15.x) using Transact-SQLTransact-SQL. Às vezes, isso é conhecido como criptografia no nível de coluna, ou criptografia no nível da célula.This is sometimes known as column-level encryption, or cell-level encryption.

SegurançaSecurity

PermissõesPermissions

As permissões a seguir são necessárias para executar as etapas abaixo:The following permissions are necessary to perform the steps below:

  • Permissão CONTROL no banco de dados.CONTROL permission on the database.

  • Permissão CREATE CERTIFICATE no banco de dados.CREATE CERTIFICATE permission on the database. Somente logons do Windows, logons do SQL Server e funções de aplicativo podem possuir certificados.Only Windows logins, SQL Server logins, and application roles can own certificates. Grupos e funções não podem possuir certificados.Groups and roles cannot own certificates.

  • Permissão ALTER na tabela.ALTER permission on the table.

  • Alguma permissão na chave, e não deve ter a permissão VIEW DEFINITION negada.Some permission on the key and must not have been denied VIEW DEFINITION permission.

Usando o Transact-SQLUsing Transact-SQL

Para usar os exemplos a seguir, é necessário ter uma chave mestra de banco de dados.To use the following examples, you must have a database master key. Se seu banco de dados ainda não tiver uma chave mestra de banco de dados, crie uma executando a instrução a seguir e fornecendo sua senha:If your database does not already have a database master key, create one by executing the following statement providing your password:

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = '<some strong password>';

Sempre faça backup da sua chave mestra de banco de dados.Always back up your database master key. Para obter mais informações sobre chaves mestras de banco de dados, consulte CREATE MASTER KEY (Transact-SQL).For more information on database master keys, see CREATE MASTER KEY (Transact-SQL).

Para criptografar uma coluna de dados usando a criptografia simétrica que inclui um autenticadorTo encrypt a column of data using symmetric encryption that includes an authenticator

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de DadosDatabase Engine.In Object Explorer, connect to an instance of Mecanismo de Banco de DadosDatabase Engine.

  2. Na barra Padrão, clique em Nova Consulta.On the Standard bar, click New Query.

  3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
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(160);   
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 uma criptografia simétrica simplesTo encrypt a column of data using a simple symmetric encryption

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de DadosDatabase Engine.In Object Explorer, connect to an instance of Mecanismo de Banco de DadosDatabase Engine.

  2. Na barra Padrão, clique em Nova Consulta.On the Standard bar, click New Query.

  3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
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 saber mais, consulte o seguinte:For more information, see the following: