SQL Server You Don't Need Database Owner (db_owner) Role


  1. 說明
    1. 讀取資料庫
      1. 特定資料表
      2. 特定欄位
    2. 寫入資料庫
    3. 同時具備讀取、寫入的權限
      1. 只能寫不能更新刪除
    4. 執行 Stored Procedures
    5. 讀取資料庫物件定義 VIEW Definition
    6. db_ddladmin
  2. ❌ 不應該給予的 Roles
    1. db_accessadmin, db_securityadmin
    2. db_backoperator
    3. db_owner
  3. 參考資料
  4. 相關連結

資料庫層級的授權不應該預設選擇 Database Owner,因為 Database Owner 所具有的 Permissions 過高,應該根據使用者的實際會使用到的權限授予,依循最小權限原則 (Least Privilege)。

SQL Server Logo

說明

讀取資料庫

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 的角色。

參考資料

Database-Level Roles

授與預存程序的權限

SQL Server Permissions Poster

相關連結

SQL Server Integrated Service 初探

SQL Server 閃電般快速查詢指南⚡

SQL Server 周邊工具彙整筆記

SQL Server 學習資源筆記