応用情報技術者 2015年 春期 午後 問06
アクセスログ監査システムの構築に関する次の記述を読んで、設問1〜4に答えよ。
K社は、システム開発を請け負う中堅企業である。セキュリティ強化策の一つとして、ファイルサーバのアクセスログを管理するシステム（以下、ログ監査システムという）を構築することになった。
現在のファイルサーバの運用について、次に整理する。
・ファイルサーバの利用者はディレクトリサーバで一元管理されている。
・利用者には、社員、パートナ、アルバイトなどの種別がある。
・利用者はいずれか一つの部署に所属する。
・部署はファイルサーバを1台以上保有している。
・ファイルサーバ上のファイルへのアクセス権は、利用者やその種別、部署、操作ごとに設定される。
・操作には、読取、作成、更新及び削除がある。
・ファイルサーバ上のファイルに対して操作を行うと、操作を行った利用者の情報や操作対象のファイルの絶対パス名、操作の内容がファイルサーバ上にアクセスログとして記録される。
・ファイルサーバのフォルダごとに社外秘や部外秘などの機密レベルが設定されている。
ログ監査システムの機能を表1に、E-R図を図1に示す。
ログ監査システムでは、E－R図のエンティティ名を表名とし、属性名を列名として、適切なデータ型と制約で定義した関係データベースによって、データを管理する。
なお、外部キーには、被参照表の主キーの値が NULL が入る。
〔非営業日利用一覧表示機能の実装〕
非営業日利用一覧表示機能で用いる SQL 文を図2に示す。
なお、非営業日表の非営業年月日列には、K社の非営業日となる年月日が格納されている。
〔部外者失敗一覧表示機能の実装〕
部外者失敗一覧表示機能で用いる SQL 文を図3に示す。
なお、アクセスログ表の操作結果列には、ファイル操作が成功した場合には'S'が、失敗した場合には'F'が入っている。
〔アクセスログインポート機能の不具合〕
アクセスログインポート機能のシステムテストのために準備したアクセスログの一部が取り込めない、との指摘を受けた。テストで用いたアクセスログを図4に示す。このログは CSV 形式であり、先頭行はヘッダ、アの行は操作対象のファイルへの削除権限がない社員('USR001')が削除を試みた場合のデータ、イの行はディレクトリサーバにログオンせずにファイル更新を試みた場合のデータ、ウの行は存在しない利用者ID('ADMIN')を指定してファイル削除を試みた場合のデータである。アクセスログ表のデータを確認したところ、gの行のデータが表に存在しなかった。この問題を解消するために、①テーブル定義の一部を変更することで対応した。
設問1：
図1のE-R図中のa、bに入れる適切なエンティティ間の関連及び属性名を答え、E-R図を完成させよ。
なお、エンティティ間の関連及び属性名の表記は、図1の凡例に従うこと。
模範解答
a：→
b：フォルダパス名
解説
解答の論理構成
-
部署とサーバの関連
- 問題文には「部署はファイルサーバを1台以上保有している。」とあります。
- “1 つの部署” に対して “1 台以上のサーバ” が対応するので、E-R 図の表記では部署（親）→サーバ（子）の “1 対多” を矢印 “→” で示します。
- よって a には「→」を入れます。
-
機密管理に追加すべき属性
- 問題文には「ファイルサーバのフォルダごとに社外秘や部外秘などの機密レベルが設定されている。」とあります。
- “フォルダごと” を特定する情報が機密管理表に必要ですが、サーバ ID は既に存在しているため、残る識別子はフォルダの場所を示す文字列です。
- フォルダの位置を一意に示す最も一般的な属性は “フォルダパス名” です。
- よって b には「フォルダパス名」を入れます。
誤りやすいポイント
- 部署とサーバの矢印向き
“サーバが部署を保有している” と逆に読んでしまい、サーバ→部署と書いてしまうケースがあります。文章の主語・述語を正確に追うことが重要です。 - フォルダ識別子の粒度
“フォルダ名” だけだと同名フォルダがサーバ間で重複する恐れがあります。パスを含めた “フォルダパス名” でなければ一意性が担保できません。 - 属性とエンティティの混同
フォルダを独立エンティティにしたくなりますが、問題文は「フォルダごとに機密レベルが設定される」だけで CRUD や多対多関係は要求していません。ここでは属性で十分です。
FAQ
Q: “→” と “−→” の違いは何ですか？
A: 図の凡例では実線 “−” が 1 対 1、矢印 “−→” が 1 対 多を示します。今回 a は 1 対 多なので “→” を書きます。
A: 図の凡例では実線 “−” が 1 対 1、矢印 “−→” が 1 対 多を示します。今回 a は 1 対 多なので “→” を書きます。
Q: “フォルダパス名” ではなく “ファイルパス名” ではダメですか？
A: 機密レベルが設定される対象はファイルではなくフォルダ全体です。フォルダを一意に表す “フォルダパス名” が適切です。
A: 機密レベルが設定される対象はファイルではなくフォルダ全体です。フォルダを一意に表す “フォルダパス名” が適切です。
Q: フォルダパス名にキー制約は必要ですか？
A: サーバ ID ＋ フォルダパス名 の組で一意になるように一意制約を掛けておくと、同一サーバ内で重複登録を防げます。
A: サーバ ID ＋ フォルダパス名 の組で一意になるように一意制約を掛けておくと、同一サーバ内で重複登録を防げます。
関連キーワード: 関係データベース, エンティティ, 主キー, 外部キー, 正規化
設問2：
図2中のc、dに入れる適切な文字列又は式を答えよ。
なお、表の列名には必ずその表の別名を付けて答えよ。
模範解答
c：EXISTS
d：AC.操作年月日 = NS.非営業年月日
解説
解答の論理構成
-
目的の再確認
【問題文】には、非営業日利用一覧表示機能について「非営業日にファイル操作を行った利用者、操作対象、操作元の IP アドレス、操作日時などを一覧表示する機能」と明記されています。よって抽出条件は「操作が行われた日が非営業日表に登録されているか否か」です。 -
図2の SQL の構造把握SELECT AC.* FROM アクセスログ AC WHERE c (SELECT * FROM 非営業日 NS WHERE d)サブクエリが WHERE 句に置かれており、外側の表は アクセスログ AC、内側は 非営業日 NS です。典型的な相関サブクエリの形で、c にはサブクエリの“存在有無”を判定する述語が入ると推測できます。
-
d の導出
非営業日かどうかを判定したいので、アクセスログの「操作年月日」と非営業日の「非営業年月日」を比較します。これも【問題文】の「非営業日表の非営業年月日列には、K社の非営業日となる年月日が格納されている。」という記述から確定できます。
・外側列：AC.操作年月日
・内側列：NS.非営業年月日
比較演算子は等価（=）です。 -
c の導出
サブクエリが行を返すかどうかだけを判定したいので、EXISTS 述語が最適です。IN でも動作しますが「相関サブクエリ」である点と、パフォーマンスや NULL への安全性を考慮すると EXISTS が教科書的解答です。 -
結果
• c：EXISTS
• d：AC.操作年月日 = NS.非営業年月日よって模範解答と一致します。
誤りやすいポイント
- IN と書きたくなる
IN (SELECT NS.非営業年月日 …) でも論理的には正しいですが、問題文の空欄位置は述語全体を求めているため EXISTS が定石です。 - 別名の付与忘れ
指示に「表の列名には必ずその表の別名を付けて答えよ」とあるにもかかわらず、操作年月日 だけを書くと減点対象になります。 - 日時型の比較
AC.操作年月日 が日時（DATE/TIMESTAMP）で、NS.非営業年月日 が日付だけの場合、RDBMS により暗黙変換が発生します。実務では TRUNC や DATE() で日付部分を切り出す必要がある点に注意が必要です。
FAQ
Q: EXISTS と IN の性能差は大きいですか？
A: RDBMS のオプティマイザ次第ですが、相関サブクエリでは EXISTS の方が行存在確認に特化しており、NULL の扱いにも強いケースが多いです。
A: RDBMS のオプティマイザ次第ですが、相関サブクエリでは EXISTS の方が行存在確認に特化しており、NULL の扱いにも強いケースが多いです。
Q: 外側と内側の列が逆でも動きますか？
A: 相関サブクエリでは常に「外側列＝内側列」の形にするのが読みやすく、実装上のバグを防げます。逆順にしても真理値は変わりませんが、可読性が下がります。
A: 相関サブクエリでは常に「外側列＝内側列」の形にするのが読みやすく、実装上のバグを防げます。逆順にしても真理値は変わりませんが、可読性が下がります。
Q: 非営業日が連休の場合でも問題なく抽出できますか？
A: はい。EXISTS は行の存在を確認するだけなので、連休など複数行がヒットしても 1 回の存在判定で真になります。
A: はい。EXISTS は行の存在を確認するだけなので、連休など複数行がヒットしても 1 回の存在判定で真になります。
関連キーワード: 相関サブクエリ, EXISTS, 日付比較, 外部表記, 別名指定
設問3：
図3中のe、fに入れる適切な文字列又は式を答えよ。
なお、表の列名には必ずその表の別名を付けて答えよ（eとfは順不同）。
模範解答
e：AC.操作結果 = 'F'
f：US.部署ID <> SV.部署ID
解説
解答の論理構成
-
功能要求の把握
表1には「部外者失敗一覧表示」について、
「他部署のファイルサーバ上のファイルへの操作のうち、その操作が失敗した利用者…を一覧表示」
と明記されています。ここから
・操作が“失敗”であること
・“他部署”のサーバに対する操作であること
の二条件で抽出する必要があると分かります。 -
“失敗”判定（e）
問題文には「アクセスログ表の操作結果列には、ファイル操作が成功した場合には'S'が、失敗した場合には'F'が入っている。」とあるため、失敗を示す値は 'F' です。
アクセスログ表には別名 AC が付いているので、
sql AC.操作結果 = 'F'が e を満たします。 -
“他部署”判定（f）
部外者＝利用者の所属部署とファイルサーバの所属部署が異なるケースを指します。
図3の FROM 句では
sql INNER JOIN 利用者 US ON AC.利用者ID = US.利用者ID INNER JOIN サーバ SV ON AC.サーバID = SV.サーバIDとあり、利用者表 US とサーバ表 SV の両方に 部署ID 列が存在します。これらが一致しないものを抽出すれば「他部署」と判定できます。したがって
sql US.部署ID <> SV.部署IDが f に入ります。 -
順不同で良い理由
WHERE 句内の条件は論理積（AND）で結合されているため、e と f の記述順は結果に影響しません。
誤りやすいポイント
- “失敗”を 'F' ではなく 'S' と誤記する
- 利用者とサーバの 部署ID を比較せず、US.部署ID IS NOT NULL など無関係な条件を書いてしまう
- 別名を忘れて 操作結果 = 'F' や 部署ID <> 部署ID のように曖昧な列参照をする
FAQ
Q: <> と != のどちらを使っても良いですか？
A: ANSI SQL では <> が正式な不等号です。試験では可読性を優先し <> を用いるのが無難です。
A: ANSI SQL では <> が正式な不等号です。試験では可読性を優先し <> を用いるのが無難です。
Q: 部外者判定で JOIN を LEFT JOIN にする必要はありませんか？
A: アクセスログに存在しない利用者やサーバは一覧表示の対象外なので、INNER JOIN で十分です。
A: アクセスログに存在しない利用者やサーバは一覧表示の対象外なので、INNER JOIN で十分です。
Q: 条件をサブクエリで書いても減点されますか？
A: 機能的に正しくても、設問は「e、f に入れる」単純条件を求めています。意図とずれると採点対象外になる可能性があります。
A: 機能的に正しくても、設問は「e、f に入れる」単純条件を求めています。意図とずれると採点対象外になる可能性があります。
関連キーワード: WHERE句, INNER JOIN, 不等比較, ログ監査, フィルタ条件
設問4：〔アクセスログインポート機能の不具合〕について、(1)、(2)に答えよ。
（1）本文中のgに入れる適切な文字をア〜ウの中から選んで答えよ。
なお、アクセスログ中の空文字('')はデータベースにNULLとしてインポートされる。
模範解答
g：ウ
解説
解答の論理構成
- インポート対象の CSV には、行ア・イ・ウの 3 レコードがあります。
- 行アは利用者 ID が 'USR001' であり、社内の正規ユーザなので該当レコードは利用者表に存在します。
- 行イは利用者 ID が空文字（''）です。問題文には「なお、外部キーには、被参照表の主キーの値が NULL が入る。」と明記されており、インポート時に空文字は NULL へ変換されます。NULL は参照整合性チェックの対象外なので、この行は取り込めます。
- 行ウは利用者 ID が 'ADMIN' ですが、これはディレクトリサーバ（利用者表）に登録されていない ID です。外部キー列に実在しない値を挿入すると参照整合性制約違反となり、インポートに失敗します。
- したがって「アクセスログ表のデータを確認したところ、gの行のデータが表に存在しなかった。」に該当するのは行ウであり、模範解答どおり
g：ウ
となります。
誤りやすいポイント
- 「空文字は取り込めない」と思い込み、行イを選んでしまう。外部キー列は NULL を許容すると明示されています。
- 行アで操作が失敗（'F'）しているためインポートされないと誤解する。参照整合性とは無関係です。
- 利用者 ID だけでなく操作名や IP アドレスを疑って本質を見落とす。原因は利用者 ID の存在有無です。
FAQ
Q: 操作結果が 'F' のレコードはインポート対象外ですか？
A: いいえ。成功か失敗かはアプリケーション側の値であり、データベースへの登録可否とは無関係です。
A: いいえ。成功か失敗かはアプリケーション側の値であり、データベースへの登録可否とは無関係です。
Q: 外部キー列を NULL 許容にした場合、参照整合性は保たれますか？
A: NULL は「値が未定義」という扱いなので整合性チェックの対象外です。実在しない値を入れなければ問題ありません。
A: NULL は「値が未定義」という扱いなので整合性チェックの対象外です。実在しない値を入れなければ問題ありません。
Q: 行ウを取り込むにはどのような対応が必要ですか？
A: 'ADMIN' を利用者表に登録するか、インポート時に利用者 ID を NULL に変換するなど、外部キー制約を満たす値に変更する必要があります。
A: 'ADMIN' を利用者表に登録するか、インポート時に利用者 ID を NULL に変換するなど、外部キー制約を満たす値に変更する必要があります。
関連キーワード: 外部キー, 参照整合性制約, NULL値, CSVインポート, データベーススキーマ
設問4：〔アクセスログインポート機能の不具合〕について、(1)、(2)に答えよ。
（2）本文中の下線①の対応内容を、35字以内で述べよ。
模範解答
アクセスログ表の利用者ID列に定義された参照制約を削除する。
解説
解答の論理構成
-
取り込みに失敗した行の存在
- 本文には「アクセスログ表のデータを確認したところ、gの行のデータが表に存在しなかった。」とあります。したがって “g” 行は アクセスログ表 へ登録できずに落ちています。
-
落ちた理由の推定
- “g” 行は「存在しない利用者 ID('ADMIN')を指定してファイル削除を試みた場合のデータ」と説明されています。
- もし アクセスログ表 の 利用者ID 列に外部キー制約があり 利用者 表に 'ADMIN' が無ければ、参照整合性違反で INSERT はエラーになります。
-
解決方針の導出
- ログ監査システムでは “監査目的” で操作ログを欠損なく保管することが最優先です。利用者マスタに存在しない ID でもログは残したい。
- そこで「①テーブル定義の一部を変更する」で最も合理的なのは、利用者ID と 利用者 表との参照制約を外し、値の存在可否にかかわらず登録できるようにすることです。
-
以上より
- 解答は「アクセスログ表の利用者ID列に定義された参照制約を削除する。」となります。
誤りやすいポイント
- “外部キーでも NULL は入れられるから大丈夫” と早合点し、'ADMIN' のような“不存在値”で失敗する点を見落とす。
- 参照制約を削除すると検索性能やデータ品質が落ちると考え、別解（利用者マスタへ仮ユーザを追加など）を選んでしまう。
- 問われているのは「対応内容」であり、原因説明だけを書いてしまう。
FAQ
Q: 参照制約を残したまま “存在しない ID” を許可する方法はありますか？
A: 一般的な RDB では不可能です。外部キー制約は NULL または被参照表に存在する値 だけを許します。したがって制約を解除するか、仮レコードを用意するしかありません。
A: 一般的な RDB では不可能です。外部キー制約は NULL または被参照表に存在する値 だけを許します。したがって制約を解除するか、仮レコードを用意するしかありません。
Q: 監査ログで参照制約を外すと検索が遅くなりませんか？
A: 検索性能はインデックスで確保できます。参照制約は整合性の仕組みであり、インデックスとは独立です。監査テーブルでは整合性より完全な保存を優先することが多いです。
A: 検索性能はインデックスで確保できます。参照制約は整合性の仕組みであり、インデックスとは独立です。監査テーブルでは整合性より完全な保存を優先することが多いです。
Q: 参照制約を削除した後、利用者名を表示したい場合はどうしますか？
A: JOIN 時に LEFT OUTER JOIN を使えば、利用者マスタに一致がない行でもアクセスログ側の行を欠落させずに表示できます。
A: JOIN 時に LEFT OUTER JOIN を使えば、利用者マスタに一致がない行でもアクセスログ側の行を欠落させずに表示できます。
関連キーワード: 参照整合性, 外部キー制約, 監査ログ, データインポート
