[研究]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