Technical Architecture Review
SQL Server 2016 資料加密
與防禦架構演進
從備份保護、欄位級加密到 2025 技術展望
2026-03-15 | 資訊部門技術分享
從備份保護、欄位級加密到 2025 技術展望
使用者個人識別資訊 (Personally Identifiable Information) 指任何可單獨或結合其他資訊，精準識別、聯絡或定位特定自然人的資料。
目標：使用欄位級加密 (Column-Level Encryption) 將單一機敏欄位徹底去識別化
| 機制名稱 | 保護粒度 | 是否為真實加密 | 能否保護單一欄位 |
|---|---|---|---|
| TDE / 備份加密 | 整庫 / 整檔 | 是 (AES) | 否 (登入後可見) |
| DDM (資料遮罩) | 單一欄位 | 否 (僅是面具) | DBA 可繞過 |
| AE / CLE | 單一欄位 | 是 (AES-256) | 是 |
| AKV / Vault / FPE | 單一欄位 | 是 | 是 |
Always Encrypted vs. Cell-Level Encryption vs. Vault
加解密完全在 Client 端 (JDBC Driver) 完成，因此 DB Server CPU 完全零負擔。
(SQL 只處理密文，不參與任何運算)
PreparedStatement。queryFromPool 與
execFromPool 參數類型必須精準，否則驅動無法對應。
LIKE 搜尋與複查運算。App 對著 View 讀寫，Trigger 內部透過每個資料庫中的 SH.encrypt/decrypt 自動加解密。
HASHBYTES 做精準搜尋。LIKE。代價：需維護 View 與 Trigger 腳本，且 DB CPU 會有解密開銷。
適用於高度分散的微服務架構，且預算充足、能接受亞毫秒級延遲的金融大型系統。
| 維度 | Always Encrypted | Cell-Level (方案二) | AKV / Vault Transit |
|---|---|---|---|
| 加密戰場 | Client Side | SQL Server Side | App + Vault |
| LIKE 搜尋 | 不支援 | 支援 (解密後) | 不支援 |
| Java 端改動 | 極大 (需 PreparedStmt) | 零改動 (甚至Java不用修改，只需資料改動 View) | 需整合 SDK |
| JCX 相容性 | 風險高 (參數類型嚴苛) | 完全相容 | 需逐處改寫 |
| 實作成本 | 0 | 0 | 每月訂閱計費或0 |
方案二：Cell-Level Encryption
搭配 View 封裝
Trigger 轉寫
影子憑證輪替
兼顧「合規、效能、低開發負擔」的最佳實踐。
這是合規稽核每年必問的考題。在我們選用的方案中...
只換外部保護層 (KEK)，不動對稱金鑰 (DEK)，不需要重掃百萬筆資料，完成輪替。
/*
=========================================================================================
Procedure Name : usp_DeployEncryption
Description : 自動化部署資料庫加密基礎設施 (DMK, Certificates)
並部署 SH 架構下的高效能加解密 Functions (DECRYPTBYKEY 版)
Date : 2024-03-06
=========================================================================================
*/
USE [master];
GO
IF OBJECT_ID('dbo.usp_DeployEncryption', 'P') IS NOT NULL
DROP PROCEDURE dbo.usp_DeployEncryption;
GO
CREATE PROCEDURE dbo.usp_DeployEncryption
@MasterKeyPassword NVARCHAR(128),
@BackupPassword NVARCHAR(128),
@CertName NVARCHAR(128) = 'CommonCert',
@CertSubject NVARCHAR(256) = 'Data Protection 2026-2031',
@SymKeyName NVARCHAR(128) = 'SymKey_Data',
@BackupFolder NVARCHAR(256) = 'D:\MSSQL\Backup\', -- 憑證備份目錄
@ProviderName NVARCHAR(128) = NULL, -- 選擇性：EKM Provider 名稱
@VaultKeyName NVARCHAR(128) = NULL, -- 選擇性：Vault 中的不對稱金鑰名稱
@ExecMode BIT = 0 -- 0: 預覽, 1: 執行
AS
BEGIN
SET NOCOUNT ON;
-- 基礎檢核
IF @MasterKeyPassword IS NULL OR @BackupPassword IS NULL
BEGIN
RAISERROR('MasterKeyPassword and BackupPassword are required.', 16, 1);
RETURN;
END
IF RIGHT(@BackupFolder, 1) <> '\' SET @BackupFolder = @BackupFolder + '\';
DECLARE @DBName NVARCHAR(128);
DECLARE @DynamicSQL NVARCHAR(MAX);
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE database_id > 4 AND state_desc = 'ONLINE' AND is_read_only = 0 ORDER BY name;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DynamicSQL = '
USE ' + QUOTENAME(@DBName) + ';
BEGIN TRY
---------------------------------------------------------
-- 1. 加密基礎設施 (SMK -> DMK -> CERT -> SymKey)
---------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''' + REPLACE(@MasterKeyPassword, '''', '''''') + ''';
IF NOT EXISTS (SELECT * FROM sys.key_encryptions WHERE key_id = 101 AND crypt_type = ''SMK'')
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = ''' + REPLACE(@CertName, '''', '''''') + ''')
BEGIN
CREATE CERTIFICATE ' + QUOTENAME(@CertName) + '
WITH SUBJECT = ''' + REPLACE(@CertSubject, '''', '''''') + ''', EXPIRY_DATE = ''' + CONVERT(NVARCHAR(10), DATEADD(YEAR, 5, GETDATE()), 120) + ''';
-- [重要]：建立後的實體加密備份 (Disaster Recovery)
DECLARE @CerPath NVARCHAR(500) = ''' + @BackupFolder + @DBName + '_' + @CertName + '.cer'';
DECLARE @PvkPath NVARCHAR(500) = ''' + @BackupFolder + @DBName + '_' + @CertName + '.pvk'';
BACKUP CERTIFICATE ' + QUOTENAME(@CertName) + '
TO FILE = @CerPath
WITH PRIVATE KEY
(
FILE = @PvkPath
, ENCRYPTION BY PASSWORD = ''' + REPLACE(@BackupPassword, '''', '''''') + '''
);
END
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''' + REPLACE(@SymKeyName, '''', '''''') + ''')
BEGIN
DECLARE @CreateKeySQL NVARCHAR(MAX) = ''CREATE SYMMETRIC KEY ' + QUOTENAME(@SymKeyName) + ' WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE ' + QUOTENAME(@CertName) + ''';
-- 若提供 EKM 參數，則嘗試使用雙重加密 (信封加密模式)
IF ''' + ISNULL(@ProviderName, '') + ''' <> '''' AND ''' + ISNULL(@VaultKeyName, '') + ''' <> ''''
BEGIN
-- 注意：這裡假設 Asymmetric Key 已經在 Master 被建立或映射
SET @CreateKeySQL += '', ASYMMETRIC KEY ' + QUOTENAME(ISNULL(@VaultKeyName, '')) + ''';
END
EXEC(@CreateKeySQL);
PRINT ''[INFO] Database [' + @DBName + '] Symmetric Key Created.'';
END
---------------------------------------------------------
-- 2. SH 封裝邏輯 (高效能、EKM 兼容)
---------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = ''SH'') EXEC(''CREATE SCHEMA [SH]'');
-- Open Key (帶有存在性檢查)
EXEC(''CREATE OR ALTER PROCEDURE SH.open_key WITH ENCRYPTION AS
BEGIN SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM sys.openkeys WHERE key_name = ''''' + REPLACE(@SymKeyName, '''', '''''') + ''''')
OPEN SYMMETRIC KEY ' + QUOTENAME(@SymKeyName) + ' DECRYPTION BY CERTIFICATE ' + QUOTENAME(@CertName) + '; END'');
-- Close Key
EXEC(''CREATE OR ALTER PROCEDURE SH.close_key WITH ENCRYPTION AS
BEGIN SET NOCOUNT ON;
IF EXISTS (SELECT * FROM sys.openkeys WHERE key_name = ''''' + REPLACE(@SymKeyName, '''', '''''') + ''''')
CLOSE SYMMETRIC KEY ' + QUOTENAME(@SymKeyName) + '; END'');
-- Encrypt Function
EXEC(''CREATE OR ALTER FUNCTION SH.encrypt (@p NVARCHAR(MAX)) RETURNS VARBINARY(MAX) WITH ENCRYPTION AS
BEGIN RETURN ENCRYPTBYKEY(KEY_GUID(''''' + REPLACE(@SymKeyName, '''', '''''') + '''''), @p); END'');
-- Decrypt Function (標準版：支授 185 萬筆高效能與未來 Vault 遷移)
EXEC(''CREATE OR ALTER FUNCTION SH.decrypt (@c VARBINARY(MAX)) RETURNS NVARCHAR(MAX) WITH ENCRYPTION AS
BEGIN RETURN CONVERT(NVARCHAR(MAX), DECRYPTBYKEY(@c)); END'');
---------------------------------------------------------
-- 3. 批量權限發放 (扁平化優化)
---------------------------------------------------------
DECLARE @GrantSQL NVARCHAR(MAX) = '''';
SELECT @GrantSQL += ''
GRANT CONTROL ON CERTIFICATE::' + QUOTENAME(@CertName) + ' TO '' + QUOTENAME(name) + '';
GRANT VIEW DEFINITION, REFERENCES ON SYMMETRIC KEY::' + QUOTENAME(@SymKeyName) + ' TO '' + QUOTENAME(name) + '';
GRANT EXECUTE ON SCHEMA::SH TO '' + QUOTENAME(name) + ''; ''
FROM sys.database_principals WHERE name IN (''apuser'',''app_user'',''tableadm'');
IF @GrantSQL <> '''' EXEC sp_executesql @GrantSQL;
PRINT ''[SUCCESS] Database [' + @DBName + '] Processing Complete.'';
END TRY
BEGIN CATCH
PRINT ''[FAILED] Database [' + @DBName + '] Error: '' + ERROR_MESSAGE();
END CATCH;
';
-- 顯示預覽或執行
IF @ExecMode = 0
SELECT @DBName AS [DB], @DynamicSQL AS [Script];
ELSE
EXEC sp_executesql @DynamicSQL;
FETCH NEXT FROM db_cursor INTO @DBName;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
END
GO
/*
=========================================================================================
Step : 2
Action : 實體表遷移與欄位擴充
實體表 AT.InsuTxX -> AT.InsuTxEX
欄位 cID -> eID
dProposerDOB -> eProposerDOB
CREATE OR ALTER VIEW AT.InsuTxX
...
ISNULL(SH.decrypt([eID]), [cID]) AS [cID]
,ISNULL(TRY_CAST(SH.decrypt([eProposerDOB]) AS DATE), [dProposerDOB]) AS [dProposerDOB]
...
FROM [AT].[InsuTxEX];
=========================================================================================
*/
USE [NYDB];
GO
SET NOCOUNT ON;
-- (1) 實體表更名
IF OBJECT_ID('AT.InsuTxX', 'U') IS NOT NULL AND OBJECT_ID('AT.InsuTxEX', 'U') IS NULL
BEGIN
EXEC sp_rename 'AT.InsuTxX', 'InsuTxEX';
PRINT '>>> [SUCCESS] 實體表已更名為 AT.InsuTxEX';
END
GO
-- (2) 欄位擴充 (VARBINARY 密文與雜湊)
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID('AT.InsuTxEX'))
BEGIN
ALTER TABLE AT.InsuTxEX
ADD
eID VARBINARY(128)
, hID VARBINARY(32)
, eInsurantName VARBINARY(256)
, eBenificiaryName VARBINARY(256)
, eCustDOB VARBINARY(128)
, eMobilePhone VARBINARY(128)
, eEmail VARBINARY(256)
, eProposerId VARBINARY(128)
, eProposerDOB VARBINARY(128)
, eProposerAddr1 VARBINARY(256)
, eProposerPhone VARBINARY(128)
, eProposerMobile VARBINARY(128)
, eProposerEmail VARBINARY(256)
, eProposerName VARBINARY(256)
, eContactAddr VARBINARY(256)
, eProposerAddr VARBINARY(256);
PRINT '>>> [SUCCESS] 加密與雜湊欄位已新增完成';
END
GO
CREATE OR ALTER VIEW AT.InsuTxX
AS
SELECT
[cAplySNO]
, [cInsuProviderIdn]
, [cEngNO]
, [cSaleEmpIdn]
, [cSaleDeptIdn]
, [cOriginEmpIdn]
, [cOriginDeptIdn]
, [cNewOrRenewal]
, [nPremiumForCarDamage]
, [nPremiumForAccident]
, [nPremiumForCompulsory]
, [nPremiumForTheft]
, [nPremiumForDisaster]
, [nTotalPremium]
, [nPremiumReceive]
, [cAssistantEmpIdn]
, [cMgtDeptIdn]
, [cCorpIdn]
, [cOrderSNo]
, [cOriginAplySNO]
, [cChangeCtg]
, [dEffectDate]
, [dExpireDate]
, [dVolEffectDate]
, [dVolExpireDate]
, [cCompulsoryPolicySNO]
, [cVoluntaryInsuPolicySNO]
, [cAgainstThief]
, [cInstallmentFlag]
, [cIdType]
, ISNULL(SH.decrypt([eID]), [cID]) AS [cID]
, ISNULL(SH.decrypt([eInsurantName]), [cInsurantName]) AS [cInsurantName]
, [cGender]
, ISNULL(SH.decrypt([eBenificiaryName]), [cBenificiaryName]) AS [cBenificiaryName]
, [cBenificiaryName2]
, [cContactTelNO]
, [cContactAddr1]
, ISNULL(TRY_CAST(SH.decrypt([eCustDOB]) AS DATE), [dCustDOB]) AS [dCustDOB]
, [dAplyDate]
, [dClearDate]
, [dTransDate]
, [cTransSeqNo]
, [dInvDate]
, [nCommisionYM]
, [dSalaryDate]
, [dConfirmDate]
, [cKeyInIdn]
, [cProgIdn]
, [dInsDate]
, [dChgDate]
, [dCancelDate]
, [nRecIdn]
, [cInsuSNo]
, [cCntForWineDanger]
, [cNotationSNo]
, [cSurrenderFlag]
, [dSurrenderDate]
, [cProposer]
, [nKpiYM]
, [cInsuKPIFlag]
, [cZipCode]
, [cStsSelNo]
, [cECardSNo]
, [cNation]
, ISNULL(SH.decrypt([eMobilePhone]), [cMobilePhone]) AS [cMobilePhone]
, ISNULL(SH.decrypt([eEmail]), [cEmail]) AS [cEmail]
, [cContactCtg]
, [cMobilePhoneOrEmailRelation]
, [nChangeCount]
, [cPreAplySNo]
, [cRelationship]
, [cProposerIdType]
, ISNULL(SH.decrypt([eProposerId]), [cProposerId]) AS [cProposerId]
, ISNULL(TRY_CAST(SH.decrypt([eProposerDOB]) AS DATE), [dProposerDOB]) AS [dProposerDOB]
, [cProposerGender]
, [cProposerZipCode]
, ISNULL(SH.decrypt([eProposerAddr1]), [cProposerAddr1]) AS [cProposerAddr1]
, ISNULL(SH.decrypt([eProposerPhone]), [cProposerPhone]) AS [cProposerPhone]
, ISNULL(SH.decrypt([eProposerMobile]), [cProposerMobile]) AS [cProposerMobile]
, ISNULL(SH.decrypt([eProposerEmail]), [cProposerEmail]) AS [cProposerEmail]
, ISNULL(SH.decrypt([eProposerName]), [cProposerName]) AS [cProposerName]
, ISNULL(SH.decrypt([eContactAddr]), [cContactAddr]) AS [cContactAddr]
, ISNULL(SH.decrypt([eProposerAddr]), [cProposerAddr]) AS [cProposerAddr]
, [nPremiumForOther]
, [cPurchaseNo]
, [cSameAddrReason]
, [cSameAddrReasonWithProposer]
, [cAplyKeyInEmpIdn]
, [cPerfDeptIdn]
, [cCarUsageCtg]
, [cCardType]
, [nPremiumForVoluntary]
FROM [AT].[InsuTxEX];
GO
/*
=========================================================================================
Step : 4 (Merged from Step 4)
Action : 同步寫入觸發器 (Transition Trigger)
Effect : 同步更新明文與密文欄位
=========================================================================================
*/
USE [NYDB];
GO
SET NOCOUNT ON;
CREATE OR ALTER TRIGGER AT.trg_InsuTxX_Update
ON AT.InsuTxX INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
EXEC SH.open_key;
UPDATE T
SET
-- (1) 非隱私欄位映射
T.[cAplySNO] = i.[cAplySNO]
, T.[cInsuProviderIdn] = i.[cInsuProviderIdn]
, T.[cEngNO] = i.[cEngNO]
, T.[cSaleEmpIdn] = i.[cSaleEmpIdn]
, T.[cSaleDeptIdn] = i.[cSaleDeptIdn]
, T.[cOriginEmpIdn] = i.[cOriginEmpIdn]
, T.[cOriginDeptIdn] = i.[cOriginDeptIdn]
, T.[cNewOrRenewal] = i.[cNewOrRenewal]
, T.[nPremiumForCarDamage] = i.[nPremiumForCarDamage]
, T.[nPremiumForAccident] = i.[nPremiumForAccident]
, T.[nPremiumForCompulsory] = i.[nPremiumForCompulsory]
, T.[nPremiumForTheft] = i.[nPremiumForTheft]
, T.[nPremiumForDisaster] = i.[nPremiumForDisaster]
, T.[nTotalPremium] = i.[nTotalPremium]
, T.[nPremiumReceive] = i.[nPremiumReceive]
, T.[cAssistantEmpIdn] = i.[cAssistantEmpIdn]
, T.[cMgtDeptIdn] = i.[cMgtDeptIdn]
, T.[cCorpIdn] = i.[cCorpIdn]
, T.[cOrderSNo] = i.[cOrderSNo]
, T.[cOriginAplySNO] = i.[cOriginAplySNO]
, T.[cChangeCtg] = i.[cChangeCtg]
, T.[dEffectDate] = i.[dEffectDate]
, T.[dExpireDate] = i.[dExpireDate]
, T.[dVolEffectDate] = i.[dVolEffectDate]
, T.[dVolExpireDate] = i.[dVolExpireDate]
, T.[cCompulsoryPolicySNO] = i.[cCompulsoryPolicySNO]
, T.[cVoluntaryInsuPolicySNO] = i.[cVoluntaryInsuPolicySNO]
, T.[cAgainstThief] = i.[cAgainstThief]
, T.[cInstallmentFlag] = i.[cInstallmentFlag]
, T.[cIdType] = i.[cIdType]
, T.[cGender] = i.[cGender]
, T.[cBenificiaryName2] = i.[cBenificiaryName2]
, T.[cContactTelNO] = i.[cContactTelNO]
, T.[cContactAddr1] = i.[cContactAddr1]
, T.[dAplyDate] = i.[dAplyDate]
, T.[dClearDate] = i.[dClearDate]
, T.[dTransDate] = i.[dTransDate]
, T.[cTransSeqNo] = i.[cTransSeqNo]
, T.[dInvDate] = i.[dInvDate]
, T.[nCommisionYM] = i.[nCommisionYM]
, T.[dSalaryDate] = i.[dSalaryDate]
, T.[dConfirmDate] = i.[dConfirmDate]
, T.[cKeyInIdn] = i.[cKeyInIdn]
, T.[cProgIdn] = i.[cProgIdn]
, T.[dInsDate] = i.[dInsDate]
, T.[dChgDate] = i.[dChgDate]
, T.[dCancelDate] = i.[dCancelDate]
, T.[cInsuSNo] = i.[cInsuSNo]
, T.[cCntForWineDanger] = i.[cCntForWineDanger]
, T.[cNotationSNo] = i.[cNotationSNo]
, T.[cSurrenderFlag] = i.[cSurrenderFlag]
, T.[dSurrenderDate] = i.[dSurrenderDate]
, T.[cProposer] = i.[cProposer]
, T.[nKpiYM] = i.[nKpiYM]
, T.[cInsuKPIFlag] = i.[cInsuKPIFlag]
, T.[cZipCode] = i.[cZipCode]
, T.[cStsSelNo] = i.[cStsSelNo]
, T.[cECardSNo] = i.[cECardSNo]
, T.[cNation] = i.[cNation]
, T.[cContactCtg] = i.[cContactCtg]
, T.[cMobilePhoneOrEmailRelation] = i.[cMobilePhoneOrEmailRelation]
, T.[nChangeCount] = i.[nChangeCount]
, T.[cPreAplySNo] = i.[cPreAplySNo]
, T.[cRelationship] = i.[cRelationship]
, T.[cProposerIdType] = i.[cProposerIdType]
, T.[cProposerGender] = i.[cProposerGender]
, T.[cProposerZipCode] = i.[cProposerZipCode]
, T.[nPremiumForOther] = i.[nPremiumForOther]
, T.[cPurchaseNo] = i.[cPurchaseNo]
, T.[cSameAddrReason] = i.[cSameAddrReason]
, T.[cSameAddrReasonWithProposer] = i.[cSameAddrReasonWithProposer]
, T.[cAplyKeyInEmpIdn] = i.[cAplyKeyInEmpIdn]
, T.[cPerfDeptIdn] = i.[cPerfDeptIdn]
, T.[cCarUsageCtg] = i.[cCarUsageCtg]
, T.[cCardType] = i.[cCardType]
, T.[nPremiumForVoluntary] = i.[nPremiumForVoluntary]
-- (2) 明文欄位同步 (過渡期保留)
, T.[cID] = i.[cID]
, T.[cInsurantName] = i.[cInsurantName]
, T.[cBenificiaryName] = i.[cBenificiaryName]
, T.[dCustDOB] = i.[dCustDOB]
, T.[cMobilePhone] = i.[cMobilePhone]
, T.[cEmail] = i.[cEmail]
, T.[cProposerId] = i.[cProposerId]
, T.[dProposerDOB] = i.[dProposerDOB]
, T.[cProposerAddr1] = i.[cProposerAddr1]
, T.[cProposerPhone] = i.[cProposerPhone]
, T.[cProposerMobile] = i.[cProposerMobile]
, T.[cProposerEmail] = i.[cProposerEmail]
, T.[cProposerName] = i.[cProposerName]
, T.[cContactAddr] = i.[cContactAddr]
, T.[cProposerAddr] = i.[cProposerAddr]
-- (3) 隱私欄位加密 (ISNULL 防護)
, T.[eID] = ISNULL(SH.encrypt(i.[cID]), i.[cID])
, T.[hID] = HASHBYTES('SHA2_256', i.[cID])
, T.[eInsurantName] = ISNULL(SH.encrypt(i.[cInsurantName]), i.[cInsurantName])
, T.[eBenificiaryName] = ISNULL(SH.encrypt(i.[cBenificiaryName]), i.[cBenificiaryName])
, T.[eCustDOB] = SH.encrypt(CONVERT(NVARCHAR(50), i.[dCustDOB], 120))
, T.[eMobilePhone] = ISNULL(SH.encrypt(i.[cMobilePhone]), i.[cMobilePhone])
, T.[eEmail] = ISNULL(SH.encrypt(i.[cEmail]), i.[cEmail])
, T.[eProposerId] = ISNULL(SH.encrypt(i.[cProposerId]), i.[cProposerId])
, T.[eProposerDOB] = SH.encrypt(CONVERT(NVARCHAR(50), i.[dProposerDOB], 120))
, T.[eProposerAddr1] = ISNULL(SH.encrypt(i.[cProposerAddr1]), i.[cProposerAddr1])
, T.[eProposerPhone] = ISNULL(SH.encrypt(i.[cProposerPhone]), i.[cProposerPhone])
, T.[eProposerMobile] = ISNULL(SH.encrypt(i.[cProposerMobile]), i.[cProposerMobile])
, T.[eProposerEmail] = ISNULL(SH.encrypt(i.[cProposerEmail]), i.[cProposerEmail])
, T.[eProposerName] = ISNULL(SH.encrypt(i.[cProposerName]), i.[cProposerName])
, T.[eContactAddr] = ISNULL(SH.encrypt(i.[cContactAddr]), i.[cContactAddr])
, T.[eProposerAddr] = ISNULL(SH.encrypt(i.[cProposerAddr]), i.[cProposerAddr])
FROM [AT].[InsuTxEX] AS T
INNER JOIN inserted AS i ON T.[nRecIdn] = i.[nRecIdn];
EXEC SH.close_key;
END
END;
GO
CREATE OR ALTER TRIGGER AT.trg_InsuTxX_Insert
ON AT.InsuTxX INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
EXEC SH.open_key;
INSERT INTO [AT].[InsuTxEX] (
-- (1) 非隱私欄位映射
[cAplySNO]
, [cInsuProviderIdn]
, [cEngNO]
, [cSaleEmpIdn]
, [cSaleDeptIdn]
, [cOriginEmpIdn]
, [cOriginDeptIdn]
, [cNewOrRenewal]
, [nPremiumForCarDamage]
, [nPremiumForAccident]
, [nPremiumForCompulsory]
, [nPremiumForTheft]
, [nPremiumForDisaster]
, [nTotalPremium]
, [nPremiumReceive]
, [cAssistantEmpIdn]
, [cMgtDeptIdn]
, [cCorpIdn]
, [cOrderSNo]
, [cOriginAplySNO]
, [cChangeCtg]
, [dEffectDate]
, [dExpireDate]
, [dVolEffectDate]
, [dVolExpireDate]
, [cCompulsoryPolicySNO]
, [cVoluntaryInsuPolicySNO]
, [cAgainstThief]
, [cInstallmentFlag]
, [cIdType]
, [cGender]
, [cBenificiaryName2]
, [cContactTelNO]
, [cContactAddr1]
, [dAplyDate]
, [dClearDate]
, [dTransDate]
, [cTransSeqNo]
, [dInvDate]
, [nCommisionYM]
, [dSalaryDate]
, [dConfirmDate]
, [cKeyInIdn]
, [cProgIdn]
, [dInsDate]
, [dChgDate]
, [dCancelDate]
, [cInsuSNo]
, [cCntForWineDanger]
, [cNotationSNo]
, [cSurrenderFlag]
, [dSurrenderDate]
, [cProposer]
, [nKpiYM]
, [cInsuKPIFlag]
, [cZipCode]
, [cStsSelNo]
, [cECardSNo]
, [cNation]
, [cContactCtg]
, [cMobilePhoneOrEmailRelation]
, [nChangeCount]
, [cPreAplySNo]
, [cRelationship]
, [cProposerIdType]
, [cProposerGender]
, [cProposerZipCode]
, [nPremiumForOther]
, [cPurchaseNo]
, [cSameAddrReason]
, [cSameAddrReasonWithProposer]
, [cAplyKeyInEmpIdn]
, [cPerfDeptIdn]
, [cCarUsageCtg]
, [cCardType]
, [nPremiumForVoluntary]
-- (2) 明文欄位同步 (過渡期保留)
, [cID]
, [cInsurantName]
, [cBenificiaryName]
, [dCustDOB]
, [cMobilePhone]
, [cEmail]
, [cProposerId]
, [dProposerDOB]
, [cProposerAddr1]
, [cProposerPhone]
, [cProposerMobile]
, [cProposerEmail]
, [cProposerName]
, [cContactAddr]
, [cProposerAddr]
-- (3) 隱私欄位加密 (ISNULL 防護)
, [eID]
, [hID]
, [eInsurantName]
, [eBenificiaryName]
, [eCustDOB]
, [eMobilePhone]
, [eEmail]
, [eProposerId]
, [eProposerDOB]
, [eProposerAddr1]
, [eProposerPhone]
, [eProposerMobile]
, [eProposerEmail]
, [eProposerName]
, [eContactAddr]
, [eProposerAddr]
)
SELECT
-- (1) 非隱私欄位映射
[cAplySNO]
, [cInsuProviderIdn]
, [cEngNO]
, [cSaleEmpIdn]
, [cSaleDeptIdn]
, [cOriginEmpIdn]
, [cOriginDeptIdn]
, [cNewOrRenewal]
, [nPremiumForCarDamage]
, [nPremiumForAccident]
, [nPremiumForCompulsory]
, [nPremiumForTheft]
, [nPremiumForDisaster]
, [nTotalPremium]
, [nPremiumReceive]
, [cAssistantEmpIdn]
, [cMgtDeptIdn]
, [cCorpIdn]
, [cOrderSNo]
, [cOriginAplySNO]
, [cChangeCtg]
, [dEffectDate]
, [dExpireDate]
, [dVolEffectDate]
, [dVolExpireDate]
, [cCompulsoryPolicySNO]
, [cVoluntaryInsuPolicySNO]
, [cAgainstThief]
, [cInstallmentFlag]
, [cIdType]
, [cGender]
, [cBenificiaryName2]
, [cContactTelNO]
, [cContactAddr1]
, [dAplyDate]
, [dClearDate]
, [dTransDate]
, [cTransSeqNo]
, [dInvDate]
, [nCommisionYM]
, [dSalaryDate]
, [dConfirmDate]
, [cKeyInIdn]
, [cProgIdn]
, [dInsDate]
, [dChgDate]
, [dCancelDate]
, [cInsuSNo]
, [cCntForWineDanger]
, [cNotationSNo]
, [cSurrenderFlag]
, [dSurrenderDate]
, [cProposer]
, [nKpiYM]
, [cInsuKPIFlag]
, [cZipCode]
, [cStsSelNo]
, [cECardSNo]
, [cNation]
, [cContactCtg]
, [cMobilePhoneOrEmailRelation]
, [nChangeCount]
, [cPreAplySNo]
, [cRelationship]
, [cProposerIdType]
, [cProposerGender]
, [cProposerZipCode]
, [nPremiumForOther]
, [cPurchaseNo]
, [cSameAddrReason]
, [cSameAddrReasonWithProposer]
, [cAplyKeyInEmpIdn]
, [cPerfDeptIdn]
, [cCarUsageCtg]
, [cCardType]
, [nPremiumForVoluntary]
-- (2) 明文欄位同步 (過渡期保留)
, [cID]
, [cInsurantName]
, [cBenificiaryName]
, [dCustDOB]
, [cMobilePhone]
, [cEmail]
, [cProposerId]
, [dProposerDOB]
, [cProposerAddr1]
, [cProposerPhone]
, [cProposerMobile]
, [cProposerEmail]
, [cProposerName]
, [cContactAddr]
, [cProposerAddr]
-- (3) 隱私欄位加密 (ISNULL 防護)
, ISNULL(SH.encrypt([cID]), [cID])
, HASHBYTES('SHA2_256', [cID])
, ISNULL(SH.encrypt([cInsurantName]), [cInsurantName])
, ISNULL(SH.encrypt([cBenificiaryName]), [cBenificiaryName])
, SH.encrypt(CONVERT(NVARCHAR(50), [dCustDOB], 120))
, ISNULL(SH.encrypt([cMobilePhone]), [cMobilePhone])
, ISNULL(SH.encrypt([cEmail]), [cEmail])
, ISNULL(SH.encrypt([cProposerId]), [cProposerId])
, SH.encrypt(CONVERT(NVARCHAR(50), [dProposerDOB], 120))
, ISNULL(SH.encrypt([cProposerAddr1]),[cProposerAddr1])
, ISNULL(SH.encrypt([cProposerPhone]), [cProposerPhone])
, ISNULL(SH.encrypt([cProposerMobile]), [cProposerMobile])
, ISNULL(SH.encrypt([cProposerEmail]), [cProposerEmail])
, ISNULL(SH.encrypt([cProposerName]), [cProposerName])
, ISNULL(SH.encrypt([cContactAddr]), [cContactAddr])
, ISNULL(SH.encrypt([cProposerAddr]),[cProposerAddr])
FROM inserted;
EXEC SH.close_key;
END
END;
GO
/*
=========================================================================================
Step : 3
Action : 產生移除隱私欄位索引語法 (Generate Drop Indexes)
Effect : 產生 DROP INDEX 語句
=========================================================================================
*/
USE [NYDB];
GO
SET NOCOUNT ON;
DECLARE @TargetTable NVARCHAR(128) = 'InsuTxEX';
WITH RawColumnData AS (
SELECT
i.name COLLATE DATABASE_DEFAULT AS IndexName
, i.type_desc COLLATE DATABASE_DEFAULT AS IndexType
, ic.is_included_column
, ic.key_ordinal
, c.name COLLATE DATABASE_DEFAULT AS OriginalColumnName
, CASE
WHEN c.name IN (
'cID', 'cInsurantName', 'cBenificiaryName', 'dCustDOB'
, 'cMobilePhone', 'cEmail', 'cProposerId', 'dProposerDOB'
, 'cProposerAddr1', 'cProposerPhone', 'cProposerMobile'
, 'cProposerEmail', 'cProposerName', 'cContactAddr', 'cProposerAddr'
) THEN 1 ELSE 0 END AS IsPrivacyColumn
, s.name AS SchemaName
, t.name AS TableName
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.tables AS t ON i.object_id = t.object_id
JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE i.object_id = OBJECT_ID('AT.InsuTxEX')
AND i.is_primary_key = 0
AND i.type > 0
)
, FilteredIndexes AS (
SELECT IndexName, SchemaName, TableName FROM RawColumnData GROUP BY IndexName, SchemaName, TableName HAVING SUM(IsPrivacyColumn) > 0
)
SELECT
'IF EXISTS (SELECT * FROM sys.indexes WHERE name = ''' + f.IndexName + ''') ' +
'DROP INDEX [' + f.IndexName + '] ON [' + f.SchemaName + '].[' + f.TableName + '];' AS [Step1_Index_Drop_Script]
FROM FilteredIndexes f;
GO
/*
=========================================================================================
Step : 4
Action : 初始資料加密移轉 (Data Migration)
Requirement: 需具備 Master Key 密碼以開啟金鑰
Note : 批次執行，支援斷線續跑 (Resumable)
=========================================================================================
*/
USE [NYDB];
GO
SET NOCOUNT ON;
---------------------------------------------------------
-- 參數設定
---------------------------------------------------------
DECLARE @BatchSize INT = 10000; -- 每批次 1 萬筆
DECLARE @RowsAffected INT = 1
, @TotalProcessed INT = 0
, @LoopCount INT = 0;
EXEC SH.open_key;
WHILE (
@RowsAffected > 0
)
BEGIN
UPDATE TOP (@BatchSize) T WITH (ROWLOCK)
SET
T.[eID] = SH.encrypt(T.[cID])
, T.[hID] = HASHBYTES('SHA2_256', T.[cID])
, T.[eInsurantName] = SH.encrypt(T.[cInsurantName])
, T.[eBenificiaryName] = SH.encrypt(T.[cBenificiaryName])
, T.[eCustDOB] = SH.encrypt(CONVERT(NVARCHAR(50), T.[dCustDOB], 120))
, T.[eMobilePhone] = SH.encrypt(T.[cMobilePhone])
, T.[eEmail] = SH.encrypt(T.[cEmail])
, T.[eProposerId] = SH.encrypt(T.[cProposerId])
, T.[eProposerDOB] = SH.encrypt(CONVERT(NVARCHAR(50), T.[dProposerDOB], 120))
, T.[eProposerAddr1] = SH.encrypt(T.[cProposerAddr1])
, T.[eProposerPhone] = SH.encrypt(T.[cProposerPhone])
, T.[eProposerMobile] = SH.encrypt(T.[cProposerMobile])
, T.[eProposerEmail] = SH.encrypt(T.[cProposerEmail])
, T.[eProposerName] = SH.encrypt(T.[cProposerName])
, T.[eContactAddr] = SH.encrypt(T.[cContactAddr])
, T.[eProposerAddr] = SH.encrypt(T.[cProposerAddr])
FROM AT.InsuTxEX AS T
WHERE
T.[eID] IS NULL
AND T.[cID] IS NOT NULL;
SET @RowsAffected = @@ROWCOUNT;
SET @TotalProcessed = @TotalProcessed + @RowsAffected;
SET @LoopCount = @LoopCount + 1;
IF (
@RowsAffected > 0
)
BEGIN
-- 每 5 萬筆進行一次檢查點
IF (
@LoopCount % 5 = 0
)
BEGIN
CHECKPOINT;
-- 偵測 Transaction Log 使用率
DECLARE @LogUsed FLOAT;
SELECT @LogUsed = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Percent Log Used' AND instance_name = DB_NAME();
IF @LogUsed > 80
BEGIN
PRINT '!!! [WARNING] Transaction Log 使用率已達 ' + CAST(@LogUsed AS VARCHAR) + '%，請 DBA 確認 Log 備份排程！';
WAITFOR DELAY '00:00:30'; -- 多等一些，讓 Log 備份有機會追上
END
ELSE
BEGIN
PRINT '>>> 已處理: ' + CAST(@TotalProcessed AS VARCHAR) + ' 筆 (Log Usage: ' + CAST(@LogUsed AS VARCHAR) + '%)';
END
END
-- 喘息時間 2 秒，讓出 CPU/IO 給其他查詢
WAITFOR DELAY '00:00:02';
END
END
EXEC SH.close_key;
PRINT '==================================================';
PRINT ' 共處理: ' + CAST(@TotalProcessed AS VARCHAR) + ' 筆';
PRINT ' 共執行: ' + CAST(@LoopCount AS VARCHAR) + ' 批次';
PRINT '==================================================';
GO
/*
=========================================================================================
Step : 5
Action : 產生重建加密欄位索引語法 (Generate Create Indexes)
Effect : 產生 CREATE INDEX 語句
=========================================================================================
*/
USE [NYDB];
GO
SET NOCOUNT ON;
DECLARE @TargetTable NVARCHAR(128) = 'AT.InsuTxEX';
WITH RawColumnData AS (
SELECT
i.name COLLATE DATABASE_DEFAULT AS IndexName
, i.type_desc COLLATE DATABASE_DEFAULT AS IndexType
, ic.is_included_column
, ic.key_ordinal
, c.name COLLATE DATABASE_DEFAULT AS OriginalColumnName
, CASE
WHEN c.name IN (
'cID', 'cInsurantName', 'cBenificiaryName', 'dCustDOB'
, 'cMobilePhone', 'cEmail', 'cProposerId', 'dProposerDOB'
, 'cProposerAddr1', 'cProposerPhone', 'cProposerMobile'
, 'cProposerEmail', 'cProposerName', 'cContactAddr', 'cProposerAddr'
) THEN 1 ELSE 0 END AS IsPrivacyColumn
, s.name AS SchemaName
, t.name AS TableName
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.tables AS t ON i.object_id = t.object_id
JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE i.object_id = OBJECT_ID(@TargetTable)
AND i.is_primary_key = 0
AND i.type > 0
)
, FilteredIndexes AS (
SELECT IndexName, SchemaName, TableName FROM RawColumnData GROUP BY IndexName, SchemaName, TableName HAVING SUM(IsPrivacyColumn) > 0
)
, ColumnMapping AS (
SELECT
r.IndexName, r.IndexType, r.is_included_column, r.key_ordinal
, CASE
WHEN r.OriginalColumnName = 'cID' AND r.is_included_column = 0 THEN 'hID'
WHEN r.OriginalColumnName = 'cID' AND r.is_included_column = 1 THEN 'eID'
WHEN r.IsPrivacyColumn = 1 THEN ('e' + SUBSTRING(r.OriginalColumnName, 2, LEN(r.OriginalColumnName))) COLLATE DATABASE_DEFAULT
ELSE r.OriginalColumnName END AS MappedColumnName
FROM RawColumnData AS r
INNER JOIN FilteredIndexes AS f ON r.IndexName = f.IndexName
)
, AggregatedKeys AS (
SELECT IndexName, MAX(IndexType) AS IndexType
, STUFF((SELECT CHAR(13)+CHAR(10)+' , ['+MappedColumnName+']' FROM ColumnMapping im WHERE im.IndexName=om.IndexName AND im.is_included_column=0 ORDER BY im.key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ') AS KeyList
FROM ColumnMapping AS om GROUP BY IndexName
)
, AggregatedIncludes AS (
SELECT IndexName
, STUFF((SELECT CHAR(13)+CHAR(10)+' , ['+MappedColumnName+']' FROM ColumnMapping im WHERE im.IndexName=om.IndexName AND im.is_included_column=1 ORDER BY im.key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ') AS IncludeList
FROM ColumnMapping AS om GROUP BY IndexName
)
SELECT
'CREATE ' + ak.IAT.IndexType + ' INDEX [' + ak.IndexName + ']' + CHAR(13) + CHAR(10) +
'ON ' + @TargetTable + CHAR(13) + CHAR(10) +
'(' + CHAR(13) + CHAR(10) + ak.KeyList + CHAR(13) + CHAR(10) + ')' +
CASE WHEN ai.IncludeList IS NOT NULL THEN CHAR(13)+CHAR(10)+'INCLUDE ('+CHAR(13)+CHAR(10)+ai.IncludeList+CHAR(13)+CHAR(10)+')' ELSE '' END + CHAR(13) + CHAR(10) +
'WITH (FILLFACTOR = 90);' AS [Step2_Index_Rebuild_Script]
FROM AggregatedKeys ak
LEFT JOIN AggregatedIncludes ai ON ak.IndexName = ai.IndexName;
GO
/*
=========================================================================================
Step : 7
Action : 物理資料清潔 (Final Sanitization)
Effect : 刪除明文欄位並更新 View / Trigger 為最終密文版
=========================================================================================
*/
USE [NYDB];
GO
SET NOCOUNT ON;
-- (1) 更新 View 為終極密文版本
ALTER VIEW AT.InsuTxX
AS
SELECT
-- (1) 非隱私欄位映射
[cAplySNO]
, [cInsuProviderIdn]
, [cEngNO]
, [cSaleEmpIdn]
, [cSaleDeptIdn]
, [cOriginEmpIdn]
, [cOriginDeptIdn]
, [cNewOrRenewal]
, [nPremiumForCarDamage]
, [nPremiumForAccident]
, [nPremiumForCompulsory]
, [nPremiumForTheft]
, [nPremiumForDisaster]
, [nTotalPremium]
, [nPremiumReceive]
, [cAssistantEmpIdn]
, [cMgtDeptIdn]
, [cCorpIdn]
, [cOrderSNo]
, [cOriginAplySNO]
, [cChangeCtg]
, [dEffectDate]
, [dExpireDate]
, [dVolEffectDate]
, [dVolExpireDate]
, [cCompulsoryPolicySNO]
, [cVoluntaryInsuPolicySNO]
, [cAgainstThief]
, [cInstallmentFlag]
, [cIdType]
, [cGender]
, [cBenificiaryName2]
, [cContactTelNO]
, [cContactAddr1]
, [dAplyDate]
, [dClearDate]
, [dTransDate]
, [cTransSeqNo]
, [dInvDate]
, [nCommisionYM]
, [dSalaryDate]
, [dConfirmDate]
, [cKeyInIdn]
, [cProgIdn]
, [dInsDate]
, [dChgDate]
, [dCancelDate]
, [nRecIdn]
, [cInsuSNo]
, [cCntForWineDanger]
, [cNotationSNo]
, [cSurrenderFlag]
, [dSurrenderDate]
, [cProposer]
, [nKpiYM]
, [cInsuKPIFlag]
, [cZipCode]
, [cStsSelNo]
, [cECardSNo]
, [cNation]
, [cContactCtg]
, [cMobilePhoneOrEmailRelation]
, [nChangeCount]
, [cPreAplySNo]
, [cRelationship]
, [cProposerIdType]
, [cProposerGender]
, [cProposerZipCode]
, [nPremiumForOther]
, [cPurchaseNo]
, [cSameAddrReason]
, [cSameAddrReasonWithProposer]
, [cAplyKeyInEmpIdn]
, [cPerfDeptIdn]
, [cCarUsageCtg]
, [cCardType]
, [nPremiumForVoluntary]
-- (2) 隱私欄位加密 (純密文模式)
, SH.decrypt([eID]) AS [cID]
, SH.decrypt([eInsurantName]) AS [cInsurantName]
, SH.decrypt([eBenificiaryName]) AS [cBenificiaryName]
, TRY_CAST(SH.decrypt([eCustDOB]) AS DATE) AS [dCustDOB]
, SH.decrypt([eMobilePhone]) AS [cMobilePhone]
, SH.decrypt([eEmail]) AS [cEmail]
, SH.decrypt([eProposerId]) AS [cProposerId]
, TRY_CAST(SH.decrypt([eProposerDOB]) AS DATE) AS [dProposerDOB]
, SH.decrypt([eProposerAddr1]) AS [cProposerAddr1]
, SH.decrypt([eProposerPhone]) AS [cProposerPhone]
, SH.decrypt([eProposerMobile]) AS [cProposerMobile]
, SH.decrypt([eProposerEmail]) AS [cProposerEmail]
, SH.decrypt([eProposerName]) AS [cProposerName]
, SH.decrypt([eContactAddr]) AS [cContactAddr]
, SH.decrypt([eProposerAddr]) AS [cProposerAddr]
FROM [AT].[InsuTxEX];
GO
-- (2) 更新 UPDATE Trigger (移除明文寫入)
CREATE OR ALTER TRIGGER AT.trg_InsuTxX_Update
ON AT.InsuTxX INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
EXEC SH.open_key;
UPDATE T
SET
-- (1) 非隱私欄位映射
T.[cAplySNO] = i.[cAplySNO]
, T.[cInsuProviderIdn] = i.[cInsuProviderIdn]
, T.[cEngNO] = i.[cEngNO]
, T.[cSaleEmpIdn] = i.[cSaleEmpIdn]
, T.[cSaleDeptIdn] = i.[cSaleDeptIdn]
, T.[cOriginEmpIdn] = i.[cOriginEmpIdn]
, T.[cOriginDeptIdn] = i.[cOriginDeptIdn]
, T.[cNewOrRenewal] = i.[cNewOrRenewal]
, T.[nPremiumForCarDamage] = i.[nPremiumForCarDamage]
, T.[nPremiumForAccident] = i.[nPremiumForAccident]
, T.[nPremiumForCompulsory] = i.[nPremiumForCompulsory]
, T.[nPremiumForTheft] = i.[nPremiumForTheft]
, T.[nPremiumForDisaster] = i.[nPremiumForDisaster]
, T.[nTotalPremium] = i.[nTotalPremium]
, T.[nPremiumReceive] = i.[nPremiumReceive]
, T.[cAssistantEmpIdn] = i.[cAssistantEmpIdn]
, T.[cMgtDeptIdn] = i.[cMgtDeptIdn]
, T.[cCorpIdn] = i.[cCorpIdn]
, T.[cOrderSNo] = i.[cOrderSNo]
, T.[cOriginAplySNO] = i.[cOriginAplySNO]
, T.[cChangeCtg] = i.[cChangeCtg]
, T.[dEffectDate] = i.[dEffectDate]
, T.[dExpireDate] = i.[dExpireDate]
, T.[dVolEffectDate] = i.[dVolEffectDate]
, T.[dVolExpireDate] = i.[dVolExpireDate]
, T.[cCompulsoryPolicySNO] = i.[cCompulsoryPolicySNO]
, T.[cVoluntaryInsuPolicySNO] = i.[cVoluntaryInsuPolicySNO]
, T.[cAgainstThief] = i.[cAgainstThief]
, T.[cInstallmentFlag] = i.[cInstallmentFlag]
, T.[cIdType] = i.[cIdType]
, T.[cGender] = i.[cGender]
, T.[cBenificiaryName2] = i.[cBenificiaryName2]
, T.[cContactTelNO] = i.[cContactTelNO]
, T.[cContactAddr1] = i.[cContactAddr1]
, T.[dAplyDate] = i.[dAplyDate]
, T.[dClearDate] = i.[dClearDate]
, T.[dTransDate] = i.[dTransDate]
, T.[cTransSeqNo] = i.[cTransSeqNo]
, T.[dInvDate] = i.[dInvDate]
, T.[nCommisionYM] = i.[nCommisionYM]
, T.[dSalaryDate] = i.[dSalaryDate]
, T.[dConfirmDate] = i.[dConfirmDate]
, T.[cKeyInIdn] = i.[cKeyInIdn]
, T.[cProgIdn] = i.[cProgIdn]
, T.[dInsDate] = i.[dInsDate]
, T.[dChgDate] = i.[dChgDate]
, T.[dCancelDate] = i.[dCancelDate]
, T.[cInsuSNo] = i.[cInsuSNo]
, T.[cCntForWineDanger] = i.[cCntForWineDanger]
, T.[cNotationSNo] = i.[cNotationSNo]
, T.[cSurrenderFlag] = i.[cSurrenderFlag]
, T.[dSurrenderDate] = i.[dSurrenderDate]
, T.[cProposer] = i.[cProposer]
, T.[nKpiYM] = i.[nKpiYM]
, T.[cInsuKPIFlag] = i.[cInsuKPIFlag]
, T.[cZipCode] = i.[cZipCode]
, T.[cStsSelNo] = i.[cStsSelNo]
, T.[cECardSNo] = i.[cECardSNo]
, T.[cNation] = i.[cNation]
, T.[cContactCtg] = i.[cContactCtg]
, T.[cMobilePhoneOrEmailRelation] = i.[cMobilePhoneOrEmailRelation]
, T.[nChangeCount] = i.[nChangeCount]
, T.[cPreAplySNo] = i.[cPreAplySNo]
, T.[cRelationship] = i.[cRelationship]
, T.[cProposerIdType] = i.[cProposerIdType]
, T.[cProposerGender] = i.[cProposerGender]
, T.[cProposerZipCode] = i.[cProposerZipCode]
, T.[nPremiumForOther] = i.[nPremiumForOther]
, T.[cPurchaseNo] = i.[cPurchaseNo]
, T.[cSameAddrReason] = i.[cSameAddrReason]
, T.[cSameAddrReasonWithProposer] = i.[cSameAddrReasonWithProposer]
, T.[cAplyKeyInEmpIdn] = i.[cAplyKeyInEmpIdn]
, T.[cPerfDeptIdn] = i.[cPerfDeptIdn]
, T.[cCarUsageCtg] = i.[cCarUsageCtg]
, T.[cCardType] = i.[cCardType]
, T.[nPremiumForVoluntary] = i.[nPremiumForVoluntary]
-- (2) 隱私欄位加密 (純密文模式)
, T.[eID] = SH.encrypt(i.[cID])
, T.[hID] = HASHBYTES('SHA2_256', i.[cID])
, T.[eInsurantName] = SH.encrypt(i.[cInsurantName])
, T.[eBenificiaryName] = SH.encrypt(i.[cBenificiaryName])
, T.[eCustDOB] = SH.encrypt(CONVERT(NVARCHAR(50), i.[dCustDOB], 120))
, T.[eMobilePhone] = SH.encrypt(i.[cMobilePhone])
, T.[eEmail] = SH.encrypt(i.[cEmail])
, T.[eProposerId] = SH.encrypt(i.[cProposerId])
, T.[eProposerDOB] = SH.encrypt(CONVERT(NVARCHAR(50), i.[dProposerDOB], 120))
, T.[eProposerAddr1] = SH.encrypt(i.[cProposerAddr1])
, T.[eProposerPhone] = SH.encrypt(i.[cProposerPhone])
, T.[eProposerMobile] = SH.encrypt(i.[cProposerMobile])
, T.[eProposerEmail] = SH.encrypt(i.[cProposerEmail])
, T.[eProposerName] = SH.encrypt(i.[cProposerName])
, T.[eContactAddr] = SH.encrypt(i.[cContactAddr])
, T.[eProposerAddr] = SH.encrypt(i.[cProposerAddr])
FROM [AT].[InsuTxEX] AS T
INNER JOIN inserted AS i ON T.[nRecIdn] = i.[nRecIdn];
EXEC SH.close_key;
END
END;
GO
-- (3) 更新 INSERT Trigger (移除明文寫入)
CREATE OR ALTER TRIGGER AT.trg_InsuTxX_Insert
ON AT.InsuTxX INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
EXEC SH.open_key;
INSERT INTO [AT].[InsuTxEX] (
-- (1) 非隱私欄位映射
[cAplySNO]
, [cInsuProviderIdn]
, [cEngNO]
, [cSaleEmpIdn]
, [cSaleDeptIdn]
, [cOriginEmpIdn]
, [cOriginDeptIdn]
, [cNewOrRenewal]
, [nPremiumForCarDamage]
, [nPremiumForAccident]
, [nPremiumForCompulsory]
, [nPremiumForTheft]
, [nPremiumForDisaster]
, [nTotalPremium]
, [nPremiumReceive]
, [cAssistantEmpIdn]
, [cMgtDeptIdn]
, [cCorpIdn]
, [cOrderSNo]
, [cOriginAplySNO]
, [cChangeCtg]
, [dEffectDate]
, [dExpireDate]
, [dVolEffectDate]
, [dVolExpireDate]
, [cCompulsoryPolicySNO]
, [cVoluntaryInsuPolicySNO]
, [cAgainstThief]
, [cInstallmentFlag]
, [cIdType]
, [cGender]
, [cBenificiaryName2]
, [cContactTelNO]
, [cContactAddr1]
, [dAplyDate]
, [dClearDate]
, [dTransDate]
, [cTransSeqNo]
, [dInvDate]
, [nCommisionYM]
, [dSalaryDate]
, [dConfirmDate]
, [cKeyInIdn]
, [cProgIdn]
, [dInsDate]
, [dChgDate]
, [dCancelDate]
, [cInsuSNo]
, [cCntForWineDanger]
, [cNotationSNo]
, [cSurrenderFlag]
, [dSurrenderDate]
, [cProposer]
, [nKpiYM]
, [cInsuKPIFlag]
, [cZipCode]
, [cStsSelNo]
, [cECardSNo]
, [cNation]
, [cContactCtg]
, [cMobilePhoneOrEmailRelation]
, [nChangeCount]
, [cPreAplySNo]
, [cRelationship]
, [cProposerIdType]
, [cProposerGender]
, [cProposerZipCode]
, [nPremiumForOther]
, [cPurchaseNo]
, [cSameAddrReason]
, [cSameAddrReasonWithProposer]
, [cAplyKeyInEmpIdn]
, [cPerfDeptIdn]
, [cCarUsageCtg]
, [cCardType]
, [nPremiumForVoluntary]
-- 加密及 Hash 寫入區域
, [eID]
, [hID]
, [eInsurantName]
, [eBenificiaryName]
, [eCustDOB]
, [eMobilePhone]
, [eEmail]
, [eProposerId]
, [eProposerDOB]
, [eProposerAddr1]
, [eProposerPhone]
, [eProposerMobile]
, [eProposerEmail]
, [eProposerName]
, [eContactAddr]
, [eProposerAddr]
)
SELECT
[cAplySNO]
, [cInsuProviderIdn]
, [cEngNO]
, [cSaleEmpIdn]
, [cSaleDeptIdn]
, [cOriginEmpIdn]
, [cOriginDeptIdn]
, [cNewOrRenewal]
, [nPremiumForCarDamage]
, [nPremiumForAccident]
, [nPremiumForCompulsory]
, [nPremiumForTheft]
, [nPremiumForDisaster]
, [nTotalPremium]
, [nPremiumReceive]
, [cAssistantEmpIdn]
, [cMgtDeptIdn]
, [cCorpIdn]
, [cOrderSNo]
, [cOriginAplySNO]
, [cChangeCtg]
, [dEffectDate]
, [dExpireDate]
, [dVolEffectDate]
, [dVolExpireDate]
, [cCompulsoryPolicySNO]
, [cVoluntaryInsuPolicySNO]
, [cAgainstThief]
, [cInstallmentFlag]
, [cIdType]
, [cGender]
, [cBenificiaryName2]
, [cContactTelNO]
, [cContactAddr1]
, [dAplyDate]
, [dClearDate]
, [dTransDate]
, [cTransSeqNo]
, [dInvDate]
, [nCommisionYM]
, [dSalaryDate]
, [dConfirmDate]
, [cKeyInIdn]
, [cProgIdn]
, [dInsDate]
, [dChgDate]
, [dCancelDate]
, [cInsuSNo]
, [cCntForWineDanger]
, [cNotationSNo]
, [cSurrenderFlag]
, [dSurrenderDate]
, [cProposer]
, [nKpiYM]
, [cInsuKPIFlag]
, [cZipCode]
, [cStsSelNo]
, [cECardSNo]
, [cNation]
, [cContactCtg]
, [cMobilePhoneOrEmailRelation]
, [nChangeCount]
, [cPreAplySNo]
, [cRelationship]
, [cProposerIdType]
, [cProposerGender]
, [cProposerZipCode]
, [nPremiumForOther]
, [cPurchaseNo]
, [cSameAddrReason]
, [cSameAddrReasonWithProposer]
, [cAplyKeyInEmpIdn]
, [cPerfDeptIdn]
, [cCarUsageCtg]
, [cCardType]
, [nPremiumForVoluntary]
-- (2) 隱私欄位加密 (純密文模式)
, SH.encrypt([cID])
, HASHBYTES('SHA2_256', [cID])
, SH.encrypt([cInsurantName])
, SH.encrypt([cBenificiaryName])
, SH.encrypt(CONVERT(NVARCHAR(50), [dCustDOB], 120))
, SH.encrypt([cMobilePhone])
, SH.encrypt([cEmail])
, SH.encrypt([cProposerId])
, SH.encrypt(CONVERT(NVARCHAR(50), [dProposerDOB], 120))
, SH.encrypt([cProposerAddr1])
, SH.encrypt([cProposerPhone])
, SH.encrypt([cProposerMobile])
, SH.encrypt([cProposerEmail])
, SH.encrypt([cProposerName])
, SH.encrypt([cContactAddr])
, SH.encrypt([cProposerAddr])
FROM inserted;
EXEC SH.close_key;
END
END;
GO
-- (4) 刪除明文欄位 (請務必確認已完成 Step 4 遷移驗證)
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID('AT.InsuTxEX'))
BEGIN
ALTER TABLE AT.InsuTxEX
DROP COLUMN
[cID]
, [cInsurantName]
, [cBenificiaryName]
, [dCustDOB]
, [cMobilePhone]
, [cEmail]
, [cProposerId]
, [dProposerDOB]
, [cProposerAddr1]
, [cProposerPhone]
, [cProposerMobile]
, [cProposerEmail]
, [cProposerName]
, [cContactAddr]
, [cProposerAddr];
PRINT '>>> [SUCCESS] 原始明文欄位已移除';
END
GO
PRINT '>>> [SUCCESS] 物理資料清潔完成：View/Trigger 已更新為純密文版、明文欄位已移除';
GO
硬體級隔離區 (Enclaves) 讓資料在運算中也是加密的。
身分動態驗證，金鑰生命週期由 AI 自動化控管。
SQL 內建帳本，防止資料在加密狀態下被篡改。
AE 初代 / CLE
Secure Enclaves
Zero Trust AI
感謝各位的參與，歡迎提出意見與交流