SQL Server 登入與系統安全

第十六章 SQL Server登入與系統安全

Chapter 16 Outline • 16-1 SQL Server的資料安全 • 16-2 SQL Server驗證 • 16-3 Windows 驗證 • 16-4 SQL Server伺服器角色與資料庫角色 • 16-5 SQL Server的存取權限 • 16-6 檢視、預存程序以及函數的存取權限 • 16-7 制定存取權限策略 • 16-8 本章總結

16-1 SQL Server的資料安全 • 當連接上SQL Server 2005的時候，系統會在3個地方檢驗系統安全， • 首先作業系統會加以檢驗， • 然後是SQL Server本身， • 最後則是每一個別的資料庫。 • 依上述所言，可以登入系統或SQL Server並不表示能夠存取資料庫，想要存取資料則需要擁有對資料庫物件的存取權限(Permission)

16-1 SQL Server的資料安全 • SQL Server 2005支援兩種安全登入模式： • 「SQL Server及Windows 驗證」(混合模式) • 「Windows 驗證模式」。 • 在混合型系統安全模式下，可以使用Windows認證或是SQL Server認證的方式登入系統；而在Windows驗證模式下，就只能夠使用Windows 認證方式登入。

16-1 SQL Server的資料安全 選擇驗證方式的選項 • 在安裝SQL Server時就已經決定要使用哪一種驗證方式，但是還是可以透過SSMSE做變更。請在SSMSE中點選連線伺服器滑鼠右鍵彈出式選單的[屬性]選項點選畫面左邊的[安全性]。

16-2 SQL Server驗證 • 在SQL Server認證模式下，整個認證過程不需要透過Windows的協助，完全是由SQL Server本身來執行。SQL Server接受使用者所提供的登入識別碼(Login ID)與通行密碼(Password)，並加以確認是否合法。 • 當選擇混合模式驗證時，系統內的登入識別碼只有預設的sa，並且需要在安裝時設定其密碼，sa代表System Administrator的意思。

16-2 SQL Server驗證 • 新增SQL Server驗證登入帳號為MaryChang(密碼也為MaryChang)，請依下列步驟操作： • 請開啟SSMSE並連接到伺服器，然後展開[安全性]展開[登入]項目 • 請於右半部視窗中的空白處(或[登入]項目)按滑鼠右鍵，於彈出式選單中點選[新增登入]選項，此時螢幕上會出現新增登入的畫面。

16-2 SQL Server驗證 輸入帳號選擇驗證方式使用該帳號連線後就會自動使該資料庫

16-3 Windows 驗證 • Windows驗證模式：可以在混合模式或Windows驗證模式下使用，當選擇使用該模式時，sa帳戶就會失效。 • 在Windows驗證模式下需要提供Windows系統安全憑證才能夠連接上SQL Server。也就以同一組帳號密碼登入Windows或是必要的網域(Domain)以取得系統安全憑證之後就可以登入SQL Server。但是在SQL Server 中還是需要以該組Windows帳號建立登入並設定權限。

16-3 Windows 驗證 • 將Windows使用者帳號加至SQL Server中可分為兩種，一是使用者帳號，另一種是使用者群組，如下圖所示：

16-3 Windows 驗證 • 群組是為了簡化管理的程序。例如：DBA依據公司的需求將管理SQL Server分成三個群組並規劃好存取權限。如果有20個員工要登錄SQL Server，則設定好這20個人的帳戶之後將之指定成某一個群組即可，這樣就不用在SQL Server中一個一個設定存取權限了。 • 另外一種簡化設定的方式就是使用SQL Server的角色。

16-3 Windows 驗證 • 授與Windows使用者(群組)登入SQL Server的權限 • 請在Windows中建立使用者或是群組 • SSMSE展開相關SQL Server的連線展開[安全性]點選[登入]滑鼠右鍵彈出式選單的[新增登入]選項。 • 出現[登入-新增]畫面之後請在[登入名稱]輸入Windows中所建立的使用者或是群組，如不確定則請按[搜尋]按鈕

16-3 Windows 驗證

16-4 伺服器角色與資料庫角色 • 再強調一次，當使用者能夠登入一SQL Server後，還需要為他設定操作資料庫伺服器或是存取資料庫的權限，因為能夠登入SQL Server並不表示能夠為所欲為的使用所有資料庫或任意的操作資料庫。例如使用上一節所建立的帳號登入SQL Server所出現的畫面如下：

