顯示具有 遮罩 標籤的文章。 顯示所有文章
顯示具有 遮罩 標籤的文章。 顯示所有文章

2024年11月12日 星期二

[研究]SQL Server 動態資料遮罩 (Dynamic Data Masking, DDM) 試用

[研究]SQL Server 動態資料遮罩 (Dynamic Data Masking, DDM) 試用

2024-11-08x

環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019 + SQL Server Management Studio (SSMS) 19

********************************************************************************

SSMS (SQL Server Management Studio)

CREATE DATABASE DynamicDataMaskTestDB
GO
USE DynamicDataMaskTestDB
GO

CREATE TABLE Employees(
		ID varchar(11) primary key,
        CName nvarchar(10),
		Birthday date,
		Marriage char(1),
        Email varchar(255),                   
        Tel varchar(20),
        Salary numeric(13,2),
        )
GO

INSERT INTO Employees
	VALUES ('A123456789', N'趙一', '20010102', 0, 'a01@company.com', '02-12345678', 20000)
	     , ('B123456789', N'錢二', '20020304', 1, 'b02@company.com', '03-23456789', 30000)
	     , ('C123456789', N'孫三', '20040405', 1, 'c03@company.com', '04-34567890', 40000)

SELECT * FROM [DynamicDataMaskTestDB].[dbo].[Employees]


********************************************************************************

遮罩

USE DynamicDataMaskTestDB
ALTER TABLE Employees ALTER COLUMN Birthday ADD MASKED WITH(FUNCTION='default()')
ALTER TABLE Employees ALTER COLUMN Marriage ADD MASKED WITH(FUNCTION='default()')
ALTER TABLE Employees ALTER COLUMN Tel ADD MASKED WITH(FUNCTION='default()')
ALTER TABLE Employees ALTER COLUMN Salary ADD MASKED WITH(FUNCTION='default()')







********************************************************************************

SQL Server, 多低的帳號，在動態資料遮罩(DDM Dynamic Data Mask)  時看不到遮罩內容 ?

ChatGPT 說：

在 SQL Server 中，當啟用動態資料遮罩 (Dynamic Data Mask, DDM) 時，只有擁有以下許可權的帳號可以看到被遮罩的實際資料內容：

  • UNMASK 權限：只要擁有 UNMASK 權限，該帳號無論是何種角色或權限等級，都可以直接存取完整的資料，而不會受到 DDM 影響。
  • sysadmin 固定伺服器角色：此角色中的成員預設擁有 UNMASK 權限，因此可以查看完整的資料內容，而不受遮罩影響。
  • db_owner 角色：資料庫的 db_owner 成員也擁有 UNMASK 權限，能查看未遮罩的實際資料。

因此，任何沒有這些高權限（如 UNMASK 權限、sysadmin 或 db_owner 角色）的帳號，即使有 SELECT 權限，仍然只能看到被遮罩過的資料內容。

********************************************************************************

db_datareader, db_datawriter 可以看到被遮罩資料? 

ChatGPT 說：

不可以。只有擁有 UNMASK 權限 或屬於 sysadmin 或 db_owner 角色的帳號，才可以看到未被遮罩的資料內容。沒有這些高權限的帳號，即使具備讀寫權限，仍會看到被遮罩的資料格式。

********************************************************************************

db_owner 可以設定為 UNMASK  嗎?

ChatGPT 說：無法。

********************************************************************************

SQL Server 類帳號僅有 datareader 和 datawriter，可以執行伺服器端操作嗎？例如 UPDATE、BULK INSERT(T-SQL)、SELECT INTO、INSERT..SELECT 等

ChatGPT 說：

