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

  • 質問

  • 皆様

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

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

     

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

    どなたかこの対処方法をご存知でしたらおしえてください。
    2007年7月20日 0:33

回答

  •  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());
            }
        }
    }

     

     

    2007年7月21日 8:36

すべての返信

  •  sp_setapprole は呼んでますか?

     

    アプリケーション ロール

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

    2007年7月20日 4:25
  • Salmiakki-san

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

     

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

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

     

    で、もう一つ

    sp_upsetapproleを使用したいので、

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

    sp_setapproleでfCreateCookie=true;

    cookie=cookiedata;

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

     

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

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

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

     

    2007年7月20日 4:48
  •  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());
            }
        }
    }

     

     

    2007年7月21日 8:36
  • Salmiakkiさん

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

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

     

    2007年7月22日 14:50
  • 解決したと思っていたら

    実際やってみると

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

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

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

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

     

    2007年7月23日 1:30
  • 上の方に私がポストしたコードは動きましたか?

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

    2007年7月23日 2:43
  •  

    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();

     

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

     

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

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

    2007年7月23日 3:37
  •  

    またまた、ミスです。

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

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

     

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

    2007年7月23日 4:15