16-4 伺服器角色與資料庫角色 • 「角色」為SQL Server中某種存取權限的代表。 • 一個角色可以包含另一角色，而且一個使用者也可以屬於多個角色。 • 一使用者指定為某一角色之後就具備了SQL Server為該角色所內定的存取權限。 • 如果內定的角色權限不夠用也可以自訂角色

16-4 伺服器角色與資料庫角色 • 在SQL Server中的角色分為兩大類：伺服器角色(Server Roles)與資料庫角色(Database Roles) • 伺服器角色是用來規劃操作整個伺服器 • 資料庫角色是用來規劃操作某個資料庫 • 我們可以將使用者登入加入一個或一個以上的伺服器角色、資料庫角色，但是不能夠移除系統內定的角色。在SQL Server中，這些內定的角色都稱之為固定式(Fixed)角色。

16-4 伺服器角色與資料庫角色 • 固定式伺服器角色 • sysadmin(系統管理員)：該角色的成員可以對SQL Server伺服器做任何事情，他們會凌駕於存取權限與安全系統之上，而且是每一個資料庫的擁有者，sa即屬於本角色。 • serveradmin(伺服器管理員)：該角色的成員可以設定伺服器組態，也可以使用SHUTDOWN命令關閉SQL Server，伺服器管理員應是本角色的成員。 • setupadmin：該角色的成員可以為連接的伺服器安裝並設定SQL Server組態。

16-4 伺服器角色與資料庫角色 • securityadmin(安全性管理員)：該角色的成員可以新增並管理使用者登入與存取權限，也可以讀取SQL Server的錯誤日誌(Error Log)。 • processadmin (處理序管理員)：該角色的成員可以管控資料庫伺服器的執行程序(Process)，也可以移除脫離控制的查詢。 • dbcreator (資料庫建立者)：該角色的成員可以新增並變更伺服器上的資料庫，DBA應是本角色的成員(如果您不希望DBA為sysadmin角色的成員時使用)。

16-4 伺服器角色與資料庫角色 • diskadmin：該角色的成員可以管控伺服檔案與檔案的增長，DBA應是本角色的成員。 • bulkadmin：該角色的成員可以執行BULK INSERT敘述 • 假設MaryChang是新進的DBA職務代理人，因此想要先授與「處理序管理員」與「資料庫建立者」的角色，則可依下列步驟操作：

16-4 伺服器角色與資料庫角色 • 請執行SSMSE展開相關的SQL Server連線展開[安全性]項目展開[登入]項目點選[MaryChang]滑鼠右鍵彈出式選單的[屬性]選項 • 進入[登入屬性 -MaryChang]畫面之後請點選左上角的[伺服器角色]選項，此時就會出剛才所介紹過的固定式伺服器角色，只要勾選想賦予的角色即可

16-4 伺服器角色與資料庫角色 • 固定式資料庫角色：所謂的固定式資料庫角色是SQL Server為每一資料庫內建的角色，其影響層面只限於單一資料庫 • db_owner：該角色的成員於一資料庫內可執行任何動作，他們對該資料庫所擁有的使用權限與dbo一模一樣 • db_accessadmin：該角色的成員可以新增或移除使用者對一資料庫的存取權限

16-4 伺服器角色與資料庫角色 • db_securityadmin：該角色的成員可以管控一資料庫的所有存取權限、角色、角色成員關係以及各資料庫物件的擁有者，但是只有db_owner才可以將其他成員加入db_owner角色內 • db_ddladmin：該角色的成員可以新增、修改以及移除所有的資料庫物件(就是可以執行DDL敘述)，但是他們不能夠執行與系統安全有關的命令(GRANT、REVOKE、DENY) • db_backupoperator：該角色的成員可以執行DBCC、CHECKPOINT以及資料庫備份命令

16-4 伺服器角色與資料庫角色 • db_datareader：該角色的成員可以在所有的資料表與檢視上執行SELECT敘述，也就是唯讀的意思。 • db_datawriter：該角色的成員可以在所有的資料表與檢視上執行INSERT、DELETE以及UPDATE敘述。 • db_denydatareader：該角色的成員不可以在任一資料表或檢視上執行SELECT敘述。 • db_denydatawriter：該角色的成員不可以在任一資料表或檢視上執行INSERT、DELETE或UPDATE敘述。

16-4 伺服器角色與資料庫角色 • public與guest為SQL Server在資料庫中所預設的使用者與角色。 • 在未授與登入帳號任何資料庫的使用權限(就是未將SQL Serer登入帳號加入資料庫的使用者) 之前，這些登入帳號都會使用guest來存取資料庫。但是在新建立的資料庫中guest依預設值都是被停用的 • public資料庫角色也是系統內定的角色，而且每一資料庫都會擁有public角色，而且它的成員包含了每一資料庫的使用者與角色

