WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

EXECUTE を使うストアドプロシージャ

by WebSurfer 8. October 2021 14:00

必要な最小の権限のみ与えるというセキュリティの基本に沿って、データを操作するのに必要なストアドプロシージャを作って、ユーザーにはストアドプロシージャに対する実行権限だけを与えるのが良いという話を聞きます (データベースやテーブル全体に対する権限を与えるのではなくて)。

しかしながら、EXECUTE ステートメントを使用するストアドプロシージャの場合、ストアドプロシージャに対する実行権限だけでは権限が不足するケースがあるということを書きます。

エラーメッセージ

上の画像はストアドプロシージャを使ってデータベースからデータを取得して表示する ASP.NET Web Forms アプリの例で、権限不足のため SqlException 例外がスローされ、「SELECT 権限がオブジェクト 'Student'、データベース 'TestDatabase'、スキーマ 'dbo' で拒否されました」というエラーメッセージが表示されています。

IIS 上で動く ASP.NET Web Forms アプリなので、そのワーカープロセスのアカウント NETWORK SERVICE にストアドプロシージャに対する実行権限は与えてあります。

そのストアドプロシージャは以下のとおりで、SELECT クエリを含む文字列を EXECUTE ステートメントで実行するようになっています。

ストアドプロシージャその 1

エラーメッセージは SELECT 権限が拒否されましたと言っています。つまり、今回の例では、NETWORK SERVICE に対象テーブル対する SELECT 権限を与える必要があると言っています。

なので、エラーメッセージに従って SELECT 権限を与えれば動くはずです。実際にやってみましたが、ストアドプロシージャの実行権限に加えて、対象テーブルに対する SELECT 権限を与えれば動きました。下の画像がアプリを実行した結果です。

アプリの実行結果

何故ストアドプロシージャの実行権限だけではダメなのかを調べてみると、そういう仕様のようです。Microsoft のドキュメント EXECUTE (Transact-SQL) の「アクセス許可」のセクションに以下の通り書いてありました。

"EXECUTE ステートメントの実行に権限は必要ありませんが、 EXECUTE 文字列内で参照されるセキュリティ保護可能なリソースに対しては権限が必要です。 たとえば、この文字列に INSERT ステートメントが含まれている場合、EXECUTE ステートメントの呼び出し元は対象のテーブルに対する INSERT 権限が必要です。"

(上の「EXECUTE ステートメントの実行に権限は必要ありませんが」というのはストアドプロシージャの「実行」権限の話ではありません。ユーザーにはストアドプロシージャの「実行」権限は必ず与える必要があります)

では、EXECUTE ステートメントを使わないストアドプロシージャ即ち以下のような場合はどうなるでしょうか? 実際に検証した結果、こちらはストアドプロシージャに対する実行権限だけを与えればよく、対象テーブル対する SELECT 権限は不要でした。(この違いが分かり難く間違いのもとになりそうです)

ストアドプロシージャその 2

もう一つ、EXECUTE + sp_executesql (Transact-SQL)を使ったらどうなるか、即ち以下のようなストアドプロシージャではどうかも試してみました。

ストアドプロシージャその 3

結果はやはり、ストアドプロシージャの実行権限に加えて、対象テーブルに対する SELECT 権限も必要でした。


以上でメインの話は終わりですが、検証に使ったテーブル、権限の与え方、ASP.NET Web Forms アプリのコードを忘れないように以下にメモしておきます。

検証に使った TestDatabase データベース内の Student テーブルは以下の通りです。

Student テーブル

NETWORK SERVICE は SQL Server のログインに設定済みです。サーバーロールはデフォルトの public だけです(public は必ず付与され、外すことはできません)。ユーザーマッピングで TestDatabase のマップにチェックを入れます。

ユーザーマッピング

public サーバーロールには接続権限が許可されていますので、上の操作で自動的に NETWORK SERVICE に TestDatabase データベースに対する接続権限が与えられます。

データベースに対する権限の設定

ストアドプロシージャに対する実行権限の設定。これだけでは権限不足でこの記事の一番上の画像のエラーとなります。

ストアドプロシージャに対する実行権限の設定

Student テーブルに対する SELECT 権限の設定を行います。

Student テーブルに対する SELECT 権限の設定

検証に使った ASP.NET Web Forms アプリのコードは以下の通りです。ストアドプロシージャ経由 Student テーブルからデータを取得して List<T> 型のオブジェクトを生成し、それを GridView にバインドしてレコード一覧を表示しています。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

public partial class test03 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string connStr = @"接続文字列";
        using (var connection = new SqlConnection(connStr))
        {
            using (var command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "[dbo].[StoredProcedure1]";
                var list = new List<StudentDTO>();

                connection.Open();                
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var student = new StudentDTO();
                        student.StudentID = reader.GetInt32(0);
                        student.FirstName = reader.IsDBNull(1) ? 
                            null : reader.GetString(1);
                        student.LastName = reader.IsDBNull(2) ? 
                            null : reader.GetString(2);
                        student.Birthday = reader.IsDBNull(3) ? 
                            null : (DateTime?)reader.GetDateTime(3);
                        student.Gender = reader.IsDBNull(4) ? 
                            null : reader.GetString(4);
                        list.Add(student);
                    }
                }

                GridView1.DataSource = list;
                GridView1.DataBind();
            }
        }
    }
}

public class StudentDTO
{
    public int StudentID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime? Birthday { get; set; }
    public string Gender { get; set; }
}

Tags: , , ,

SQL Server

About this blog

2010年5月にこのブログを立ち上げました。その後 ブログ2 を追加し、ここは ASP.NET 関係のトピックス、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  November 2021  >>
MoTuWeThFrSaSu
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345

View posts in large calendar