sp_describe_parameter_encryption (Transact-SQL)

Статья
05/23/2023

Область применения: SQL Server 2016 (13.x) и более поздние версии Управляемого экземпляра Базы данных SQL Azure SQL Azure

Анализирует указанную инструкцию Transact-SQL и ее параметры, чтобы определить, какие параметры соответствуют столбцам базы данных, защищенным с помощью функции Always Encrypted. Возвращает метаданные шифрования для параметров, соответствующих зашифрованным столбцам.

Синтаксис

sp_describe_parameter_encryption   
    [ @tsql = ] N'Transact-SQL_batch' ,   
    [ @params = ] N'parameters'   
[ ;]

Аргументы

[ @tsql = ] "Transact-SQL_batch"
Одна или несколько инструкций Transact-SQL. Transact-SQL_batch может быть nvarchar(n) или nvarchar(max).

[ @params = ] N'parameters'
@params предоставляет строку объявления для параметров пакета Transact-SQL, который аналогичен sp_executesql. Параметры могут быть nvarchar(n) или nvarchar(max).

Одна строка, содержащая определения всех параметров, внедренных в Transact-SQL_batch. Строка должна представлять собой константу в Юникоде либо переменную в этом же формате. Определение каждого параметра состоит из имени параметра и типа данных. n — это заполнитель, указывающий дополнительные определения параметров. Каждый параметр, указанный в инструкции, должен быть определен в @params. Если инструкция Transact-SQL или пакет в инструкции не содержит параметров, @params не требуется. Значением по умолчанию для этого аргумента является NULL.

Возвращаемое значение

Значение 0 указывает на успешность. Все остальное указывает на сбой.

Результирующие наборы

sp_describe_parameter_encryption возвращает два результирующих набора:

Результирующий набор, описывающий криптографические ключи, настроенные для столбцов базы данных, соответствуют параметрам указанной инструкции Transact-SQL.
Результирующий набор, описывающий, как следует шифровать определенные параметры. Этот результирующий набор ссылается на ключи, описанные в первом результирующем наборе.

Каждая строка первого результирующий набор описывает пару ключей; зашифрованный ключ шифрования столбцов и соответствующий главный ключ столбца.

Имя столбца	Тип данных	Описание:
column_encryption_key_ordinal	int	Идентификатор строки в наборе результатов.
database_id	int	Идентификатор базы данных.
column_encryption_key_id	int	Идентификатор ключа шифрования столбца. Примечание. Этот идентификатор обозначает строку в представлении каталога sys.column_encryption_keys (Transact-SQL ).
column_encryption_key_version	int	Зарезервировано для последующего использования. В настоящее время всегда содержит 1.
column_encryption_key_metadata_version	binary(8)	Метка времени, представляющая время создания ключа шифрования столбца.
column_encryption_key_encrypted_value	varbinary(4000)	Зашифрованное значение ключа шифрования столбца.
column_master_key_store_provider_name	sysname	Имя поставщика хранилища ключей, содержащего главный ключ столбца, который использовался для создания зашифрованного значения ключа шифрования столбца.
column_master_key_path	nvarchar(4000)	Путь к ключу главного ключа столбца, который использовался для создания зашифрованного значения ключа шифрования столбца.
column_encryption_key_encryption_algorithm_name	sysname	Имя алгоритма шифрования, используемого для создания значения шифрования ключа шифрования столбца.

Каждая строка второго результирующий набор содержит метаданные шифрования для одного параметра.

Имя столбца	Тип данных	Описание:
parameter_ordinal	int	Идентификатор строки в результирующем наборе.
parameter_name	sysname	Имя одного из параметров, указанных в аргументе @params .
column_encryption_algorithm	tinyint	Код, указывающий алгоритм шифрования, настроенный для столбца, соответствует параметру. Поддерживаемые в настоящее время значения: 2 для AEAD_AES_256_CBC_HMAC_SHA_256.
column_encryption_type	tinyint	Код, указывающий тип шифрования, настроенный для столбца, соответствует параметру. Поддерживаются такие значения: 0 — открытый текст (столбец не зашифрован) 1 — детерминированное шифрование 2 — случайное шифрование.
column_encryption_key_ordinal	int	Код строки в первом результирующем наборе. В указанной строке описывается ключ шифрования столбца, настроенный для столбца, соответствующий параметру.
column_encryption_normalization_rule_version	tinyint	Номер версии алгоритма нормализации типов.

Замечания

Драйвер клиента SQL Server, поддерживающий Always Encrypted, автоматически вызывает sp_describe_parameter_encryption для получения метаданных шифрования для параметризованных запросов, выданных приложением. Впоследствии драйвер использует метаданные шифрования для шифрования значений параметров, соответствующих столбцам базы данных, защищенным с помощью Always Encrypted, и заменяет значения параметров обычного текста, отправленные приложением, с зашифрованными значениями параметров перед отправкой запроса в ядро СУБД.

Разрешения

Требуется разрешение VIEW ANY COLUMN ENCRYPTION KEY DEFINITION и VIEW ANY COLUMN MASTER KEY DEFINITION в базе данных.

Примеры

CREATE COLUMN MASTER KEY [CMK1]  
WITH  
(  
       KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',  
    KEY_PATH = N'CurrentUser/my/A66BB0F6DD70BDFF02B62D0F87E340288E6F9305'  
);  
GO  
  