若 SQL Server 類帳號（SQL Server Login）僅被授予 db_datareader 和 db_datawriter 角色，則其對資料庫的操作權限會有以下限制：

  • UPDATE：db_datawriter 角色允許對表格進行 INSERT、UPDATE、DELETE 操作，因此這些帳號可以執行 UPDATE 操作。
  • SELECT INTO：SELECT INTO 會將查詢結果插入到一個新的表格，這需要 CREATE TABLE 權限。單純的 db_datareader 和 db_datawriter 角色不包括 CREATE TABLE 權限，因此無法直接使用 SELECT INTO。
  • BULK INSERT：BULK INSERT 操作需要 BULK ADMIN 伺服器級別權限，db_datareader 和 db_datawriter 角色無法滿足此需求。如果需要執行 BULK INSERT，必須顯式分配 BULK ADMIN 角色或相應權限。
  • INSERT..SELECT：INSERT..SELECT 允許從一個表格或查詢結果插入資料到另一個已存在的表格。由於 db_datawriter 角色允許執行 INSERT 操作，因此可以使用 INSERT..SELECT。

總結來說，僅有 db_datareader 和 db_datawriter 權限的帳號可以執行 UPDATE 和 INSERT..SELECT 操作，但無法執行 SELECT INTO 和 BULK INSERT。
********************************************************************************
【伺服器角色】

【資料庫角色】


SQL Server 2019 請問那些 伺服器角色 看不到 DDM 遮罩欄位內容 ?
bulkadmin
dbcreator
diskadmin
processadmin
public
securityadmin
serveradmin
setupadmin
sysadmin

ChatGPT 說：

總結來說，除了 sysadmin 外，其他伺服器角色皆無法看到。

****************************************

SQL Server 2019 請問那些 資料庫角色 看不到 DDM 遮罩欄位內容 ?
db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner
db_securityadmin
public

ChatGPT 說：

總結來說，除了 db_owner 資料庫角色以外，其他資料庫角色皆無法看到。
********************************************************************************

(完)

相關

[研究]SQL Server 動態資料遮罩 (Dynamic Data Masking, DDM) 試用
https://shaurong.blogspot.com/2024/11/sql-server-dynamic-data-masking-ddm.html

[研究]Microsoft SQL Server 數據加密技術比較
https://shaurong.blogspot.com/2024/06/microsoft-sql-server.html

[研究]SQL Server 角色權限、可執行 SQL 指令、可檢視動態資料遮罩 (Dynamic Data Masking, DDM)比較
https://www.blogger.com/blog/post/edit/3479310643037793626/5394507395545162744

[研究]ASP.NET,WebForm, SQL Server Always Encrypted 加密資料行、加密資料庫試用
https://shaurong.blogspot.com/2024/06/aspnetwebform-sql-server-always.html

張貼者： 於 沒有留言:
標籤： , , , , , , , , , ,

2024年6月6日 星期四

[研究]Microsoft SQL Server 數據資料加密、遮罩技術比較

[研究]Microsoft SQL Server 數據資料加密、遮罩技術比較

2024-06-06

環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019 + SQL Server Management Studio (SSMS) 19

********************************************************************************

Microsoft SQL Server 提供數據加密技術有哪些 ? 哪一版開始支援 ? 用表格比較 ?

ChatGPT

以下是 Microsoft SQL Server 提供的主要數據加密技術及其支援版本的比較：

加密技術 描述 優點 缺點 適用場景 支援版本
Cell-level Encryption 或
Column-Level Encryption
(CLE)
(單元級加密)
(儲存格加密)		 對單個數據列或數據單元進行加密。 細粒度控制，僅加密敏感數據，降低性能開銷。 需要修改應用程序代碼來處理加密和解密邏輯。 保護特定的敏感數據，如信用卡號、社會保障號。 SQL Server 2005 及以上版本
Transparent Data Encryption (TDE)
(透明資料加密)		 對整個數據庫進行加密，包括備份文件。加密和解密對用戶透明。 簡單易用，無需修改應用程序代碼。 性能開銷較大，僅對靜態數據進行加密，無法保護正在處理的數據。 保護數據庫文件和備份文件免受未經授權訪問。 SQL Server 2008 及以上版本
Always Encrypted
(始終加密)		 保護數據庫中的敏感數據，使其對數據庫管理員和高權限用戶也不可見。 在應用程序層進行加密和解密，數據庫管理員無法看到明文數據，強安全性。 需要應用程序修改，僅支援某些數據類型和操作。

