Sql Server來龍去脈系列 必須知道的許可權控制基礎篇

標籤：庫檔案   off   中繼資料   read_only   [1]   線上   基礎   文法   資料庫管理   

題外話：最近看到各種吐槽.NET怎麼落寞、.NET怎麼不行了、.NET工資低的文章。我也吐槽一句：一個程式猿的自身價值不是由他選擇了哪一門技術來決定，而是由他自身能創造出什麼價值來決定。

    在進入本篇內容之前，這裡有幾個問題：

    1.一般程式猿都知道怎樣建立、修改、登入帳號，但知不知道登陸帳號儲存在哪個表或者視圖？

    2.資料庫中其實存在登入帳號和使用者兩個概念，你能解釋清楚這兩個概念嗎？

    3.對於一個登入帳號，我們可以為他設定哪些許可權？

    4.你清不清楚資料庫資訊儲存在哪些表或試圖？

    5.我們可以給登入帳號設定許可權，但清不清楚具體有哪兩種許可權？

   

    如果你回答不上，那接下來的內容有必要知道。如果都能很清楚的回答出來，接下來的內容你可以直接忽略。

    前一段時間寫了Sql Server來龍去脈系列的前四篇，包括資料庫的架構和配置、查詢過程跟蹤、資料庫庫和檔案。說實話， 當時寫的時候自己都是一知半解，有些內容瞭解得不是很清楚。近來我一直沒有更新系列的後續內容，而是把時間用來寫一個小的資料庫許可權管理系統，為得就是把前面寫的系列隨筆涉及到的知識鞏固下。系統的主介面如：

    介面上的導覽功能表也就是這篇隨筆的主要內容，包括登陸帳號、資料庫、許可權控制。許可權控制包括了服務許可權和資料庫許可權。需要說明的是：介面上的說的使用者實際是登陸帳號，而資料庫使用者和帳號是有區別的。

    像登入帳號、資料庫以及許可權等系統中繼資料一般是不允許直接操作的，那麼我們怎樣讀取以及怎樣修改更新這些中繼資料？Sql Server為開發人員提供了系統檢視表、預存程序、DDL（資料定義語言 (Data Definition Language)）用來讀取以及更改系統中繼資料。DDL全稱是Data Definition Laguage，它的文法包括我們經常用到的CREATE、ALTER、DROP等。開發人員在MSDN上也能找到對應的線上說明。下面分別給出協助連結：

    1.系統檢視表：https://msdn.microsoft.com/zh-cn/library/ms177862(v=sql.120).aspx，查看中繼資料一般都是通過目錄檢視尋找。

    2.預存程序：https://msdn.microsoft.com/zh-cn/library/ms187961(v=sql.120).aspx

    3.DDL：https://msdn.microsoft.com/zh-cn/library/ff848799(v=sql.120).aspx

    接下來我們分別介紹前面說的登入帳號管理、資料庫管理以及許可權管理。

登入帳號管理

帳號查詢

    管理維護Sql Server資料庫，一般都是使用Sql Server Management Studio。當我們成功連結資料庫執行個體後，可通過Security/Logins管理登入帳號。如：

    這裡有個疑問：這些帳號資訊是從哪裡查出來的？之前我們介紹了系統中繼資料可通過系統檢視表查詢。登入帳號的目錄檢視是sys.syslogins，我們可通過下面的查詢語句查詢登入帳號資訊：

select * from sys.syslogins， 欄位說明：https://msdn.microsoft.com/zh-cn/library/ms178593(v=sql.120).aspx

    執行結果如下：

    返回的結果包括了name、dbname、password、language，這幾個欄位是經常涉及到的，每行資料不止上面這些欄位，還包括服務許可權欄位，如下：

    至於這些欄位有什麼用，後面介紹許可權時再說明。在我自己寫的資料管理系統中就是通過從sys.syslogins查詢資料。系統介面如下：

建立帳號

    知道資料怎麼查詢後，我們繼續看怎樣建立登入帳號，就是建立登入帳號的介面：

    介面包括了登入名稱、密碼、預設資料庫、預設語言，登陸名和密碼操作者自己輸入，但預設資料庫和預設語言只能選擇資料庫存在的。所有我們必須得知道怎樣查詢資料庫表和語言表，這裡就提出了 另外兩個目錄檢視sys.databases、sys.syslanguages。通過名字也能知道這兩個表分別儲存的是資料庫和語言的中繼資料。執行下面語句查詢資料庫中存在哪些資料庫：

select name, database_id, owner_sid, create_date from sys.databases，欄位說明：https://msdn.microsoft.com/zh-cn/library/ms178534(v=sql.120).aspx

    結果如下：

    結果中包含owner_sid欄位，這個欄位就是儲存的登入帳號的系統ID，一般是哪個登入帳號建立的資料庫，這個資料庫就屬於這個建立它的登陸帳號。執行下面語句：

