SQL Server的權限管理和數據恢復

一、SQL Server權限設置

SQL Server與其他服務器不同，通常保存著運營、開發、市場等重要數據，其中大量數據還涉及機密。因此掌握SQL Server的權限設置是DBA必備的關鍵技能。

1、SQL Server的安全機制

SQL Server的安全性是建立在認證和訪問許可兩種安全機制之上的。其中，認證是用來確定登錄SQL Server的用戶的登錄賬號和密碼是否正確，以此來驗證其是否具有連接SQL Server的權限；
訪問許可用來授予用戶或組能夠在數據庫中執行哪些操作，如創建表、查詢數據等。

SQL Server的安全機制主要包括以下四個方面：

• 客戶機的安全機制；

• 服務器的安全機制；

• 數據庫的安全機制；

• 數據對象的安全機制；

• 用戶必須能夠登錄客戶機，然后才能使用SQL Server應用程序或管理工具來訪問數據庫。對于使用Windows系統的客戶來說，它主要涉及到操作系統的安全。主要是Windows賬號的安全。

• 用戶登錄服務器時，必須使用一個賬號（也稱為登錄賬號）和密碼，服務器會按照不同的身份驗證方式來判斷這個賬號和密碼的正確性。

• 任何能夠登錄服務器的賬號和密碼都對應著一個默認的數據庫。SQL Server對數據庫的權限管理采用的”數據庫用戶”的概念。

• 用戶通過前面三道防線后才能訪問數據庫中的數據對象，對數據對象能夠做什么樣的訪問稱為訪問權限。常見的訪問權限包括數據的查詢、更新、插入和刪除。

2、登錄權限設置

1）SQL Server的身份驗證方式

SQL Server有以下兩種身份驗證模式：
Windows身份驗證模式；
SQL Server和Windows身份驗證模式（混合驗證模式）；

①Windows身份驗證模式

當使用Windows身份驗證時，SQL Server會使用操作系統中的Windows用戶密碼。也就是說，用戶身份由Windows進行確認。SQL Server不再提供密碼，也不執行身份驗證。Windows身份驗證使用Kerberos安全協議。比SQL Server身份驗證更為安全協議，比SQL Server身份驗證更為安全，可以保護SQL Server免受大部分來自網絡的非法操作。

Windows身份驗證模式適用于局域網內部，訪問數據庫的情況。

②SQL Server和Windows身份驗證模式

SQL Server和Windows 身份驗證模式也稱為混合驗證模式。允許用戶使用Windows身份驗證或者使用SQL Server身份驗證進行連接。該模式通常用于運行非Windows系統環境的用戶、Internet用戶或混雜的工作組用戶訪問SQL Server時。例如，外界用戶訪問數據庫或不能登錄Windows域時。

當使用SQL Server身份驗證時，在SQL Server中創建的登錄名并不是基于Windows用戶賬號的。用戶名和密碼通過使用SQL Server創建并存儲在SQL Server中。如果必須使用SQL Server身份驗證，必須為所有SQL Server賬號啟用密碼策略，如強制密碼過期、密碼復雜度等。

在SSMS中設置服務器身份驗證模式，方法如下：

打開SSMS，右擊SSMS實例，在彈出的快捷菜單中選擇“屬性”命令

在打開的“服務器屬性”窗口中選中“安全性”選項，可以設置身份驗證模式

2）登錄賬戶設置

通過使用SSMS可以創建和管理登錄賬戶。在默認情況下，SQL Server使用Windows身份驗證模式，SQL Server的登錄賬戶無法登錄。如果需要利用創建的SQL Server賬戶登錄，需要將身份驗證模式改為SQL Server和Windows身份驗證模式

在SQL Server中，對SQL Server登錄賬戶提供了以下安全策略：

• 強制實施密碼策略；

• 強制密碼過期；

• 用戶在下次登錄時必須更改密碼；

當新建SQL Server登錄賬號時，為了提高其安全性，建議選中“強制密碼策略”復選框。

sa是SQL Server的默認管理員賬號，由于該賬號廣為人知，容易成為惡意用戶的非法操作目標，因此除非某些程序要求必須使用sa賬號，否則建議禁用此賬號。絕對不能將sa賬號設置為空密碼或弱密碼，建議創建一個擁有與sa賬戶權限相同的超級用戶來管理數據庫。但是不能讓擁有管理員權限的賬號泛濫。

二、SQL Server訪問許可權限設置

SQL Server中的訪問許可可以分別從服務器、數據庫及對象三個級別進行設置。

1、服務器級別的權限設置

