アプリのエンドユーザー TM1_User に、アプリ用スキーマ TM1App のビューだけを見せ、ソース側の TMS1 テーブルやビューは直接触らせたくない——この要件は SQL Server／Azure SQL でも頻出です。ありがちな「TMS1 に DENY」を選ぶと、依存ビュー経由の参照が壊れることがあります。この記事では DENY を使わず、所有権チェーンを活かしてスキーマ単位で確実に閲覧権限を設計・実装・検証する方法を詳説します。
目次
シナリオの整理
データベース TM1 には次のオブジェクトが存在します。
- TMS1 スキーマ … テーブル
TMS1.Table1、ビューTMS1.View1～View5 - TM1App スキーマ … ビュー
TM1App.View6～View10（中身はTMS1.View*を参照）
エンドユーザー用ログイン TM1_User には TM1App 内のオブジェクトだけを「読める」ようにし、TMS1 のオブジェクトは直接読ませないのがゴールです。
結論（先に全体像）
核心はシンプルです。
- DENY を使わない。 DENY は最優先で評価されるため、設定場所によってはビュー経由の参照すらブロックします。
- ユーザーはデータベースロール
publicのみ。 便利だからとdb_datareaderを付けると全テーブルが読めてしまいます。 TM1AppスキーマにだけSELECTを GRANT。 スキーマ単位の GRANT で既存・将来のオブジェクトを一括許可します。TMS1には GRANT も DENY もしない。 同一所有者（通常dbo）なら所有権チェーンが効き、TM1App.View6 → TMS1.View1 → TMS1.Table1の参照に追加権限は不要です。
なぜ DENY は使わないのか
SQL Server は「許可より拒否が強い」評価規則を持ちます。特に以下のケースは要注意です。
- スキーマ単位の DENY（例：
DENY SELECT ON SCHEMA::TMS1 TO TM1_User） … 所有者が異なると所有権チェーンが切れ、下位オブジェクトに対して個別チェックが走るため、DENY が効いてビュー経由も失敗します。 db_denydatareaderへの追加 … データベース全体の SELECT を否認するため、ビュー自体の SELECT も否認されます。
逆に、同一所有者かつ 所有権チェーンが有効な場合、下位のテーブル・ビューへの個別チェックはスキップされるため、下位オブジェクトに設定した DENY は参照されません。ただし、スキーマ所有者が異なるとチェーンが切れるため、そこで DENY が効果を持ってしまう点が落とし穴です。
実装手順（そのまま使えるスクリプト付き）
前提：所有者（オーナー）をそろえる
所有権チェーンは「同一データベース内のオブジェクトで所有者が一致」していることが条件です。まずは所有者が一致しているか確認します。
-- 所有者の確認
SELECT s.name AS schema_name, dp.name AS owner
FROM sys.schemas s
JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
WHERE s.name IN ('TMS1','TM1App');
もし TMS1 と TM1App の所有者が違えば、以下で合わせます（例：両方とも dbo に統一）。
ALTER AUTHORIZATION ON SCHEMA::TMS1 TO dbo;
ALTER AUTHORIZATION ON SCHEMA::TM1App TO dbo;
1) ログインとユーザーの作成（オンプレミス SQL Server）
USE [master];
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'TM1Login')
CREATE LOGIN TM1Login WITH PASSWORD = 'StrongPw123!', CHECK_POLICY = ON;
USE [TM1];
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'TM1_User')
CREATE USER TM1_User FOR LOGIN TM1Login;
-- ロールは既定の public のまま（余計なロールは付けない） （参考）Azure SQL Database / Contained DB の場合
-- マスターではなくターゲット DB 上で実施
USE [TM1];
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'TM1_User')
CREATE USER TM1_User WITH PASSWORD = 'StrongPw123!'; -- Contained User
2) TM1App スキーマにだけ SELECT を付与
GRANT SELECT ON SCHEMA::TM1App TO TM1_User;
これで TM1App に存在するビュー（既存・将来）をすべて読めるようになります。追加の作業は不要です。
3) TMS1 には付与・否認を一切しない
ここが設計のキモです。TMS1 には GRANT も DENY もしません。同一所有者であれば、TM1App のビューから参照する際に所有権チェーンが働き、下位の TMS1 に対する権限チェック自体がスキップされます。
動作検証（実行例）
ユーザーとして動作させ、期待どおりか確認しましょう。
USE [TM1];
-- ユーザーの権限確認（一覧）
SELECT * FROM fn_my_permissions(NULL, 'DATABASE') WHERE subentity_name = '';
SELECT * FROM fn_my_permissions('TM1App', 'SCHEMA'); -- スキーマ単位の権限
GO
-- ユーザーに切替（オンプレミス）
EXECUTE AS USER = 'TM1_User';
-- 期待どおり：TM1App のビューは読める
SELECT TOP (10) * FROM TM1App.View6;
-- 期待どおり：TMS1 のテーブル/ビューは直接読めない
BEGIN TRY
SELECT TOP (1) * FROM TMS1.Table1;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS number, ERROR_MESSAGE() AS message; -- 229 などのエラーを確認
END CATCH;
-- 期待どおり：TM1App.View6 経由で TMS1.Table1 のデータ参照は成功
SELECT COUNT(*) FROM TM1App.View6;
REVERT; -- コンテキスト戻す メタデータの見せ方（VIEW 定義の秘匿）
メタデータ可視性は「アクセス権のあるオブジェクトのみ見える」のが既定動作です。したがって TM1_User には TM1App のオブジェクトだけが見え、TMS1 は一覧にも出てきません。さらにビューの定義を見せたくないなら、以下の方針を組み合わせます。
GRANT VIEW DEFINITIONを付与しない（既定は権限なし）。- 必要に応じて
DENY VIEW DEFINITIONをTM1_Userに与える（SELECT とは別系統なので安全）。 - どうしてもソース非公開にしたい場合だけ
WITH ENCRYPTIONオプションを検討（復号が困難・運用上のデメリット有）。
チェックリスト（運用時につまずきやすい点）
| チェック項目 | 合格条件 | 備考 |
|---|---|---|
| スキーマ所有者 | TMS1 と TM1App が同一所有者（例：dbo） | ALTER AUTHORIZATION で統一 |
| ユーザーロール | public のみ | db_datareader や db_denydatareader は付けない |
| スキーマ権限 | GRANT SELECT ON SCHEMA::TM1App | 将来のオブジェクトも自動で許可 |
TMS1 への設定 | GRANT/DENY ともに未設定 | 所有権チェーンの邪魔をしない |
| メタデータ公開 | VIEW DEFINITION を付与しない | 必要なら DENY VIEW DEFINITION |
補足：所有権チェーンの要点
- 成立条件 … 同一データベース内で、呼び出し元（ビュー/ストアド）と参照先（テーブル/ビュー/関数）の所有者が一致。
- 効果 … 参照先の個別権限チェックをスキップ（＝GRANT/ DENY を見に行かない）。
- 途切れる場所 … 所有者が異なる／動的 SQL／一部の権限昇格が伴う操作／データベース境界。
- データベースをまたぐ参照 … 既定ではチェーンしません。必要なら署名付きモジュール等の別設計を検討。
ベストプラクティス（セキュリティ設計）
- 最小権限原則（Least Privilege） … エンドユーザーには
publicのまま、TM1AppのSELECTだけを許可。 - 読み取りはビュー経由に限定 … 業務ロジックや列/行のマスキング、連結、正規化解除はすべて
TM1App側のビューに集約。 - 列・行の絞り込み … 列はビューで、行は必要に応じて Row-Level Security (RLS) を併用。
- スキーマ分離 …
TMS1はデータ保管、TM1Appは公開インターフェース。責務を分けると保守しやすい。 - メタデータ監査 … 定期的に
sys.database_permissions、sys.objectsを点検し、意図しない GRANT を排除。
権限の可視化・監査クエリ集
-- スキーマ単位の権限一覧
SELECT
pr.principal_id,
pr.name AS principal_name,
pe.class_desc,
SCHEMA_NAME(pe.major_id) AS schema_name,
pe.permission_name,
pe.state_desc
FROM sys.database_permissions pe
JOIN sys.database_principals pr ON pe.grantee_principal_id = pr.principal_id
WHERE pe.class_desc = 'SCHEMA'
AND SCHEMA_NAME(pe.major_id) IN ('TM1App','TMS1')
ORDER BY principal_name, schema_name;
-- オブジェクト単位（ビュー/テーブル）で TM1_User に付与された SELECT を確認
SELECT
o.schema_id, SCHEMA_NAME(o.schema_id) AS schema_name,
o.name AS object_name, dp.permission_name, dp.state_desc
FROM sys.database_permissions dp
JOIN sys.objects o ON dp.major_id = o.object_id
JOIN sys.database_principals u ON dp.grantee_principal_id = u.principal_id
WHERE u.name = 'TM1_User' AND dp.permission_name = 'SELECT';
-- TM1_User から見えるオブジェクト（メタデータ可視性）
EXECUTE AS USER = 'TM1_User';
SELECT schema_name = SCHEMA_NAME(o.schema_id), o.name, o.type_desc
FROM sys.objects o
WHERE SCHEMA_NAME(o.schema_id) IN ('TM1App','TMS1')
ORDER BY schema_name, o.name;
REVERT; トラブルシューティング
「ビュ―経由の SELECT が失敗する」「TMS1 も見えてしまう」といった場合は、次の順で切り分けます。
- 所有者不一致の確認 … 前述のクエリで
TMS1とTM1Appの所有者が一致しているか。 - 余計なロール付与の有無 …
db_datareaderやdb_denydatareaderに所属していないか。 - 不要な DENY の有無 …
TMS1やデータベーススコープにDENY SELECTが入っていないか。 - ビュー定義の内容 … 動的 SQL を使っていないか。別 DB、別サーバーの参照が無いか。
- 権限の伝播経路 … 同名スキーマでも所有者が異なる DB からの参照は所有権チェーンが効きません。
やってはいけない NG パターン
GRANT SELECT ON DATABASE::TM1 TO TM1_User;… データベース全体の SELECT を許可してしまいます。EXEC sp_addrolemember 'db_datareader','TM1_User';… すべてのユーザーテーブル/ビューが読めてしまいます。DENY SELECT ON SCHEMA::TMS1を安易に設定 … 所有者差異があるとビュー経由も失敗します。- シノニム経由の公開 … シノニムには権限が付かず、基底オブジェクトの権限が必要です。公開インターフェースは必ずビューで。
将来拡張：運用設計のコツ
- リリース手順の固定化 … 新しい公開ビューは
TM1Appに作成し、所有者がdboであることを CI などで検証。 - テストユーザーの常設 …
TM1_Userを本番・検証環境ともに保持し、毎リリース時に自動テストで SELECT 動作を確認。 - マスキング … 個人情報など秘匿列は
TM1App側で計算列・CASE 式・マスキング関数等により露出しない設計に。 - 監査 … 監査ログ（SQL Audit 等）で
TM1_Userのデータアクセスを記録し、異常検知へ。
一括セットアップ & 検証スクリプト（テンプレート）
以下は記事の要点をまとめたテンプレートです。環境に合わせて名称を変更してください。
/* 0. スキーマ所有者の統一 */
ALTER AUTHORIZATION ON SCHEMA::TMS1 TO dbo;
ALTER AUTHORIZATION ON SCHEMA::TM1App TO dbo;
/* 1. ログイン/ユーザー（オンプレ） */
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'TM1Login')
CREATE LOGIN TM1Login WITH PASSWORD = 'StrongPw123!', CHECK_POLICY = ON;
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'TM1_User')
CREATE USER TM1_User FOR LOGIN TM1Login;
/* 2. TM1App スキーマにだけ SELECT を付与 */
GRANT SELECT ON SCHEMA::TM1App TO TM1_User;
/* 3. TMS1 には何もしない（GRANT/DENY ともに設定しない） */
/* 4. 動作検証 */
EXECUTE AS USER = 'TM1_User';
SELECT TOP (1) * FROM TM1App.View6; -- OK のはず
BEGIN TRY
SELECT TOP (1) * FROM TMS1.Table1; -- 失敗するはず
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS number, ERROR_MESSAGE() AS message;
END CATCH;
REVERT; 参考となる設計判断の比較（表）
| 選択肢 | 長所 | 短所/リスク | おすすめ度 |
|---|---|---|---|
本記事の方式（TM1App にだけ SELECT、TMS1 は無設定、所有者統一） | 保守が容易。将来の追加にも自動対応。意図しない広がりがない。 | 所有者がずれると壊れる。クロス DB には非対応。 | ★★★★★ |
db_datareader を付与 | 即効で動く | 全テーブル/ビューが読める＝要件を満たせない | ★☆☆☆☆ |
TMS1 に DENY | 見た目は安全そう | 所有者差異でチェーンが切れ、依存ビューも読めなくなる | ★☆☆☆☆ |
| オブジェクト個別 GRANT | 粒度が細かい | 運用が煩雑。将来追加のたびに付与が必要 | ★★☆☆☆ |
補足情報（要点サマリ）
| 補足ポイント | 内容 |
|---|---|
| 所有権チェーン | 同一 DB 内でオブジェクト所有者が一致すると、下位オブジェクトに対する個別の権限チェックをスキップできる。スキーマの所有者が異なる場合は ALTER AUTHORIZATION ON SCHEMA::TMS1 TO dbo; などでそろえる。 |
| VIEW 定義の隠蔽 | オブジェクト一覧や定義を見せたくない場合は GRANT VIEW DEFINITION を与えない／必要なら DENY VIEW DEFINITION を使う。 |
| メリット | 将来 TM1App にビューを追加しても自動で参照可能。余計な権限を与えないぶんセキュリティが単純化。 |
| デメリット | TMS1 と TM1App の所有者が異なる場合は動かない。別スキーマの個別テーブルを直接読ませたい場合は追加 GRANT が必要。 |
まとめ
「DENY を使わず、TM1App だけに SELECT を GRANT、TMS1 は無設定」という設計は、所有権チェーンに基づく SQL Server 本来の権限モデルを活かし、最小権限・低運用コスト・将来拡張に強いアプローチです。実運用では所有者の統一と不要なロール付与の排除だけを徹底すれば、TM1_User は TM1App のビューだけを安全に参照し、TMS1 のオブジェクトには直接アクセスできません。迷ったら本記事のテンプレートをベースに、まずは検証環境で「作る→付ける→試す→見る」を繰り返し、シンプルな権限設計を体得してください。
コメント