e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第119回「WEBアプリ作成(17)(アクセスログを表示する)」

2015.02.26

こんにちは。インストラクターの蓑島です。

前回(第118回)はデータベーストリガーを使って、特定の表を更新する際に更新履歴を自動的に監査表(ログ表)に記録する処理を作成しました。この処理はデータベーストリガーなので、それが有効である限り、誰がどのような手段で対象となる表を更新しても、必ず監査表に記録されるわけです。では今回はこの監査表の内容をWEBアプリで表示します。

監査表は以下の定義でしたね。

SQL> SHOW USER
ユーザーは"SCOTT"です。

1
2
3
4
5
6
7
8
SQL> DESC AUDIT_TABLE
  名前                                      NULL ?    型
  ----------------------------------------- -------- ----------------------------
  OPER_DATE                                          DATE              -- いつ、
  OPERATOR                                           VARCHAR2(100)     -- 誰が、
  TABLE_NAME                                         VARCHAR2(100)     -- どの表の
  KEY_VALUE                                          VARCHAR2(100)     -- どの行に対して、
  DETAIL                                             CLOB              -- どのような操作をしたか

前回作成したデータベーストリガーにより、SCOTT.EMP2表にDML(INSERT,UPDATE, DELETE)を行えば、必ずこの監査表に更新履歴が記録されます。

例えば、社員表(SCOTT.EMP2)の更新画面を開いたときに、その社員レコードの今までの更新履歴がその画面に表示されていれば便利ですね。早速そのように修正します。更新画面は、バックナンバー第110回で作成した、EMP2_UPDATE_FORMプロシージャです。このプロシージャを以下のように修正します。

