ユーザーとスキーマの分離

[アーティクル]
12/16/2008

更新 : 2006 年 12 月 12 日

SQL Server 2005 からは、各オブジェクトがデータベーススキーマに属するようになりました。データベーススキーマは、データベースユーザーとは別の、独立した名前空間です。スキーマはオブジェクトのコンテナと考えることができます。スキーマはデータベース内で作成したり変更することができ、ユーザーにはスキーマに対するアクセス権を与えることができます。どのユーザーもスキーマを所有でき、スキーマの所有権は譲渡できます。

メモ :
データベーススキーマは、XML スキーマとは異なるものです。XML スキーマの詳細については、「サーバー上の XML スキーマコレクションの管理」を参照してください。

データベースオブジェクトスキーマの作成の詳細については、「CREATE SCHEMA (Transact-SQL)」を参照してください。

新しい動作

SQL Server の以前のバージョンでは、データベースユーザーとデータベーススキーマは概念的に同じオブジェクトでした。SQL Server 2005 からは、ユーザーとスキーマは切り離され、スキーマはオブジェクトのコンテナとして機能するようになりました。

スキーマから所有権を切り離すと、重要な変化があります。次に示すように、データベーススキーマを使用すると、データベースオブジェクトのセキュリティをより柔軟に制御できます。

スキーマとスキーマ内のセキュリティ保護可能なリソースの権限を、以前のリリースより厳密に管理できます。詳細については、「GRANT (スキーマ権限の許可) (Transact-SQL)」および「GRANT (オブジェクトの権限の許可) (Transact-SQL)」を参照してください。
スキーマとスキーマスコープのセキュリティ保護可能なリソースの所有権を譲渡できます。詳細については、「ALTER AUTHORIZATION (Transact-SQL)」を参照してください。
スキーマ間でオブジェクトを移動できます。詳細については、「ALTER SCHEMA (Transact-SQL)」を参照してください。
1 つのスキーマに複数のデータベースユーザーが所有するオブジェクトを含めることができます。
複数のデータベースユーザーが 1 つの既定のスキーマを共有できます。
どのデータベースプリンシパルでも、1 つのスキーマを所有できます。このデータベースプリンシパルにはロールおよびアプリケーションロールが含まれます。
対応するスキーマ内のオブジェクトを削除しなくても、データベースユーザーを削除できます。

データベーススキーマを使用すると、以前のバージョンと比べて次のようなセキュリティ関連の変更もあります。

以前のリリースの SQL Server 用に記述されたコードでスキーマとデータベースユーザーとを同等であると見なしている場合、このコードは不適切な結果を返す場合があります。
以前のリリースの SQL Server 用にデザインされたカタログビューは、不適切な結果を返す場合があります。このようなカタログビューには sysobjects があります。
ユーザーが複数のスキーマを所有できるため、所有権の継承とユーザーコンテキストの切り替えの動作は変更されます。所有権の継承の詳細については、「所有権の継承」および「権限の階層」を参照してください。コンテキストの切り替えの詳細については、「コンテキストの切り替え」を参照してください。
SQL Server 2000 では、データベースオブジェクトはユーザーが所有するものでした。SQL Server 2000 では、データベースオブジェクトへの参照で指定する 4 つの要素は [DatabaseServer].[DatabaseName].[ObjectOwner].[DatabaseObject] でした。SQL Server 2005 からは、データベースオブジェクトへの参照で指定する 4 つの要素は [DatabaseServer].[DatabaseName].[DatabaseSchema].[DatabaseObject] になります。

オブジェクトの所有権の変更

次のオブジェクトの所有者プロパティは、ユーザーではなくスキーマを指すようになります。

CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER VIEW
CREATE INDEX
ALTER INDEX
CREATE FUNCTION
ALTER FUNCTION
DROP FUNCTION
VIEW_TABLE_USAGE
VIEW_COLUMN_USAGE
TABLE_CONSTRAINTS
REFERENTIAL_CONSTRAINTS
KEY_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
CONSTRAINT_COLUMN_USAGE
CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMNS
DOMAIN_CONSTRAINTS
ROUTINE_COLUMNS

列でユーザーメタデータとスキーマメタデータのどちらが返されるかの詳細については、後の「スキーマカタログビューと関数」を参照してください。

カタログビューと関数に置き換えられたシステムテーブル

SQL Server 2005 では 250 を超える新しいカタログビューが導入されました。そのいくつかは、データベースユーザーとスキーマオブジェクトを扱うもので、SQL Server 2000 システムテーブルの代わりに使用されます。新しいカタログビューを使用してメタデータにアクセスすることを強くお勧めします。詳細については、「カタログビュー (Transact-SQL)」を参照してください。

次の表に、SQL Server 2000 のシステムテーブルと、対応する SQL Server 2005 のカタログビューとのマッピングを示します。

SQL Server 2000 システムテーブル	SQL Server 2005 カタログビュー
sysusers	sys.database_principals (Transact-SQL) sys.schemas (Transact-SQL)
syslogins	sys.server_principals (Transact-SQL)

sysusers

sys.database_principals (Transact-SQL)

sys.schemas (Transact-SQL)

syslogins

sys.server_principals (Transact-SQL)

既定のスキーマ

セキュリティ保護可能なリソースの名前が完全修飾されていない場合、SQL Server 2000 ではこれを解決するため名前解決が使用され、呼び出し側のデータベースユーザーが所有するスキーマと dbo が所有するスキーマが照合されます。

SQL Server 2005 では、各ユーザーに既定のスキーマを割り当てることができます。既定のスキーマは、CREATE USER や ALTER USER の DEFAULT_SCHEMA オプションを使用して、設定および変更できます。DEFAULT_SCHEMA が定義されていない場合、SQL Server 2005 では dbo スキーマが既定のスキーマと見なされます。

