UPDATE権限がオブジェクト「テーブル名」データベース「データベース名」スキーマdboで拒否されました

Question

SQL Server2008 R2 ExpressとAccess2010で開発しています。

更新可能なレコードセットに転結したフォームを作成したいのですが、読み込んだデータを更新しようとすると
“UPDATE権限がオブジェクト「テーブル名」データベース「データベース名」スキーマdboで拒否されました”
と怒られます。

Accessにはデータベースロール db_denydatawriterに属するhogeでログインしています。
処理の流れはVBA内でユーザdbo ( sa ) で接続を作成し、その接続でselect,update,insertをユーザhogeにgrantし、
currentproject.connectionを利用して更新可能なレコードセットを作成し、それをフォームのレコードセットにセットしています。

権限がちゃんとgrantされているかどうかはManagementStuidioのユーザのセキュリティー保護可能なリソースで確認しています。
この権限をgrantしてと言う処理は他の部分でも利用しており問題なく動作しています。

# 現時点では問題に気づいていないだけかも知れませんが

ただ、フォームのソースに使用するという処理はこれが最初なので、この現象がSQLサーバ側なのかAccess側なのかも解らず困っております。
ただ、Accessにsaでログインすると更新できたりするのでSQLサーバ側での権限設定のような気もでするのですが、前述のManagementStudioで確認した内容と矛盾するし…

あと、関係ないかも知れませんがレコードセットのCursorLocationプロパティーをサーバに設定すると読み込みすらできないのでクライアントに設定しています。サーバー側に設定できないのも解らないのですがこの辺りも少し気になります。

どこを調べたら良いのかご教授頂けませんでしょうか。よろしくお願いします。

 

Answer 1

該当するデータベースのセキュリティー、ユーザ、dboと進んでプロパティーを選択して表示される全般ダイアログで[
所有されているスキーマ」と言うのがありあますが、guest以外のすべてのスキーマにグレーアウト？したチェックが入っています。
何かヒントにならないでしょうか？

ちなみに自習書と付属のスクリプトを利用して確認作業をやってみたのですが、特に問題なく更新も挿入も選択も可能でした。
違いは上に書いた通り、guestユーザ以外はどのユーザにも「所有されているスキーマ」にチェックが入っていなかった事です。

こいつが怪しいような気がするのですが、どうしてグレーアウトしたチェックを消せば良いのでしょうか…

Answer 2

oeameu3sngpさん　こんにちは。
VBAからDB接続した場合と直接DBにSQLを発行した場合の違いなどがはっきりしていないので、断定的なことは言えませんが、
hogeをdb_denydatawriterのメンバから外せば、更新ができるのではないでしょうか。

MSDNの(http://msdn.microsoft.com/ja-jp/library/ms191291.aspx)のページの『権限チェック アルゴリズムの概要』に以下の文章が記述されています。
--------------------------------------------------------------------------------------
権限のチェックは複雑な場合があります。権限チェック アルゴリズムには、グループ メンバーシップの重複、所有権の継承、明示的および暗黙的な権限が含まれます。また、セキュリティ保護可能なエンティティを含むセキュリティ保護可能なクラスに対する権限の影響を受けることもあります。アルゴリズムの一般的な手順では、関連する権限がすべて収集されます。ブロックする DENY が見つからない場合、十分なアクセス権を付与する GRANT が検索されます。アルゴリズムには、不可欠な要素が 3 つあります。セキュリティ コンテキスト、権限領域、および必要な権限です。
--------------------------------------------------------------------------------------

この記述を見る限り、GRANTされている権限よりも先に、DENYでブロックされている権限をチェックしているので、db_denydatawriterロールによるブロックがgrantに勝ってしまい、
更新が拒否されているように思えます。

Answer 3

tenmantenさんこんにちは。

まさに仰る通りでした。ユーザーhogeをdb_denydatawriterのメンバーから外したら無事動作しました。
自習書にもあったように、denyはgrantに優先するという原則が適用されているのでしょうか…

# なんか今後も権限に関しては同様の問題に悩まされる様な予感がします。

いずれにせよ、お陰様でスッキリしました。DBが壊れたのかと思い、最初からやり直そうかと考え始めていたので大変助かりました。
有難うございます。

あと、以下、ちょっと長くなりますが、お許し下さい。

ご教授頂いたページの下の方の「アルゴリズムの一般的な手順」の4を見ると、ロールに権限を与えると、そのロールの属しているユーザにも継承？されそうに思えたので、試してみたのですが、「特別なロールに対する権限を許可、拒否、または取り消すことはできません」と怒られ、あっけなく失敗に終わりました。

Access側でテーブルを表示させたくないという思いがあるのと、ユーザはどこかのロールに属する必要があるとの思い込みから、da_denydatawriterロールに属させていたのですが、a_denydatawriterから外してもテーブルは表示されませんでした。フロントエンドにAccess adpを利用して、テーブルを表示させない方法を考える？のに悩んだので、「目からウロコ」でした。

最後に、「所有されているスキーマ」の変更ですが、セキュリティー、ロール、データベースロールと進んでスキーマと同じ名前のロールに所有者を変更することができました

どうも有難うございました。

Answer 4

oeameu3sngpさん 追加情報ありがとうございます。
「特別なロールに対する権限を許可、拒否、または取り消すことはできません」
↑　↑　↑
この件ですが、おそらく固定データベースロールに対して権限を追加しようとしたのではないでしょうか。
ユーザーが自作したロールへの権限追加であれば、このエラーは発生しないと思われます。
固定データベースロールについては以下のページなどに説明がありますので、参考までにお知らせします。

http://msdn.microsoft.com/ja-jp/library/ms189612.aspx

Answer 5

tenmantenさんたびたびお世話になります。

> おそらく固定データベースロールに対して権限を追加しようとしたのではないでしょうか。

またもやご明察通りです。
db_denydatawriterにgrantしようとしました。苦笑

> ユーザーが自作したロールへの権限追加であれば、このエラーは発生しないと思われます。

帰宅後、ふと同様の事に思いつき試してみようと思っていました。
先ほど、試してみたところバッチリでした。
ピントはずれかも知れませんが“ユーザ自作ロールの存在意義のひとつ”かと理解しました。

またもや、少し長くなりますがお許し下さい。
最初の投稿で、grantとrevokeを使って同じような処理をして動作していると書きましたが、改めてコードを見直すと dbo権限で更新追加の可能なレコードセットを作成して処理おりました。汗
一時テーブルの処理部分とごっちゃになっていたようです。申し訳ございませんでした。