select db.name, db.database_id, lg.name, db.create_date from sys.databases db inner join sys.syslogins lg on db.owner_sid = lg.sid

    結果如下：

    通過結果一目瞭然的看出每個資料庫的所屬者。記得在初始化系統資料庫指令碼時，一般都會判斷資料庫是否存在。現在我們知道可以通過sys.databases視圖查看資料庫資訊，那麼我們也通過以下語句判斷一個資料庫是否存在：

if exists (select * from sys.databases where name = ‘資料庫名‘)     drop database [資料庫名]

    sys.syslanguages的資料比較簡單，這裡就不再說明。可通過https://msdn.microsoft.com/zh-cn/library/ms190303(v=sql.120).aspx查看欄位解釋。現在再回到之前的建立介面，資料庫和語言我們都能提供出來了，登入名稱和密碼也輸入了。但怎麼把資料插入到資料庫？這裡就提到前面說的資料定義語言 (Data Definition Language)DDL，用CREATE LOGIN關鍵字建立登入帳號。先看下在系統中我拼湊的SQL語句：

string sql = string.Format("CREATE LOGIN [{3}] WITH PASSWORD = N‘{0}‘, DEFAULT_DATABASE =[{1}], DEFAULT_LANGUAGE =[{2}]" , login.Password, login.DbName, login.Laguage, login.Name);

   語句很簡單，PASSWORD設定密碼，DEFAULT_DATABASE設定預設資料庫，DEFAULT_LANGUAGE設定預設語言。當然建立登入帳號的參數肯定不止這些，我們可通過https://msdn.microsoft.com/zh-cn/library/ms189751(v=sql.120).aspx查看建立登入帳號所有的參數。

修改帳號

   現在已經知道怎樣建立登入帳號了，但有些時候我們需要修改登入帳號的某些資訊，例如密碼、預設資料庫。修改登入帳號使用DDL語言的ALTER LOGIN關鍵字。修改語句和建立語句極其相似，只是把CREAT關鍵字改成了ALTER。看看下面的系統修改登入帳號代碼：

string sql = string.Format("ALTER LOGIN {0} WITH PASSWORD = N‘{1}‘, DEFAULT_DATABASE =[{2}],DEFAULT_LANGUAGE =[{3}]", name, login.Password, login.DbName, login.Laguage)//參考：https://msdn.microsoft.com/zh-cn/library/ms189828(v=sql.120).aspx

刪除帳號

    最後還剩下刪除操作，我們知道刪除表的語句一般是 DROP TABLE [表名]，而刪除登陸帳號也相似，執行：

DROP LOGIN [登入名稱]， https://msdn.microsoft.com/zh-cn/library/ms188012(v=sql.120).aspx

    知道了上面這些內容，我們就可以很容易理解怎樣增刪改查登入帳號了。但一個登入帳號不是單獨的存在資料庫中，它還關聯了服務許可權、資料庫許可權等。

資料庫管理

查詢資料庫

    資料庫同樣也涉及到增刪改查，首先看看怎樣查詢資料庫。在上一節我們知道資料庫可以從sys.databases目錄檢視中查看，先看看許可權系統的資料庫查詢介面，如所示：

    通過列表我們能看到資料庫名稱、資料庫擁有者、建立時間以及檔案路徑。然後我們執行語句:

select * from sys.databases

    執行結果如下：

    分析查詢結果，我們單從sys.databases視圖中是看不到資料庫擁有者的名字以及資料庫檔案路徑。但上一節我們講了登入帳號，我們知道它也有一個sid，所有可以通過sys.databases中的owner_sid和sys.logins關聯。但資料庫檔案從哪裡查詢？這裡又提出了另外一個目錄檢視sys.master_files，它包含了資料庫檔案的資訊。先執行下面語句：

select * from sys.master_files，https://msdn.microsoft.com/zh-cn/library/ms186782(v=sql.120).aspx

    執行結果如下：

    從查詢結果可以看出，每個資料庫基本上包含兩行資料。其中，type欄位分別為ROWS、LOG，表示資料和記錄檔。結果欄位中還有一個file_id，它關聯了另外一張資料庫檔案視圖sys.database_files。sys.database_files儲存了資料庫檔案的詳細資料，包括檔案類型、檔案大小、檔案最大值、檔案增長值。執行下面語句：

