SQL SERVER 2005/2008 中關於架構的理解(二)

標籤：

本文上接SQL SERVER 2005/2008 中關於架構的理解(一)     

 

架構的作用與樣本

      使用者與架構（schema）分開，讓資料庫內各對象不再綁在某個使用者帳號上，可以解決SQL SERVER 2000及以前版本中“使用者離開公司＂問題，也就是在擁有該對象的使用者離開公司，或離開該職務時，不必要大費周章地更改該使用者所有的對象屬於新的使用者所有。另外，也可讓 DBA 在安裝某個套裝軟體時，設定該套裝軟體所用的資料庫物件都屬於某個特定的架構，容易區別。也就是說，在單一資料庫內，不同部門或目的的對象，可以通過架構區分不同的對象命名原則與許可權。

       在 SQL Server 2005 /2008中，架構獨立於建立它們的資料庫使用者而存在。可以在不更改架構名稱的情況下轉讓架構的所有權。並且可以在架構中建立具有方便使用名稱的對象，明確指示對象的功能。例如，除了 cus.app.entry.customEntry 外，您還可以建立名為 cus.app.manifest.customEntry 的架構。因為“manifest”不是使用者，所以從資料庫中刪除使用者後，無需更改此名稱。這就簡化了資料庫管理員和開發人員的工作。

       SQL Server 2005/2008 還引入了“預設架構”的概念，用於解析未使用其完全限定名稱引用的對象的名稱。在 SQL Server 2000 中，首先檢查的是調用資料庫使用者所擁有的架構，然後是 DBO 擁有的架構。在 SQL Server 2005 /2008中，每個使用者都有一個預設架構，用於指定伺服器在解析對象的名稱時將要搜尋的第一個架構。可以使用 CREATE USER 和 ALTER USER 的 DEFAULT_SCHEMA 選項設定和更改預設架構。如果未定義 DEFAULT_SCHEMA，則資料庫使用者將把 DBO 作為其預設架構。

       下面的顯示SQL Server許可權階層的圖可能會給我們一個直觀的認識：

 

       SQL Server 2005/2008 Database Engine 管理著可以通過許可權進行保護的實體的分層集合。這些實體稱為“安全性實體”。在安全性實體中，最突出的是伺服器和資料庫，但可以在更細的層級上設定離散許可權。SQL Server 通過驗證主體是否已獲得適當的許可權來控制主體對安全性實體執行的操作。

 

安全性實體關係如：

 

下面舉個具體的樣本來說明以一下架構的作用。

 

--命令對架構進行操作use mastergosetusergo--建立測試資料庫create database schTestgo create login df with password=‘sj1234‘,default_database=schTestcreate login xhl with password=‘sj1245‘,default_database=schTestgouse schTestgo -- 建立兩個使用者時沒有指定屬於哪個架構create user df for login df create user xhl for login xhl-- 這個表沒指定屬於哪個架構屬於預設DBO 架構gocreate table tb1 (姓名 varchar(8),性別 char(2)) go--這個表就屬於sch架構create schema schgocreate table sch.tb2(姓名 varchar(8),性別 char(2),年齡 int)go-- 賦予schTest這個使用者查詢sche架構中的對象的許可權. grant select on schema::sch to df gosetuser ‘df‘  --切換使用者df select * from  tb2-- 此時報告"對象名無效" 是因為沒有指定tb2的架構,系統預設為dbo,而我們的tb2屬於sch架構. --帶上架構名稱，就可以查詢了goselect * from sch.tb2gosetuser  -- 切換到sa---切換到xhlsetuser ‘xhl‘ --不能查詢,是因為沒有許可權 select * from sch.tb2 gosetuser --切換sa--給df使用者賦預設架構alter user df with default_schema=schgosetuser ‘df‘  --切換df-- 此時不需要指定sch 也可以了,如果架構中還有其他對象，也可以查詢select * from tb2 gosetuser  --切換sa--建立第三張測試表，同樣的是sch架構下create table sch.tb3 (id int,uname varchar(8))go--切換使用者dfsetuser ‘df‘---可以進行查詢select * from tb3go---但是無法進行資料插入，因為沒有插入許可權insert into tb3 values (1,‘abcde‘)   --拒絕了insert許可權gosetuser--賦插入許可權grant  insert on schema::sch to df--切換使用者dfsetuser ‘df‘go---可以進行資料插入insert into tb3 values (1,‘abcde‘)   --OK! ---查詢結果select * from tb3GOGrant alter on schema::sch to df --  使schTest 這個使用者對所有的架構都有可更改的能力。--錯誤--無法對sa、dbo、實體所有者、information_schema、sys 或您自己授予、拒絕或撤消許可權。GOUse master goGrant control server to df  -- 使schTest這個使用者能夠控制伺服器。--錯誤--無法對sa、dbo、實體所有者、information_schema、sys 或您自己授予、拒絕或撤消許可權。setusergouse schtestgo--建立架構sch1create schema sch1 go-- 修改對象的架構tb2表的架構由sch 轉移到sch1alter schema sch1 transfer sch.tb2go--建立一個新使用者，同時指定預設schema,預設屬於DBO create login yhy with password=‘sj1234‘,default_database=masterGOuse schTestGOcreate user yhy for login yhy with default_schema=sch --屬於sch--切換使用者yhysetuser ‘yhy‘--查詢表,對象名‘tb2‘ 無效。select * from tb2gosetuser--賦架構許可權grant select on schema::sch1 to yhy--切換使用者yhysetuser ‘yhy‘goselect * from tb2   ---還是無效，因為不在同一個schemagosetusergoalter user yhy with default_schema=sch1   --更改yhy的預設架構GO--由於yhy不是目前使用者，所以無法進行查詢select * from tb2  --顯示目前使用者GOselect userGO--切換使用者yhysetuser ‘yhy‘-- 此時就可以進行查詢了,如果架構中還有其他對象，也可以查詢goselect * from tb2  --顯示目前使用者select usergo--切換使用者Setuser--切換使用者為dfsetuser ‘df‘-- 此時由於tb2的架構由sch變為了sch1，所以df就不可以查詢tb2了go--查詢報錯select * from tb2  --顯示目前使用者select user gosetusergouse mastergo---刪除使用者drop user dfdrop user xhldrop user yhy---刪除登入名稱drop login dfdrop login yhydrop login xhl--刪除資料庫drop database schTest  

 

SQL SERVER 2005/2008 中關於架構的理解(二)