在SQL Server中內置了一組服務器角色（也稱為固定服務器角色），這些角色用來執行服務器級別的管理，如創建數據庫、管理和審核登錄賬戶、中止SQL Server實例中長時間運行的進程等。他們作用域整臺服務器，而非單獨某個數據庫。SQL Server2016中的“角色”類似于Windows中的“組”。通過將不同的角色賦予不同的用戶，用戶可以具有不同的操作權限。

一個用戶可以擁有多個固定服務器角色權限，但是固定服務器角色不能被修改、添加和刪除。具體服務器角色及其能夠執行的操作如下：

SQL Server中還有一個特殊的服務器角色public，所有登錄名都屬于該角色。該角色只擁有查看數據庫的權限。

1）為用戶分配服務器級別權限的具體步驟如下：

2、數據庫級別權限設置

1）新建數據庫用戶

建立了SQL Server登錄賬戶后，需要賦予用戶權限，用戶才能夠對數據庫進行查詢與更新等操作。登錄賬戶用戶訪問SQL Server實例，數據庫用戶則用于訪問數據庫。如果需要訪問某個數據庫，則需要在數據庫中建立相應的數據庫用戶。

實際上，數據庫用戶是映射到登錄賬戶上的。建立過程如下：

2）內置數據庫用戶

創建的任何一個數據庫中默認包含兩個特殊用戶dbo和guest，且不能被修改和刪除，如圖：

• dbo是數據庫的所有者，對數據庫具有所有權限。默認情況下，創建數據庫的登錄賬號自動對應于數據庫的dbo用戶，dbo用戶又屬于db_owner數據庫角色的成員，而db_owner數據庫角色具有對數據庫的全部權限。因此，可以說那個登錄賬戶創建了數據庫，這個登錄賬戶就具備了該數據庫的所有管理權限。此外，登錄賬戶sa對應所有系統數據庫的dbo用戶。

• guest是來賓用戶，同樣不可刪除。guest用戶不映射任何登錄賬戶。所有沒映射的登錄賬戶都將以guest用戶身份訪問數據庫。guest用戶默認沒有任何權限，且處于禁用狀態，因此啟用并授予權限時一定要慎重。

3）數據庫角色

服務器角色主要控制服務器登錄名在服務器范圍內的訪問，但是在設置具體數據庫的管理和操作權限方面就顯得權限太大了，因此需要依靠數據庫角色對數據庫級別的對象進行更加細化的權限劃分。SQL Server默認提供了一組固定數據庫角色，用來賦予用戶操作數據庫的權限，如查詢數據表、備份和還原數據庫等。固定數據庫角色及其權限如下圖：

• 固定數據庫還包含有一個名為public的角色，該角色用來捕捉數據庫用戶的所有默認權限，所有用戶都屬于public角色，該角色同樣不可以被刪除；

• 數據控制語言包括GRANT和REVOKE；

為用戶設置數據庫級別權限的具體步驟如下：

根據自己需求自行配置選項即可

3、對象級別權限設置

一個數據庫中通常包含多個數據表、視圖、存儲過程等對象，如果賦予了某個用戶對數據庫的讀取權限，用戶就可以讀取該數據庫下的所有表或視圖等。

1）為表授權

SQL Server還提供了一些更細化的權限，如查詢、插入或更新數據的權限等，這些權限可以作用于單個表上。常用的權限及其說明如下圖：

在單個表上授予用戶權限的具體步驟如下：

如果同時賦予了“授予”和“拒絕”權限，則“拒絕”權限的優先級高于“授予”權限。

2）為數據庫授權

數據庫本身也是對象，為數據庫授權是，可以為其授予在數據庫中創建其他各種對象的權限，如更改用戶、創建表、查詢數據等。

在單個數據庫上授權用戶權限的具體操作步驟如下：

三、SQL Server備份和還原

數據庫管理員最擔心的情況就是數據庫癱瘓，造成數據丟失。而備份作為數據的副本，可以有效的保護和恢復數據。

1、為什么要備份

對于生產數據來講，數據的安全性是至關重要的，任何數據的丟失都可能產生嚴重的后果。通常，造成數據丟失的原因主要包括以下幾類：

• 程序錯誤；
• 人為錯誤（如管理員誤操作）；
• 計算機失敗（系統崩潰）；
• 磁盤失敗；
• 災難和盜竊；
  所以，數據庫管理員的主要職責之一就是實施和規劃一個妥善的備份和還原策略以保護數據庫，避免由于各種故障造成損失而丟失數據，以在系統失效后盡快地恢復數據庫。

2、備份類型

在SQL Server中提供了三種常用的備份類型，分別是完整備份、差異備份和事務日志備份。

1）完整備份