伺服器端操作無法運作。包括（但不限於）：UPDATE、BULK INSERT(T-SQL)、SELECT INTO、INSERT..SELECT 		 高安全性要求的應用場景，如醫療、金融數據。 SQL Server 2016 及以上版本
Dynamic Data Masking (DDM)
(動態資料遮罩)		 掩蓋敏感數據以防止未經授權的數據讀取。 簡單易用，無需修改數據或應用程序代碼。 僅掩蓋數據展示，不進行數據加密，對有權訪問的用戶無效。 掩蓋敏感數據展示，防止未經授權的數據讀取。 SQL Server 2016 及以上版本
Backup Encryption
(備份加密)		 對數據庫備份進行加密，以保護備份文件。 簡單易用，保護備份文件，減少數據洩露風險。 僅對備份文件進行保護，無法保護活動數據。 保護數據庫備份文件免受未經授權訪問。 SQL Server 2014 及以上版本
BitLocker
( BitLocker磁碟機加密)		 使用 Windows 的 BitLocker 對整個磁盤加密，包括 SQL Server 數據文件。 操作系統級別的加密，保護整個磁盤上的數據。 需要操作系統支持，對 SQL Server 性能可能有影響。 保護整個磁盤數據，適用於高度敏感的數據存儲。 支援 Windows Server 版本


********************************************************************************
註：Always Encrypted (始終加密) 某些 SQL 指令無法用

Always Encrypted vs CLE
https://learn.microsoft.com/en-us/answers/questions/517753/always-encrypted-vs-cle
加密和解密透過客戶端驅動程式進行。這意味著使用始終加密時，某些僅在伺服器端發生的操作將無法運作。這些行動包括（但不限於）：
• 透過UPDATE、BULK INSERT(T-SQL)、SELECT INTO、INSERT..SELECT 將資料從一列複製到另一列。
• 觸發器、時態表、稀疏列、全文、記憶體中OLTP 和變更資料擷取(CDC)。

********************************************************************************
2024-11-12 補，DDM 要生效，權限要比 db_owner 低，例如僅 datareader 加上 datawriter


SQL Server 類帳號僅有 datareader 和 datawriter，可以執行伺服器端操作嗎？例如 UPDATE、BULK INSERT(T-SQL)、SELECT INTO、INSERT..SELECT 等

ChatGPT 說：

若 SQL Server 類帳號（SQL Server Login）僅被授予 db_datareader 和 db_datawriter 角色，則其對資料庫的操作權限會有以下限制：

  • UPDATE：db_datawriter 角色允許對表格進行 INSERT、UPDATE、DELETE 操作，因此這些帳號可以執行 UPDATE 操作。
  • SELECT INTO：SELECT INTO 會將查詢結果插入到一個新的表格，這需要 CREATE TABLE 權限。單純的 db_datareader 和 db_datawriter 角色不包括 CREATE TABLE 權限，因此無法直接使用 SELECT INTO。
  • BULK INSERT：BULK INSERT 操作需要 BULK ADMIN 伺服器級別權限，db_datareader 和 db_datawriter 角色無法滿足此需求。如果需要執行 BULK INSERT，必須顯式分配 BULK ADMIN 角色或相應權限。
  • INSERT..SELECT：INSERT..SELECT 允許從一個表格或查詢結果插入資料到另一個已存在的表格。由於 db_datawriter 角色允許執行 INSERT 操作，因此可以使用 INSERT..SELECT。

總結來說，僅有 db_datareader 和 db_datawriter 權限的帳號可以執行 UPDATE 和 INSERT..SELECT 操作，但無法執行 SELECT INTO 和 BULK INSERT。
********************************************************************************

(完)

相關

加密資料行 (Encrypt a Column of Data)
https://learn.microsoft.com/zh-tw/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver16
Encrypt a Column of Data
https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver16

