[SQL] Schema

利用下述的範例來說明使用者預設 Schema 和撰寫 T-SQL 時，明確指定 Schema 的重要性

基礎建設

-- 在 Person 和 dbo 兩個 Schema 內，建立 SchemaDemo Table
-- HumanResources Schema 內，則是故意不建立 SchemaDemo Table
USE [AdventureWorks2012]
GO
 
IF OBJECT_ID('Person.SchemaDemo') IS NOT NULL
    DROP TABLE [Person].[SchemaDemo]
 
CREATE TABLE [Person].[SchemaDemo] (descript nchar(100))
INSERT INTO [Person].[SchemaDemo] (descript)
    VALUES(N'Schema 為 Person 的 SchemaDemo Table')
GO
 
IF OBJECT_ID('dbo.SchemaDemo') IS NOT NULL
    DROP TABLE [dbo].[SchemaDemo]
 
CREATE TABLE [dbo].[SchemaDemo] (descript nchar(100))
INSERT INTO [dbo].[SchemaDemo] (descript)
    VALUES(N'Schema 為 dbo 的 SchemaDemo Table')
GO
 
-- 建立測試 UserPerson 和 UserHumanResources，故意建立跟預設 Schema 同名的 User，方便辨識
USE [master]
GO
 
IF EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = 'UserPerson')
    DROP LOGIN UserPerson
IF EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = 'UserHumanResources')
    DROP LOGIN UserHumanResources
 
CREATE LOGIN [UserPerson]
    WITH PASSWORD = N'P@ssw0rd' , DEFAULT_DATABASE = [AdventureWorks2012]
 
CREATE LOGIN [UserHumanResources]
    WITH PASSWORD = N'P@ssw0rd' , DEFAULT_DATABASE = [AdventureWorks2012]
 
USE [AdventureWorks2012]
GO
 
IF EXISTS(SELECT 1 FROM sys.database_principals WHERE name = 'UserPerson')
    DROP USER UserPerson
 
CREATE USER [UserPerson] FOR LOGIN [UserPerson] WITH DEFAULT_SCHEMA = Person;
GO
 
IF EXISTS(SELECT 1 FROM sys.database_principals WHERE name = 'UserHumanResources')
    DROP USER UserHumanResources
 
CREATE USER [UserHumanResources] FOR LOGIN [UserHumanResources] WITH DEFAULT_SCHEMA = HumanResources;
GO
 
-- 授予存取 Table 權限
GRANT SELECT ON [Person].[SchemaDemo] TO [UserPerson]
GRANT SELECT ON [dbo].[SchemaDemo] TO [UserPerson]
GRANT SELECT ON [dbo].[SchemaDemo] TO [UserHumanResources]
GO
 
-- 確認使用者
SELECT
    Name,
    type_desc,
    default_database_name
FROM sys.sql_logins
WHERE name IN ('UserPerson','UserHumanResources')

-- 確認 User 的 預設 Schema
SELECT
    name,
    type_desc,
    default_schema_name
FROM sys.database_principals
WHERE default_schema_name IS NOT NULL

進行 Demo1

-- 切換至 UserPerson 使用者
SELECT USER_NAME() AS [原使用者]
EXECUTE AS USER = 'UserPerson'
SELECT USER_NAME() AS [切換後使用者]
 
-- UserPerson 使用者預設 Schema 為 Person，
-- TSQL 中沒有明確地指定，會優先搜尋 Person.Schema
SELECT * FROM SchemaDemo
-- 明確指定 Schema
SELECT * FROM dbo.SchemaDemo
 
-- 取消身分變更
REVERT

Demo2

-- 切換至 UserPerson 使用者
SELECT USER_NAME() AS [原使用者]
EXECUTE AS USER = 'UserHumanResources'
SELECT USER_NAME() AS [切換後使用者]
 
-- UserHumanResources 使用者預設 Schema 為 HumanResources
-- 但是 HumanResources 內並沒有 SchemaDemo Table 存在，所以
-- 會改搜尋 dbo.SchemaDemo Table
SELECT * FROM SchemaDemo
  
REVERT

根據使用者預設 Schema  來進行 Table 的搜尋在效能上是感覺不到，但明確地指定 Schema，在開發系統時才能更容易管理物件

參考資料
• 【SQL 新手達人】保護資料、抵禦攻擊--以 SQL Server 2005 安全機制為例
• SQL Server Performance Tuning 效能調校 P556
• Super SQL Server 討論
• 技術論壇討論