完整備份包括對整個數據庫、部分事務日志、數據庫結構和文件結構的備份。完成備份代表的是備份完成時刻的數據庫。

完整備份是備份的基礎，提供了任何其他備份的基準。其他備份（如差異備份）只有在執行完成備份之后才能被執行。

2）差異備份

差異備份是指對上一次完整備份之后所有更改的數據做備份，備份過程能夠識別出數據庫哪一部分被修改了，并只對這一部分做備份。

差異備份的優勢是速度快、備份數據庫所需要的時間很少，但差異備份要求事先執行一次完成備份。

3）事務日志備份

事務日志備份記錄了數據庫的所有改變。在備份事務日志時需要考慮以下因素：

• 在執行了至少一次完成備份后，才能備份事務日志；

• 在簡單恢復模式下不能備份事務日志；

• 如果沒有與其一致的數據庫備份，則不能恢復事務日志；

在執行數據庫事務日志備份期間，SQL Server將做以下工作：

• 備份事務日志：從上一次成功的事務日志備份到當前事務日志的尾部；

• 截斷事務日志：到事務日志活動部分的開始，丟棄不活動的部分；

此外，事務日志備份主要是SQL語句，而不是整個數據庫結構、文件結構或數據。

3、恢復模式

不管是備份還是還原，都是在一定的恢復模式中進行的。恢復模式是數據庫的一種特性、控制這數據庫備份和還原的基本行為。書籍庫管理員應根據企業系統實際運作的需求及對數據可用性和恢復的需求來選擇合適的恢復模式。

SQL Server提供了三種恢復模式：簡單模式、完整恢復模式和大容量恢復模式。

1）簡單恢復模式

在簡單恢復下，，不活動的日志將被刪除，所以不支持事務日志備份，如下圖所示,圖中進行了一些數據庫備份。在最近的備份t5之后的一段時間，此數據庫中出現了數據丟失。數據庫管理員將使用t5備份來講數據庫還原到備份完成的時間點，之后對數據庫進行更改將丟失。

由于不支持事務日志備份、數據庫只能恢復到備份的時間點，而無法將數據庫還原到故障點或特定的時間點。因此，簡單恢復模式主要用于小型數據庫和不經常更改的數據庫。

2）完整恢復模式

對于十分重要的生產數據庫，在發生故障時可能要求恢復到歷史上某個時刻。一旦發生故障時，必須保證數據不丟失，一旦發生故障時，必須保證數據不丟失，保證數據能夠恢復到發生故障時的狀態。這樣，就必須采用完整恢復模式。

完整恢復模式可在最大范圍內防止出現故障時丟失數據，它包括數據庫備份和事務日志備份，并提供全面保護，使數據庫免受媒體故障的影響。這個模式使用數據庫和所有日志的備份區恢復數據庫如果日志沒有損壞，SQL Server可以恢復所有數據，除了在失敗那一刻的事務。

由于支持事務日志備份，因此完整恢復模式可以將數據庫恢復到任意一個指定的時間點。如圖：

圖中，能將數據庫恢復到故障點，從而恢復所有數據，恢復操作可以還原所有已提交的事務，正在進行的事務將回滾。

3）大容量日志恢復模式

DBA在某些場合需要對數據庫執行一些大批量的數據插入、更新或刪除操作，如一次需要導入上百萬條數據。如果在完整恢復模式下，那么這些操作將產生大量的日志記錄，導致數據庫性能很低。在這種環境下，可以采用大容量日志恢復模式來提高性能。

雖然大容量日志恢復模式會完整地記錄其他事務，但它只對大容量操作進行最小記錄。大容量日志恢復模式是對完整恢復模式的補充，提供了良好的性能，且占用較小的日志空間d.anshi，大容量日志恢復模式增加了這些大容量復制操作丟失數據的風險，因為最小日志記錄大容量操作不會逐個記錄書屋。

只要日志備份包含大容量操作，數據庫就只能恢復到日志備份的結尾，而不能恢復懂啊某個時間點，或日志備份中某個標記的事務。

4）更改恢復模式

4、備份設備

在進行備份的時候，首先需要建立一個文件來存儲備份的數據，這個備份文件就叫備份設備。

SQL Server使用物理備份設備和邏輯備份設備標識備份設備。

物理備份設備是操作系統用來表示備份設備的名稱；比如：C:\Backups\Accounting\Full.bak。

邏輯備份設備是用戶定義的別名，用來標識物理備份設備；邏輯備份設備永久地存儲在SQL Server內的系統表中，使用邏輯備份設備的優點是引用它比引用物理設備名稱簡單。

四、備份和還原

1、數據庫備份

2、數據庫還原

———————— 本文至此結束，感謝閱讀 ————————