しばらく前に SQL Azure から SQL Database に名前が変わりましたが、変わってから初めて触ってます。
権限回りで遊んでたら不思議な現象に出くわしました。
新しくログイン を作成し、master データベースと検証用のデータベースにログインに紐付けたユーザーを追加します。
検証用のデータベースのユーザーに対し以下のスキーマに SELECT 可否の権限を設定しました。
スキーマ | dbo | sys | INFOMATION_SCHEMA |
SELECT | 許可 | 拒否 | 拒否 |
このログインで、SQL Server 2012 の SQL Server Management Studio(SSMS) を使って SQL Database に接続します。
すると、SSMS の オブジェクト エクスプローラー から、データベース => 検証用のデータベース => ビュー => システムビュー の各ビューの存在が確認出来てしまいます。
出来てしまうと書いたのは、オンプレミスの SQL Server なら、スキーマー sys の SELECT 権限が無いと、システムビューの一覧を表示出来ません。
ですが、SQL Database に接続した場合は表示されてしまいます。
次に、SQL Database に繋いでいる オブジェクト エクスプローラー から、システムビュー の一つ sys.all_columns を選択、右クリックから 上位N行の編集をクリックすると、あろうことかデータまで表示されてしまいます!SELECT 権限が無いのに!
この 上位N行の編集は、オンプレミスの SQL Server のテーブル/ビューに対して行うと裏では
select top (N) 〜
といったクエリが実行されます。が、SQL Database で SELECT 権限を拒否しているユーザーの場合は、このクエリが実行されている事を確認出来ませんでした。*1
ちなみに SELECT 権限がある SQL Database 初期ユーザーの場合は、ちゃんと select top (N) 〜 を実行していました。
データが表示出来るってことは、実は SELECT 権限が拒否出来てないのでは?と思いますが、クエリエディター に SELECT を書いた場合は、権限エラーで結果が返ってきません。
select * from sys.all_columnsエラー
メッセージ 229、レベル 14、状態 5、行 1 The SELECT permission was denied on the object 'all_columns', database 'mssqlsystemresource', schema 'sys'.
どうも該当のデータベースに対して権限が無い場合は、master データベース に対してデータを取りにいくような動きをしているのでは?と推測しました。
ただ、master データベースに対して DMV でデータを引っ張る権限が無いようでクエリの実行は確認出来ませんでした。
公式のドキュメントに探したら載っているかもしれませんね。
以下検証用クエリ
初期ユーザーでログインして実行するクエリ
master データベースで実行する。(SQL Database では USE ステートメントが使えないため)CREATE LOGIN [reflogin] WITH PASSWORD=N'適当なパスワード' GO -- SSMS でログインするには、master データベースへアクセス出来るユーザーが必要。 -- アプリケーションで使用するだけ(SSMS でログインしない)なら不要です。 CREATE USER [refuser] FOR LOGIN [reflogin] GO検証用のデータベースで実行するクエリ。(SQL Database では USE ステートメントが使えないため)
CREATE USER [refuser] FOR LOGIN [reflogin] GO GRANT SELECT ON SCHEMA::[dbo] TO [refuser] GO DENY SELECT ON SCHEMA::[INFORMATION_SCHEMA] TO [refuser] GO DENY SELECT ON SCHEMA::[sys] TO [refuser] GO