SQLServer データベースロールで権限管理
概要
SQLServerと連携しているとあるシステムでデータベースの一部のViewからだけデータ取得が出来るような権限管理がしたいという要望があったので、ロール作ってそこにユーザ紐づければいいんじゃないですか？と言ったもののよく知らなかったので調べてみました。
環境
Microsoft SQL Server 2012 Express Edition
ちなみにSQL Server Management Studioより以下のSQLでバージョンがチェックできます。
SELECT @@VERSION
実践
データベースの構成と仕様について
SampleデータベースにはCustomer、Product、TransactionDataの各テーブルに対してビューがあり、TransactionDataのビューのみデータ取得できるようなロールを作成します。
データベースロールの作成
1.[Sample]データベースの[セキュリティ]-[ロール]-[データベース ロール]を右クリックで[新しいデータベース ロール]を選択します。
2.[データベース ロール - 新規]ダイアログで[ロール名]を入力します。今回は「MY_SYSTEM_ROLE」としました。
3.[ページの選択]より[セキュリティ保護可能なリソース]を選択し、[検索]ボタンを押下します。
4.[オブジェクトの追加]ダイアログで[特定のオブジェクト]を選択し、[OK]ボタンを押下します。
5.[オブジェクトの選択]ダイアログで[オブジェクトの種類]を選択します。
6.今回はビューに権限を付与するので、[ビュー]を選択し、[OK]ボタンを押下します。
7.再度[オブジェクトの選択]ダイアログに戻るので、[参照]から[オブジェクトの参照]ダイアログにてTransactionDataのビューを選択します。
8.オブジェクトの種類とオブジェクト名が選択されたので[OK]ボタンを押下します。
9.[データベース ロール - 新規]ダイアログに戻って来ると[セキュリティ保護可能なリソース]にTransactionDataのビューが追加されています。画面下にある[dbo.V_TransactionDataの権限]の[選択]の[許可]にチェックを入れ、[OK]ボタンを押下します。
これでユーザー定義のデータベースロールが作成されました。
ユーザーの追加
1.インスタンス直下の[セキュリティ]-[ログイン]を右クリックで[新しいログイン]を選択します。
2.[ログイン - 新規作成]ダイアログで[ログイン名]を入力し、[Windows認証]にチェックをいれます。
3.[ページの選択]より[ユーザーマッピング]を選択し、[このログインにマップされたユーザー]で[Sample]データベースにチェック、[Sampleのデータベース ロール メンバーシップ]で[MY_SYSTEM_ROLE]にチェックを入れ、[OK]ボタンを押下します。
4.[MY_SYSTEM_ROLE]データベースロールのプロパティを確認すると、[このロールのメンバー]にユーザーが追加されていることが確認できます。
これで新しいユーザーが追加され、データベースロールに紐づきました。
新しいユーザーのロールを確認
1.先ほど追加したユーザーでログインし、[Sample]データベースを見てみると、テーブルはなく、ビューもTransactionDataのみが表示されていることが確認できます。
2.TransactionDataビューに対し[SELECT]をかけてみると正常にデータが取得できます。
3.一方、Productビューに対して同じように[SELECT]をかけると以下のように拒否され、エラーとなります。きっとロールのおかげ。
対象のユーザーが増えたら、同じようにユーザーの追加を行いデータベースロールに紐づけてあげればよさそうです。
