ロールを利用したグループ単位での権限設定：SQL実践講座（18）

[篠原光太郎，＠IT]

　今回は、前回に引き続き、権限の設定です。「ロール」という、ユーザーやログインのグループ機能についてチャレンジします。ロールを利用することによって、個々のユーザーへ権限を設定するのではなく、グループに対して権限の設定が可能になるため、設定をよりシンプルにすることができます。

　では早速、新規にロールを作成して、画面で確認しながら進めていきましょう。

SQL Server Enterprise Managerによるロールの新規作成

　SQL Server Enterprise Managerで、「CardInfoUsers」というロールを作成します。CardInfoUsersは、顧客のメンバカード情報にアクセスできるメンバのグループです。メンバカード情報は機密性が高いため、特定のユーザー「yamada」と「tanaka」にのみアクセス権を与えることとします。

　まずは、yamadaとtanakaという名前のSQL Serverログインを作成しましょう。

例題1：「テスト用のユーザーを作成する」

(1) Enterprise Managerを起動する

(2) 左端に表示される「ツリー」で、以下を順に展開する

1.Microsoft SQL Servers
2. SQL Serverグループ
3. サーバー名
4. セキュリティ
5. ログイン

(3) ツリーから「ログイン」を右クリックし、「新規ログイン」を選択する

(4) ログイン「yamada」と「tanaka」を順に作成する

	yamada	tanaka
名前	yamada	tanaka
認証	SQL Server認証	SQL Server認証
パスワード	yamada	tanaka
規定値 データベース	Northwind	Northwind
データベースアクセス	Northwind	Northwind

　無事作成できたでしょうか？ できなかった場合は、前回（第18回「SQL Serverのオブジェクトに権限を設定する」）の例題2を参考にしてください。

　次に、ロール「CardInfoUsers」を作成し、いま作成したユーザー「tanaka」と「yamada」をメンバに追加しましょう。

例題2：「作成したユーザーをロールのメンバに追加する」

(1) 左端に表示される「ツリー」で、以下を順に展開する。

3.Microsoft SQL Servers
4. SQL Server グループ
5. サーバー名
6. データベース
7. Northwind
8. ロール

(2) ツリーから「ロール」を右クリックし、「新規データベースロール」を選択する

(3) 表示される画面で、「名前」に「CardInfoUsers」と入力する

画面1　新規ロールの作成ダイアログボックス。ここで「追加」ボタンを押してユーザーを追加する（画面をクリックすると拡大表示します）

(4) 「追加」ボタンをクリックし、「ロールメンバの追加」画面を表示する

画面2　ロールメンバに参加可能なユーザーの一覧が表示されるので、必要なユーザーを順に追加する

(5) ユーザーtanakaを選択し、「OK」ボタンをクリックする

(6) 同様に、ユーザーyamadaをメンバーに追加する

(7) データベースロールのプロパティ画面が下の画面3のとおりであることを確認し、「OK」ボタンを押して終了する

画面3　画面1からロールメンバが追加された状態。この状態であることを確認して「OK」ボタンを押す（画面をクリックすると拡大表示します）

　これで、ロールCardInfoUsersが作成できました。ただし、まだこの状態では、ロールCardInfoUsersのメンバは、データベースに対して何もすることができません。ユーザーを新規作成したときに、データベースオブジェクトに対する権限がまったく設定されていなかったのと同じですね。

　では、ロールCardInfoUsersに対して、CardInfoテーブルとCardInfoViewビューへの参照（SELECT）／挿入（INSERT）／更新（UPDATE）の権限を付与してみましょう。

例題3：「ロールに権限を付与する」

(1) ロールCardInfoUsersを右クリックし、「プロパティ」を選択する

(2) データベースロールのプロパティ画面が表示されるので、「権限」ボタンをクリックする

(3) CardInfoテーブル、CardInfoViewビューに対して、「SELECT」「INSERT」「UPDATE」をチェックし、権限を付与する。設定した画面は以下のとおり

画面4　作成したロールに、CardInfoとCardInfoViewへの「SELECT」「INSERT」「DELETE」権限を付与する（画面をクリックすると拡大表示します）

(4) 「OK」ボタンを押して、権限の設定画面、データベースロールのプロパティ画面を閉じる

　これで、権限の設定は完了しました。実際にクエリアナライザを使用して、tanakaとyamadaでそれぞれログインし、CardInfoテーブル、CardInfoViewにアクセスできることを確認してみましょう。

権限の継承

　これまでの例で、CardInfoUsersというロールに対して権限を設定することにより、ロールのメンバであるユーザーtanakaとyamadaに対して権限が付与されることが分かりました。ここで、ユーザーtanakaとyamadaに対しては、個別に権限が設定されていないことをプロパティで確認してみてください。