column level encryption (CLE，欄位層級加密)
https://learn.microsoft.com/en-us/answers/questions/1320534/column-level-encryption

使用 [Always Encrypted 精靈] 設定資料行加密
https://learn.microsoft.com/zh-tw/sql/relational-databases/security/encryption/always-encrypted-wizard?view=sql-server-ver16

[研究]SQL Server 動態資料遮罩 (Dynamic Data Masking, DDM) 試用
https://shaurong.blogspot.com/2024/11/sql-server-dynamic-data-masking-ddm.html

[研究]Microsoft SQL Server 數據加密技術比較
https://shaurong.blogspot.com/2024/06/microsoft-sql-server.html

[研究]SQL Server 角色權限、可執行 SQL 指令、可檢視動態資料遮罩 (Dynamic Data Masking, DDM)比較
https://www.blogger.com/blog/post/edit/3479310643037793626/5394507395545162744

張貼者： 於 沒有留言:
標籤： , , , , , , , ,

[研究]ASP.NET,WebForm, SQL Server, Always Encrypted 始終加密 試用

[研究]ASP.NET,WebForm, SQL Server, Always Encrypted 始終加密 試用

2024-06-06

SQL Server Always Encrypted

https://learn.microsoft.com/zh-tw/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver16

環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019 + SQL Server Management Studio (SSMS) 19

教學課程：開始使用 Always Encrypted - SQL Server | Microsoft Learn
https://learn.microsoft.com/zh-tw/sql/relational-databases/security/encryption/always-encrypted-tutorial-getting-started?view=sql-server-ver16&tabs=ssms

使用SQL Server 2016的Always Encrypt功能防止系統管理員讀取私密性資料https://www.uuu.com.tw/Public/content/article/19/20190805.htm

********************************************************************************

SQL Server Always Encrypted 是甚麼 ? SQL Server 哪一版開始支援 ?

ChatGPT

SQL Server Always Encrypted 是一種用來保護敏感數據的功能，它使得數據在傳輸過程中和存儲在數據庫中的時候都保持加密狀態。這項技術允許客戶端應用程式將敏感數據加密，然後將其存儲在 SQL Server 中，而數據庫引擎本身無法解密這些數據。這樣，即使數據庫被攻擊者入侵或被未經授權的人訪問，數據依然是不可讀的。

SQL Server Always Encrypted 的主要特點包括：

  1. 客戶端加密： 敏感數據在客戶端應用程式中進行加密和解密，數據庫引擎無法看到未加密的數據。
  2. 密鑰管理： 加密密鑰存儲在客戶端或安全密鑰管理器中，而不是 SQL Server 中。這確保了數據庫管理員無法訪問密鑰。
  3. 透明加密： 對應用程序來說，加密和解密過程是透明的，不需要對應用程序做大量修改。

SQL Server Always Encrypted 支援的版本：

SQL Server Always Encrypted 最早在 SQL Server 2016 版本中引入，並在隨後的版本中進行了改進和擴展。後續版本（如 SQL Server 2017、2019 及更新的版本）在功能性和易用性上進行了進一步的增強。

這項技術主要是為了應對合規性要求和提升數據安全性，是許多企業在處理敏感數據時採用的重要策略之一。

********************************************************************************

SQL Server Always Encrypted 中，資料行加密金鑰 (CEK) 是甚麼？資料行主要金鑰 (CMK) 是甚麼？

ChatGPT

在 SQL Server Always Encrypted 中，資料行加密金鑰（Column Encryption Key, CEK）和資料行主要金鑰（Column Master Key, CMK）是兩種關鍵的加密金鑰，它們在保護敏感數據方面起著重要作用。以下是它們的詳細說明：

