クラウドで提供されている SQL Server の権限周りを調べたくていくつかのサンプルクエリを作ってみました。
SQL Server の権限ですが、大きく分類してしまうと
になってくると思います。
# アプリケーションロールというのもあるのですが今のところ使う機会がなかったです。
オブジェクトレベルの権限はどのテーブルを参照できるかというような権限になってきますのでアプリケーション要件との兼ね合いが出てきます。
データベース管理者としてはまず、サーバー / データベースレベルの権限を把握しておくとよいかと思います。
Contents
■サーバーレベルの権限
サーバーレベルの権限は SQL Server のインスタンスに対してどのような操作ができるかの制御に使用します。
たとえば、SQL Server のサービスの再起動 / インスタンスへの接続 / サーバーレベルの設定変更 / データベースの作成というような操作になります。
これらの権限をログインに対して付与することでサーバーレベルの権限を変更することができます。
サーバーレベルの権限は
- 固定サーバーロール
- ユーザーサーバーロール (SQLServer 2012 から)
- ログインに個別に権限を付与
で付与することになってくるかと。
そのため、サーバーレベルの権限を確認するためにはログインに対してこれらの権限の付与状況を確認していくことになります。
情報は以下のようなクエリで取得することができます。
|
— イスタンスレベルの権限確認 — サーバーロールの参加状況 |
ログインの情報については sys.server_principals から取得することができます。
どのようなセキュリティが設定されているかを sys.server_permissions から確認することができますのでこの二つを使用してログインにどのような権限が付与されているかを確認しています。
サーバーロールの情報は sys.server_role_members から取得することができますのでこちらを利用します。
これらを使用することでログインに対してどのような権限が付与されているかをざっくりと把握することができます。
■データベースレベルの権限
次にデータベースレベルの権限を確認してみます。
こちらは各データベースに設定されているユーザー単位での権限となります。
データベースレベルの権限は、オブジェクトの作成やデータベースプロパティの設定変更等の権限設定となってきます。
データベースレベルの権限は
- 固定データベースロール
- ユーザーデータベースロール
- ユーザーに個別に権限を付与
で付与することになってくるかと。
サーバーレベルの権限はインスタンスに対して取得すればよいのですが、データベースレベルの権限はデータベース単位で取得する必要があります。
また、msdb のデータベースレベルの権限は SQL Server エージェント (SQL Server Agent) の権限にも影響してきますので、SQL Server エージェント関連の権限を調整している場合にはこちらの権限に注意する必要が出てきます。
サンプルのクエリは以下のようになります。
|
— データベースロールの参加状況 — データベースレベルの権限確認 |
データベースレベルの権限は sys.database_principals / sys.database_role_members / sys.database_permissions から取得することができます。
これらのクエリを使用するとどのような権限が付与されているかの全体像をつかむことができるかと思います。