16-5 SQL Server的存取權限 • 限制那些使用者能夠如何存取那些資料、那些使用者能夠備份那些資料庫或是那些使用者能夠新增並維護那些資料庫等等都屬於存取權限的設定範圍，這樣可以避免發生安全上的問題。 • 設定資料存取權限除了能夠強化資料庫的安全性外，系統還可以對各種動作進行稽核(Auditing)，稽核可以確定「誰做了什麼事」，這樣就可以決定責任歸屬的問題。

16-5 SQL Server的存取權限 • SQL Server 2005使用3種方式來處理使用者存取權限 -- 授與、撤銷以及拒絕承認，它們對應的SQL敘述分別為GRANT、REVOKE以及DENY • 授與(GRANT)使用者存取權限，使他們可以存取資料庫 • 拒絕承認(DENY)一項已授與的存取權限，則可暫時禁止他們使用該存取權； • 撤銷(REVOKE)已授與的存取權限，則永遠禁止他們使用該存取權。

16-5 SQL Server的存取權限 • SQL Server決定某一使用者對某一資料庫物件的存取權限是採用加總式的，但是拒絕承認則片面否定某一存取權限。 • 例如：李四因角色1而擁有SELECT存取權限，因角色2而擁有INSERT存取權限，則實際上她對該資料庫物件就擁有SELECT與INSERT存取權限。如果資料庫管理員使用DENY敘述拒絕承認李四的SELECT存取權限，則李四不再能夠讀取該資料庫的資料。

16-6 檢視、預存程序以及函數的存取權限 • 指定存取權限給檢視、預存程序或是函數，以避免直接指定較多的存取權限給一資料表 • 不用使用複雜的欄位層次存取權限，這樣可以使得管理存取權限更加容易 • 例如：有一Employees資料表包含下列欄位：e_name(員工姓名)、address(地址)以及salary(薪資)，如果MaryChang可以修改e_name與address但是不能讀取salary欄位時該如何設定呢？

16-6 檢視、預存程序以及函數的存取權限 • 做法一：將salary欄位拒絕授與給MaryChang • 做法二：新增一包含e_name與address的檢視，再授與MChang完全存取該檢視的存取權限。 • 預存程序、函數的存取權限與檢視很類似，但不同之處是它可以擁有程式碼，所以只要授與Execute權限給一使用者，那麼該使用者就能夠執行一預存程序或函數所能做的事，可能包含對一個或多個資料表執行SELECT、INSERT、DELETE以及UPDATE(函數就只能做SELECT的動作)。

16-7 制定存取權限策略 • 管理系統安全的基本原則 • 授與public角色一般使用者應具備的存取權限 • 授與一Windows群組該群組應具備的存取權限，或是新增一角色並授與該角色存取權限 • 在無法使用以上兩種方式時，才個別的授與使用者存取權限 • 授與一使用者他所需要的存取權限，不要為了方便而過度的授與他不需要的存取權限 • 要對每一使用者所擁有的存取權限加以記錄，資料庫管理員必須能夠全盤掌握系統安全相關資訊

16-7 制定存取權限策略 • 指定一資料庫的負責人為資料庫擁有者，如果有其他使用者需要對該資料庫的完全存取權限，可以將他們納入db_owner角色 • 不要為了解決問題而授與一使用者所有的存取權限，應逐步尋求該使用者所需的存取權限而授與 • 不要授與一般使用者新增資料庫或新增物件的存取權限

16-8 本章總結 • SQL Server 2005 Express 支援兩種安全存取模式：「SQL Server及Windows 驗證模式」(混合型)與「Windows 驗證模式」 • 在混合型系統安全模式下，我們可以使用Windows認證或是SQL Server認證的方式登入系統；在Windows驗證模式下，就只能夠使用Windows認證方式登入 • 使用者可以登入SQL Server並不表示能夠使用所有的資料庫，一使用者必須被加入資料庫的使用者，才能夠存取一資料庫。

16-8 本章總結 • SQL Server 2005的角色分為兩大類：伺服器角色與資料庫角色，而資料庫角色又除了固定式之為還可以由使用者自行定義資料庫角色，但是伺服器角色就不行由使用者自行定義。 • SQL Server使用3種方式來處理使用者存取權限 -- 授與(GRANT)、撤銷(REVOKE)以及拒絕承認(DENY)。 • 不要為了解決問題而授與一使用者過多的存取權限，應逐步尋求該使用者所需的存取權限而授與之。

SQL Server 登入與系統安全