select * from sys.database_files，視圖說明：https://msdn.microsoft.com/zh-cn/library/ms174397(v=sql.120).aspx

    查詢結果如下：

    這裡有個問題：為什麼查詢結果只返回了兩條資料？這是因為我當前選擇的資料是master，sys.database_files只返回當前資料庫的檔案資訊。現在資料庫資訊都知道了.另外，還有一個相容資料庫檢視sys.sysdatabases（視圖說明：https://msdn.microsoft.com/zh-cn/library/ms179900(v=sql.120).aspx）。它和sys.databases差不多，但能查詢出檔案的實體路徑。sys.sysdatabases是sql server 2000中的系統檢視表，現在已經不建議使用了。以上幾個資料庫的關聯關係如下：

    視圖表已經有了，可直接通過多表關聯查詢出許可權管理展示的結果。關聯的查詢語句如下：

select db.name as Name, db.database_id as DbId, db.create_date as CrTime, db.owner_sid as OwnerId, lg.name as OwnerName, mf.physical_name  as FileName     from sys.databases db    left join sys.syslogins lg on db.owner_sid = lg.sid    left join sys.master_files mf on db.database_id = mf.database_id and mf.type = 0

    執行結果如下：

建立資料庫

    作為一個開發人員，肯定會經常涉及到建立資料庫。那麼怎樣建立資料庫以及建立資料庫需要哪些參數？通過建立登入帳號（CREATE LOGINS ）語句，我們 可以推理建立資料庫也是使用資料定義語句CREATE DATABASE來建立資料。我們先看看許可權管理資料庫的查詢介面：

    包含了資料庫名稱、資料庫擁有者、初始化大小、增長方式、以及檔案的最大值。當然，還必須有資料庫檔案資訊，我直接在後台把檔案路徑寫死了，和系統資料庫同目錄。資料庫名稱由我們自己定義，資料庫擁有者可通過sys.syslogins查詢選擇，初始值大小由我們自己設定，增長方式包括按照固定值大小或者按照百分比增長、檔案最大值包括無限制或者設定最大值、資料庫檔案路徑配置為磁碟路徑。

    參數已經瞭解，接下來分析具體的建立語句，我們可以從msdn上查看到完整的資料庫建立語句:

Create a databaseCREATE DATABASE database_name [ CONTAINMENT = { NONE | PARTIAL } ][ ON       [ PRIMARY ] <filespec> [ ,...n ]       [ , <filegroup> [ ,...n ] ]       [ LOG ON <filespec> [ ,...n ] ] ] [ COLLATE collation_name ][ WITH  <option> [,...n ] ][;]<option> ::={      FILESTREAM ( <filestream_option> [,...n ] )    | DEFAULT_FULLTEXT_LANGUAGE = { lcid | language_name | language_alias }    | DEFAULT_LANGUAGE = { lcid | language_name | language_alias }    | NESTED_TRIGGERS = { OFF | ON }    | TRANSFORM_NOISE_WORDS = { OFF | ON}    | TWO_DIGIT_YEAR_CUTOFF = <two_digit_year_cutoff>     | DB_CHAINING { OFF | ON }    | TRUSTWORTHY { OFF | ON }}<filestream_option> ::={      NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }    | DIRECTORY_NAME = ‘directory_name‘ }<filespec> ::= {(    NAME = logical_file_name ,    FILENAME = { ‘os_file_name‘ | ‘filestream_path‘ }     [ , SIZE = size [ KB | MB | GB | TB ] ]     [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]     [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ])}<filegroup> ::= {FILEGROUP filegroup name [ [ CONTAINS FILESTREAM ] [ DEFAULT ] | CONTAINS MEMORY_OPTIMIZED_DATA ]    <filespec> [ ,...n ]}<service_broker_option> ::={    ENABLE_BROKER  | NEW_BROKER  | ERROR_BROKER_CONVERSATIONS}

    具體參數請參考：https://msdn.microsoft.com/zh-cn/library/ms176061(v=sql.120).aspx

    現在我們通過許可權管理系統建立資料庫，在建立介面輸入資料庫參數，如所示：

    點擊“建立”按鈕後， 伺服器自動產生資料庫建立SQL語句 ，如下：

USE master;CREATE DATABASE HeaviDb ON(Name = HeaviDb,FILENAME = ‘D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\HeaviDb.mdf‘,SIZE = 10,FILEGROWTH = 2MB,MAXSIZE = 500)

    在建立資料庫時，我們一般要把當前資料庫切換到master資料庫。上面的語句只指定了FILENAME，而沒有指定log的FILENAME，這種情況SQL SERVER會自動在FILENAME同目錄下為我們自動建立HeaviDb_log.ldf檔案。SIZE設定為10，這裡沒有單位，預設單位為MB，也可以顯示指定KB、MB、GB、TB尾碼，例如SIZE = 10MB或者SIZE = 10GB。FILEGROWTH設定為2MB，即資料庫按照2MB自增長，另外我們也可以按照百分比增長，例如FILEGROWTH = 10%。MAXSIZE設定為500，預設單位也是MB。如果我們不限制資料庫檔案大小，可直接用UNLIMITED代替MAXSIZE = 500。執行以上SQL語句，我們就能在資料庫中查看到該資料庫了。

