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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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; }
}

SQL Server の数値型は、文字列型と同様に、そのまま直接 LIKE 句を使ってあいまい検索ができるようです。下の画像を見てください。UnitPrice 列は money 型ですが、[UnitPrice] LIKE '%2%' という条件が有効になっています。

知ってました？　実は何を隠そう自分は最近まで知らなかったです。(汗)　数字型はまず文字列型に変換してから、それに LIKE 句を使うものだと思ってました。

調べてみると、Microsoft のドキュメント「LIKE (Transact-SQL)」に、

"引数が文字列データ型でない場合、SQL Server データベース エンジン は可能であれば引数を文字列データ型に変換します。 If any one of the arguments isn't of character string data type, the SQL Server Database Engine converts it to character string data type, if it's possible."

・・・と書いてあります。実際に試してみると、上の画像のように money 型の UnitPrice 列も LIKE 句を使ってあいまい検索ができました。

Microsoft のドキュメントが言う「可能であれば」がどこまでの範囲か調べ切れていませんが、自分が SQL Server 2012 で試した限りでは int 型と money 型は可能な範囲に入るようです。

ADO.NET + SqlClient を使った .NET Framework のアプリケーションでも同じことができます。パラメータ化も可能です。ただし、パラメータ化する場合は、パラメータは文字列型として扱う必要がありますが。

上の画像と同様な LIKE 句を使って検索を行う .NET Framework コンソールアプリのサンプルコードを以下に載せておきます。ADO.NET + SqlClient を使い、SQL 文はパラメータ化しています。

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

namespace ConsoleAppLIKE
{
    public class Product
    {
        public int ProductID { get; set; }

        public string ProductName { set; get; }

        // UnitPrice 列は NULL 可なので Nullable とした
        public decimal? UnitPrice { set; get; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            string connString = "接続文字列";
            string selectQuery =
                "SELECT [ProductID] ,[ProductName] ,[UnitPrice] FROM [Products] " +
                "WHERE [ProductName] LIKE N'%' + @ProductName + N'%' AND " +
                "[UnitPrice] LIKE N'%' + @UnitPrice + N'%'";

            string productName = "ch";
            string unitPrice = "2";            
            List<Product> productList = new List<Product>();

            using (var connection = new SqlConnection(connString))
            {
                using (var command = new SqlCommand(selectQuery, connection))
                {
                    var p1 = new SqlParameter("@ProductName", SqlDbType.NVarChar);
                    p1.Value = productName;

                    // UnitPrice 列は money 型だが LIKE 句を使ってあいまい検索
                    // する場合はパラメータの型は文字列とする
                    var p2 = new SqlParameter("@UnitPrice", SqlDbType.NVarChar);
                    p2.Value = unitPrice;

                    command.Parameters.Add(p1);
                    command.Parameters.Add(p2);

                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var product = new Product
                            {
                                ProductID = reader.GetInt32(0),
                                ProductName = reader.GetString(1),

                                // UnitPrice 列は NULL 可なのでその対応
                                UnitPrice = reader.IsDBNull(2) ?
                                            null : (decimal?)reader.GetDecimal(2)
                            };

                            productList.Add(product);
                        }
                    }
                }
            }

            foreach (Product p in productList)
            {
                Console.WriteLine($"PriductID: {p.ProductID}, " +
                    $"ProductName: {p.ProductName}, UnitPrice: {p.UnitPrice}");
            }
        }
    }
}

サンプルコード中のコメントにも書きましたが、UnitPrice 列は money 型ですが LIKE 句を使ってあいまい検索する場合はパラメータの型は文字列とする必要がありますので注意してください (例えば、SqlDbType.NVarChar を SqlDbType.Deciaml にするとエラーになります)。

上のコードの実行結果は以下の通りで、上の画像の SSMS での実行結果と同じになっています。

Microsoft のサンプル SQL Server データベース Northwind の Employees テーブルの Photo フィールドの画像データを取得して表示する方法を書きます。

アプリから Photo フィールドのバイト列データを取得するのは ADO.NET + SqlClient を使って可能です。問題はそのバイト列を加工して jpg とか png 形式にすることで、それをどうしたらよいかという話です。

Photo フィールドの画像データは Bitmap 形式のバイト列なのですが、ネットの記事 Displaying images in SAPUI5 received from the Northwind OData service によると "The 78 superfluous bytes are a proprietary OLE header that Access creates when saving bitmaps." という余計なものが付いています。（Microsoft のドキュメントもそのような情報があったのですが行方不明）

実際、Photo フィールドのバイト列を取得してそれをそのまま stream に変換し、new Bitmap(stream) で Bitmap オブジェクトを取得しようとすると ArgumentException がスローされます。下のサンプルコードでコメントアウトした部分を見てください。

ということで、Photo フィールドのバイト列を stream に変換するとき最初の 78 バイトをスキップしてやる必要があります。それは MemoryStream(Byte[], Int32, Int32) コンストラクタを使って可能です。

以下にそのサンプルコードを載せておきます。上の画像の Photo を表示するのに使った HTTP ジェネリックハンドラで、Employees テーブルの Photo データを取得して、最初の 78 バイトをスキップして MemoryStream を作り、new Bitmap(stream) で Bitmap オブジェクトを取得し、Bitmap.Save メソッドで jpg 形式にして HTTP 応答ストリームに書き込んでいます。

using System.Web;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Drawing;
using System.Drawing.Imaging;

namespace WebApplication1
{
    public class EmployeePhotoHandler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            string id = context.Request.QueryString["id"];
            context.Response.ContentType = "image/jpeg";
            byte[] phote;
            int employeeId;
            if (!string.IsNullOrEmpty(id) && int.TryParse(id, out employeeId))
            {
                var connString = WebConfigurationManager
                    .ConnectionStrings["NORTHWINDConnectionString"]
                    .ConnectionString;
                var query = "SELECT photo FROM Employees WHERE EmployeeID = @ID";
                using (var connection = new SqlConnection(connString))
                {
                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@ID", employeeId);
                        connection.Open();
                        phote = (byte[])command.ExecuteScalar();
                    }
                }

                // これはダメ
                // new Bitmap(stream) で ArgumentException がスローされる
                // "使用されたパラメーターが有効ではありません。"
                //using (var stream = new MemoryStream(phote))
                //{
                //    using (var bitmap = new Bitmap(stream))
                //    {
                //        bitmap.Save(context.Response.OutputStream, ImageFormat.Jpeg);
                //    }
                //}

                // これは OK
                // The 78 superfluous bytes are a proprietary OLE header that Access
                // creates when saving bitmaps. ・・・ということで最初の 78 バイトは
                // スキップしないと有効な Bitmap 形式とはみなされないらしい
                using (var stream = new MemoryStream(phote, 78, phote.Length - 78))
                {
                    using (var bitmap = new Bitmap(stream))
                    {
                        bitmap.Save(context.Response.OutputStream, ImageFormat.Jpeg);
                    }
                }
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}