如何正確的授權 Azure SQL Database 資料庫權限給開發人員

我們公司許多開發測試用的資料庫都直接使用開在 Azure SQL Database 中，然而如果錯誤的授權可能會導致權限過大，甚至有可能刪除資料庫本身。本篇文章我打算分享幾個建立 SQL Server 登入與資料庫使用者的過程，幫助管理者更加正確的授權給開發人員與應用程式。

正確授權給開發人員

我假設你已經登入 Azure SQL Server 並擁有系統管理員權限，並且已經建立好一個資料庫名稱為 testdb 的資料庫。此時你需要先建立一個名為 testdb_admin 的 SQL Login 登入帳號，然後再建立一個 testdb 資料庫下名為 testdb_admin 的「資料庫使用者」，最後授予足夠的資料庫存取權限！

先選擇 master 資料庫，並執行以下 T-SQL 建立登入帳號
```
CREATE LOGIN [testdb_admin] WITH PASSWORD=N'68SKYPPBGrws'
GO
```
再選擇專案資料庫 testdb 建立使用者並授予 db_owner 權限
```
CREATE USER [testdb_admin] FOR LOGIN [testdb_admin]
GO
ALTER ROLE [db_owner] ADD MEMBER [testdb_admin]
GO
```
由於開發階段需要足夠的權限才能對資料庫進行設計，因此可以開給開發人員 db_owner 這個最大權限。

若要撤銷使用者與登入，請參考以下命令：

先選擇專案資料庫 testdb 並刪除使用者
```
DROP USER [testdb_admin]
```
再選擇 master 資料庫刪除登入帳號
```
DROP LOGIN [testdb_admin]
```

連線失敗的解決方法

上述這種授權方式，由於完全沒有授予 master 資料庫的任何權限，因此預設透過 SSMS 連接上來時，會發生以下錯誤：

Connect to Server

請記得切換到 Connection Properties 頁籤，明確指定資料庫名稱才能成功連接資料庫：

Connect to Server / Connect to Database

正確授權給測試機網站存取資料庫

網站在開發階段與測試部署階段，應該使用不同的登入與資料庫使用者，以免程式問題導致資料庫受損，因此應該採用「最小權限原則」進行授權。

先選擇 master 資料庫，並執行以下 T-SQL 建立登入帳號
```
CREATE LOGIN [testdb_user] WITH PASSWORD=N'Thw2DEahZPc9'
```
選擇專案資料庫 testdb 並建立一個可以執行預存程序 (Stored Procedure) 的資料庫角色
```
CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
```
選擇專案資料庫 testdb 並建立使用者並授予最小權限
```
USE [testdb]
CREATE USER [testdb_user] FOR LOGIN [testdb_user]
ALTER ROLE [db_datareader] ADD MEMBER [testdb_user]
ALTER ROLE [db_datawriter] ADD MEMBER [testdb_user]
ALTER ROLE [db_executor] ADD MEMBER [testdb_user]
```
上述權限可以對整份資料庫進行讀寫，也可執行任意預存程序，權限其實也蠻大的，但至少無法對資料庫結構進行調整。必要時應該對個別資料庫物件進行安全性設定！

若要撤銷使用者與登入，請參考以下命令：

先選擇專案資料庫 testdb 並刪除使用者
```
DROP USER [testdb_user]
```
這裡的 db_executor 角色不一定要刪除，因為之後或許還用的到！
```
DROP ROLE db_executor
```
再選擇 master 資料庫刪除登入帳號
```
DROP LOGIN [testdb_user]
```

關於 SQL Server 資料庫權限的補充

你可以從 Permissions (Database Engine) 文件找到非常完整的說明，而且你會發現 SQL Server 對權限的定義非常的精細，要完整理解各個權限、角色之間的關係，確實需要花上一點功夫！這篇文章有個 Chart of SQL Server Permissions 圖表，他把所有內建角色與擁有權限之間的關係劃的非常仔細！

以下有一段我從 SQL Server query to find all permissions/access for all users in a database - Stack Overflow 複製過來的 T-SQL 語法，這段程式在 Azure SQL Database 執行會出錯，所以我有做出一點修正。這段 T-SQL 可以直接複製貼上執行，它會列出目前資料庫的所有授權情況，可以幫助你 Review 資料庫的授權是否有異常狀況：

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE,
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectName      : Name of the object that the user/role is assigned permissions on.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.
*/

--List all access provisioned to a sql user or windows user/group directly
SELECT
    [UserName] = CASE princ.[type]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = princ.[name],
    [Role] = null,
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --database user
    sys.database_principals princ
LEFT JOIN
    --Login accounts
    sys.user_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
    [UserName] = CASE memberprinc.[type]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = memberprinc.[name],
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.user_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Roles
    sys.database_principals roleprinc
LEFT JOIN
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
JOIN
    --All objects
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]

The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享