画面5　ユーザーtanakaのプロパティを参照してみると、先ほど設定した権限が個別のユーザーには反映されていないことが分かる（画面をクリックすると拡大表示します）

　ロールに対して設定したすべての権限は、すべてのメンバに対して適用されます。また、ロールの権限を変更すると、ロールのメンバ全員に対してその権限の変更が適用されます。データベース・アプリケーションを構築する際、あらかじめアプリケーション運用に際して必要になる役割を定義し、それを「ロール」として設定し、ロールに対して権限設定を行う運用とすれば、テーブルやビューが追加されたときに、すべてのメンバに対して権限の設定変更を行う必要がなくなり、運用をシンプルにすることが可能です。

　さて、ロールに所属させておけば、メンバの権限を設定する必要がないことを説明しました。逆に、ロールに所属しているメンバに対して権限を設定すると、そのメンバに与えられる権限はどうなるのでしょうか？ 例題を実行しながら、確認してみましょう。

例題4：「ユーザーyamadaにDELETE権限がないことを確認する」

(1) クエリアナライザに「yamada」でログインする

(2) 次のSQLを実行し、データの削除ができないことを確認する

DELETE FROM CardInfo WHERE CardID = 'ALFKI'

　ユーザー「yamada」が属しているロール「CardInfoUsers」は、CardInfoテーブルに対してDELETE権限が設定されていないため、例題4のとおりエラーとなります。

　では、ユーザーyamadaに対して、CardInfoテーブルのDELETE権限を設定してみましょう。

例題4の続き：「ロールメンバ個々に権限を与えるとどうなるか？」

(3) SQL Server Enterprise Managerで、ユーザー「yamada」のプロパティを表示し、「権限」ボタンをクリックする

(4) CardInfoテーブルに対して、「DELETE」をチェックし、権限を付与する

画面6　ユーザーyamadaに「DELETE」権限を付与する（画面をクリックすると拡大表示します）

(5) CardInfoテーブルのプロパティ画面を閉じる

(6) (1)のクエリアナライザに戻り、再度(2)のSQL文を実行する

　無事に削除が実行できたでしょうか？ 例題4のとおり、ロールに権限が設定されていない場合、メンバに対して権限を付与すればその権限が有効になります。例題4では、CardInfoテーブルに対するDELETE権限は、ロールCardInfoUsersに対しては設定されていなかったため、メンバのユーザーyamadaに対して設定したDELETE権限が有効になったわけです。

　このように、ロールの権限とメンバの権限は、それぞれの権限を合わせた権限がメンバに対して付与されます。

権限の拒否

　では次に、ロールに与えられた権限を拒否する場合はどうするのでしょうか。例えば、ユーザtanakaはロールCardInfoUsersのメンバですので、CardInfoテーブルに対してINSERTの権限が与えられています。これを、ユーザーtanakaだけはCardInfoテーブルに対して挿入（INSERT）ができないように設定するとします。

　次の例題を実行してみましょう。

例題5：「権限を拒否するとどうなるか？」

(1) クエリアナライザに「tanaka」でログインする

(2) 次のSQLを実行し、データの挿入ができることを確認する

INSERT INTO CardInfo
(CardID, CustomerID, IssueDate, "ExpireDate", EmployeeID)
VALUES
('TANAK', 'TANAK', '2001/12/1', '2003/11/30' , 7)

(3) SQL Server Enterprise Managerで、ユーザー「tanaka」のプロパティを表示し、「権限」ボタンをクリックする

(4) CardInfoテーブルに対して、「INSERT」を拒否設定し、権限を拒否する

画面7　ユーザーtanakaのプロパティを開き、CardInfoデータベースへの「INSERT」権限を明示的に拒否する（画面をクリックすると拡大表示します）

(5) CardInfoテーブルのプロパティ画面を閉じる

(6) クエリアナライザで次のSQLを実行し、データの挿入ができないことを確認する

INSERT INTO CardInfo
(CardID, CustomerID, IssueDate, "ExpireDate", EmployeeID)
VALUES
('YAMADA', 'YAMAD', '2001/12/1', '2003/11/30' , 7)

　例題5のとおり、ロールで権限が設定されていても、メンバで権限を拒否すると、結果としてメンバの権限は拒否されることが確認できました。逆に、メンバで権限が設定されていても、ロールで権限が拒否されていれば、結果としてメンバの権限は拒否されます。例えば、ロールCardInfoUsersに対して、CardInfoテーブルのDELETE権限を拒否に設定すると、先ほどyamadaに対して許可したDELETE権限が拒否されるのが確認できますので、実際に実行してみてください。

　ロールとメンバの権限に関して、設定と実際に付与される権限を一覧でまとめると、以下の表のとおりになります。