(注意)以下のソースコードはブラウザ表示のために、山括弧(「<」と「>」)を全角にしています。
コピーして実行する方は、必ず、すべての山括弧(「<」と「>」)を半角に変換してください

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
create or replace
PROCEDURE EMP2_UPDATE_FORM (P_EMPNO IN VARCHAR2)
     IS
       V_DEPT  VARCHAR2(1000);
       REC     EMP2%ROWTYPE;   -- 該当レコードを読み込むレコード変数
       V_ERRM  VARCHAR2(1000);
     BEGIN
     /*******************************************/
     /*-部門のリストを生成する                  */
     /*******************************************/
       V_DEPT  := '<SELECT NAME="P_DEPTNO"><OPTION VALUE="NULL">-</OPTION>' ;
       FOR REC IN ( SELECT * FROM DEPT ORDER BY DEPTNO) LOOP
           V_DEPT := V_DEPT || '<OPTION VALUE="' ||TO_CHAR(REC.DEPTNO) || '">' ||
                                      REC.DNAME || '</OPTION>' ;
       END LOOP;
       V_DEPT := V_DEPT || '</SELECT>' ;
     /*******************************************/
     /*-更新対象の行を取得する                  */
     /*******************************************/
       SELECT * INTO REC FROM EMP2 WHERE EMPNO = TO_NUMBER(P_EMPNO);
     /*******************************************/
     /*-部門リストの該当値を選択された状態にする*/
     /*******************************************/
       V_DEPT := REPLACE (V_DEPT, 'VALUE="' ||TO_CHAR(REC.DEPTNO) || '"' ,
                                'VALUE="' ||TO_CHAR(REC.DEPTNO) || '" SELECTED' );
     -- ここから画面表示処理
       HTP.P( '<HTML>' );
       HTP.P( '<HEAD><TITLE>社員更新</TITLE></HEAD>' );
       HTP.P( '<BODY>' );
       HTP.P( '<H1>社員更新</H1>' );
       HTP.P( '<HR>' );
       HTP.P( '<FORM ACTION="emp2_update_exe" METHOD="POST">' );
       HTP.P( '<TABLE BORDER>' );
     -- 社員番号は主キーなので表示のみで更新対象外とした(パラメータも値もなし)
       HTP.P( '<TR><TD BGCOLOR="SILVER">社員番号</TD>' ||
                 '<TD><FONT COLOR="GRAY">' ||TO_CHAR(REC.EMPNO) || '</FONT></TD></TR>' );
     /*******************************************/
     /*-社員名をセット                          */
     /*******************************************/
       HTP.P( '<TR><TD BGCOLOR="SILVER">社員名</TD>' ||
       '<TD><INPUT TYPE="TEXT" NAME="P_ENAME" VALUE="' ||REC.ENAME || '"></TD></TR>' );
     /*******************************************/
     /*-入社日をセット                          */
     /*******************************************/
       HTP.P( '<TR><TD BGCOLOR="SILVER">入社日(YYMMDD)</TD>' '
            <TD><INPUT TYPE="TEXT" NAME="P_HIREDATE" VALUE="' ||
                             TO_CHAR(REC.HIREDATE, 'YYMMDD' ) || '">' || '</TD></TR>' );
     /*****************************************************************/
     /*-部門番号は上記のリスト(V_DEPT)で該当の値がSELECTEDになっている*/
     /*****************************************************************/
       HTP.P( '<TR><TD BGCOLOR="SILVER">部門</TD><TD>' ||V_DEPT || '</TD></TR>' );
     /***********************************************/
     /*-TEXTエリアタグで囲まれた範囲がセットされた値*/
     /***********************************************/
       HTP.P( '<TR><TD BGCOLOR="SILVER" COLSPAN="2">備考</TD></TR>' );
       HTP.P( '<TR><TD COLSPAN="2"><TEXTAREA NAME="P_NOTE" rows="10" cols="70">' ||
                                  REC.NOTE ||
                                 '</TEXTAREA></TD><TR>' );
       HTP.P( '</TABLE>' );
     /*******************************************************************/
     /*-社員番号は更新処理で必要なので、非表示のパラメータとして送信する*/
     /*******************************************************************/
       HTP.P( '<INPUT TYPE="HIDDEN" NAME="P_EMPNO" VALUE="' ||P_EMPNO || '">' );
       HTP.P( '<INPUT TYPE="SUBMIT" VALUE="送信">' );
       HTP.P( '</FORM>' );
     /********************************************************************/
     /*  更新履歴情報の表示処理     ▼▼▼ ここから ▼▼▼             */
     /********************************************************************/
       HTP.P( '<HR>' );                -- 水平線
       HTP.P( '<TABLE BORDER>' );
       HTP.P( '<TR><TD>いつ</TD><TD>だれが</TD><TD>どうした</TD></TR>' );
       FOR REC IN ( SELECT * FROM AUDIT_TABLE
                   WHERE TABLE_NAME = 'EMP2'  AND
                         KEY_VALUE = P_EMPNO ORDER BY OPER_DATE) LOOP
           HTP.P( '<TR><TD>' ||TO_CHAR(REC.OPER_DATE, 'YY/MM/DD HH24:MI:SS' -- いつ
              || '</TD><TD>' || REC.OPERATOR                                  -- だれが
              || '</TD><TD>' ||REC.DETAIL || '</TD></TR>' );                    -- どうした
       END LOOP;
     /********************************************************************/
     /*  更新履歴情報の表示処理     ▲▲▲▲ ここまで ▲▲▲           */
     /********************************************************************/
       HTP.P( '</TABLE>' );
       HTP.P( '</BODY>' );
       HTP.P( '</HTML>' );
   EXCEPTION
       WHEN OTHERS THEN
            V_ERRM := SQLERRM;
            HTP.P(V_ERRM);
   END EMP2_UPDATE_FORM;

上記は指定された社員(パラメータ P_EMPNO ソースコード2行目)のレコードを読み込んだ状態の更新画面を生成するプロシージャで、バックナンバー110回のときに作成したものです。今回はこのソースコードにその社員レコードのそれまでの更新履歴を表示する処理を追加しました。(69行目~78行目) これにより更新画面が開いた時点で、その社員レコードのそれまでの更新履歴情報が画面の下部に表示されます。

この追加した処理(69行目~78行目)では、監査表(AUDIT_TABLE)内のこの社員の更新履歴情報をカーソルFORループ文で取得しながら、HTMLのTABLEタグを使い表形式で表示させています。このカーソルFORループ文のSELECT文のWHERE句(73~74行目)に注目すると、TABLE_NAME列の値が'EMP2'であり、KEY_VALUE列の値が、P_EMPNO(社員番号)なので、まさにこの指定された社員番号を対象に更新履歴情報を取得していることがわかります。

これでプログラムの修正が終わったので、早速、新規の社員レコードを登録し、次に更新して、履歴情報の表示を検証してみましょう。

検証のためにはSCOTTユーザ以外でも、データ操作をする必要があります。

本メルマガのこのシリーズ(WEBアプリ作成)では、すべて、サンプルユーザのSCOTTでWEBアプリのシステムを構築していますので、基本的にはSCOTTユーザでログインして操作します。しかし、バックナンバー第117回「WEBアプリ作成(15) 複数ユーザで共有する方法」の回で、PAC_EMPパッケージだけは、USER01ユーザでも操作が可能なように、権限とパブリックシノニムを設定しました。このパッケージには、社員レコードの登録処理である、EMP2_INSERT_FORMと、EMP2_INSERT_EXEプロシージャが格納されております。

ですから、新規の社員レコードの登録はUSER01ユーザで行って、その社員レコードの更新はSCOTTユーザで行いましょう。これより、「だれが、いつ、どのような操作を・・」という検証がしやすくなりますね。

早速、社員レコードの登録です。いままで本メルマガの通りにお手元の環境で構築をされている方は以下のようにブラウザから、URLを入力してください。

http://localhost:8080/dad/pac_emp.emp2_insert_form

ログイン画面が出てきますので、user01ユーザでログインします。テストですからなにか適当な内容で社員登録します。
(スクリーンショットは、バックナンバー117回を参照)

その結果、私の環境では、社員番号 7944で登録されました。

今度は、SCOTTユーザでログインします。そのためには、ホスト名を指定して違うPCからアクセスするか、あるいは同じPCを使うなら、以下のURLを入力していったんログオフし、その後ブラウザを閉じて再度開いてください。(logmeoffは事前定義されたコマンドでありログオフ処理をします)

実際にやってみます。まずこのURLです。
http://localhost:8080/dad/logmeoff

これにより、ログオフされるので、ブラウザをいったん閉じて再度、開きます。

次にSCOTTユーザで更新です。私の場合、新規の社員番号は7944だったので、以下のURLです。

http://localhost:8080/dad/emp2_update_form?p_empno=7944
再びログインを求められますので、scottユーザでログインします。

そして画面の項目を適当に変更して送信ボタンをクリックし、更新処理をしてください。そのうえで、もう一度同じURLで更新画面を開きます。

http://localhost:8080/dad/emp2_update_form?p_empno=7944

そうすると、以下のような画面が開きます。

119-1

ご覧のように、この社員レコードの画面の下部に「いつ、だれが、どうした」という情報があります。
これを見ると、最初に USER01ユーザがこの社員レコードを登録(INSERT)し、次にSCOTTユーザが更新(UPDATE)をしたことがわかりますね。さらにINSERT時の各列の値や、UPDATEでどの列をどの値からどの値へ更新したのかもわかります。これらの更新履歴情報は前回(第118回)作成したデータベーストリガーがセットしたものです。それを更新画面の生成プロシージャ(emp2_update_form)で該当社員の分だけを表示しているわけです。

日時の情報も含めて更新履歴情報が正しいことがわかりますね。これでWEBアプリで表示する目的を達しました。

いかがですか? データベーストリガーが自動的に記録した更新履歴レコードをそれが必要なWEBアプリケーションで表示しているわけですね。全体の流れがつかめると思います。

なお、この監査表は特定の表に依存しないフォーマット(つまりEMP2表だけでなく他の表でも利用可能)なので、本当は更新履歴の表示処理(上記ソースコードでいえば、69行目~78行目)を共通のプロシージャにして、それをコールする形にしておけば便利です。すなわち、将来他の表で、同様のアクセスログ表示の要件があった場合にそのプロシージャをコールするように修正すればよいので対応しやすくなります。(もちろん、その表にEMP2表と同じようなデータベーストリガーは必要です)

また、当然ではありますが、更新履歴の表示方法は今回解説した方法だけが唯一の方法ではありません。プログラミングはそれが可能でさえあれば、後は応用次第なので、他の方法でももちろん可能です。しかし、データベースの更新履歴ということでは、おそらくデータベーストリガーを使う今回のような方法は、実装しやすいお勧めの方法であると言えます。

しかし課題もあります。データベーストリガーはSELECT文では起動しないので、問い合わせや参照(SELECT)の記録を残したい場合には使えません。ですから、そのような要件がある場合は、SELECTに対応したWEBアプリから、監査表に直接記録する必要があります。例えば、バックナンバー第104回「キーを指定して1レコードを表示する」のプロシージャ内で、「いつ、誰が、どの表の、どの行に対して、問い合わせ(SELECT)したのか・・」といった情報を監査表に直接記録(INSERT)する処理を追加することで対応できます。いろいろと試してみてください。

それでは、今回はここまでにいたします。参考になれば幸いです。
次回ご期待ください。

先頭へ戻る