アプリケーションロールを使用した実行

Question

皆様

アプリケーションロールを使用しないで、ストアドプロシージャを実行すると問題なく処理されることを確認して、アプリケーションロールを使用して、実行できるかの確認を行っております。

ところが、以下のようなエラーが出ていまして、セキュリティの問題であると思い、対象のアプリケーションロールにdb_datawriterやdboなどほとんどの権限を与えてみましたが、ダメでした。

 

{System.Data.SqlClient.SqlException: EXECUTE 権限がオブジェクト 'ストアド名称'、データベース 'DB名称'、スキーマ 'dbo' で拒否されました。

どなたかこの対処方法をご存知でしたらおしえてください。

Answer 1

 sp_setapprole は呼んでますか？

 

アプリケーション ロール

http://technet.microsoft.com/ja-jp/library/ms190998.aspx

Answer 2

Salmiakki-san

そのあたりは、問題ないです。

 

そうこうしているうちに自己解決しました。

一部の権限が欠落していたのが原因でしたすみません。

 

で、もう一つ

sp_upsetapproleを使用したいので、

(byte[] _cookiedata = new byte[8000])

sp_setapproleでfCreateCookie=true;

cookie=cookiedata;

を設定しているのですが、cookieに値がもどってきてくれません。

 

プログラムからストアドをコールしているのですが、プログラムでは、OUTパラメータを意識する必要はないと思いますので、

なにが悪いのかがわかりません。

どなたかおしえてください。

 

Answer 3

Trading Freedom さんからの引用

プログラムからストアドをコールしているのですが、プログラムでは、OUTパラメータを意識する必要はないと思いますので、

 

この部分がよくわからないのですが、軽く試したら動いたのでコードを載せておきます。C# のコンソール アプリケーションです。

 

 

Code Snippet

 

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

namespace AppRoleTest
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString;
            connectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDB;Data Source=(local)";
           
            SqlConnection cn = new SqlConnection(connectionString);
            SqlCommand cmd1 = new SqlCommand(); // sp_setapprole
            SqlCommand cmd2 = new SqlCommand(); // select ステートメント用
            SqlCommand cmd3 = new SqlCommand(); // sp_unsetapprole

            // sp_setapprole の準備
            cmd1.CommandText = "sp_setapprole";
            cmd1.CommandType = CommandType.StoredProcedure;
            SqlParameter paramRolename = cmd1.Parameters.Add("@rolename", SqlDbType.VarChar, 20);
            SqlParameter paramPassword = cmd1.Parameters.Add("@password", SqlDbType.VarChar, 20);
            SqlParameter paramFCreateCookie = cmd1.Parameters.Add("@fCreateCookie", SqlDbType.Bit);
            SqlParameter paramCookie = cmd1.Parameters.Add("@cookie", SqlDbType.VarBinary, 8000);
            paramCookie.Direction = ParameterDirection.Output;
            paramRolename.Value = "MyAppRole";
            paramPassword.Value = "fdsd896#gfdbfdkjgh700mM";
            paramFCreateCookie.Value = 1;

            // 接続を開いて SP を実行する
            cmd1.Connection = cn;
            cn.Open();
            cmd1.ExecuteNonQuery();

            // コンテキストが変わったか確認してみる
            cmd2.Connection = cn;
            cmd2.CommandText = "SELECT USER_NAME()";
            Console.WriteLine(cmd2.ExecuteScalar().ToString());

            // コンテキストを元に戻してみる
            // sp_unsetapprole の準備
            cmd3.Connection = cn;
            cmd3.CommandText = "sp_unsetapprole";
            cmd3.CommandType = CommandType.StoredProcedure;
            cmd1.Parameters.Remove(paramCookie);    // cmd1 (sp_setapprole) から paramCookie をはずして
            paramCookie.Direction = ParameterDirection.Input;   // パラメータの方向を INPUT にして
            cmd3.Parameters.Add(paramCookie);   // こっち (sp_unsetapprole) に付け替える
            cmd3.ExecuteNonQuery();

            // コンテキストが戻ったか確認してみる
            Console.WriteLine(cmd2.ExecuteScalar().ToString());
        }
    }
}

 

 

Answer 4

Salmiakkiさん

恥ずかしながら、あまりOUTパラメータを使ったことがなかったので、ParameterDirection.Outputを知りませんでした。

誤解が解けました。ありがとうございます。

 

Answer 5

解決したと思っていたら

実際やってみると

プロシージャまたは関数 'sp_unsetapprole' にはパラメータ '@cookie' が必要ですが、指定されませんでした。

というエラーが出てしまいます。

エラーの意味はわかりますが、なぜ、出るのかわかりません。

とうぜん、きちんと例の通りに書いています。

 

Answer 6

上の方に私がポストしたコードは動きましたか？

あのコードの "// sp_unsetapprole の準備" をしている箇所、特に右側にコメントしている３行の処理が正しく行われていないのでしょう。

Answer 7

 

Salmiakkiさん

以下の通り、ほとんどコピペして試しています。

Code Snippet

                SqlCommand cmdstart = new SqlCommand(); // sp_setapprole
                cmdstart.CommandText = "sp_setapprole";
                cmdstart.CommandType = CommandType.StoredProcedure;
                SqlParameter paramRolename = cmdstart.Parameters.Add("@rolename", SqlDbType.VarChar, 20);
                SqlParameter paramPassword = cmdstart.Parameters.Add("@password", SqlDbType.VarChar, 20);
                SqlParameter paramFCreateCookie = cmdstart.Parameters.Add("@fCreateCookie", SqlDbType.Bit);
                SqlParameter paramCookie = cmdstart.Parameters.Add("@cookie", SqlDbType.VarBinary, 8000);
                paramCookie.Direction = ParameterDirection.Output;
                paramRolename.Value = "XXX";
                paramPassword.Value = "XXX";
                paramFCreateCookie.Value = 1;
                cmdstart.Connection = (SqlConnection)dbcon;

 

この間に、トランザクション処理を行っております。 

 

Code Snippet

               

 

SqlCommand cmdend = new SqlCommand(); // sp_unsetapprole
                cmdend.Connection = (SqlConnection)dbcon;
                cmdend.CommandText = "sp_unsetapprole";
                cmdend.CommandType = CommandType.StoredProcedure;
                cmdstart.Parameters.Remove(paramCookie);    // cmd1 (sp_setapprole) から paramCookie をはずして
                paramCookie.Direction = ParameterDirection.Input;   // パラメータの方向を INPUT にして
                cmdend.Parameters.Add(paramCookie);   // こっち (sp_unsetapprole) に付け替える
                cmdend.ExecuteNonQuery();

 

エラーが出てしまいます。

 

もしかして、間の処理がトランザクション処理なのが原因でしょうか？

でも、エラーメッセージがおかしいような。

Answer 8

 

またまた、ミスです。

cmdstartでExecuteしていませんでした。

申し訳ございませんでした。

 

なんとかうまくいきました。ありがとうございました。