CREATE COLUMN ENCRYPTION KEY [CEK1]  
WITH VALUES  
(  
       COLUMN_MASTER_KEY = [CMK1],  
    ALGORITHM = 'RSA_OAEP',  
    ENCRYPTED_VALUE =   
0x016E000001630075007200720065006E00740075007300650072002F006D007  
9002F00610036003600620062003000660036006400640037003000620064006  
6006600300032006200360032006400300066003800370065003300340030003  
200380038006500360066003900330030003500CA0D0CEC74ECADD1804CF991  
37B4BD06BBAB15D7EA74E0C249A779C7768A5B659E0125D24FF827F5EA8CA51  
7A8E197ECA1353BA814C2B0B2E6C8AB36E3AE6A1E972D69C3C573A963ADAB6  
686CF5D24F95FE43140C4F9AF48FBA7DF2D053F3B4A1F5693A1F905440F8015B  
DB43AF8A04BE4E045B89876A0097E5FBC4E6A3B9C3C0D278C540E46C53938B8  
C957B689C4DC095821C465C73117CBA95B758232F9E5B2FCC7950B8CA00AFE3  
74DE42847E3FBC2FDD277035A2DEF529F4B735C20D980073B4965B4542A3472  
3276A1646998FC6E1C40A3FDB6ABCA98EE2B447F114D2AC7FF8C7D51657550E  
C5C2BABFFE8429B851272086DCED94332CF18FA854C1D545A28B1EF4BE64F8E0  
35175C1650F6FC5C4702ACF99850A4542B3747EAEC0CC726E091B36CE24392D8  
01ECAA684DE344FECE05812D12CD72254A014D42D0EABDA41C89FC4F545E88B  
4B8781E5FAF40D7199D4842D2BFE904D209728ED4F527CBC169E2904F6E711FF8  
1A8F4C25382A2E778DD2A58552ED031AFFDA9D9D891D98AD82155F93C58202F  
C24A77F415D4F8EF22419D62E188AC609330CCBD97CEE1AEF8A18B0195883360  
4707FDF03B2B386487CC679D7E352D0B69F9FB002E51BCD814D077E82A09C14E  
9892C1F8E0C559CFD5FA841CEF647DAB03C8191DC46B772E94D579D8C80FE93C  
3827C9F0AE04D5325BC73111E07EEEDBE67F1E2A73580085  
);  
GO  
  
CREATE TABLE t1 (  
c1 INT ENCRYPTED WITH (  
    COLUMN_ENCRYPTION_KEY = [CEK1],   
    ENCRYPTION_TYPE = Randomized,   
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,  
);  
  
EXEC sp_describe_parameter_encryption N'INSERT INTO t1 VALUES(@c1)',  N'@c1 INT';

Ниже приведен первый результирующий набор:

column_encryption_key_ordinal	database_id	column_encryption_key_id	column_encryption_key_version	column_encryption_key_metadata_version	column_encryption_key_encrypted_value
1	5	1	1	0x99EDA60083A50000	0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006100360036006200620030006600360064006400370030006200640066006600300032006200360032006400300066003800370065003300340030003200380038006500360066003900330030003500CA0D0CEC74ECADD1804CF99137B4BD06BBAB15D7EA74E0C249A779C7768A5B659E0125D24FF827F5EA8CA517A8E197ECA1353BA814C2B0B2E6C8AB36E3AE6A1E972D69C3C573A963ADAB6686CF5D24F95FE43140C4F9AF48FBA7DF2D053F3B4A1F5693A1F905440F8015BDB43AF8A04BE4E045B89876A0097E5FBC4E6A3B9C3C0D278C540E46C53938B8C957B689C4DC095821C465C73117CBA95B758232F9E5B2FCC7950B8CA00AFE374DE42847E3FBC2FDD277035A2DEF529F4B735C20D980073B4965B4542A34723276A1646998FC6E1C40A3FDB6ABCA98EE2B447F114D2AC7FF8C7D51657550EC5C2BABFFE8429B851272086DCED94332CF18FA854C1D545A28B1EF4BE64F8E035175C1650F6FC5C4702ACF99850A4542B3747EAEC0CC726E091B36CE24392D801ECAA684DE344FECE05812D12CD72254A014D42D0EABDA41C89FC4F545E88B4B8781E5FAF40D7199D4842D2BFE904D209728ED4F527CBC169E2904F6E711FF81A8F4C25382A2E778DD2A58552ED031AFFDA9D9D891D98AD82155F93C58202FC24A77F415D4F8EF22419D62E188AC609330CCBD97CEE1AEF8A18B01958833604707FDF03B2B386487CC679D7E352D0B69F9FB002E51BCD814D077E82A09C14E9892C1F8E0C559CFD5FA841CEF647DAB03C8191DC46B772E94D579D8C80FE93C3827C9F0AE04D5325BC73111E07EEEDBE67F1E2A73580085

(Результаты продолжаются.)

column_master_key_store_provider_name	column_master_key_path	column_encryption_key_encryption_algorithm_name
MSSQL_CERTIFICATE_STORE	CurrentUser/my/A66BB0F6D70BDFF02B62B62D0F87E34028E6F9305	RSA_OAEP

Ниже приведен второй результирующий набор:

parameter_ordinal	parameter_name	column_encryption_algorithm	column_encryption_type
1	@c1	1	1