資料行加密金鑰 (Column Encryption Key, CEK)

  1. 定義：CEK 是用來加密資料行中的實際數據的金鑰。每一個需要加密的資料行都會使用一個 CEK 來進行加密操作。
  2. 特性：CEK 是對稱金鑰，這意味著同一個金鑰用於加密和解密操作。CEK 是存儲在 SQL Server 中的，但它本身是加密的，不能被直接讀取或使用。
  3. 加密方式：CEK 是使用資料行主要金鑰（CMK）加密的。只有擁有解密 CMK 權限的實體才能解密並使用 CEK 來訪問加密的數據。

資料行主要金鑰 (Column Master Key, CMK)

  1. 定義：CMK 是用來加密 CEK 的金鑰。它提供了一個額外的安全層，確保即使攻擊者獲取了加密的 CEK 也無法解密數據，除非他們也能訪問 CMK。
  2. 特性：CMK 是非對稱金鑰，包含一對公鑰和私鑰。通常，私鑰存儲在硬體安全模組（HSM）、Azure Key Vault 或 Windows Certificate Store 等安全位置。
  3. 加密方式：CMK 的私鑰用於加密 CEK，這樣確保只有擁有 CMK 私鑰的人能解密 CEK，進而使用 CEK 來加密或解密資料行中的數據。

工作流程

  1. 當需要加密資料行中的數據時，首先生成 CEK 並使用 CMK 加密該 CEK。
  2. 加密後的 CEK 存儲在 SQL Server 中，而 CMK 保存在外部安全存儲中。
  3. 當客戶端需要訪問加密數據時，它會向 SQL Server 請求加密的 CEK。
  4. SQL Server 將加密的 CEK 傳送給客戶端，客戶端使用 CMK 的私鑰解密 CEK。
  5. 一旦獲取到解密的 CEK，客戶端便可使用它來加密或解密所需的數據。

這種雙層金鑰管理機制使得敏感數據的安全性大大增強，因為即使數據庫遭到攻擊，攻擊者也無法輕易解密數據，除非他們同時擁有 CEK 和 CMK 的私鑰。

********************************************************************************

SQL Server 2019 + SSMS 20.1 (SQL Server Management Studio 20.1) 上測試

建立測試用DB

