DECRYPTBYKEY (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse Analytics

Essa função usa uma chave simétrica para descriptografar dados.

Convenções de sintaxe de Transact-SQL

Observação

Não há suporte a essa sintaxe para o pool de SQL sem servidor no Azure Synapse Analytics.

Para pools de SQL dedicados no Azure Synapse Analytics, o cache do conjunto de resultados não deve ser usado em conjunto com DECRYPTBYKEY. Se essa função criptográfica precisar ser usada, verifique se você tem o cache do conjunto de resultados desabilitado (no nível da sessão ou no nível do banco de dados) no momento da execução.

Sintaxe

  
DecryptByKey ( { 'ciphertext' | @ciphertext }   
    [ , add_authenticator, { authenticator | @authenticator } ] )  

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

ciphertext
Uma variável do tipo varbinary que contém dados criptografados com a chave.

@ciphertext
Uma variável do tipo varbinary que contém dados criptografados com a chave.

add_authenticator
Indica se o processo de criptografia original incluía, e criptografava, um autenticador junto com o texto não criptografado. Deve corresponder ao valor passado para ENCRYPTBYKEY (Transact-SQL) durante o processo de criptografia de dados. add_authenticator tem um tipo de dados int.

authenticator
Os dados usados como base para a geração do autenticador. Deve corresponder ao valor fornecido para ENCRYPTBYKEY (Transact-SQL). authenticator tem um tipo de dados sysname.

@authenticator
Uma variável que contém dados dos quais um autenticador é gerado. Deve corresponder ao valor fornecido para ENCRYPTBYKEY (Transact-SQL). @authenticator tem um tipo de dados sysname.

Tipos de retorno

varbinary, com um tamanho máximo de 8.000 bytes. DECRYPTBYKEY retornará NULL se a chave simétrica usada para criptografia de dados não estiver aberta ou se ciphertext for NULL.

Comentários

DECRYPTBYKEY usa uma chave simétrica. O banco de dados deve ter essa chave simétrica já aberta. DECRYPTBYKEY permitirá várias chaves abertas ao mesmo tempo. Não é necessário abrir a chave imediatamente antes da descriptografia do texto cifrado.

Normalmente a criptografia e descriptografia simétricas operam de maneira relativamente rápida e funciona bem para operações que envolvem grandes volumes de dados.

A chamada DECRYPTBYKEY precisa ocorrer no contexto em que o banco de dados contém a chave de criptografia. Verifique isso chamando DECRYPTBYKEY de um objeto (como uma exibição, procedimento armazenado ou função) que reside no banco de dados.

Permissões

A chave simétrica já deve estar aberta na sessão atual. Confira OPEN SYMMETRIC KEY (Transact-SQL) para obter mais informações.

Exemplos

a. Descriptografando com o uso de uma chave simétrica

Este exemplo descriptografa o texto cifrado com uma chave simétrica.

-- 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, EncryptedNationalID   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalID))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO  

B. Descriptografando com o uso de uma chave simétrica e um hash de autenticação

Este exemplo descriptografa dados originalmente criptografados juntamente com um autenticador.

-- 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  
  

C. Falha ao descriptografar quando não está no contexto de um banco de dados com chave

O exemplo a seguir demonstra que DECRYPTBYKEY precisa ser executado no contexto de um banco de dados que contém a chave. A linha não será descriptografada quando DECRYPTBYKEY for executado no banco de dados mestre. O resultado será NULL.

-- Create the database
CREATE DATABASE TestingDecryptByKey
GO

USE [TestingDecryptByKey]

-- Create the table and view
CREATE TABLE TestingDecryptByKey.dbo.Test(val VARBINARY(8000) NOT NULL);
GO
CREATE VIEW dbo.TestView AS SELECT CAST(DecryptByKey(val) AS VARCHAR(30)) AS DecryptedVal FROM TestingDecryptByKey.dbo.Test;
GO

-- Create the key, and certificate
USE TestingDecryptByKey;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ItIsreallyLong1AndSecured!Passsword#';
CREATE CERTIFICATE TestEncryptionCertificate WITH SUBJECT = 'TestEncryption';
CREATE SYMMETRIC KEY TestEncryptSymmmetricKey WITH ALGORITHM = AES_256, IDENTITY_VALUE = 'It is place for test',
KEY_SOURCE = 'It is source for test' ENCRYPTION BY CERTIFICATE TestEncryptionCertificate;

-- Insert rows into the table
DECLARE @var VARBINARY(8000), @Val VARCHAR(30);
SELECT @Val = '000-123-4567';
OPEN SYMMETRIC KEY TestEncryptSymmmetricKey DECRYPTION BY CERTIFICATE TestEncryptionCertificate;
SELECT @var = EncryptByKey(Key_GUID('TestEncryptSymmmetricKey'), @Val);
SELECT CAST(DecryptByKey(@var) AS VARCHAR(30)), @Val;
INSERT INTO dbo.Test VALUES(@var);
GO

-- Switch to master
USE [Master];
GO

-- Results show the date inserted
SELECT DecryptedVal FROM TestingDecryptByKey.dbo.TestView;

-- Results are NULL because we are not in the context of the TestingDecryptByKey Database
SELECT CAST(DecryptByKey(val) AS VARCHAR(30)) AS DecryptedVal FROM TestingDecryptByKey.dbo.Test;
GO

-- Clean up resources
USE TestingDecryptByKey;

DROP SYMMETRIC KEY TestEncryptSymmmetricKey REMOVE PROVIDER KEY;
DROP CERTIFICATE TestEncryptionCertificate;

Use [Master]
DROP DATABASE TestingDecryptByKey;
GO

Consulte Também

ENCRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
Hierarquia de criptografia
Escolher um algoritmo de criptografia