SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server のセキュリティを調査するためのサンプルクエリ

leave a comment

クラウドで提供されている SQL Server の権限周りを調べたくていくつかのサンプルクエリを作ってみました。

SQL Server の権限ですが、大きく分類してしまうと

になってくると思います。
# アプリケーションロールというのもあるのですが今のところ使う機会がなかったです。

オブジェクトレベルの権限はどのテーブルを参照できるかというような権限になってきますのでアプリケーション要件との兼ね合いが出てきます。
データベース管理者としてはまず、サーバー / データベースレベルの権限を把握しておくとよいかと思います。

■サーバーレベルの権限


サーバーレベルの権限は SQL Server のインスタンスに対してどのような操作ができるかの制御に使用します。
たとえば、SQL Server のサービスの再起動 / インスタンスへの接続 / サーバーレベルの設定変更 / データベースの作成というような操作になります。

これらの権限をログインに対して付与することでサーバーレベルの権限を変更することができます。

サーバーレベルの権限は

  • 固定サーバーロール
  • ユーザーサーバーロール (SQLServer 2012 から)
  • ログインに個別に権限を付与

で付与することになってくるかと。

そのため、サーバーレベルの権限を確認するためにはログインに対してこれらの権限の付与状況を確認していくことになります。

情報は以下のようなクエリで取得することができます。

— イスタンスレベルの権限確認
SELECT
    spr.name,
    spr.type_desc,
    spe.type,
    spe.permission_name,
    spe.state,
    spe.state_desc
FROM
    sys.server_permissions spe
    LEFT JOIN
        sys.server_principals spr
    ON
        spe.grantee_principal_id = spr.principal_id
ORDER BY state_desc

— サーバーロールの参加状況
SELECT
    sp_role.name AS role_name,
    sp_member.name AS member_name
FROM
    sys.server_role_members rm
    LEFT JOIN
        sys.server_principals sp_role
    ON
        rm.role_principal_id = sp_role.principal_id
    LEFT JOIN
        sys.server_principals sp_member
    ON
        rm.member_principal_id = sp_member.principal_id

ログインの情報については sys.server_principals から取得することができます。
どのようなセキュリティが設定されているかを sys.server_permissions から確認することができますのでこの二つを使用してログインにどのような権限が付与されているかを確認しています。

サーバーロールの情報は sys.server_role_members から取得することができますのでこちらを利用します。

これらを使用することでログインに対してどのような権限が付与されているかをざっくりと把握することができます。

 

■データベースレベルの権限


次にデータベースレベルの権限を確認してみます。
こちらは各データベースに設定されているユーザー単位での権限となります。
データベースレベルの権限は、オブジェクトの作成やデータベースプロパティの設定変更等の権限設定となってきます。

データベースレベルの権限は

  • 固定データベースロール
  • ユーザーデータベースロール
  • ユーザーに個別に権限を付与

で付与することになってくるかと。

サーバーレベルの権限はインスタンスに対して取得すればよいのですが、データベースレベルの権限はデータベース単位で取得する必要があります。

また、msdb のデータベースレベルの権限は SQL Server エージェント (SQL Server Agent) の権限にも影響してきますので、SQL Server エージェント関連の権限を調整している場合にはこちらの権限に注意する必要が出てきます。

サンプルのクエリは以下のようになります。

— データベースロールの参加状況
SELECT
    sp_role.name AS role_name,
    sp_member.name AS member_name
FROM
    sys.database_role_members rm
    LEFT JOIN
        sys.database_principals sp_role
    ON
        rm.role_principal_id = sp_role.principal_id
    LEFT JOIN
        sys.database_principals sp_member
    ON
        rm.member_principal_id = sp_member.principal_id

— データベースレベルの権限確認
SELECT
    name,
    dpr.type,
    type_desc,
    object_name(major_id) OBJECT_NAME,
    permission_name,
    state,
    state_desc
FROM
    sys.database_permissions  dpe
    LEFT JOIN
        sys.database_principals dpr
    ON
        grantee_principal_id = principal_id
ORDER BY 1

データベースレベルの権限は sys.database_principals / sys.database_role_members / sys.database_permissions から取得することができます。

これらのクエリを使用するとどのような権限が付与されているかの全体像をつかむことができるかと思います。

Written by masayuki.ozawa

7月 20th, 2013 at 1:09 pm

Posted in SQL Server

Tagged with

Leave a Reply

*