メモ :
Windows 認証されたグループ経由で接続するユーザーには、既定のスキーマが関連付けられません。このようにスキーマを持たないユーザーがオブジェクトを作成すると、新しいスキーマが作成されます。名前は現在のユーザー名に設定され、このユーザー名が付けられた新しい名前空間内にテーブルオブジェクトが作成されます。

Windows 認証されたグループ経由で接続するユーザーには、既定のスキーマが関連付けられません。このようにスキーマを持たないユーザーがオブジェクトを作成すると、新しいスキーマが作成されます。名前は現在のユーザー名に設定され、このユーザー名が付けられた新しい名前空間内にテーブルオブジェクトが作成されます。

新しいデータ定義言語 (DDL) ステートメントによって、システムメタデータが複雑になり、sysobjects などの古いシステムテーブルにはこのデータが正確に反映されない可能性があります。次の例では、sysobjects によって返されるユーザー ID とスキーマ名は同期されていません。これは、SQL Server 2005 で導入されたユーザーとスキーマの区別を示すものです。

USE tempdb
GO
CREATE LOGIN u1 WITH PASSWORD = 'Mdfjd$sakj943857l7sdfh##30'
CREATE USER u1 WITH DEFAULT_SCHEMA = u1
GO
GRANT CREATE TABLE TO u1 
GO
CREATE SCHEMA sch1
GO
CREATE SCHEMA u1 AUTHORIZATION u1
GO
EXECUTE AS USER = 'u1'
GO
CREATE TABLE t1(c1 int)
GO
REVERT
GO
SELECT user_name(uid) , * FROM sysobjects WHERE name = 't1'
GO

注意 :
次のいずれかの DDL ステートメントが使用されたデータベースでは、新しいカタログビューを使用する必要があります。CREATE/ALTER/DROP SCHEMA、CREATE/ALTER/DROP USER、CREATE/ALTER/DROP ROLE、CREATE/ALTER/DROP APPROLE、ALTER AUTHORIZATION。

スキーマカタログビューと関数

SQL Server 2005 からは、スキーマはメタデータに反映される明確なエンティティになりました。この結果として、スキーマの所有者には 1 ユーザーだけが許可される一方、1 ユーザーが 1 つ以上のスキーマを所有できます。この複雑なリレーションシップは、SQL Server 2000 のシステムテーブルには反映されないため、SQL Server 2005 では、新しいメタデータを正確に反映する新しいカタログビューが導入されています。

次の表に、SQL Server 2005 のスキーマのカタログビュー、メタデータ、関数を示します。

項目	参照先
一般的なスキーマメタデータ	sys.schemas (Transact-SQL)
情報スキーマビュー	情報スキーマビュー (Transact-SQL)
INFORMATION_SCHEMA.SCHEMATA ビューにより返される列の定義	SCHEMATA (Transact-SQL)

例

A. スキーマを作成しユーザーに所有権を付与する

次の例では、Marjorie という SQL Server ログインおよびユーザーと、Auditing という新しいスキーマを AdventureWorks データベースに追加し、Marjorie を Auditing スキーマの所有者として割り当てます。

CREATE LOGIN Marjorie
    WITH PASSWORD = '8fdKJl3$nlNv3049jsKK';
USE AdventureWorks;
CREATE USER Marjorie FOR LOGIN Marjorie
GO
CREATE SCHEMA Auditing AUTHORIZATION Marjorie;
GO

B. ユーザーに別のスキーマに対する権限を付与する

次の例では、Marjorie というユーザーに、AdventureWorks データベースの Purchasing スキーマに対する SELECT 権限を与えます。

USE AdventureWorks;
GO
GRANT SELECT ON SCHEMA::Purchasing TO Marjorie;
GO

C. スキーマの所有権を変更する

次の例では、新しいユーザー Jon を AdventureWorks データベースに作成し、Jon に AdventureWorks データベースの Auditing スキーマの所有権を与えます。その後、Marjorie というユーザーを AdventureWorks データベースから削除します。

USE AdventureWorks;
GO
/* Create a new user in the database */
CREATE LOGIN Jon
    WITH PASSWORD = '1fdKJl3$nlNv3049jsBB';
USE AdventureWorks;
CREATE USER Jon FOR LOGIN Jon
GO
ALTER AUTHORIZATION ON SCHEMA::Auditing TO Jon;
GO
/* Removes the user from the system */
DROP LOGIN Marjorie;
GO
DROP USER Marjorie;
GO

D. スキーマの所有権を表示する

次の例では、AdventureWorks データベースの Auditing スキーマの所有者を表示します。

USE AdventureWorks;
GO
/* This method uses the INFORMATION_SCHEMA views */
SELECT * 
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'Auditing';
GO

/* This method uses the sys.schemas catalog and links
   the names of the database users and server logins */
SELECT s.name AS 'Schema Name'
, db.name AS 'Database User Name'
, svr.name AS 'SQL Server Login Name'
FROM sys.schemas s
/* Obtains the name of the database user */
   INNER JOIN sys.database_principals db
      ON s.principal_id = db.principal_id
/* Obtains the name of the server login */
      INNER JOIN sys.server_principals svr
         ON db.sid = svr.sid
WHERE s.name = 'Auditing'
ORDER BY s.name

参照

変更履歴

リリース	履歴
2006 年 12 月 12 日	変更内容 : 新しい例、スキーマの使用に関する追加情報、新しいリンクを追加しました。
2006 年 7 月 17 日	変更内容 : トピックの内容を大幅に再編成しました。

ユーザーとスキーマの分離

新しい動作

オブジェクトの所有権の変更

カタログ ビューと関数に置き換えられたシステム テーブル

既定のスキーマ

スキーマ カタログ ビューと関数

例