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)