分享方式：

使用 Transact-SQL 就地設定資料行加密

  • 文章

適用於： SQL Server 2019 (15.x) 與更新版本 - 僅限 Windows Azure SQL 資料庫

本文描述如何使用具有安全記憶體保護區的 Always Encrypted 來搭配 ALTER TABLE 陳述式/ALTER COLUMN 陳述式，就地對資料行執行密碼編譯作業。 如需就地加密和一般必要條件的基本資訊，請參閱使用具有安全記憶體保護區的 Always Encrypted 就地設定資料行加密。

您可以使用 ALTER TABLE 或 ALTER COLUMN 陳述式來設定資料行的目標加密設定。 當您執行陳述式時，伺服器端安全記憶體保護區會根據陳述式內資料行定義中所指定目前和目標加密設定來加密、重新加密或解密資料行中儲存的資料。

  • 如果資料行目前未加密，則當您在資料行定義中指定 ENCRYPTED WITH 子句時，就會將其加密。
  • 如果資料行目前已加密，則當您未在資料行定義中指定 ENCRYPTED WITH 子句時，就會將其解密 (轉換成純文字資料行)。
  • 如果資料行目前已加密，則當您指定 ENCRYPTED WITH 子句，且所指定資料行加密類型或資料行加密金鑰與目前使用的加密類型或資料行加密金鑰不同，就會重新加密該資料行。

注意

除了將資料行變更為 NULL 或 NOT NULL，或變更定序以外，您無法將密碼編譯作業與其他變更合併到單一的 ALTER TABLE/ALTER COLUMN 陳述式中。 例如，您無法加密資料行，同時在單一 ALTER TABLE/ALTER COLUMN Transact-SQL 陳述式中變更資料行的資料類型。 請使用兩個個別的陳述式。

作為使用伺服器端安全記憶體保護區的任何查詢，就地觸發加密的 ALTER TABLE/ALTER COLUMN 陳述式必須透過已啟用 Always Encrypted 和記憶體保護區計算的連線來傳送。

本文的其餘部分將描述如何從 SQL Server Management Studio 使用 ALTER TABLE/ALTER COLUMN 陳述式來觸發就地加密。 或者，您也可以從 Azure Data Studio 或應用程式發出 ALTER TABLE/ALTER COLUMN。

注意

目前，SqlServer PowerShell 模組中的 Invoke-Sqlcmd Cmdlet 以及 sqlcmd) 不支援使用 ALTER TABLE/ALTER COLUMN 進行就地密碼編譯作業。

請使用 SSMS 中的 Transact-SQL 執行就地加密

必要條件

步驟

  1. 使用 Always Encrypted 和資料庫連接中所啟用的記憶體保護區計算來開啟查詢視窗。 如需詳細資料，請參閱針對資料庫連線啟用和停用 Always Encrypted。

  2. 在查詢視窗中，發出 ALTER TABLE/ALTER COLUMN 陳述式，針對所要加密、解密或重新加密的資料行指定目標加密組態。 若要加密或重新加密資料行，請使用 ENCRYPTED WITH 子句。 如果您的資料行是字串資料行 (例如，char、varchar、nchar、nvarchar)，則您也可能需要將定序變更為 BIN2 定序。

    注意

    如果您的資料行主要金鑰儲存至 Azure Key Vault，則系統可能會提示您登入 Azure。

  3. 清除會存取資料表的所有批次和預存程序的計畫快取，以重新整理參數加密資訊。

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    注意

    如果您未從快取中移除受影響查詢的計畫，則加密後的第一次執行查詢可能會失敗。

    使用 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 或 DBCC FREEPROCCACHE 小心地清除計畫快取，因為它可能會導致暫時性的查詢效能降低。 若要將清除快取的負面影響降到最低，您可以選擇性地僅移除受影響查詢的計畫。

  4. 呼叫 sp_refresh_parameter_encryption 來針對在 sys.parameters 中保存且因對資料行加密而可能已經失效的每個模組 (預存程序、函式、檢視、觸發程序) 的參數更新中繼資料。

範例

就地加密資料行

下列範例假設：

  • CEK1 是已啟用記憶體保護區的資料行加密金鑰。
  • SSN 資料行是純文字，且目前使用 Latin1、非 BIN2 定序 (例如 Latin1_General_CI_AI_KS_WS) 之類的預設資料庫定序。

此陳述式會使用確定性加密和已啟用記憶體保護區的資料行加密金鑰來就地加密 SSN 資料行。 它也會將預設資料庫定序覆寫為對應 (在相同的字碼頁中) BIN2 定序。

作業會在線上執行 (ONLINE = ON)。 另請注意，ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 呼叫可重新建立受資料表結構描述變更所影響的查詢計畫。

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

就地重新加密資料行以變更加密類型

下列範例假設：

  • 使用確定性加密和已啟用記憶體保護區的資料行加密金鑰 CEK1 來加密 SSN 資料行。
  • 目前的定序 (設定於資料行層級) 為 Latin1_General_BIN2。

下列陳述式會使用隨機化加密和相同的金鑰 (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

就地重新加密資料行以輪替資料行加密金鑰

下列範例假設：

  • 使用隨機化加密和已啟用記憶體保護區的資料行加密金鑰 CEK1 來加密 SSN 資料行。
  • CEK2 是已啟用記憶體保護區的資料行加密金鑰 (不同於 CEK1)。
  • 目前的定序 (設定於資料行層級) 為 Latin1_General_BIN2。

下列陳述式會使用 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

就地解密資料行

下列範例假設：

  • 使用已啟用記憶體保護區的資料行加密金鑰來加密 SSN 資料行。
  • 目前的定序 (設定於資料行層級) 為 Latin1_General_BIN2。

以下陳述式會解密資料行，並讓定序保持不變。 或者，您也可以選擇變更定序。 例如，將定序變更為相同陳述中的非 BIN2 定序。

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

下一步

另請參閱