Compartir a través de

DECRYPTBYKEY (Transact-SQL)

  • Artículo

Se aplica a: SQL Server Base de datos de Azure SQL Azure SQL Managed Instance Azure Synapse Analytics

En esta función se usa una clave simétrica para descifrar los datos.

Convenciones de sintaxis de Transact-SQL

Nota

El grupo de SQL sin servidor no admite esta sintaxis en Azure Synapse Analytics.

En el caso de los grupos de SQL dedicados en Azure Synapse Analytics, el almacenamiento en caché del conjunto de resultados no debe usarse junto con DECRYPTBYKEY. Si se debe usar esta función criptográfica, asegúrese de que el almacenamiento en caché del conjunto de resultados está deshabilitado (en el nivel de sesión o en el nivel de base de datos) en el momento de la ejecución.

Sintaxis

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

Argumentos

ciphertext
Una variable de tipo varbinary que contiene los datos cifrados con la clave.

@ciphertext
Una variable de tipo varbinary que contiene los datos cifrados con la clave.

add_authenticator
Indica si el proceso de cifrado original incluía, y cifraba, un autenticador junto con el texto sin formato. Debe coincidir con el valor que se pasa a ENCRYPTBYKEY (Transact-SQL) durante el proceso de cifrado de datos. add_authenticator tiene un tipo de datos int.

authenticator
Los datos que se usaron como base para la generación del autenticador. Debe coincidir con el valor que se proporcionó a ENCRYPTBYKEY (Transact-SQL). autenticador tiene un tipo de datos sysname.

@authenticator
Una variable que contiene datos a partir de los que se genera un autenticador. Debe coincidir con el valor que se proporcionó a ENCRYPTBYKEY (Transact-SQL). @authenticator tiene un tipo de datos sysname.

Tipos de valor devuelto

varbinary, con un tamaño máximo de 8 000 bytes. DECRYPTBYKEY devuelve NULL si la clave simétrica usada para el cifrado de los datos no está abierta o si ciphertext es NULL.

Comentarios

DECRYPTBYKEY usa una clave simétrica. La base de datos debe tener esta clave simétrica ya abierta. DECRYPTBYKEY permitirá varias claves abiertas a la vez. No es necesario abrir la clave inmediatamente antes de descifrar el texto cifrado.

El cifrado y descifrado simétricos suelen funcionar con relativa rapidez, y funcionan bien para las operaciones que implican grandes volúmenes de datos.

La llamada a DECRYPTBYKEY debe producirse en el contexto de la base de datos que contiene la clave de cifrado. Asegúrese de esto mediante una llamada a DECRYPTBYKEY desde un objeto (por ejemplo, una vista, un procedimiento almacenado o una función) que reside en la base de datos.

Permisos

La clave simétrica ya debe estar abierta en la sesión actual. Para obtener más información, vea OPEN SYMMETRIC KEY (Transact-SQL).

Ejemplos

A. Descifrar utilizando una clave simétrica

En este ejemplo se descifra texto cifrado con una clave 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. Descifrar utilizando una clave simétrica y un hash de autenticación

En este ejemplo se descifran datos que originalmente se cifraron juntos con un 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. No se puede descifrar cuando no está en el contexto de base de datos con clave

En el ejemplo siguiente se muestra que DECRYPTBYKEY debe ejecutarse en el contexto de la base de datos que contiene la clave. La fila no se descifra cuando DECRYPTBYKEY se ejecuta en la base de datos maestra (master); el resultado es 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!Password#';
CREATE CERTIFICATE TestEncryptionCertificate WITH SUBJECT = 'TestEncryption';
CREATE SYMMETRIC KEY TestEncryptSymmetricKey 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 TestEncryptSymmetricKey DECRYPTION BY CERTIFICATE TestEncryptionCertificate;
SELECT @var = EncryptByKey(Key_GUID('TestEncryptSymmetricKey'), @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 TestEncryptSymmetricKey REMOVE PROVIDER KEY;
DROP CERTIFICATE TestEncryptionCertificate;

Use [Master]
DROP DATABASE TestingDecryptByKey;
GO

Vea también

ENCRYPTBYKEY (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
Jerarquía de cifrado
Elegir un algoritmo de cifrado