[DBA] 帳號權限管理

點閱: 1073

概念

想像一家人,內有4個成員,每人各自有各自的房間,每個人要先能回到家後,才能開門進入自己的房間,每個人對於每個房間有各自的權限。

  • 4個家人:對應到使用者

    每個人能否回到家,是由登入權限(Login)設定的

  • 家:對應到實體的server

    每個人在家中,可以作什麼事情,是由伺服器角色(server roles)控制權限

  • 房間:對應到每個資料庫

    每個人對資料庫的設定,是由資料庫角色(DB roles)控制權限

  • 在房間能幹嘛?對應到安全性實體

伺服器角色

SQL Server會提供伺服器層級角色管理伺服器的權限。權限範圍為整個伺服器。有九種固定的伺服器角色。從 SQL Server 2012 (11.x)開始,可以建立使用者定義伺服器角色,並可套用至使用者。

下表所列為固定的伺服器角色,參考自官方文件

固定伺服器層級角色 描述
bulkadmin 可以執行BULK INSERT指令
dbcreator 可以建立、改變、卸除及還原任何資料庫。
diskadmin 可以管理磁碟檔案
processadmin 可以結束執行的處理序(process)
public 每一個登入都屬於public伺服器角色。當伺服器主體未被授與或拒絕安全性實體物件的特定權限時,該使用者會繼承授與給該物件之 public 的權限。只有當您想要將任何物件提供給所有使用者使用時,才指派該物件的public權限。您無法變更 public 的成員資格。
securityadmin 可以管理登入及其屬性。可以GRANT(授與)、DENY(拒絕)和REVOKE(撤銷) 伺服器層級權限
serveradmin 可以變更全伺服器組態選項及關閉伺服器
setupadmin 可以使用 Transact-SQL 陳述式加入和移除連結的伺服器
sysadmin 可以執行伺服器中的所有活動

微軟官方文件中,也列出了下表,清楚的標示出每個固定伺服器角色有的權限
[DBA] 帳號權限管理 1

資料庫角色

資料庫層級角色類型有預先定義的「固定資料庫角色」以及可以自行建立的「使用者定義資料庫角色」。

固定資料庫角色義於資料庫層級,並存在每個資料庫中。db_owner資料庫角色的成員可以管理固定的資料庫角色成員資格。

您可以將任何資料庫帳戶和其他 [SQL Server] 角色加入資料庫層級角色中。 固定資料庫角色的每個成員可以在相同的角色中加入其他使用者。

下表所列為固定的資料庫角色,參考自官方文件

資料庫角色 描述
db_accessadmin 可以針對 Windows 登入、Windows 群組及 [SQL Server] 登入加入或移除資料庫的存取權
db_backupoperator 可以備份資料庫
db_datareader 可以讀取資料
db_datawriter 可以加入、刪除或變更資料表中的資料(INSERT, UPDATE, DELETE)
db_ddladmin 可在資料庫中執行任何「資料定義語言」(DDL) 的命令(CREATE, DROP, ALTER, TRUNCATTE, COMMENT, RENAME )
db_denydatareader 不能加入、修改或刪除資料庫中使用者資料表的任何資料(也就是datareader的相反)
db_owner db_owner 固定資料庫角色的成員可以在資料庫上執行所有的組態和維護活動,也可以在 [SQL Server]中卸除資料庫
db_securityadmin 可以修改角色成員資格及管理權限
public 每個資料庫使用者都屬於 public 資料庫角色

微軟官方文件中,也列出了下表,清楚的標示出每個固定資料庫角色有的權限
[DBA] 帳號權限管理 2

安全性實體

官方定義的安全性實體定義是:安全性實體是一種資源,SQL Server Database Engine 授權系統會管理其存取權。

舉例來說,我要建立一個登入帳號,他具有登入server、對特定DB具有查詢、寫入、更新、刪除等權限,作法如下:

  1. 在伺服器建立該帳號,給予public的伺服器角色
  2. 在可被CRUD的DB,設定安全性實體權限(針對DB, 針對表)

[DBA] 帳號權限管理 3

建立一個只能select, 以及insert權限的帳戶

--create user
USE [DB_NAME]
CREATE USER [USER_NAME] FOR LOGIN [USER_NAME]
    ALTER ROLE [db_datareader] ADD MEMBER [USER_NAME] -- Add select permission
    GRANT INSERT TO [USER_NAME] -- Add insert permission

About the Author

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

Related Posts