読者です 読者をやめる 読者になる 読者になる

1.21 jigowatts

Great Scott!

SQLServer データベースロールで権限管理

概要

SQLServerと連携しているとあるシステムでデータベースの一部のViewからだけデータ取得が出来るような権限管理がしたいという要望があったので、ロール作ってそこにユーザ紐づければいいんじゃないですか?と言ったもののよく知らなかったので調べてみました。

環境

Microsoft SQL Server 2012 Express Edition

ちなみにSQL Server Management Studioより以下のSQLでバージョンがチェックできます。

SELECT @@VERSION

実践

データベースの構成と仕様について

SampleデータベースにはCustomerProductTransactionDataの各テーブルに対してビューがあり、TransactionDataのビューのみデータ取得できるようなロールを作成します。
f:id:sh_yoshida:20150812060851p:plain

データベースロールの作成

1.[Sample]データベースの[セキュリティ]-[ロール]-[データベース ロール]を右クリックで[新しいデータベース ロール]を選択します。
f:id:sh_yoshida:20150812061429p:plain
2.[データベース ロール - 新規]ダイアログで[ロール名]を入力します。今回は「MY_SYSTEM_ROLE」としました。
f:id:sh_yoshida:20150812061926p:plain
3.[ページの選択]より[セキュリティ保護可能なリソース]を選択し、[検索]ボタンを押下します。
f:id:sh_yoshida:20150812062324p:plain
4.[オブジェクトの追加]ダイアログで[特定のオブジェクト]を選択し、[OK]ボタンを押下します。
f:id:sh_yoshida:20150812062457p:plain
5.[オブジェクトの選択]ダイアログで[オブジェクトの種類]を選択します。
f:id:sh_yoshida:20150812062828p:plain
6.今回はビューに権限を付与するので、[ビュー]を選択し、[OK]ボタンを押下します。
f:id:sh_yoshida:20150812062614p:plain
7.再度[オブジェクトの選択]ダイアログに戻るので、[参照]から[オブジェクトの参照]ダイアログにてTransactionDataのビューを選択します。
f:id:sh_yoshida:20150812063215p:plain
8.オブジェクトの種類とオブジェクト名が選択されたので[OK]ボタンを押下します。
f:id:sh_yoshida:20150812063138p:plain
9.[データベース ロール - 新規]ダイアログに戻って来ると[セキュリティ保護可能なリソース]にTransactionDataのビューが追加されています。画面下にある[dbo.V_TransactionDataの権限]の[選択]の[許可]にチェックを入れ、[OK]ボタンを押下します。
f:id:sh_yoshida:20150812063751p:plain
これでユーザー定義のデータベースロールが作成されました。

ユーザーの追加

1.インスタンス直下の[セキュリティ]-[ログイン]を右クリックで[新しいログイン]を選択します。
f:id:sh_yoshida:20150812064438p:plain
2.[ログイン - 新規作成]ダイアログで[ログイン名]を入力し、[Windows認証]にチェックをいれます。
f:id:sh_yoshida:20150812064626p:plain
3.[ページの選択]より[ユーザーマッピング]を選択し、[このログインにマップされたユーザー]で[Sample]データベースにチェック、[Sampleのデータベース ロール メンバーシップ]で[MY_SYSTEM_ROLE]にチェックを入れ、[OK]ボタンを押下します。
f:id:sh_yoshida:20150812064649p:plain
4.[MY_SYSTEM_ROLE]データベースロールのプロパティを確認すると、[このロールのメンバー]にユーザーが追加されていることが確認できます。
f:id:sh_yoshida:20150812065125p:plain
これで新しいユーザーが追加され、データベースロールに紐づきました。

新しいユーザーのロールを確認

1.先ほど追加したユーザーでログインし、[Sample]データベースを見てみると、テーブルはなく、ビューもTransactionDataのみが表示されていることが確認できます。
f:id:sh_yoshida:20150812065432p:plain
2.TransactionDataビューに対し[SELECT]をかけてみると正常にデータが取得できます。
f:id:sh_yoshida:20150812065608p:plain
3.一方、Productビューに対して同じように[SELECT]をかけると以下のように拒否され、エラーとなります。きっとロールのおかげ。
f:id:sh_yoshida:20150812065738p:plain
対象のユーザーが増えたら、同じようにユーザーの追加を行いデータベースロールに紐づけてあげればよさそうです。

参考

www.sqlquality.com