[研究]SQL Server 角色權限、可執行 SQL 指令、可檢視動態資料遮罩 (Dynamic Data Masking, DDM)比較
2024-06-04
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
| 角色 | 可以執行的 SQL 指令 | 不能執行的 SQL 指令 |
| db_owner | 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 ..., EXECUTE, GRANT, REVOKE DBCC | 無 |
| db_securityadmin | DDL: 無 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_accessadmin | DDL: 無 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_backupoperator | DDL: BACKUP DATABASE, BACKUP LOG | DDL: 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_ddladmin | DDL: 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 TABLE | DDL: BACKUP, RESTORE |
| db_datawriter | DDL: 無 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_datareader | DDL: 無 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_denydatawriter | DDL: 無 DML: 無 | DDL: 無 DML: INSERT, UPDATE, DELETE, INSERT INTO ... SELECT ... |
| db_denydatareader | DDL: 無 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 權限，因此不能查看原始資料。
********************************************************************************
(完)
相關
[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
沒有留言:
張貼留言