USE [MyAlwaysEncryptedDB]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable1]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable1]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable1](
	[SN] [int] IDENTITY(1,1) NOT NULL,
	[CNAME] [nvarchar](50) NULL,
	[CID] [nvarchar](50) NULL,
	[CITY] [nvarchar](50) NULL,
	[CTel] [nvarchar](50) NULL,
	[CEmail] [nvarchar](50) NULL,
 CONSTRAINT [PK_MyTable1] PRIMARY KEY CLUSTERED 
(
	[SN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[MyTable1] ON 
GO
INSERT [dbo].[MyTable1] ([SN], [CNAME], [CID], [CITY], [CTel], [CEmail]) 
VALUES 
(1, N'趙一', N'F123456789', N'台北', N'0912-345-678', N'a1@mytest.idv'),
(2, N'錢二', N'F234567891', N'台北', N'0923-345-678', N'a2@mytest.idv'),
(3, N'孫三', N'F345678912', N'台北', N'0934-345-678', N'a3@mytest.idv'),
(4, N'李四', N'F456789123', N'台中', N'0945-345-678', N'a4@mytest.idv'),
(5, N'周五', N'F567891234', N'台中', N'0956-345-678', N'a5@mytest.idv'),
(6, N'吳六', N'F678912345', N'台中', N'0967-345-678', N'a6@mytest.idv'),
(7, N'鄭七', N'F789123456', N'高雄', N'0978-345-678', N'a7@mytest.idv'),
(8, N'王八', N'F891234567', N'高雄', N'0989-345-678', N'a8@mytest.idv'),
(9, N'馮九', N'F912345678', N'高雄', N'0990-345-678', N'a9@mytest.idv')
GO
SET IDENTITY_INSERT [dbo].[MyTable1] OFF
GO






(下圖)
加密類型
隨機化：相同的值，加密後，值也會不同。
確定性：相同的值，加密後，值會相同。









(下圖) 加密後，內容看不到了

下圖，插入一行，失敗

USE [MyAlwaysEncryptedDB]
GO

INSERT INTO [dbo].[MyTable1] ([CNAME],[CID],[CITY],[CTel],[CEmail])
VALUES (N'陳十', N'F123456789', N'台北', N'0912-345-678', N'a1@mytest.idv');

訊息 206，層級 16，狀態 2，行 3

運算元類型衝突: nvarchar 與 nvarchar(4000) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'MyAlwaysEncryptedDB') 不相容


(下圖)SSMS重新連線該資料庫

(下圖)勾選「Always Encrypted」頁籤的「啟用Always Encrypted (資料行加密)」功能

(下圖)按下「啟用」按鈕

(下圖)在SSMS，用新的連線，開啟新的查詢，SELECT查詢成功

(下圖) 選 SSMS 的「查詢」下拉選單，選擇「查詢選項」

(下圖)選擇「執行」下的「進階」，看到「啟用 Always Encrpyted 的參數化」被勾選


(下圖)用參數化查詢 (parameterized query) 方式插入一筆資料，成功

USE [MyAlwaysEncryptedDB]
GO

DECLARE @CNAME nvarchar(50) = N'陳十';
DECLARE @CID nvarchar(50) = N'F112345678';
DECLARE @CITY nvarchar(50) = N'南投';
DECLARE @CTel nvarchar(50) = N'0903-456-789';
DECLARE @CEmail nvarchar(50) = N'a10@mytest.idv';

INSERT INTO [dbo].[MyTable1] ([CNAME],[CID],[CITY],[CTel],[CEmail])
VALUES (@CNAME,@CID,@CITY,@CTel,@CEmail);




(下圖) 匯出金鑰保管好；如果 Web Server 和 DB Server 是不同台，要拿去 Web Server 匯入

(下圖) 用 certmgr 匯出金鑰










(下圖)如果 Web Server 和 DB Server 是不同台，金鑰要拿去 Web Server 匯入









********************************************************************************
撰寫 ASP.NET WebForm WebApplication (.NET Framework ) 網頁程式存取加密資料庫

Web.config，要加上 Column Encryption Setting=Enabled

<?xml version="1.0" encoding="utf-8"?>
<!--
  如需如何設定 ASP.NET 應用程式的詳細資訊，請前往
  https://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <connectionStrings>
    <add name="MyAlwaysEncryptedDBConnectionString" connectionString="Data Source=.;Initial Catalog=MyAlwaysEncryptedDB;User ID=sa;Password=P@ssw0rd;Column Encryption Setting=Enabled"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.8" />
    <httpRuntime targetFramework="4.8" />
  </system.web>
  <system.codedom>
    <compilers>
      <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701" />
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+" />
    </compilers>
  </system.codedom>
</configuration>

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1.Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:MyAlwaysEncryptedDBConnectionString %>" 
            DeleteCommand="DELETE FROM [MyTable1] WHERE [SN] = @SN" 
            InsertCommand="INSERT INTO [MyTable1] ([CNAME], [CID], [CITY], [CTel], [CEmail]) VALUES (@CNAME, @CID, @CITY, @CTel, @CEmail)" 
            SelectCommand="SELECT * FROM [MyTable1]" 
            UpdateCommand="UPDATE [MyTable1] SET [CNAME] = @CNAME, [CID] = @CID, [CITY] = @CITY, [CTel] = @CTel, [CEmail] = @CEmail WHERE [SN] = @SN">
            <DeleteParameters>
                <asp:Parameter Name="SN" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="CNAME" Type="String" />
                <asp:Parameter Name="CID" Type="String" />
                <asp:Parameter Name="CITY" Type="String" />
                <asp:Parameter Name="CTel" Type="String" />
                <asp:Parameter Name="CEmail" Type="String" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="CNAME" Type="String" />
                <asp:Parameter Name="CID" Type="String" />
                <asp:Parameter Name="CITY" Type="String" />
                <asp:Parameter Name="CTel" Type="String" />
                <asp:Parameter Name="CEmail" Type="String" />
                <asp:Parameter Name="SN" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>
        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" 
            AutoGenerateColumns="True" DataKeyNames="SN" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
            </Columns>
        </asp:GridView>
    </form>
</body>
</html>

Default.aspx.cs

內容不用改


********************************************************************************
測試，插入一筆資料程式

Default2.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default2.aspx.cs" Inherits="WebApplication1.Default2" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:Button ID="Button_Insert" runat="server" Text="插入" OnClick="Button_Insert_Click" />
    </form>
</body>
</html>

Default2.aspx.cs

using System;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace WebApplication1
{
    public partial class Default2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button_Insert_Click(object sender, EventArgs e)
        {
            string queryString = @"INSERT INTO [dbo].[MyTable1] ([CNAME],[CID],[CITY],[CTel],[CEmail])
VALUES (@CNAME,@CID,@CITY,@CTel,@CEmail);";
            using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyAlwaysEncryptedDBConnectionString"].ConnectionString))
            {
                SqlCommand command = new SqlCommand(queryString, conn);
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@CNAME", "褚十一");
                command.Parameters.AddWithValue("@CID", "F123456780");
                command.Parameters.AddWithValue("@CITY", "嘉義");
                command.Parameters.AddWithValue("@CTel", "0904-456-789");
                command.Parameters.AddWithValue("@CEmail", "a11@mytest.idv");
                conn.Open();
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }
    }
}

執行結果


********************************************************************************
金鑰的期限




********************************************************************************

(以下尚未測試)

SQL Server Always Encrypted 的金鑰期限到達後，要如何重做新的金鑰？如何替換設定？

ChatGPT

在 SQL Server 中，Always Encrypted 功能確保了靜態數據在數據庫中的加密。當金鑰的期限到達後，您需要生成新的金鑰並替換現有的金鑰。以下是具體步驟：

步驟 1: 創建新的金鑰

生成新的 Column Master Key (CMK)：

如果您需要替換 Column Master Key，首先需要創建新的 CMK。在 SQL Server Management Studio (SSMS) 中，可以使用以下命令創建新的 CMK：

CREATE COLUMN MASTER KEY [NewColumnMasterKey]

WITH (

    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',

    KEY_PATH = N'CurrentUser/My/NewCert'

);

生成新的 Column Encryption Key (CEK)：

接著，創建新的 Column Encryption Key，並使用新的 CMK 進行加密：

CREATE COLUMN ENCRYPTION KEY [NewColumnEncryptionKey]

WITH VALUES (

    COLUMN_MASTER_KEY = [NewColumnMasterKey],

    ALGORITHM = 'RSA_OAEP',

    ENCRYPTED_VALUE = ENCRYPTBYKEY(KEY_GUID('NewColumnMasterKey'), CONVERT(varbinary, 'YourPlainTextValue'))

);

步驟 2: 更新表中的加密列

定義新金鑰的使用：

您需要更新表中的加密列以使用新的 CEK。假設您有一個名為 MyTable 的表和一個名為 MyColumn 的加密列：

ALTER TABLE MyTable

ALTER COLUMN MyColumn

ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = NewColumnEncryptionKey)

步驟 3: 刷新並驗證

刷新密鑰定義：

刷新金鑰以確保更改生效：

EXEC sp_refresh_parameter_encryption

驗證更改：

驗證新的金鑰已經正確應用到您的列：

SELECT * FROM MyTable WHERE MyColumn = 'YourTestValue'

注意事項

確保備份原有的金鑰和證書，防止數據丟失。

更新金鑰的操作應該在停機窗口內進行，以免影響生產環境中的應用程序。

如果有應用程序直接連接數據庫，請確保這些應用程序也更新其連接設置以使用新的金鑰。

通過上述步驟，您可以有效地替換 SQL Server Always Encrypted 的金鑰，確保數據的持續保護。

********************************************************************************

SQL Server Always Encrypted 的金鑰期限到達後，如何用 SSMS 或 certmgr 重做新的金鑰？如何用 SSMS 或 certmgr 設定 SQL Server Always Encrypted 使用新的金鑰？

ChatGPT

當 SQL Server Always Encrypted 的金鑰期限到達後，您需要生成新的金鑰並配置 SQL Server 使用這些新的金鑰。您可以使用 SQL Server Management Studio (SSMS) 或 Windows 的證書管理工具 (certmgr) 來完成這些任務。以下是具體步驟：

步驟 1: 使用 certmgr 生成新的證書

1.打開 certmgr：

在 Windows 上按 Win + R，輸入 certmgr.msc 並按回車。

2.創建新的證書：

在左側導航欄中選擇 Personal 資料夾，然後右鍵選擇 All Tasks -> Request New Certificate。

按照向導步驟生成新的證書。


步驟 2: 在 SSMS 中配置新的 Column Master Key (CMK)

1.打開 SSMS 並連接到您的 SQL Server 實例。

2.創建新的 CMK：

使用下列 SQL 腳本創建新的 Column Master Key：

CREATE COLUMN MASTER KEY [NewColumnMasterKey]

WITH (

    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',

    KEY_PATH = N'CurrentUser/My/ThumbprintOfNewCertificate'

);


步驟 3: 創建新的 Column Encryption Key (CEK)

創建新的 CEK：

使用下列 SQL 腳本創建新的 Column Encryption Key：

CREATE COLUMN ENCRYPTION KEY [NewColumnEncryptionKey]

WITH VALUES (

    COLUMN_MASTER_KEY = [NewColumnMasterKey],

    ALGORITHM = 'RSA_OAEP',

    ENCRYPTED_VALUE = ENCRYPTBYKEY(KEY_GUID('NewColumnMasterKey'), CONVERT(varbinary, 'YourPlainTextValue'))

);


步驟 4: 更新現有的加密列使用新的 CEK

更新表中的加密列：

假設您有一個名為 MyTable 的表和一個名為 MyColumn 的加密列：

ALTER TABLE MyTable

ALTER COLUMN MyColumn

ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = NewColumnEncryptionKey)


