近年來公司在開發專案時，已經漸漸習慣直接使用 Azure 上提供的各項資源服務，而資料庫就是其中一個大量使用到的服務。所以這邊就筆記一下如何管理並新增 Azure SQL 資料庫的存取帳號，由於看的文件還不算多，如果有寫錯或會錯意的地方，可以直接留言告知我，感謝!
首先大概講一下，Azure SQL Server 目前可以擁有兩種無限制的系統管理員帳戶，分別為 Server 管理員(Server Admin)和 Active Directory 管理員(Active Directory Admin)
**Server 管理員:** *當建立 Azure SQL Server 時，就會要求你建立一個，每個 Azure SQL Server 只能有一個 Server 管理員*
**Active Directory 管理員:** *該種管理員為選擇性建立，不一定要有，可為單一帳號或一特定群組，由於自己還沒用過這玩意，所以有關這部分的指令都僅先附上帶過，日後有用到再回來補充*
根據[微軟上的文件](https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-manage-logins#unrestricted-administrative-accounts)說明，以上兩種管理員具有以下特性
- 唯一可連到伺服器上任一資料庫的帳號(其它帳號需透過給予權限的方式才能連上)
- 對任何資料庫都有最大的權限
- 無法將 master 資料庫權限設定給任何帳戶，也就是說 master 資料庫的存取權限只能是該兩種管理員
- 不是標準的 SQL Server sysadmin 固定伺服器角色成員，不適用於 SQL 資料庫(從原文看不太懂是什麼意思)
- 可以建立、改變和刪除 master 資料庫中的登入者及使用者，以及伺服器層級(server-level)的防火牆規則
- 可以新增和移除 dbmanager 和 loginmanager 角色(role)的成員
- 可以查看 sys.sql_logins 系統資料表
# 額外的伺服器層級系統管理員帳戶 除了上述提到的兩種系統管理員帳戶，Azure SQL Server 也允許在 master 資料庫上新增其它受限制的系統管理帳戶(就是沒上面兩種強大就對了)，有以下兩種 **資料庫建立者(Database creators)** 當指定帳號被給予 `dbmanager` 角色權限時，此角色的帳號就可建立新的資料庫。然後該角色的使用者必須是 master 資料庫中以 SQL Server 登入為基礎的使用者，或是以 Azure Active Directory 使用者為基礎的自主資料庫使用者。建立步驟如下 1. 使用系統管理員帳戶連線到 master 資料庫 2. 使用 `Create Login` 建立一個 SQL Server 登入帳號 ```SQL Create Login [login_user] With Password = ''; // e.g. Create Login CoCo With Password = 'Qazwsx789';
```
3. 在 master 資料庫中，使用 `Create User` 指令來建立使用者，建立的使用者可為以下三種
- **Azure Active Directory 驗證的自主資料庫使用者 (如果您已開啟 Azure AD 相關的功能)**
- **SQL Server 驗證的自主資料庫使用者**
- **SQL Server 驗證登入(在上一個步驟中建立)的 SQL Server 驗證使用者**
```SQL
// 以 Azure Active Directory 驗證為基礎的自主資料庫使用者
Create User [coco@azure.com] From External Provider;
// SQL Server 驗證的自主資料庫使用者
Create User [user_name] With Password = ''; // e.g. Create User Monie With Password = 'Qazwsx789';
// 以 SQL Server 驗證登入為基礎的 SQL Server 使用者
Create User [user_name] From Login [login_user]; // e.g. Create User CoCo From Login CoCo; ([user_name] 可以和 [login_user] 同名稱)
```
4. 使用 `Alter Role` 指令，將新的使用者新增至 master 中的 dbmanager 資料庫角色
```SQL
Alter Role dbmanager Add Member [user_name]; // e.g. Alter Role dbmanager Add Member CoCo;
Alter Role dbmanager Add Member [coco@azure.com];
```
5. 現在新建立的使用者可以連線至 master 資料庫，並建立新的資料庫，並成為該資料庫的擁有者。
**登入管理員(Login managers)**
另一個可給予的系統管理角色權限是 `loginmanager`，具有此權限的帳號可以在 master 資料庫建立新的登入帳號，建立方式跟建立 `dbmanager` 步驟一樣(第四步驟的 `dbmanager` 改為 `loginmanager` 即可)，不過微軟官方建議多採用[自主資料庫使用者(Contained Database User)](https://docs.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?redirectedfrom=MSDN&view=sql-server-2017) 的帳號來管理資料庫，
這會在資料庫層級(database-level)進行驗證，而不是從登入帳號也就是伺服器層級(server-level)來進行驗證
# 非系統管理員的帳戶
大部分情況下，應用程式並不需要有對 master 資料庫的存取權，所以自己上面的指令用到的機會其實並不多，反而是使用 `Create User` 指令在資料庫層級下建立自主資料庫使用者帳號的方式較常發生
1. 請先用系統管理員帳戶連線到***目標資料庫***，然後再執行以下其中一個指令
```SQL
// SQL Server 驗證的自主資料庫使用者
Create User [user_name] With Password = '';
// 以 SQL Server 驗證登入為基礎的 SQL Server 使用者
Create User [user_name] From Login CoCo; // 此範例的 SQL Server Authentication Login 帳號 CoCo 需先建立
// 以 Azure Active Directory 驗證為基礎的自主資料庫使用者
Create User [coco@azure.com] From External Provider;
```
2. 若要授權使用者可以在該資料庫裡建立新的使用者，則可以使用以下指令(非必要)
```SQL
Grant Alter Any User To [user_name];
```
3. 若要授權使用者有資料庫的完整控制權，則可以使用以下指令
```SQL
Alter Role db_owner Add Member [user_name];
```
4. 若要授權使用者能對資料庫執行寫入、讀取的權限，則可以使用以下指令
```SQL
Alter Role db_datareader Add Member [user_name];
Alter Role db_datawriter Add Member [user_name];
```
# 防火牆規則 以往對 Azure SQL Server 的防火牆設定只停留在 Azure 入口網站裡設定白名單及黑名單 IP，藉由這次的筆記，開始對這一塊有更進一步的認知，防火牆可分為`伺服器層級(server-level)`及`資料庫層級(database-level)`兩種。一般我們在 Azure 入口網站裡設定的屬於**伺服器層級**，正確設定後，我們就能透過用戶端工具(例如 SQL Server Management Studio 或 SQL Server Data Tools)以 Server 管理員或 Active Directory 管理員的身份對 master 資料庫進行連接，如下圖  而**資料庫層級**的防火牆只能透過 `sp_set_database_firewall_rule` 陳述式來設定(Azure 入口網站現階段沒有提供)，一般來說，非系統管理員的使用者應該要被設定只能透過資料庫層級的防火牆來存取所使用的資料庫，也就是說如果**伺服器層級的防火牆**沒有開放對該使用者的 IP 有存取資料庫的權限，但**資料庫層級的防火牆**有時，該使用者仍然可以使用如 SQL Server Management Studio 或 SQL Server Data Tools 之類的用戶端工具來連接，利用資料庫層級的防火牆來管理資料庫使用者的存取權限可以避免在伺服器層級的防火牆上開一堆對外的白名單 IP，而造成後續維護上的困難或資安上的疑慮，下面是一個典型非系統管理員對資料庫層級的防火牆存取示意圖  至於兩者實際的運作方式為所有來自外部的連線都會**先訪問資料庫層級的防火牆**是否有存取目標資料庫的權限，無權限時才會**再詢問伺服器層級的防火牆**是否有存取權限，運作流程圖如下 
# 結論 以上算是自己對 Azure SQL Server 上一些基本的權限管理認知，事實上這塊還有很多方面都還沒有提到(資料實在他X的有夠多...)，像是資料庫內建角色的種類及影響範圍或群組，所以目前也是先以實務上常用到的功能來做精進，之後會在補上新學到的內容或修正錯誤的觀念。最後附上一些自己實際常在用的懶人包，讓以後自己不小心忘記時可以回來查詢XD ## 資料庫使用者 1. 建立一個以 SQL Server 驗證登入為基礎的資料庫使用者，並賦與對目標資料庫讀寫的權限 ```SQL // 1.Create SQL auth login in the master database Create Login [login_user] With Password = 'qazwsx789' // 2.Choose a database to use and create a database user mapped to the [login_user] you created in previous steps Use [selected_database_name]; Create User [user_name] For Login [login_user] With DEFAULT_SCHEMA = dbo; // 3.Add user to roles (read/write) Alter Role db_datareader Add Member [user_name]; Alter Role db_datawriter Add Member [user_name]; ``` 2. 建立一個不需依賴 SQL Server 驗證登入的自主資料庫驗證使用者(SQL Authentication Contained User)，並賦與對目標資料庫讀寫的權限 ```SQL // 1.Choose a database to use and create a SQL authentication contained user Use [selected_database_name]; Create User [user_name] With Password = 'qazwsx789', DEFAULT_SCHEMA = dbo; // 2.Add user to roles (read/write) Alter Role db_datareader Add Member [user_name]; Alter Role db_datawriter Add Member [user_name]; ``` 3. 建立以 Azure Active Directory 驗證為基礎的資料庫使用者，並賦與對目標資料庫讀寫的權限 ```SQL // 1.Choose a database to use and create a Azure Active Directory user Use [selected_database_name]; Create User [coco@azure.com] From External Provider With DEFAULT_SCHEMA = dbo; // 2.Add user to roles (read/write) Alter Role db_datareader Add Member [coco@azure.com]; Alter Role db_datawriter Add Member [coco@azure.com]; ``` 4. 移除資料庫使用者指定的角色權限 ```SQL // Remove specific user's read permission Alter Role db_datareader Drop Member [user_name]; // Remove specific user's write permission Alter Role db_datawriter Drop Member [user_name]; ``` 5. 移除指定的資料庫使用者 ```SQL // Remove specific database user Drop User [user_name]; ``` 6. 列出當下使用的資料庫所擁有的角色及資料庫使用者 ```SQL Select * From sys.sysusers; ``` 7. 列出所有資料庫使用者所擁有的角色資訊(當下使用的資料庫) ```SQL Select Upper(SDPL.name) As [Database Role Name], SDPR.[name] As [Database User Name], SDPR.type_desc As [User Type] From sys.database_principals SDPL Inner Join sys.database_role_members SDRM On SDPL.principal_id = SDRM.role_principal_id Inner Join sys.database_principals SDPR On SDRM.member_principal_id = SDPR.principal_id Order By [Database User Name] ```
## 伺服器管理者 1. 建立一個以 SQL Server 驗證登入為基礎的伺服器管理者 ```SQL // 1.Create SQL auth login in the master database Create Login [login_user] With Password = 'qazwsx789' // 2.Create a user mapped to the [login_user] you created in previous steps Create User [user_name] For Login [login_user] With DEFAULT_SCHEMA = dbo; // 3.Add user to roles Alter Role dbmanager Add Member [user_name]; Alter Role loginmanager Add Member [user_name]; ``` 2. 加入一個以 Azure Active Directory 使用者為基礎的伺服器管理者 ```SQL // 1.Create Azure AD user in the master database Create User [coco@azure.com] From External Provider With DEFAULT_SCHEMA = dbo; // 2.Add user to roles Alter Role dbmanager Add Member [coco@azure.com]; Alter Role loginmanager Add Member [coco@azure.com]; ``` 3. 列出所有 SQL Server 驗證登入的帳號 ```SQL // 1. Show all SQL auth login in the master database Select * From sys.sql_logins; ``` 4. 刪除指定的 SQL Server 驗證登入帳號 ```SQL // 1. Delete SQL auth login in the master database Drop LOGIN [login_user]; ```
## 管理伺服器層級(Server-Level)的防火牆 1. 建立伺服器層級的防火牆 ```SQL // 1. Choose the master database Use [master]; // 2. Add a server-level IP firewall rule Execute sp_set_firewall_rule @name = N'ServerFirewallName', @start_ip_address = '192.168.1.10', @end_ip_address = '192.168.1.100' ``` 2. 刪除指定名稱的伺服器層級防火牆 ```SQL // 1. Choose the master database Use [master]; // 2. Delete a server-level IP firewall rule Execute sp_delete_firewall_rule @name = N'ServerFirewallName' ``` 3. 查詢所有伺服器層級的防火牆 ```SQL // 1. Choose the master database Use [master]; // 2. List all server-level IP firewall rule Select * From sys.firewall_rules Order By [name]; ```
## 管理資料庫層級(Database-Level)的防火牆 1. 建立資料庫層級的防火牆 ```SQL // 1. Choose a database Use [target_database_name]; // 2. Add a server-level IP firewall rule Execute sp_set_database_firewall_rule @name = N'DatabaseFirewallName', @start_ip_address = '192.168.1.10', @end_ip_address = '192.168.1.100' ``` 2. 刪除指定名稱的資料庫層級防火牆 ```SQL // 1. Choose a database Use [target_database_name]; // 2. Delete a server-level IP firewall rule Execute sp_delete_database_firewall_rule @name = N'DatabaseFirewallName' ``` 3. 查詢所有資料庫層級的防火牆 ```SQL // 1. Choose a database Use [target_database_name]; // 2. List all server-level IP firewall rule Select * From sys.database_firewall_rules Order By [name]; ```
##參考資料 [\[Azure\] 設定 Azure SQL Database 上的資料庫存取權](https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-manage-logins#unrestricted-administrative-accounts) [\[Azure\] Azure SQL Database 的防火牆規則](https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-firewall-configure) [\[SQL Docs\] 所有內建的資料庫層級角色](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-2017) [\[MSSQL Tips\] Adding Users to Azure SQL Databases](https://www.mssqltips.com/sqlservertip/5242/adding-users-to-azure-sql-databases)
# 額外的伺服器層級系統管理員帳戶 除了上述提到的兩種系統管理員帳戶，Azure SQL Server 也允許在 master 資料庫上新增其它受限制的系統管理帳戶(就是沒上面兩種強大就對了)，有以下兩種 **資料庫建立者(Database creators)** 當指定帳號被給予 `dbmanager` 角色權限時，此角色的帳號就可建立新的資料庫。然後該角色的使用者必須是 master 資料庫中以 SQL Server 登入為基礎的使用者，或是以 Azure Active Directory 使用者為基礎的自主資料庫使用者。建立步驟如下 1. 使用系統管理員帳戶連線到 master 資料庫 2. 使用 `Create Login` 建立一個 SQL Server 登入帳號 ```SQL Create Login [login_user] With Password = '
# 防火牆規則 以往對 Azure SQL Server 的防火牆設定只停留在 Azure 入口網站裡設定白名單及黑名單 IP，藉由這次的筆記，開始對這一塊有更進一步的認知，防火牆可分為`伺服器層級(server-level)`及`資料庫層級(database-level)`兩種。一般我們在 Azure 入口網站裡設定的屬於**伺服器層級**，正確設定後，我們就能透過用戶端工具(例如 SQL Server Management Studio 或 SQL Server Data Tools)以 Server 管理員或 Active Directory 管理員的身份對 master 資料庫進行連接，如下圖  而**資料庫層級**的防火牆只能透過 `sp_set_database_firewall_rule` 陳述式來設定(Azure 入口網站現階段沒有提供)，一般來說，非系統管理員的使用者應該要被設定只能透過資料庫層級的防火牆來存取所使用的資料庫，也就是說如果**伺服器層級的防火牆**沒有開放對該使用者的 IP 有存取資料庫的權限，但**資料庫層級的防火牆**有時，該使用者仍然可以使用如 SQL Server Management Studio 或 SQL Server Data Tools 之類的用戶端工具來連接，利用資料庫層級的防火牆來管理資料庫使用者的存取權限可以避免在伺服器層級的防火牆上開一堆對外的白名單 IP，而造成後續維護上的困難或資安上的疑慮，下面是一個典型非系統管理員對資料庫層級的防火牆存取示意圖  至於兩者實際的運作方式為所有來自外部的連線都會**先訪問資料庫層級的防火牆**是否有存取目標資料庫的權限，無權限時才會**再詢問伺服器層級的防火牆**是否有存取權限，運作流程圖如下 
# 結論 以上算是自己對 Azure SQL Server 上一些基本的權限管理認知，事實上這塊還有很多方面都還沒有提到(資料實在他X的有夠多...)，像是資料庫內建角色的種類及影響範圍或群組，所以目前也是先以實務上常用到的功能來做精進，之後會在補上新學到的內容或修正錯誤的觀念。最後附上一些自己實際常在用的懶人包，讓以後自己不小心忘記時可以回來查詢XD ## 資料庫使用者 1. 建立一個以 SQL Server 驗證登入為基礎的資料庫使用者，並賦與對目標資料庫讀寫的權限 ```SQL // 1.Create SQL auth login in the master database Create Login [login_user] With Password = 'qazwsx789' // 2.Choose a database to use and create a database user mapped to the [login_user] you created in previous steps Use [selected_database_name]; Create User [user_name] For Login [login_user] With DEFAULT_SCHEMA = dbo; // 3.Add user to roles (read/write) Alter Role db_datareader Add Member [user_name]; Alter Role db_datawriter Add Member [user_name]; ``` 2. 建立一個不需依賴 SQL Server 驗證登入的自主資料庫驗證使用者(SQL Authentication Contained User)，並賦與對目標資料庫讀寫的權限 ```SQL // 1.Choose a database to use and create a SQL authentication contained user Use [selected_database_name]; Create User [user_name] With Password = 'qazwsx789', DEFAULT_SCHEMA = dbo; // 2.Add user to roles (read/write) Alter Role db_datareader Add Member [user_name]; Alter Role db_datawriter Add Member [user_name]; ``` 3. 建立以 Azure Active Directory 驗證為基礎的資料庫使用者，並賦與對目標資料庫讀寫的權限 ```SQL // 1.Choose a database to use and create a Azure Active Directory user Use [selected_database_name]; Create User [coco@azure.com] From External Provider With DEFAULT_SCHEMA = dbo; // 2.Add user to roles (read/write) Alter Role db_datareader Add Member [coco@azure.com]; Alter Role db_datawriter Add Member [coco@azure.com]; ``` 4. 移除資料庫使用者指定的角色權限 ```SQL // Remove specific user's read permission Alter Role db_datareader Drop Member [user_name]; // Remove specific user's write permission Alter Role db_datawriter Drop Member [user_name]; ``` 5. 移除指定的資料庫使用者 ```SQL // Remove specific database user Drop User [user_name]; ``` 6. 列出當下使用的資料庫所擁有的角色及資料庫使用者 ```SQL Select * From sys.sysusers; ``` 7. 列出所有資料庫使用者所擁有的角色資訊(當下使用的資料庫) ```SQL Select Upper(SDPL.name) As [Database Role Name], SDPR.[name] As [Database User Name], SDPR.type_desc As [User Type] From sys.database_principals SDPL Inner Join sys.database_role_members SDRM On SDPL.principal_id = SDRM.role_principal_id Inner Join sys.database_principals SDPR On SDRM.member_principal_id = SDPR.principal_id Order By [Database User Name] ```
## 伺服器管理者 1. 建立一個以 SQL Server 驗證登入為基礎的伺服器管理者 ```SQL // 1.Create SQL auth login in the master database Create Login [login_user] With Password = 'qazwsx789' // 2.Create a user mapped to the [login_user] you created in previous steps Create User [user_name] For Login [login_user] With DEFAULT_SCHEMA = dbo; // 3.Add user to roles Alter Role dbmanager Add Member [user_name]; Alter Role loginmanager Add Member [user_name]; ``` 2. 加入一個以 Azure Active Directory 使用者為基礎的伺服器管理者 ```SQL // 1.Create Azure AD user in the master database Create User [coco@azure.com] From External Provider With DEFAULT_SCHEMA = dbo; // 2.Add user to roles Alter Role dbmanager Add Member [coco@azure.com]; Alter Role loginmanager Add Member [coco@azure.com]; ``` 3. 列出所有 SQL Server 驗證登入的帳號 ```SQL // 1. Show all SQL auth login in the master database Select * From sys.sql_logins; ``` 4. 刪除指定的 SQL Server 驗證登入帳號 ```SQL // 1. Delete SQL auth login in the master database Drop LOGIN [login_user]; ```
## 管理伺服器層級(Server-Level)的防火牆 1. 建立伺服器層級的防火牆 ```SQL // 1. Choose the master database Use [master]; // 2. Add a server-level IP firewall rule Execute sp_set_firewall_rule @name = N'ServerFirewallName', @start_ip_address = '192.168.1.10', @end_ip_address = '192.168.1.100' ``` 2. 刪除指定名稱的伺服器層級防火牆 ```SQL // 1. Choose the master database Use [master]; // 2. Delete a server-level IP firewall rule Execute sp_delete_firewall_rule @name = N'ServerFirewallName' ``` 3. 查詢所有伺服器層級的防火牆 ```SQL // 1. Choose the master database Use [master]; // 2. List all server-level IP firewall rule Select * From sys.firewall_rules Order By [name]; ```
## 管理資料庫層級(Database-Level)的防火牆 1. 建立資料庫層級的防火牆 ```SQL // 1. Choose a database Use [target_database_name]; // 2. Add a server-level IP firewall rule Execute sp_set_database_firewall_rule @name = N'DatabaseFirewallName', @start_ip_address = '192.168.1.10', @end_ip_address = '192.168.1.100' ``` 2. 刪除指定名稱的資料庫層級防火牆 ```SQL // 1. Choose a database Use [target_database_name]; // 2. Delete a server-level IP firewall rule Execute sp_delete_database_firewall_rule @name = N'DatabaseFirewallName' ``` 3. 查詢所有資料庫層級的防火牆 ```SQL // 1. Choose a database Use [target_database_name]; // 2. List all server-level IP firewall rule Select * From sys.database_firewall_rules Order By [name]; ```
##參考資料 [\[Azure\] 設定 Azure SQL Database 上的資料庫存取權](https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-manage-logins#unrestricted-administrative-accounts) [\[Azure\] Azure SQL Database 的防火牆規則](https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-firewall-configure) [\[SQL Docs\] 所有內建的資料庫層級角色](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-2017) [\[MSSQL Tips\] Adding Users to Azure SQL Databases](https://www.mssqltips.com/sqlservertip/5242/adding-users-to-azure-sql-databases)
