## 背景 SQL Server 作為一種強大的關系型數據庫，能夠提供所有場景的應用。在絕大多數云計算公司中，都提供了SQL Server作為服務的云數據庫服務，譬如阿里云。但既然是服務，那么服務就需要可管理，可控制，因此，在云計算初期，都對云數據庫服務進行了嚴格的權限控制，好處就是可控可管理，但給用戶會帶來一些限制，某些限制實際上是可以再細粒度管控。因此，今天我們就要介紹一下阿里云數據庫SQL Server 2012在權限限制方面的提升與改善。 ## 用戶最關注的權限和使用 根據我們對用戶的理解和日常用戶的反饋，最常見的需求如下：? 1\. 需要創建、修改和刪除登錄和用戶的權限 2\. 需要創建、修改和刪除數據庫的權限 3\. 數據庫上的所有權限 4\. 需要結束連接的權限 5\. 需要方便讀取數據庫日志的權限? 6\. 需要鏈接服務器權限 7\. 需要SQL Server Profiler的跟蹤功能? 8\. 需要Database Engine Tuning Advisor來調校性能 ## 一、 RDS SQL Server 2012權限新增功能使用介紹 我們在SQL Server 2012的版本中，用戶的關注是我們改進和提升的方向，因此，經過論證與設計，會將這些權限還給用戶，讓用戶有更多的自由選擇和方便調試性能、管理自己的數據。那么如何來使用這些功能，我這里會將每個需求都演示一下如何使用（針對阿里云數據庫SQL Server 2012）： ### 1\. 創建、修改和刪除登錄和用戶的權限 首先，阿里云數據庫SQL Server 2012會提供一個根帳戶，這個根帳戶也可以與你新建帳戶的權限一致，但強烈建議你新建一個帳戶,根帳戶可以修改密碼，也可以DISABLE掉，甚至刪除掉，你還可以重置根賬號。 #### 1.1 創建LOGIN ~~~ --check current login user SELECT SUSER_NAME(),ORIGINAL_LOGIN() --create login CREATE LOGIN Test11 WITH PASSWORD=N'4C9ED138-C8F5-4185-9E7A-8325465CA9B7' ~~~ 創建登錄過程中，會將服務器級、數據庫級別等權限授予用戶。你會在message(消息)看到下列信息：  #### 1.2 更改LOGIN ~~~ --create login ALTER LOGIN Test11 WITH PASSWORD=N'123', CHECK_POLICY=OFF ~~~ 你不能更改你創建的任何之外的LOGIN，否則會出現一下錯誤：  #### 1.3 刪除LOGIN ~~~ --drop login DROP LOGIN Test11 ~~~ 同樣你不能刪除你創建的任何之外的LOGIN，否則會出現一下錯誤：?  #### 1.4 創建USER 你只能在自己新建的用戶數據庫創建用戶，在系統數據庫上是無法做操作的, 所以為了演示創建用戶，需要先創建一個數據庫testdb(稍后介紹具體信息)：? 使用login test11登錄到實例（假如你的密碼是123，因為剛才新建的用戶已經刪除，請重新創建一下） 請注意，如果是當前用戶創建數據庫，那么就將當前用戶設置該數據庫的一個用戶，并且角色自動分配為db_owner, 你也可為數據庫創建其他用戶和角色： ~~~ USE TestDB GO --create user CREATE USER [Test] FOR LOGIN [Test] --add database role ALTER ROLE [db_owner] ADD MEMBER [Test] GO -- query user name and role name SELECT dpp.name,dpm.name FROM sys.database_principals dpp INNER JOIN sys.database_role_members drm ON dpp.principal_id=drm.member_principal_id INNER JOIN sys.database_principals dpm ON dpm.principal_id=drm.role_principal_id WHERE dpp.name='test' ~~~ #### 1.5 更新USER 你可以更改USER,與SQL SERVER原始的操作方式一樣，比如更改用戶映射的登錄，如下： ~~~ USE TestDB GO ALTER USER test WITH LOGIN=test ~~~ #### 1.6 刪除USER 你還可以更改USER，與SQL SERVER原始的操作方式一樣，如下： ~~~ USE TestDB GO DROP USER test ~~~ ## 二、創建、修改和刪除數據庫 ## 2.1 創建數據庫 正如上面所說，如果是當前用戶創建數據庫，那么就將當前用戶設置該數據庫的一個用戶，并且角色自動分配為db_owner。如下： ~~~ CREATE DATABASE TestDb ~~~ 你會收到下列消息： ~~~ Change database owner to sa sucessfully. Create user Test11 and grant db_owner role in database TestDb. ~~~ 表示當前創建的登錄用戶是Test11,并且已經分配db_owner角色 注意： 在創建數據庫是，你不能隨意指定文件路徑，你可以不指定任何路徑，例如上面的SQL語句，但你也可以指定正確的路徑。如果你指定錯誤的路徑，會收到錯誤提示： ~~~ USE [master] GO CREATE DATABASE [TestDb_error_path] ON PRIMARY ( NAME = N'TestDb_error_path', FILENAME = N'E:\KKKK\DDD\\DATA\TestDb_error_path.mdf' ) LOG ON ( NAME = N'TestDb_error_path_Log', FILENAME = N'E:\\KKKK\DDD\\DATA\TestDb_error_path_log.ldf') ~~~ 你會收到下列信息: ~~~ Msg 50000, Level 16, State 1, Procedure *******, Line 57 The file path [ E:\\KKKK\DDD\\DATA\TestDb_error_path.mdf E:\\KKKK\DDD\\DATA\TestDb_error_path_log.ldf ] is invalid,please specify correct path folder [ E:\mmm\gggg\ ]. Msg 3609, Level 16, State 2, Line 2 The transaction ended in the trigger. The batch has been aborted. ~~~ 顯然，這個正確的路徑只有E:\mmm\gggg\，其他任何路徑都不會創建成功。 這個只是為了規范正確的路徑而已，沒有其他任何功能限制。 ## 2.2 更改數據庫 你可以更改大部分的數據庫屬性，但有幾個地方你需要注意，有些特性是不可以隨便更改的,。 ### 2.2.1 不能移動到錯誤的文件路徑 ~~~ ALTER DATABASE [TestDb] MODIFY FILE ( NAME = N'TestDb', FILENAME = N'E:\KKKK\DDD\DATA\TestDb.mdf' ) ~~~ 你會得到下列信息： ~~~ Msg 50000, Level 16, State 1, Procedure ******, Line 152 The file path [ E:\KKKK\DDD\DATA\TestDb.mdf ] is invalid,please specify correct path folder [ E:\mmm\gggg\ ]. Msg 3609, Level 16, State 2, Line 2 The transaction ended in the trigger. The batch has been aborted. ~~~ ### 2.3.2 不能將數據庫的恢復模式設置為FULL之外的其他模式 ~~~ ALTER DATABASE [TestDb] SET RECOVERY SIMPLE ~~~ 你會得到下列信息： ~~~ Msg 50000, Level 16, State 1, Procedure ******, Line 46 Login User [Test11] can't change database [TestDb] recovery model. Msg 3609, Level 16, State 2, Line 2 The transaction ended in the trigger. The batch has been aborted. ~~~ ### 2.3.3 將數據庫設置為OFFLINE后，不能直接ONLINE ~~~ USE [master] GO --set offline --ALTER DATABASE [TestDb] --SET OFFLINE --WITH ROLLBACK AFTER 0 ALTER DATABASE [TestDb] SET ONLINE ~~~ 你會得到下列錯誤信息： ~~~ Msg 5011, Level 14, State 9, Line 1 User does not have permission to alter database 'TestDb', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. ~~~ 當前設置為OFFLINE時，也不必驚慌，你可以使用sp_rds_set_db_online存儲過程，讓數據庫ONLINE: ~~~ EXEC sp_rds_set_db_online 'TestDb' ~~~ ## 2.2 刪除數據庫 刪除數據庫沒有任何特別之處，但是如果你的數據庫沒有進行過任何備份，也會提示： ~~~ DROP DATABASE [TestDb] ------------------------------------------------------------------------------------------------- Kindly reminder: your database [TestDb] does not exist any backup set. ------------------------------------------------------------------------------------------------- Login User [Test11] has dropped database [TestDb] . ~~~ ## 三、數據庫上的所有權限 數據庫我們首先設置為db_owner,但是回收了備份等必要的管控機制，現在的數據庫級別權限非常的自由，但也是有兩面性，權限更多了，那么自己操作的時候就需要更嚴謹和更仔細，避免導致數據庫的使用問題。 ## 四、結束連接的權限 結束連接的權限，我們更通俗地將是KILL權限，RDS SQL Server 2012已經授予這個權限，但是用戶只能KILL自己的的連接，用戶不要KILL其他連接，比如備份的連接。 ~~~ KILL (SPID) ~~~ ## 五、需要方便讀取數據庫日志的權限 以前你需要在控制臺讀取數據庫錯誤日志，現在你可以直接使用存儲過程sp_rds_read_error_logs讀取錯誤日志，使用方法與sp_readerrorlog是一樣的： ~~~ -- eg.1 EXEC sp_rds_read_error_logs -- eg.2 EXEC sp_rds_read_error_logs 0,1 ,'error' ~~~ ## 六、需要鏈接服務器權限 鏈接服務器現在還存在兩個問題，首先，不能用UI界面去創建鏈接服務器，UI界面創建鏈接服務器需要sysadmin權限，可以使用一系列的存儲過程創建。第二，由于RDS的設計，不能直接通過DNS和對應的IP創建，具體創建方式，暫時不能提供。但有解決方案。但我們提供了一個簡單的一鍵創建方式： ~~~ DECLARE @linked_server_name sysname = N'my_link_server', @data_source sysname = N'***********', --style: 10.1.10.1,1433 @user_name sysname = N'****' , @password nvarchar(128) = N'**********', @link_server_options xml = N' <rds_linked_server> <config option="data access">true</config> <config option="rpc">true</config> <config option="rpc out">true</config> </rds_linked_server> ' EXEC sp_rds_add_linked_server @linked_server_name, @data_source, @user_name, @password, @link_server_options ~~~ 如下圖：?  message(消息): The linked server ‘my_link_server’ has set option ‘data access’ to ‘true’. The linked server ‘my_link_server’ has set option ‘rpc’ to ‘true’. The linked server ‘my_link_server’ has set option ‘rpc out’ to ‘true’. create link server ‘my_link_server’ successfully. ## 七、SQL Server Profiler的跟蹤功能 RDS SQL Server 2012的 Profiler功能沒有變化，但是不能生成文件到服務器上，因為你不能登錄到RDS的實例服務器上。你可以放在表里。在使用SQL Server Profiler功能時，請注盡可能使用多的過濾條件和使用少量的列。 ## 八、Database Engine Tuning Advisor來調校性能 這個功能的使用方法沒有辦法，不過，絕大多數時，用戶不會使用這個功能。只是提供一種可選的方式。 ## 總結 RDS SQL Server在權限方面做了很多改變，能夠滿足絕大多數用戶的使用，性能調優和管理方面的需求。但是權限開放了，也需要用戶謹慎使用，比如數據庫層面，可以有更多設置了，設置不同的值會對數據庫的性能產生不同影響。