ianhuang筆記THE ADVENTURER'S JOURNAL
知識世界・持續探索中

ADVENTURE JOURNAL · 探索筆記

SQL Server 許可權:Login、User 與最小角色

任務導覽 TABLE OF CONTENTS

補上伺服器與資料庫身份差異,以無登入測試使用者驗證 SELECT 許可權。

適用:SQL Server2019/2022測試資料庫,需由有相應管理許可權的帳號建立測試物件。

兩層身份

LOGIN在伺服器層負責認證,USER在資料庫層負責資料庫存取,通常以FOR LOGIN建立對應。contained user是另一種配置,Azure SQL與本機也有差異,先確認實際服務。

原CHECK_POLICY=OFF與共用Password、預設db_owner不適合作通用範例。真實SQL登入使用部署提供強密碼並核對Windows身份/SQL身份模式;本篇用WITHOUT LOGIN展示DB授權,沒有可直接登入的密碼帳號。

測試 DB 內操作

在隔離測試DB中:

CREATE TABLE dbo.NoteTasks (Id int PRIMARY KEY, Title nvarchar(100) NOT NULL);
INSERT INTO dbo.NoteTasks VALUES (1, N'測試任務');
CREATE USER NoteReader WITHOUT LOGIN;
CREATE ROLE NoteReadRole;
GRANT SELECT ON OBJECT::dbo.NoteTasks TO NoteReadRole;
ALTER ROLE NoteReadRole ADD MEMBER NoteReader;
EXECUTE AS USER = 'NoteReader';
SELECT Id, Title FROM dbo.NoteTasks;
REVERT;

SELECT應回1/測試任務。另在EXECUTE AS之後測試UPDATE應被拒絕,再REVERT;錯誤測試要確保還原身份,不讓後續DDL在低許可權context執行。

清理與許可權邊界

ALTER ROLE NoteReadRole DROP MEMBER NoteReader;
DROP USER NoteReader;
DROP ROLE NoteReadRole;
DROP TABLE dbo.NoteTasks;

只清除本篇建立的測試物件。db_datareader可讀DB內廣泛user表、db_datawriter可寫,仍需判斷是否比單表授權過大;db_owner是很高許可權,不等於「所有開發者都需要」。DROP USER不會自動DROP LOGIN,兩者生命週期不同。

確認許可權用受測身份執行允許/拒絕操作與資料庫metadata,不只看GRANT沒有報錯。本文僅官方文件核對,未對實際SQL Server寫入賬號或修改許可權。

參考資料

冒險者留言

comments powered by Disqus

搜尋筆記

輸入關鍵字,探索所有筆記。

ESC 關閉 · 搜尋標題、標籤與文章全文