Compartir a través de

DECRYPTBYKEY (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsBase de datos de Azure SQL en Microsoft Fabric

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 se debe usar 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 pasado a ENCRYPTBYKEY 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 proporcionado a ENCRYPTBYKEY. authenticator es sysname.

@authenticator

Una variable que contiene datos a partir de los que se genera un autenticador. Debe coincidir con el valor proporcionado a ENCRYPTBYKEY. @authenticator es sysname.

Tipos de retorno

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

Comentarios

DECRYPTBYKEY usa una clave simétrica. La base de datos debe tener esta clave simétrica ya abierta. DECRYPTBYKEY permite abrir varias claves al mismo tiempo. No es necesario abrir la clave inmediatamente antes del descifrado de texto cifrado.

Normalmente, el cifrado y el descifrado simétricos funcionan rápidamente 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, consulte OPEN SYMMETRIC KEY.

Ejemplos

A. Descifrado mediante 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. Descifrado mediante 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;

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 master base de datos; 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 AS VARBINARY (8000), @Val AS 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

Contenido relacionado

  • Last updated on