堀川です、こんにちは

以前私がボードリーダーを担当していたSQLServerユーザ
グループの、「ビギナー」のメーリングリストで、

[pml-begin,05778] ストアドでのログ出力方法について
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=1&current_page=1&disp_mode=0&detail_mode=1&message_id=20102

という質問が出ていました。

質問者は、トランザクションがロールバックする際に、ロールバック
した原因などの情報を、テーブルに書き込みたいという内容です。

BEGIN TRAN
       処理
       IF( 失敗したか？ )
             BEGIN
                         テーブルに失敗した原因を記録
                         ROLLBACK TRAN
                         どこかにジャンプ、または、呼び出し元に戻る
              END

このような雰囲気です。

ところが、失敗した原因をテーブルに記録する命令は、トランザクションを
構成しているので、当然ROLLBACKの対象に含まれ、何も記録することは
できません。

トランザクションが入れ子ではない、シンプルなものであれば、

BEGIN TRAN
       処理
       IF( 失敗したか？ )
             BEGIN
                    　   ROLLBACK TRAN
                         どこかにジャプ
              END

COMMIT TRAN
呼び出し元に戻る

ここにジャンプして来る：
      ROLLBACK した原因をテーブルに書き込む
      呼び出し元に戻る

のような対処もできるというアドバイスがあります。

[pml-begin,05779] Re: ストアドでのログ出力方法について
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=1&current_page=1&disp_mode=4&detail_mode=1&message_id=20103


しかし質問者は、トランザクションが入れ子構成（多重）になっており、
GOTOでジャンプしても、その部分はまだ外側のトランザクション内部
になっているので、それはできないという問題です。


SQLServerユーザグループの理事である河端氏が、

[pml-begin,05780] Re: ストアドでのログ出力方法について
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=1&current_page=1&disp_mode=0&detail_mode=1&message_id=20106

で、いくつかの解決方法が提案されていますが、イベントログに
書き込んだり、メッセージキューを使ったりしているので、その
書き込んだ情報を後からSQL文で調べるのが困難な方法
ばかりです。

テーブル変数を使った方法は良いと思うのですが、
SQLServer2000以上のバージョン依存になります。


バージョンに依存せず、書き込んだ情報をSQL文で後から
調査できるようにするための、一般的な方法は無いのでしょうか？
もし無ければ、ROLLBACKした後の障害復旧などの処理が出来なくて
とても困ったデータベースサーバーになってしまいます。

何か良いアドバイスがあるかな？とメーリングリストを見ていたのですが、
無かったようです。


以下にその方法を示します。

解決のポイントとなるのは、xp_cmdshell 拡張ストアドプロシージャを
使います。

ですから、セキュリティ上の配慮から、この拡張ストアドの実行を
禁止している場合は、許可にしなければいけません。


トランザクションの内部で、ログを記録したい部分で、

    master.dbo.xp_cmdshell  'osql  -E  -Q"テーブルに記録するSQL文"  '

を実行して、トランザクションの中で、osqlコマンドによる別のデータベース
への接続を作ることにあります。

xp_cmdshell が コマンドの標準出力結果をレコードセットにするので、
それを防ぐためには

    master.dbo.xp_cmdshell   'osql  コマンド'  ,  NO_OUTPUT

の書式を使います。


一般ログインユーザが xp_cmdshell を実行するときは、

    　　xp_sqlagent_proxy_account 拡張ストアドプロシージャ

で定義されたWindowsユーザとして、引数で与えられたコマンドが
実行されます。
SQLServer7.0では、SQLAgentCmdExecアカウントになります。

そこで、このWindowsユーザのOS上のセキュリティ権限をできる限り
狭めて定義しておくことが大事です。

Windows上のGuestユーザに近い権限、極端に言えば、osqlコマンドの
実行に支障が出なければ良いので、Windows上の他のコマンドは
実行出来なくするようなファイルパーミション設定をWindowsに登録します。
（セキュリティ上、問題にならないようなサーバーなら、ファイルパーミション
設定は省略しても良いでしょう）


xp_cmdshell で実行を行なうコマンドには、osqlコマンドを記述し、
その中に、テーブルへ記録するINSERT命令やUPDATE命令などを
書きます。

ROLLBACK管理情報記録テーブルへ書き込むストアドプロシージャを
準備しても良いでしょう。


またosqlコマンドは-Eスイッチを付けて、信頼関係接続で行なえば、
                    -U ログイン名   -P　パスワード
の記述が不要になり、セキュリティが良くなります。


信頼関係接続をするために、xp_sqlagent_proxy_account で定義した
Windowsユーザを、データベースサーバーに接続許可にして下さい。

また、osqlコマンド内に記述したクエリが実行できるように、対象と
なっているデータベースにユーザ登録を行い、INSERT命令などの
実行権限をこのユーザに与えておくことも忘れないで下さい。



次に、xp_cmdshell 拡張ストアドが実行できるように、masterデータベース
の拡張ストアドの実行権限を調整します。

一般的には、sysadmin以外は実行できないようにしていると思いますが、
一般ログインユーザがROLLBACKした原因をテーブルに記録するために
そのログインユーザに対して実行権限を与えなければいけません。

ログインユーザが何名で特定できるのであれば、そのログインユーザを
masterデータベースのデータベースユーザに登録し、そのデータベース
ユーザに対して、xp_cmdshell 実行権限を与えます。

ログインユーザが多すぎて特定できないのであれば、masterデータベースの
publicロールに実行権限を与えますが、このような場合は非常に危険なので、
Windowsのセキュリティ設定をしっかり登録して下さい。

逆に言えば、xp_cmdshellを実行するログインユーザの特定ができない
状況では（人数が多い場合など）、このような方法はセキュリティ上の
リスクになります。


xp_cmdshell 拡張ストアドプロシージャは、覚えておくと便利な側面が
いろいろありますが、セキュリティ対策をしっかり取る必要があります。

ぜひ、xp_cmdshell 拡張ストアドプロシージャをご活用下さい。



------------------------------------
Epata-IT/日本技術ソフト開発
  　    　堀川　明  (Akira Horikawa)
    ０１月１３日（金曜日）　１５時２０分記
        mailto:who@example.ne.jp
        http://www.horikawa.ne.jp/msde/