[食譜好菜] 既生登入,何生使用者?SQL Server「登入」及「使用者」的關係。

當我們建立了一個 SQL Server 實例之後,在開始處理資料之前,如果是對資安意識相對高一點的開發者,應該會有一個步驟是為我們的應用程式建立帳號,並且授予相對應的權限,而不會用 sa 走天下,當我們建立了一個登入時,通常會去使用者對應的頁面勾選對應的資料庫,此時,SQL Server 也會幫我們建立一個與登入名稱一樣的使用者,我們可能沒有感覺,但是 SQL Server 的登入及使用者其實是拆開來的。

登入 vs 使用者

登入是伺服器層級的物件,而使用者是資料庫層級的物件,剛開始的時候其實我很混亂,為什麼登入要跟使用者拆開?使用者可以不登入嗎? 其實這不難理解,SQL Server 做為一個大大小小企業所使用的資料庫產品,在權限上的設計是很精細的,所謂的登入是登入到 Database Engine,而使用者是使用 Database 的成員。

所以原則上是這樣,我們要從外部進入 SQL Server 一定要登入進行驗證,而登入進去之後要使用哪一個使用者操作資料庫,是可以選擇的,甚至是可以動態切換使用者,從 SQL Server 2005 開始多了一個東西 SQL user without login(沒有登入的 SQL 使用者),顧名思義,SQL 使用者不一定都要連結到一個登入。

如果想要了解 SQL Server 權限設計,可以參考 Microsoft_SQL_Server_2017_and_Azure_SQL_Database_permissions_infographic.pdf 這份文件。

SQL user without login(沒有登入的 SQL 使用者)

接下來我們就來玩看看「沒有登入的 SQL 使用者」,實際地來觀察登入跟使用者是如何協作的?首先我們在資料庫內建立一個沒有登入的 SQL 使用者,只要在新增使用者的畫面,改變使用者類型的選項可以了。

這類型的使用者就不必連結到一個登入,不過這也意謂著,使用者無法透過登入的驗證程序登入到 SQL Server 來,那它們用來幹嘛? 其中一種應用就是讓已經登入的使用者,切換成另一個使用者,這樣子做,我們就不必將某A所擁有的權限,重複性地設定到某B身上,只要賦予某B可以切換成某A身份的權限就行了。

我們來看看怎麼設定,我先在 Test 資料庫建一個 test_login 登入,及 test_user 使用者,然後賦予 test_user 擁有 db_datareader 的角色。

所以現在 test_user 這個使用者只有讀取資料的權限,再來我新增一個 test_without_login 的使用者,賦予他 db_datawriter 的權限。

接下來是最重要的步驟,要將模擬 test_without_login 的權限賦予 test_user。

原本 test_user 是只能讀取資料的,嘗試新增資料時,會看到沒有 INSERT 權限的錯誤訊息。

整個完成之後,我們透過 EXECUTE AS USER='test_without_login' 指令,就可以讓 test_user 模擬 test_without_login 的身份來執行 SQL 語句,之後透過 REVERT 指令就可以返回 test_user 身份。

以上,關於 SQL Server 登入及使用者的關係就跟大家分享到這邊,希望對大家在理解 SQL Server 登入及使用者的設計上有一點點幫助。

參考資料

C# 指南ASP.NET 教學ASP.NET MVC 指引
Azure SQL Database 教學SQL Server 教學Xamarin.Forms 教學