修改資料庫

    這一節討論怎樣修改資料庫，但這裡會涉及到比較核心的知識點。在許可權管理系統中，選中資料庫列表某一列，點擊“修改”按鈕，彈出修改資料庫介面。但這裡有一個問題，在資料庫列表中只包含了 資料庫名稱、檔案以及資料庫擁有者資訊。而我們修改介面展示了資料庫初始大小以及自增長值。這些資訊在列表中是沒有的。但通過資料庫查詢我們知道這些資訊儲存在sys.master_files中。執行查詢語句：

select mf.database_id as DbId, mf.name as Name, mf.size as InitSize, mf.max_size as MaxSize, mf.growth as Growth, mf.is_percent_growth as IsPercentGrowth from sys.master_files mf where mf.type = 0

    查詢結果如下：

    查詢的資料結果我們不能直接使用，資料庫大小InitSize、資料庫最大值MaxSize、自增長值Growth這些資料不能直接展示在介面，那這些資料到底是什麼意思呢？先看看msdn對這幾個欄位的描述：

size	int	當前檔案大小（以 8 KB 為單位的頁數）。 對於資料庫快照集來說，size 表示該快照可以一直用於檔案的最大空間。
max_size	int	最大檔案大小（以 8 KB 為單位的頁數）： 0 = 不允許增長。 -1 = 檔案將一直增長到磁碟充滿為止。 268435456 = 記錄檔將增長到最大大小 2 TB。
growth	int	0 = 檔案大小固定，不會增長。 >0 = 檔案將自動成長。 如果 is_percent_growth = 0，則以若干個 8 KB 頁為增量遞增，舍入為 64 KB 如果 is_percent_growth = 1，增量將用整數百分比表示。
is_percent_growth	bit	1 = 檔案的增長以百分比表示。 0 = 以頁數為單位表示絕對增長大小。

    分析幾個欄位：

    1.size：沒有直接隱藏檔大小，而是隱藏檔的總頁數，每一頁大小為8KB。以MB為size單位，size的實際值則應該為：（size * 8 /1024）MB；

    2. max_size：有三種值，一是等於-1，二是等於0，另一種是大於0 。等於-1表示UNLIMITED（沒有限制）。等於0表示不允許增長。大於0時和size相似，以MB為max_size單位，max_size實際值則應該為：（max_size * 8 /1024）MB；

    3.is_percent_growth：值等於0或者1，等於1表示按百分比自增長。等於0表示按固定值增長;

    4.growth：等於0表示固定大小，不會自增增長。大於0時依賴於is_percent_growth的值。如果is_percent_growth=0，growth按固定數字自增長。以MB為單位，growth實際值為（growth * 8 /1024）MB。如果is_percent_growth=1，則growth的實際值為growth%；

    以上欄位瞭解清楚後，我們的資料庫檔案參數就能顯示出來了。例如，修改剛才建立的HeaviDb資料庫，彈出修改介面如下：

   修改初始值為20，增長方式為pecent並且值為10，檔案最大值沒有限制。這裡需要說明幾點：

    1.修改的初始值不能小於當前資料庫檔案的實際大小；

    2.如果檔案最大值限制為固定值，那麼這個固定值不能小於當前資料庫大小以及修改後的資料庫大小（初始值）;

    修改參數後介面如下：

    點擊“儲存”按鈕，後台產生的SQL語句如下：

USE master;ALTER DATABASE HeaviDb MODIFY FILE (Name = HeaviDb,SIZE = 20,FILEGROWTH = 20,MAXSIZE = UNLIMITED)

    修改資料庫使用DDL的ALTER DATABASE語句，修改檔案使用MODIFY FILE語句。完整的資料庫修改SQL請查看：

https://msdn.microsoft.com/zh-cn/library/ms174269(v=sql.120).aspx。

刪除資料庫

    資料庫的刪除語句比較簡單，SQL與語句如下：

USE master;DROP DATABASE HeaviDb

    刪除資料庫必須知道：系統資料庫是不能刪除的；不能刪除正在使用的資料庫。

總結

    本篇隨筆主要介紹了登入帳號的查詢、建立、修改、刪除，以及資料庫的查詢、建立、修改、刪除所涉及的系統檢視表以及DDL語句。通過這篇內容我們能夠比較清楚的瞭解登入帳號以及資料庫在SQL SERVER中的儲存中繼資料結構。本篇的內容都是一些基礎知識，但同時也是比較重要的基礎知識。下一篇將會詳細分析資料庫中登入帳號和資料庫的使用權限設定。

Sql Server來龍去脈系列 必須知道的許可權控制基礎篇