DECRYPTBYKEY (Transact-SQL)
Aplica-se a: SQL Server
Banco de Dados
SQL do Azure Instância
Gerenciada de SQL do Azure Azure 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