第 6 章 「データベースセキュリティ計画の設計」～ MCDBA をめざそう!! MCP 70-229 SQL Server 2000 開発編 ～
NEC
Eラーニング事業部
鈴木 智行
2002 年 8 月 16 日
目次
.gif){kind=icon}
1. 前回の復習問題の解答と解説
2. データアクセスの制御
3. オブジェクトレベルの権限の定義
4. アプリケーションロールの作成と管理
5. 復習問題
6. 今回の復習問題の解答と解説
7.まとめ
1. 前回の復習問題の解答と解説
問 1 の解答 ウ
（解説）統計情報は列値のサンプリングデータから生成された分布ステップ情報であり、SQL Server 2000のクエリオプティマイザは、この情報を利用してクエリの最適化に役立てます。私たちは統計情報を最新に保つことでクエリオプティマイザをサポートすることが必要です。
問 2 の解答 イ
（解説）数多くのクエリからボトルネックとなるクエリを探しだすのは、SQLプロファイラが最も適切です。他の監視ツールもいろいろな側面からパフォーマンスチューニングに役立つ情報を提示してくれます。特徴を理解しましょう。
問 3 の解答 ウ
（解説）制約で作成されたインデックスはDROP INDEXステートメントでは削除できません。制約自体の定義を削除して初めてインデックスは削除できます。CREATE INDEXステートメントで作成されたインデックスを削除するのがDROP INDEXステートメントです。
2. データアクセスの制御
2.1 所有権の継承
SQL Server 2000でビュー／ストアドプロシージャ／ユーザー定義関数にアクセスするときには、ビュー／ストアドプロシージャ／ユーザー定義関数そのものの権限だけ保持していてもクエリを実行できるとは限りません。こういった依存関係をもつデータベースオブジェクトは所有権の継承を考慮する必要があります。
所有権の継承は大きく分けて2つあります。
依存関係をもつデータベースオブジェクトの所有者が同じ場合
（壊れていない所有権の継承）.gif)
図2-1-1 所有者が同じ場合
図2-1-1のようにテーブルdbo.売上を用いてdbo.本日売上確認ストアドプロシージャをdboが作成した場合、dbo.本日売上確認のExecute実行権限を与えられたデータベースユーザーSuzukiはテーブルdbo.売上のSELECT権限がなくてもdbo.本日売上確認ストアドプロシージャを実行することができます。すなわちSQL Server 2000は依存関係をもつデータベースオブジェクトの所有者が同じ場合、元のオブジェクトの権限のチェックを行いません。
依存関係をもつデータベースオブジェクトの所有者が異なる場合
（壊れた所有権の継承）.gif)
図2-1-2 所有者が異なる場合
図2-1-2のようにテーブルdbo.売上を用いて本日売上確認ストアドプロシージャをデータベースユーザーSatoが作成した場合、Sato.本日売上確認のExecute実行権限を与えられたデータベースユーザーSuzukiはテーブルdbo.売上のSELECT権限があればSato.本日売上確認ストアドプロシージャを実行することができますし、テーブルdbo.売上のSELECT権限がなければSato.本日売上確認ストアドプロシージャを実行することができません。すなわちSQL Server 2000は依存関係をもつデータベースオブジェクトの所有者が異なる場合、元のオブジェクトの権限のチェックを行います。
2.2 低レベルのセキュリティとテーブルへの直接アクセスの制限
壊れた所有権の継承は管理が煩雑になるため、あまり好ましくありません。したがってデータベースオブジェクトを共通のユーザー（たとえばdbo）で作成するか、データベースオブジェクト所有者をsp_changeobjectownerシステムストアドプロシージャを使用して共通のユーザーに変更します。
（例）データベースオブジェクト所有者を dboに変更する
sp_changeobjectowner N'Sato.本日売上確認',N'dbo'
以上のように共通のユーザーに統一すれば元オブジェクトの直接権限を持たせずに、実際に利用するビュー／ストアドプロシージャ／ユーザー定義関数そのものの権限だけを用いて管理を簡便化することが可能です。
3. オブジェクトレベルの権限の定義
オブジェクト権限はデータベースオブジェクトを操作するためのもので以下の種類があります（表3-1）。
オブジェクト権限の種類
設定対象
SELECT
テーブル、ビュー、列、ユーザー定義関数
INSERT
テーブル、ビュー、ユーザー定義関数
UPDATE
テーブル、ビュー、列、ユーザー定義関数
DELETE
テーブル、ビュー、ユーザー定義関数
EXECUTE
ストアドプロシージャ
REFERENCES
テーブル、ビュー、列、ユーザー定義関数
表 3-1 オブジェクト権限の種類
上記のオブジェクト権限をデータベースユーザーやユーザー定義データベースロールに設定し、最終的なオブジェクト操作を決定します。設定には以下のステートメントを使用します。
ステートメント
説明
GRANT
操作の実行を許可する
DENY
操作の実行を拒否する
REVOKE
操作の実行は他に依存する（許可、拒否の状態をクリアする）
表 3-2 オブジェクト権限の設定
（例）データベースユーザーSuzuki にdbo.本日売上確認のExecute実行権限を与える
GRANT Execute ON dbo.本日売上確認 TO Suzuki
ロールに与えられた権限は所属するユーザーに継承するため、GRANTステートメントで与えられた権限は累積します（図3-3）。REVOKEステートメントそのものは権限を与えませんが、許可、拒否の状態をクリアするので、データベースユーザーが所属している他のロールやデータベースユーザー個人に設定された権限に影響を受けます。
.gif)
図 3-3 GRANT ステートメントで与えられた権限は累積する
DENYステートメントは操作の実行を拒否します。継承によって権限がGRANTステートメントと矛盾した場合にはDENYステートメントでの権限が優先されます（図3-4）
.gif)
図 3-4 DENY ステートメントは優先する
オブジェクト権限については、上記の他にも固定サーバーロールのsysadminや固定データベースロール（db_owner,db_datareader,db_datawriter, db_denydatareader,db_denydatawriter）に所属して権限をもつこともできます。またデータベースオブジェクト所有者自身はすべてのアクティビィティを実行できる暗黙の権限を持っています。このように、いろいろな形で権限が設定されている可能性があるので知識を整理しておいてください。
4. アプリケーションロールの作成と管理
SQL Server 2000のロールをまとめると図4-1のようになります。
.gif)
図 4-1 SQL Server 2000 のロール
このようにアプリケーションロールはデータベースロールの一種なのですが、他のロールのようにログイン、ユーザー、ロール（いわゆる"人"）をターゲットにしているのではなく、"アプリケーション"をターゲットにしていることが大きく異なります。すなわちクエリを実行しているユーザーに権限を設定するのではなく、クエリを発行しているアプリケーション自身に権限を設定します。これにより、たとえクエリを実行しているユーザーがデータベースオブジェクトの権限を持っていなくても、権限を設定されたアプリケーションを通してデータベースオブジェクトの操作が可能になります（図4-2）。
.gif)
図 4-2 アプリケーションを通して操作が可能
アプリケーションロールはsp_addapproleシステムストアドプロシージャを用いて、まずはロールの名前とパスワードを設定します。
（例）売上確認OKアプリケーションロールの作成
sp_addapprole N'売上確認OK','password'
次にアプリケーションロールに必要なデータベースオブジェクトの権限を与えます。
（例）売上確認OKアプリケーションロールにテーブル売上のSELECT権限を与える
GRANT SELECT ON dbo.売上 TO 売上確認OK
最後にクエリを発行するアプリケーションからアプリケーションロールを使用できるようにsp_setapproleシステムストアドプロシージャでアプリケーションロールをアクティブ化します。
（例）売上確認OKアプリケーションロールをアクティブ化する
sp_setapprole N'売上確認OK','password'
アプリケーションロールを使用する際の注意点は2つです。
アプリケーションロールはノンアクティブ化できない。
（セッション終了まで有効であり、無効にはできない）
データベース内で有効である。
（他のデータベースに切り替えた際はそのデータベースのGUEST権限に基づく）
したがってアプリケーションロールを用いてデータベースオブジェクトのセキュリティを管理する場合には、アプリケーションが使用するデータベースオブジェクトを同じデータベースに格納することで管理を簡便化することが可能です。
5. 復習問題
解答、解説は次回に記述します、復習として考えてみてください。
問1 下記の図5-1のように依存関係があるオブジェクトに関し、データベースユーザーSuzukiがyoshida.本日売上確認ストアドプロシージャを実行するためにはどのオブジェクトのどういった権限が必要ですか？正しいものを全て選択してください。
.gif)
図 5-1 依存関係があるオブジェクト
ア．ア． テーブル売上のSelect権限
イ．テーブル売上のUpdate権限
ウ．ビュー上期売上のSelect権限
エ．ビュー上期売上のUpdate権限
オ．ストアドプロシージャ本日売上確認のSelect権限
カ．ストアドプロシージャ本日売上確認のExecute権限
問2 上記の図5-1のように依存関係があるオブジェクトに関し、データベースユーザーSatoがSato.上期売上ビューを閲覧するためにはするためにはどのオブジェクトのどういった権限が必要ですか？正しいものを全て選択してください。
ア．ア． テーブル売上のSelect権限
イ．テーブル売上のUpdate権限
ウ．ビュー上期売上のSelect権限
エ．ビュー上期売上のUpdate権限
オ．ストアドプロシージャ本日売上確認のSelect権限
カ．ストアドプロシージャ本日売上確認のExecute権限
問3 下記の表5-3のようにテーブル売上のオブジェクト権限を設定した場合、データベースユーザーSuzuki（Managerロールに所属）はテーブル売上に対してどういう操作ができますか？正しいものを全て選択してください。
設定対象ユーザー／ロール
設定ステートメント
オブジェクト権限種類
Public
GRANT
SELECT,INSERT,DELETE
Manager
DENY
INSERT,DELETE
Suzuki
REVOKE
SELECT,UPDATE
表5-3 テーブル売上のオブジェクト権限
ア．SELECT
イ．UPDATE
ウ．INSERT
エ．DELETE
オ．何もできない
6. 今回の復習問題の解答と解説
問1の解答 ア、ウ、カ
（解説）所有者が異なるので、SQL Serverは元のオブジェクトの権限をチェックします。この場合、ストアドプロシージャ本日売上確認ではビュー上期売上をSelectしているので、ア、ウ、カが正解になります。ストアドプロシージャにSelect権限はありません。
問2 の解答 ア
（解説）所有者が異なるので、SQL Serverは元のオブジェクトの権限をチェックします。この場合、ビュー上期売上は所有者Satoであるため、ビュー上期売上に関してはすべてのアクティビィティを実行できる暗黙の権限を持っています。したがってアのみが正解になります。ストアドプロシージャに対しては操作を行っていないのでストアドプロシージャに対する権限は必要ありません。
問3 の解答 ア
（解説）データベースユーザーSuzukiはPublicロールとManagerロールに含まれています。PublicロールとManagerロールに設定された権限を継承します。INSERT,DELETE権限は矛盾していますが、DENYステートメントの方が強いので操作は実行できません。今回データベースユーザー個人に関してはREVOKEステートメントが設定されているだけなので、意識する必要はありません。以上のことから正解はアとなります。
7.まとめ
以上で試験番号70-229 (Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition)の試験トピックスに基づいた解説は終了です。まえがきにも記述しましたが、管理者といえども開発技法、開発技術に携わっていくことが必要であると感じていますし、70-229に合格するためには、70-228の知識も非常に重要だと思います。70-228を取得していない方はぜひ並行に勉強を進めてください。大変でしょうが、それが逆に最短の近道であると思っています。このコラムが合格の一助となれば幸いです、ぜひがんばってください。
.jpg)
鈴木 智行 : NEC Eラーニング事業部に所属。 入社以来、インストラクタとして教育業務に従事。汎用機、UNIX を経て、1994 年より マイクロソフト認定トレーナー (MCT) として、管理者向け教育を担当。SQL Server は 4.21a から携わっており、現在は主に SQL Server 2000 に関わるデータベース教育を中心に担当。Windows 2000 および SQL Server 2000 での MCSA, MCSE, MCDBA を取得しており、情報処理技術者試験のテクニカルエンジニア （データベース） も取得済。最近は MCA の 3 科目 （データベース、OS／ネットワーク、アプリケーション構築） 全てに合格し、C# を勉強中。