步驟 5: 刷新並驗證

刷新密鑰定義：

使用下列 SQL 命令刷新金鑰：

EXEC sp_refresh_parameter_encryption

驗證更改：

驗證新的金鑰已經正確應用到您的列：

SELECT * FROM MyTable WHERE MyColumn = 'YourTestValue'


【完整示範】

以下是一個完整的範例，假設您已經生成了一個新的證書，其指紋為 ABCD1234：

創建新的 CMK：

CREATE COLUMN MASTER KEY [NewColumnMasterKey]

WITH (

    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',

    KEY_PATH = N'CurrentUser/My/ABCD1234'

);

創建新的 CEK：

CREATE COLUMN ENCRYPTION KEY [NewColumnEncryptionKey]

WITH VALUES (

    COLUMN_MASTER_KEY = [NewColumnMasterKey],

    ALGORITHM = 'RSA_OAEP'

);

更新加密列：

ALTER TABLE MyTable

ALTER COLUMN MyColumn

ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = NewColumnEncryptionKey)

刷新密鑰定義：

EXEC sp_refresh_parameter_encryption

驗證：

SELECT * FROM MyTable WHERE MyColumn = 'YourTestValue'

這樣您就完成了使用新的金鑰替換 SQL Server Always Encrypted 中過期金鑰的整個過程。

********************************************************************************

(完)

相關

教學課程：開始使用 Always Encrypted - SQL Server | Microsoft Learn
https://learn.microsoft.com/zh-tw/sql/relational-databases/security/encryption/always-encrypted-tutorial-getting-started?view=sql-server-ver16&tabs=ssms

使用SQL Server 2016的Always Encrypt功能防止系統管理員讀取私密性資料https://www.uuu.com.tw/Public/content/article/19/20190805.htm






張貼者： 於 沒有留言:
標籤： , , , , , , , , , ,
訂閱： 文章 (Atom)