顯示具有 DDM 標籤的文章。 顯示所有文章
顯示具有 DDM 標籤的文章。 顯示所有文章

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

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

2024年6月4日 星期二

[研究]SQL Server 角色權限、可執行 SQL 指令、可檢視動態資料遮罩 (Dynamic Data Masking, DDM)比較

[研究]SQL Server 角色權限、可執行 SQL 指令、可檢視動態資料遮罩 (Dynamic Data Masking, DDM)比較

2024-06-04

資料庫層級角色
https://learn.microsoft.com/zh-tw/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16

DBCC（Database Console Commands）是SQL Server中的一組命令，用於維護、診斷、檢查和修復數據庫和伺服器的狀態。這些命令可以幫助數據庫管理員（DBA）檢測和解決潛在的問題，優化數據庫性能，以及確保數據庫的完整性和一致性。
https://learn.microsoft.com/zh-tw/sql/t-sql/database-console-commands/dbcc-transact-sql?view=sql-server-ver16

伺服器角色

伺服器角色:

bulkadmin

dbcreator

diskadmin

processadmin

public

securityadmin

serveradmin

setupadmin

sysadmin

**********

資料庫角色


資料庫角色:
db_accessadmin

db_backupoperator

db_datareader

db_datawriter

db_ddladmin

db_denydatareader

db_denydatawriter

db_owner

db_securityadmin

public


角色可以執行的 SQL 指令不能執行的 SQL 指令
db_owner數據定義語言（Data Definition Language, DDL）
DDL: CREATE, ALTER, DROP, CREATE VIEW, ALTER VIEW, DROP VIEW, CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION, BACKUP DATABASE, RESTORE DATABASE, TRUNCATE TABLE

數據操作語言（Data Manipulation Language, DML）
DML: SELECT, INSERT, UPDATE, DELETE, INSERT INTO ... SELECT ..., EXECUTE, GRANT, REVOKE

DBCC (Database Console Command，資料庫控制命令)
DBCC		無
db_securityadminDDL: 無
DML: GRANT, REVOKE, DENY		DDL: CREATE, ALTER, DROP, CREATE VIEW, ALTER VIEW, DROP VIEW, CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION, BACKUP DATABASE, RESTORE DATABASE, TRUNCATE TABLE

DML: SELECT, INSERT, UPDATE, DELETE, INSERT INTO ... SELECT ...
db_accessadminDDL: 無
DML: GRANT CONNECT, REVOKE CONNECT		DDL: CREATE, ALTER, DROP, CREATE VIEW, ALTER VIEW, DROP VIEW, CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION, BACKUP DATABASE, RESTORE DATABASE, TRUNCATE TABLE

DML: SELECT, INSERT, UPDATE, DELETE, INSERT INTO ... SELECT ...
db_backupoperatorDDL: BACKUP DATABASE, BACKUP LOGDDL: RESTORE DATABASE, RESTORE LOG, CREATE, ALTER, DROP, CREATE VIEW, ALTER VIEW, DROP VIEW, CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION, TRUNCATE TABLE

DML: SELECT, INSERT, UPDATE, DELETE, INSERT INTO ... SELECT ...
db_ddladminDDL: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE VIEW, ALTER VIEW, DROP VIEW, CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION, TRUNCATE TABLEDDL: BACKUP, RESTORE
db_datawriterDDL: 無
DML: INSERT, UPDATE, DELETE, INSERT INTO ... SELECT ...		DDL: CREATE, ALTER, DROP, CREATE VIEW, ALTER VIEW, DROP VIEW, CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION, BACKUP, RESTORE, TRUNCATE TABLE

DML: SELECT
db_datareaderDDL: 無
DML: SELECT		DDL: CREATE, ALTER, DROP, CREATE VIEW, ALTER VIEW, DROP VIEW, CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION, BACKUP, RESTORE, TRUNCATE TABLE

DML: INSERT, UPDATE, DELETE, INSERT INTO ... SELECT ...
db_denydatawriterDDL: 無
DML: 無		DDL: 無

DML: INSERT, UPDATE, DELETE, INSERT INTO ... SELECT ...
db_denydatareaderDDL: 無
DML: 無		DDL: 無

DML: SELECT

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

