本文上接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 master
go
setuser
go
--創建測試數據庫
create database schTest
go
create login df with password='sj1234',default_database=schTest
create login xhl with password='sj1245',default_database=schTest
go
use schTest
go
-- 創建兩個用戶時沒有指定屬于哪個架構
create user df for login df
create user xhl for login xhl
-- 這個表沒指定屬于哪個架構屬于默認DBO 架構
go
create table tb1 (姓名 varchar(8),性別 char(2))
go
--這個表就屬于sch架構
create schema sch
go
create table sch.tb2(姓名 varchar(8),性別 char(2),年齡 int)
go
-- 賦予schTest這個用戶查詢sche架構中的對象的權限.
grant select on schema::sch to df
go
setuser 'df' --切換用戶df
select * from tb2
-- 此時報告"對象名無效" 是因為沒有指定tb2的架構,系統默認為dbo,而我們的tb2屬于sch架構.
--帶上架構名稱，就可以查詢了
go
select * from sch.tb2
go
setuser -- 切換到sa
---切換到xhl
setuser 'xhl'
--不能查詢,是因為沒有權限
select * from sch.tb2
go
setuser --切換sa
--給df用戶賦默認架構
alter user df with default_schema=sch
go
setuser 'df' --切換df
-- 此時不需要指定sch 也可以了,如果架構中還有其他對象，也可以查詢
select * from tb2
go
setuser --切換sa
--創建第三張測試表，同樣的是sch架構下
create table sch.tb3 (id int,uname varchar(8))
go
--切換用戶df
setuser 'df'
---可以進行查詢
select * from tb3
go
---但是無法進行數據插入，因為沒有插入權限
insert into tb3 values (1,'abcde') --拒絕了insert權限
go
setuser
--賦插入權限
grant insert on schema::sch to df
--切換用戶df
setuser 'df'
go
---可以進行數據插入
insert into tb3 values (1,'abcde') --OK!
---查詢結果
select * from tb3
GO
Grant alter on schema::sch to df -- 使schTest 這個用戶對所有的架構都有可更改的能力。
--錯誤
--無法對sa、dbo、實體所有者、information_schema、sys 或您自己授予、拒絕或撤消權限。
GO
Use master
go
Grant control server to df -- 使schTest這個用戶能夠控制服務器。
--錯誤
--無法對sa、dbo、實體所有者、information_schema、sys 或您自己授予、拒絕或撤消權限。
setuser
go
use schtest
go
--創建架構sch1
create schema sch1
go
-- 修改對象的架構tb2表的架構由sch 轉移到sch1
alter schema sch1 transfer sch.tb2
go
--創建一個新用戶，同時指定默認schema,默認屬于DBO
create login yhy with password='sj1234',default_database=master
GO
use schTest
GO
create user yhy for login yhy with default_schema=sch --屬于sch
--切換用戶yhy
setuser 'yhy'
--查詢表,對象名'tb2' 無效。
select * from tb2
go
setuser
--賦架構權限
grant select on schema::sch1 to yhy
--切換用戶yhy
setuser 'yhy'
go
select * from tb2 ---還是無效，因為不在同一個schema
go
setuser
go
alter user yhy with default_schema=sch1 --更改yhy的默認架構
GO
--由于yhy不是當前用戶，所以無法進行查詢
select * from tb2
--顯示當前用戶
GO
select user
GO
--切換用戶yhy
setuser 'yhy'
-- 此時就可以進行查詢了,如果架構中還有其他對象，也可以查詢
go
select * from tb2
--顯示當前用戶
select user
go
--切換用戶
Setuser
--切換用戶為df
setuser 'df'
-- 此時由于tb2的架構由sch變為了sch1，所以df就不可以查詢tb2了
go
--查詢報錯
select * from tb2
--顯示當前用戶
select user
go
setuser
go
use master
go
---刪除用戶
drop user df
drop user xhl
drop user yhy
---刪除登錄名
drop login df
drop login yhy
drop login xhl
--刪除數據庫
drop database schTest
文章列表
留言列表
