Configurar criptografia de coluna in-loco com Transact-SQL

  • Artigo

Aplica-se a: SQL Server 2019 (15.x) e versões posteriores (somente para Windows) Banco de Dados SQL do Azure

Este artigo descreve como executar operações de criptografia in-loco em colunas usando o Always Encrypted com enclaves seguros com a instrução ALTER TABLE/ALTER COLUMN. Para obter informações básicas sobre a criptografia in-loco e pré-requisitos gerais, confira Configurar a criptografia de coluna in-loco usando o Always Encrypted com enclaves seguros.

Com a instrução ALTER TABLE ou ALTER COLUMN, você pode definir a configuração da criptografia de destino para uma coluna. Quando você executa a instrução, o enclave seguro do lado do servidor criptografa, criptografa novamente ou descriptografa os dados armazenados na coluna, dependendo da configuração de criptografia atual e de destino especificada na definição de coluna na instrução.

  • Se a coluna não estiver criptografada no momento, ela será criptografada se você especificar a cláusula ENCRYPTED WITH na definição de coluna.
  • Se a coluna estiver criptografada no momento, ela será descriptografada (convertida em uma coluna de texto não criptografado) se você não especificar a cláusula ENCRYPTED WITH na definição de coluna.
  • Se a coluna estiver criptografada no momento, ela será criptografada novamente se você especificar a cláusula ENCRYPTED WITH e o tipo de criptografia de coluna especificada ou a chave de criptografia de coluna forem diferentes do tipo de criptografia ou da chave de criptografia de coluna usada no momento.

Observação

Não é possível combinar operações criptográficas com outras alterações em apenas uma instrução ALTER TABLE/ALTER COLUMN, exceto para alterar a coluna para NULL ou NOT NULL ou, então, para alterar uma ordenação. Por exemplo, não é possível criptografar uma coluna E alterar um tipo de dados da coluna em uma única instrução ALTER TABLE/ALTER COLUMN do Transact-SQL. É necessário usar duas instruções separadas.

Como qualquer consulta que usa um enclave seguro no lado do servidor, uma instrução ALTER TABLE/ALTER COLUMN que dispara a criptografia in-loco deve ser enviada por uma conexão com o Always Encrypted e os cálculos de enclave habilitados.

O restante deste artigo descreve como disparar a criptografia in-loco usando a instrução ALTER TABLE/ALTER COLUMN do SQL Server Management Studio. Como alternativa, você pode emitir ALTER TABLE/ALTER COLUMN do Azure Data Studio ou do aplicativo.

Observação

Atualmente, o cmdlet Invoke-Sqlcmd no módulo do SqlServer do PowerShell e sqlcmd não dão suporte ao uso de ALTER TABLE/ALTER COLUMN para operações criptográficas in-loco.

Executar a criptografia in-loco com o Transact-SQL no SSMS

Pré-requisitos

Etapas

  1. Abra uma janela de consulta com Always Encrypted e cálculos de enclave habilitados na conexão de banco de dados. Para obter detalhes, confira Habilitando e desabilitando o Always Encrypted para uma conexão de banco de dados.

  2. Na janela de consulta, emita a instrução ALTER TABLE/ALTER COLUMN, especificando a configuração de criptografia de destino para uma coluna que você deseja criptografar, descriptografar ou criptografar novamente. Se você estiver criptografando ou criptografando novamente a coluna, use a cláusula ENCRYPTED WITH. Se a coluna for de cadeia de caracteres (por exemplo, char, varchar, nchar, nvarchar), você também precisará alterar a ordenação para uma ordenação BIN2.

    Observação

    Se a chave mestra de coluna estiver armazenada no Azure Key Vault, talvez seja solicitado que você entre no Azure.

  3. Limpe o cache do plano para todos os lotes e procedimentos armazenados que acessam a tabela, para atualizar as informações de criptografia de parâmetros.

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    Observação

    Se você não remover o plano da consulta afetada do cache, a primeira execução da consulta após a criptografia poderá falhar.

    Use ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ou DBCC FREEPROCCACHE para limpar o cache de planos com cuidado, pois isso pode resultar na degradação temporária do desempenho de consulta. Para minimizar o impacto negativo da limpeza do cache, você pode remover seletivamente somente os planos das consultas afetadas.

  4. Chame sp_refresh_parameter_encryption para atualizar os metadados dos parâmetros de cada módulo (procedimento armazenado, função, exibição, gatilho) que persistiu em sys.parameters e possa ter sido invalidado criptografando as colunas.

Exemplos

Criptografar uma coluna in-loco

O exemplo abaixo pressupõe que:

  • CEK1 é uma chave de criptografia de coluna habilitada para enclave.
  • A coluna SSN é de texto sem formatação e atualmente está usando a ordenação de banco de dados padrão, como uma ordenação Latin1, não BIN2 (por exemplo, Latin1_General_CI_AI_KS_WS).

A instrução criptografa a coluna SSN usando criptografia determinística e a chave de criptografia de coluna habilitada para enclave in-loco. Ela também substitui a ordenação do banco de dados padrão pela ordenação BIN2 correspondente (na mesma página de código).

A operação é executada online (ONLINE = ON). Observe também que a chamada para ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, que recria os planos das consultas, é afetada pela alteração do esquema de tabela.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH
(ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Criptografar novamente uma coluna in-loco para alterar o tipo de criptografia

O exemplo abaixo pressupõe que:

  • A coluna SSN foi criptografada usando criptografia determinística e uma chave de criptografia de coluna habilitada para enclave, CEK1.
  • A ordenação atual, definida no nível da coluna, é Latin1_General_BIN2.

A instrução abaixo criptografa novamente a coluna usando criptografia aleatória e a mesma chave (CEK1)

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Criptografar novamente uma coluna in-loco para girar uma chave de criptografia de coluna

O exemplo abaixo pressupõe que:

  • A coluna SSN foi criptografada usando criptografia aleatória e uma chave de criptografia de coluna habilitada para enclave, CEK1.
  • CEK2 é uma chave de criptografia de coluna habilitada para enclave (diferente de CEK1).
  • A ordenação atual, definida no nível da coluna, é Latin1_General_BIN2.

A instrução abaixo criptografa novamente a coluna com CEK2.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK2]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Descriptografar uma coluna no local

O exemplo abaixo pressupõe que:

  • A coluna SSN foi criptografada usando uma chave de criptografia de coluna habilitada para enclave.
  • A ordenação atual, definida no nível da coluna, é Latin1_General_BIN2.

A instrução abaixo descriptografa a coluna e mantém a ordenação inalterada. Como alternativa, você pode optar por alterar a ordenação. Por exemplo, altere a ordenação para uma ordenação não BIN2 na mesma instrução.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
WITH (ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Próximas etapas

Confira também