DBCC指令需要 db_owner 才可以執行。例如常用的「重置自動編號(識別計數器)的語法」：

TRUNCATE TABLE 資料表名稱
DBCC CHECKIDENT (資料表名稱,reseed,1)

[研究] SQL Server 2016 識別值 (identity value) 設定研究
https://shaurong.blogspot.com/2017/03/sql-server-2016-identity-value.html
truncate  Table TestTable;
DBCC CHECKIDENT ("TestTable", RESEED, 1);

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

請說明 SQL指令、數據操作語言（DML）、數據定義語言（DDL）差別？

ChatGPT

SQL（Structured Query Language）是一種專門用於管理和操作關聯數據庫的語言。SQL指令可以分為多種類型，主要包括數據操作語言（DML）和數據定義語言（DDL）。以下是它們的區別和各自的功能：

數據操作語言（DML）

數據操作語言（Data Manipulation Language, DML）主要用於對數據庫中的數據進行查詢和操作。常見的DML指令包括：

  • SELECT：用於查詢數據。範例：SELECT * FROM users;
  • INSERT：用於插入新數據。範例：INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com');
  • UPDATE：用於更新已有數據。範例：UPDATE users SET email = 'john.new@example.com' WHERE name = 'John Doe';
  • DELETE：用於刪除數據。範例：DELETE FROM users WHERE name = 'John Doe';


數據定義語言（DDL）

數據定義語言（Data Definition Language, DDL）主要用於定義和修改數據庫結構，包括表、索引、視圖等。常見的DDL指令包括：

  • CREATE：用於創建數據庫對象（如表、索引、視圖等）。範例：

CREATE TABLE users (

    id INT PRIMARY KEY,

    name VARCHAR(100),

    email VARCHAR(100)

);

  • ALTER：用於修改已存在的數據庫對象。範例：ALTER TABLE users ADD COLUMN age INT;

  • DROP：用於刪除數據庫對象。範例：DROP TABLE users;

  • TRUNCATE：用於快速清空表中的所有數據，但保留表結構。範例：TRUNCATE TABLE users;


DML和DDL的區別

功能目的：

  • DML專注於數據的操作和查詢，如插入、更新、刪除和選擇數據。
  • DDL專注於數據庫結構的定義和修改，如創建、修改和刪除表和其他數據庫對象。

操作影響：

  • DML操作通常會影響表中的數據，而不改變表的結構。
  • DDL操作會改變數據庫的結構，而不直接操作表中的數據。

事務處理：

  • DML操作可以在事務（transaction）中進行，並且可以被回滾（ROLLBACK）。
  • DDL操作通常會自動提交（auto-commit），不能被回滾。

總結來說，DML和DDL是SQL中的兩種主要指令集，分別用於操作數據和定義數據庫結構。理解這兩者之間的區別對於有效地管理和操作數據庫非常重要。

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

動態資料遮罩 (Dynamic Data Masking, DDM)

動態資料遮罩 - SQL Server | Microsoft Docs  ( SQL Server 2016 開始提供)
https://docs.microsoft.com/zh-tw/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15

SQL Server 版本Express 版Standard 版Enterprise 版Developer 版
SQL Server 2014否否否否
SQL Server 2016是是是是
SQL Server 2017是是是是
SQL Server 2019是是是是
SQL Server 2022是是是是

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

SQL Server，欄位設定 動態資料遮罩 後，下面哪些角色可以看到原始資料？

  • db_owner
  • db_securityadmin
  • db_accessadmin
  • db_backupoperator
  • db_ddladmin
  • db_datawriter
  • db_datareader
  • db_denydatawriter
  • db_denydatareader

ChatGPT

在 SQL Server 中，當對某個欄位應用了動態資料遮罩後，只有具有 UNMASK 權限的角色或使用者才能查看原始資料。因此，只有 db_owner 和 db_securityadmin 角色可以看到原始資料。其他角色不會被授予 UNMASK 權限，因此不能查看原始資料。

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

(完)

相關

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

[Day11] 資料使用安全(SQL資料遮罩) - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天
https://ithelp.ithome.com.tw/articles/10187606

[研究]SQL Server 帳號如果伺服器角色連預設的 public 都沒勾選，可以 SELECT 嗎？
https://shaurong.blogspot.com/2024/06/sql-server-public-select.html


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