Partilhar via

Configurar a criptografia de coluna no local com Transact-SQL

  • Artigo

Aplica-se a: SQL Server 2019 (15.x) e posterior - somente Windows Banco de Dados SQL do Azure

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

Com a instrução ALTER TABLE ou ALTER COLUMN, você pode definir a configuração de 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 atualmente encriptada, será desencriptada (convertida numa coluna de texto simples), se não especificar a cláusula ENCRYPTED WITH na definição da 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 especificado ou a chave de criptografia de coluna forem diferentes do tipo de criptografia usado atualmente ou da chave de criptografia de coluna.

Observação

Não é possível combinar operações criptográficas com outras alterações em uma única instrução ALTER TABLE/ALTER COLUMN, exceto alterar a coluna para NULL ou NOT NULLou alterar um agrupamento. 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 Transact-SQL. Use duas instruções separadas.

Como qualquer consulta que utiliza um enclave seguro do lado do servidor, uma instrução ALTER TABLE/ALTER COLUMN que aciona a criptografia no local deve ser enviada por uma conexão com Sempre Criptografado e cálculos de enclave habilitados.

O restante deste artigo descreve como acionar a criptografia no local 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 seu aplicativo.

Observação

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

Executar criptografia no local com Transact-SQL no SSMS

Pré-requisitos

Passos

  1. Abra uma janela de consulta com Always Encrypted e cálculos de enclave ativados na ligação à base de dados. Para obter detalhes, consulte 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 sua coluna for uma coluna de cadeia de caracteres (por exemplo, char, varchar, nchar, nvarchar), também talvez seja necessário alterar o agrupamento para um agrupamento BIN2.

    Observação

    Se a sua chave mestra de coluna estiver armazenada no Cofre da Chave do Azure, poderá ser-lhe pedido para iniciar sessão no Azure.

  3. Limpe o cache de 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 para a 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 cuidadosamente o cache do plano, pois isso pode resultar em degradação temporária do desempenho da consulta. Para minimizar o impacto negativo da limpeza do cache, você pode remover seletivamente os planos apenas para as consultas afetadas.

  4. Chame sp_refresh_parameter_encryption para atualizar os metadados dos parâmetros de cada módulo (procedimento armazenado, função, vista, gatilho) que são persistidos em sys.parameters e que podem ter sido invalidados pela criptografia das colunas.

Exemplos

Criptografando uma coluna no próprio local

O exemplo abaixo pressupõe:

  • CEK1 é uma chave de criptografia de coluna com suporte a enclave.
  • A coluna SSN é de texto sem formatação e atualmente está usando o agrupamento de banco de dados padrão, como Latin1, agrupamento 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 no local. Ele também substitui o agrupamento de banco de dados padrão pelo agrupamento BIN2 correspondente (na mesma página de código).

A operação é realizada online (ONLINE = ON). Observe também que a chamada para ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, a qual recria os planos das consultas, é afetada pela alteração do esquema da 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

Recriptografar uma coluna no local para alterar o tipo de criptografia

O exemplo abaixo pressupõe:

  • A coluna SSN é criptografada usando criptografia determinística e uma chave de criptografia de coluna compatível com enclave, CEK1.
  • O agrupamento atual, definido ao 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

Recriptografar uma coluna no local para rodar uma chave de criptografia de coluna

O exemplo abaixo pressupõe:

  • A coluna SSN é encriptada usando criptografia aleatória e uma chave de encriptação de coluna compatível com enclave, CEK1.
  • CEK2 é uma chave de criptografia de coluna habilitada para enclave (diferente de CEK1).
  • O agrupamento atual, definido 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

Desencriptar uma coluna diretamente

O exemplo abaixo pressupõe:

  • A coluna SSN é encriptada usando uma chave de criptografia de coluna compatível com enclave.
  • O agrupamento atual, definido no nível da coluna, é Latin1_General_BIN2.

A instrução abaixo descriptografa a coluna e mantém o agrupamento inalterado. Como alternativa, pode optar por alterar a ordenação. Por exemplo, altere o agrupamento para um agrupamento 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óximos passos

Ver também