SQL Server You Don't Need Database Owner (db_owner) Role
2021-07-21
資料庫層級的授權不應該預設選擇 Database Owner，因為 Database Owner 所具有的 Permissions 過高，應該根據使用者的實際會使用到的權限授予，依循最小權限原則 (Least Privilege)。
說明
讀取資料庫
db_datareader 會給與全部資料表、檢視的讀取權限。
USE [demoDB]
ALTER ROLE [db_datareader] ADD MEMBER [userName]特定資料表
如果只想要授權特定資料表中，可以使用下列方式授權：
USE [demoDB]
GRANT DELETE ON [dbo].[DBName] TO [userName]
GRANT UPDATE ON [dbo].[DBName] TO [userName]
GRANT INSERT ON [dbo].[DBName] TO [userName]
GRANT SELECT ON [dbo].[DBName] TO [userName]
GRANT VIEW DEFINITION ON [dbo].[DBName] TO [userName]特定欄位
如果只想要授權資料表中的特定欄位，可以使用下列方式授權：
use [demoDB]
GRANT SELECT ON [dbo].[TableName] ([Col1]) TO [userName] AS [dbo]
GRANT SELECT ON [dbo].[TableName] ([Col2]) TO [userName] AS [dbo]
GRANT SELECT ON [dbo].[TableName] ([Col3]) TO [userName] AS [dbo]寫入資料庫
USE [demoDB]
GRANT INSERT ON [dbo].[DBName] TO [userName]
GRANT UPDATE ON [dbo].[DBName] TO [userName]
GRANT DELETE ON [dbo].[DBName] TO [userName]USE [demoDB]
ALTER ROLE [db_datawriter] ADD MEMBER [userName]同時具備讀取、寫入的權限
最常見的應用程式授權情境，應用程式的使用者僅須具備讀取以及寫入、更新、刪除資料的權限，可以藉由提供 datareader 及 datawriter 的權限來授予充分的權限。
USE [demoDB]
ALTER ROLE [db_datareader] ADD MEMBER [userName]
ALTER ROLE [db_datawriter] ADD MEMBER [userName]⚠️ truncate table 的權限不包含在 datawriter 之中，而 truncate table 也無法單獨 grant 給使用者，必須包裝成 stored_proceduers 或者 function 的方式來提供使用者使用。
只能寫不能更新刪除
如果想要進一步排除使用者更新、刪除資料的權限，可以使用 DENY
USE [demoDB]
DENY UPDATE ON [dbo].[DBName] TO [userName]
DENY DELETE ON [dbo].[DBName] TO [userName]執行 Stored Procedures
USE [demoDB]
GRANT EXECUTE ON OBJECT::dbo.uspSP1 TO [userName]授權使用者執行所有的 Stored Procedures。
GRANT EXECUTE TO [userName]讀取資料庫物件定義 VIEW Definition
GRANT VIEW DEFINITION on SCHEMA::dbo to [userName]db_ddladmin
如果應用系統有動態設計 Table、View 的需求，可以評估提供 db_ddladmin 的權限。
USE [demoDB]
ALTER ROLE [db_ddladmin] ADD MEMBER [userName]取消角色授權的方法：
ALTER ROLE [db_ddladmin] DROP MEMBER [userName]
❌ 不應該給予的 Roles
db_accessadmin, db_securityadmin
這兩個角色具備會允許使用者可以自行調整安全性的授權，相當於授予了完整的資料庫管理權限。
db_backoperator
backoperator 可以對資料庫進行備份、還原以及建立 CheckPoint，使用者不正確的使用這些行為會影響資料庫排程的正常進行。
db_owner
應用程式只是 Data Owner 而不是 Database Owner，很多資料庫的層級的工作是由資料庫管理者所負責，因此不應授權 db_owner 的角色。