ロールの権限	メンバの権限	結果として付与される権限
未設定	未設定	拒否
未設定	許可	許可
未設定	拒否	拒否
許可	未設定	許可
許可	許可	許可
許可	拒否	拒否
拒否	未設定	拒否
拒否	許可	拒否
拒否	拒否	拒否

　つまり、「許可が1つもないと拒否」「拒否が1つでもあると拒否」と覚えるとよいでしょう。

固定データベースロール

　これまでの例で気が付いたかもしれませんが、ロールの一覧には、今回作成したCardInfoUsers以外にもいくつかのロールが表示されています。

画面8　Enterprise Managerでロールの一覧を表示させたところ。今回作成したCardInfoUsers以外にも、あらかじめロールが用意されていることが分かる（画面をクリックすると全体を表示します）

　「db_accessadmin」など、「db_」が頭に付いたロールと、「public」というロールがあるのを確認できますね。これらは、「固定データベースロール」といって、SQL Serverでデータベースを作成すると自動的に作成される標準のロールです。それぞれのロールは、データベースに対しての権限があらかじめ設定されているため、条件が合えば新たにロールを作成せずに、これらの固定データベースロールを使用した方が便利なことがあります。各ロールの設定内容は下記の表のとおりです。

固定データベースロール	説明
db_owner	データベースに対するすべての権限を付与
db_accessadmin	ユーザーの追加／削除／設定を行う権限を付与
db_datareader	データベース内のすべてのテーブル、ビューに対してデータの参照権限（SELECT）を付与
db_datawriter	データベース内のすべてのテーブル、ビューに対してデータの挿入（INSERT）／更新（UPDATE）／削除（DELETE）権限を付与
db_ddladmin	データベース内のオブジェクト自身の追加／変更／削除を実行する権限を付与する（CREATE／ALTER／DROP）
db_securityadmin	「SQL Server 2000 データベース ロール」のロールおよびメンバ、さらにデータベース内のステートメント権限およびオブジェクト権限を管理
db_backupoperator	データベースをバックアップする権限を付与
db_denydatareader	データベース内のデータを参照する権限を拒否
db_denydatawriter	データベース内のデータを更新する権限を拒否

　特に、db_datareader、db_datawriterは、すべてのテーブルとビューに対しての権限を設定することが可能です。ほとんどのテーブルやビューに対して権限を許可し、一部のテーブルやビューに対して権限を拒否する場合は、db_datareaderやdb_datawriterのメンバに指定し、ユーザーの権限を設定する方が、テーブル数が多い場合に便利です。

　この一覧にない固定データベースロールが「public」です。ロールpublicは、データベースのすべてのユーザーがメンバとして自動的に指定されるロールです。ユーザーを新規に作成すると、必ずロールpublicのメンバに指定されます。先の例で作成したユーザーyamadaとtanakaもロールpublicのメンバに指定されているので、確認してみてください。

画面9　ロールpublicには、すべてのユーザーがメンバとして自動的に登録される。ユーザーyamadaが所属するロールの一覧を確認してみると、特に設定していないのにpublicに所属していることが分かる（画面をクリックすると拡大表示します）

　ただし、ロールpublicには権限の設定がまったくされていないので、1つ1つのオブジェクトに対して権限を設定する必要があります。データベースのすべてのユーザーに一律の権限を設定する際は、新たにロールを作成するのではなく、ロールpublicに対して権限を設定すると便利です。

次回の予定

今回は、Enterprise Managerを使用したロールの設定について解説しました。次回はSQLでの権限設定について紹介する予定です

「次回」へ

「SQL実践講座」バックナンバー

• SQL Serverで「デッドロック」を回避する
• トランザクションの一貫性を保証するロック
• トランザクションを用いて注文登録をする
• トランザクションでデータの不整合を防ぐ
• テーブルで複数の処理を実行させるトリガー
• ユーザー定義関数を作成するストアドファンクション
• ストアドプロシージャによる繰り返し処理
• 条件分岐のあるストアドプロシージャ
• ストアドプロシージャの作成
• システム・ストアドプロシージャを用いたロールの詳細設定
• ロールを利用したグループ単位での権限設定
• SQL Serverのオブジェクトに権限を設定する
• Enterprise Managerによるビューの作成
• 作成したSELECT文をDBに登録する「ビュー」
• データの更新と主キーの重要性
• テーブル中のデータ識別に必要な主キーを定義する
• データの登録を行うINSERT文
• CREATE文をさらに使いこなそう
• CREATE文でテーブルを作成する
• SELECT文を統合する「UNION」
• サブクエリーの応用「相関サブクエリー」
• SELECT文の結果を抽出条件に使う
• テーブル結合のバリエーションを増やす
• テーブル結合の仕組みを理解する
• 異なるテーブル同士を結合する「JOIN」句
• 集計を行う「GROUP BY」句
• SELECT文で並べ替えを行うには？
• SQLの基礎 「SELECT」文を覚えよう