SQL Server 2012 自習書 「No.18 セキュリティ」（HTML 版）

3.10　ユーザー定義データベース ロール

◆ ユーザー定義データベース ロール

ユーザー定義データベース ロールは、ユーザーが作成することができる「データベース ロール」です。データベース ユーザーをグループ化して、オブジェクト権限をまとめて設定したい場合などに利用すると便利です。

◆ Let's Try

それでは、これを試してみましょう。

1．ユーザー定義データベース ロールを作成するには、オブジェクト エクスプローラーで、「sampleDB」データベースの［セキュリティ］→「ロール」フォルダーを展開し、［データベース ロール］フォルダーを右クリックして、［新しいデータベース ロール］をクリックします。

［データベース ロール - 新規］ダイアログが表示されたら、［ロール名］へ「sqlUsers」など任意のロール名を入力して、［追加］ボタンをクリックします。

2．［データベース ユーザーまたはロールの選択］ダイアログが表示されたら、［参照］ボタンをクリックします。

［オブジェクトの参照］ダイアログでは、「sqlUser01」ユーザーをチェックして、［OK］ボタンをクリックします。

［データベース ユーザーまたはロールの選択］ダイアログへ戻ったら、［OK］ボタンをクリックしてダイアログを閉じます。

3．［データベース ロール - 新規］ダイアログへ戻ったら、［このロールのメンバー］へ「sqlUser01」が追加されていることを確認して、［OK］ボタンをクリックします。

これにより、sqlUsers データベース ロールが作成されて、sqlUser01ユーザーをメンバーとして登録することができました。

4．次に、作成した sqlUsers データベース ロールに対してロールに権限を設定します。「社員」テーブルを右クリックして［プロパティ］をクリックし、［テーブルのプロパティ］の［権限］ページを開きます。

現在は、public ロールに対して、「更新」と「挿入」権限が「許可」（GRANT）されていることを確認できます（Step 5.3 で設定）。

5．また、次のように sqlUser01 ユーザーをクリックすると、sqlUser01 に対して、「選択」権限が「許可」、「更新」権限が「拒否」（REVOKE）されていることを確認できます（Step 5.3 で設定）。

6．続いて、sqlUsers ロールに対して、オブジェクト権限を設定するために、次のように［ユーザーまたはロール］の［検索］ボタンをクリックします。

7．［ユーザーまたはロールの選択］ダイアログが表示されたら、［参照］ボタンをクリックします。

［オブジェクトの参照］ダイアログでは、「sqlUsers」ロールをチェックして、［OK］ボタンをクリックします。

［ユーザーまたはロールの選択］ダイアログへ戻ったら、［OK］ボタンをクリックしてダイアログを閉じます。

8．［テーブルのプロパティ］ダイアログへ戻ったら、［ユーザーまたはロール］で sqlUsers ロールを選択した状態で、「選択」の「拒否」をチェックします。

設定後、［OK］ボタンをクリックしてダイアログを閉じます。

設定した権限をまとめると、次のようになります。

◆ sqlUser01 でのログイン

次に、sqlUser01 ログイン アカウントでログインして、オブジェクト権限の設定を確認してみましょう。

1．まずは、ツールバーの［データベース エンジン クエリ］ボタンをクリックします。

［データベース エンジンへの接続］ダイアログでは、［認証］で「SQL Server 認証」を選択して、［ログイン］へ「sqlUser01」、［パスワード］へ「P@ssword」と入力し、［接続］ボタンをクリックします。

2．クエリ エディターが表示されたら、「sampleDB」データベースへ接続して、「社員」テーブルを参照する SELECT ステートメントを実行してみましょう。

この操作はエラーとなり、sqlUser01 ユーザーが、社員テーブルに対して選択権限を拒否されていることを確認できます。sqlUsers ロールに対しては「拒否」していたので、こちらが優先されています。