平成27年春期試験午後問題　問6

問6　データベース

⇱問題PDF
アクセスログ監査システムの構築に関する次の記述を読んで，設問1～4に答えよ。
広告
　K社は，システム開発を請け負う中堅企業である。セキュリティ強化策の一つとして，ファイルサーバのアクセスログを管理するシステム(以下，ログ監査システムという)を構築することになった。

　現在のファイルサーバの運用について，次に整理する。
  • ファイルサーバの利用者はディレクトリサーバで一元管理されている。
  • 利用者には，社員，パートナ，アルバイトなどの種別がある。
  • 利用者はいずれか一つの部署に所属する。
  • 部署はファイルサーバを1台以上保有している。
  • ファイルサーバ上のファイルへのアクセス権は，利用者やその種別，部署，操作ごとに設定される。
  • 操作には，読取，作成，更新及び削除がある。
  • ファイルサーバ上のファイルに対して操作を行うと，操作を行った利用者の情報操作対象のファイルの絶対パス名，操作の内容がファイルサーバ上にアクセスログとして記録される。
  • ファイルサーバのフォルダごとに社外秘や部外秘などの機密レベルが設定されている。

　ログ監査システムの機能を表1に，E-R図を図1に示す。
pm06_1.gif
　ログ監査システムでは，E-R図のエンティティ名を表名にし，属性名を列名にして，適切なデータ型と制約で表定義した関係データベースによって，データを管理する。
　なお，外部キーには，被参照表の主キーの値かNULLが入る。

〔非営業日利用一覧表示機能の実装〕
　非営業日利用一覧表示機能で用いるSQL文を図2に示す。
　なお，非営業日表の非営業年月日列には，K社の非営業日となる年月日が格納されている。
pm06_2.gif
〔部外者失敗一覧表示機能の実装〕
　部外者失敗一覧表示機能で用いるSQL文を図3に示す。
　なお，アクセスログ表の操作結果列には，ファイル操作が成功した場合には'S'が，失敗した場合には'F'が入っている。
pm06_3.gif
〔アクセスログインポート機能の不具合〕
　アクセスログインポート機能のシステムテストのために準備したアクセスログの一部が取り込めない，との指摘を受けた。テストで用いたアクセスログを図4に示す。このログはCSV形式であり，先頭行はヘッダ，アの行は操作対象のファイルへの削除権限がない社員('USR001')が削除を試みた場合のデータ，イの行はディレクトリサーバにログオンせずにファイル更新を試みた場合のデータ，ウの行は存在しない利用者ID('ADMIN')を指定してファイル削除を試みた場合のデータである。
　アクセスログ表のデータを確認したところ，gの行のデータが表に存在しなかった。この問題を解消するために，①テーブル定義の一部を変更することで対応した。
pm06_4.gif
広告

設問1

図1のE-R図中のa，bに入れる適切なエンティテイ間の関連及び属性名を答え，E-R図を完成させよ。
　なお，エンティティ間の関連及び属性名の表記は，図1の凡例に倣うこと。

解答例・解答の要点

a：→
b：フォルダパス名

解説

〔aについて〕
部署エンティティとサーバエンティティには共通の属性"部署ID"があります。この属性"部署ID"は、部署エンティティ側では主キーであり、部署を一意に特定します。また、サーバエンティティ側では外部キーであり、本文中のファイルサーバの運用の箇条書きには「部署はファイルサーバを1台以上保有している。」とあるため、サーバエンティティには同一の部署IDの値を持つ複数のレコードが存在しえるということがいえます。したがって、部署エンティティとサーバエンティティのカーディナリティ（多重度）は1対多であり、[a]には「→」が当てはまります。

原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティは、主キー側を"1"、外部キー側を"多"とした「1対多」になります。
pm06_5.gif
∴a＝→

〔bについて〕
本文中のファイルサーバの運用の箇条書きには「ファイルサーバのフォルダごとに社外秘や部外秘などの機密レベルが設定されている」とあります。機密管理エンティティは、サーバごとフォルダごとに存在することになりますが、属性としてはサーバを特定するサーバIDしかありません。よって、フォルダに関する情報を属性に含める必要があります。

ここで「フォルダ名」とする解答案が考えられますが、単なる「フォルダ名」では、"/aaa/bbb"と"/ccc/bbb"などのように別の階層に存在する同一名称のフォルダの区別がつかないため、フォルダを正確に示すには（絶対）フォルダパスでなければなりません。問題文中には、アクセスログには「操作対象のファイルの絶対パス名」が記録されると説明されています。アクセスログとフォルダの機密レベルを紐付けるには、やはりフォルダのパス名が必要です。したがって、[b]には「フォルダパス名」などが当てはまります。

∴b＝フォルダパス名
広告

設問2

図2中のc，dに入れる適切な字句又は式を答えよ。なお，表の列名には必ずその表の別名を付けて答えよ。

解答例・解答の要点

c：EXISTS
d：AC.操作年月日 = NS.非営業年月日

解説

〔c、dについて〕
図2のSQL文では、アクセスログ表から操作年月日が非営業年月日であるレコードを抽出することになります。非営業年月日の一覧は非営業日表に存在していますが、アクセスログ表と非営業日表には関連がありません。このように結合ができない表同士において、ある表のある列の値が、別の表のある列に存在するかを確認したいときには、IN句で副問合せを記述する方法またはEXISTS句を用いた相関副問合せによる方法により判定することができます。

副問合せのSQL文を見ると、SELECT句で「*」が指定されています。IN句を使う場合には特定列の値のリストが返されるようになっていなければならないので、ここでは、レコード自体の存在自体を確認するEXISTS句を用いた相関副問合せを行っていることがわかります。したがって[c]には「EXISTS」が当てはまります。

相関副問合せを実現するには、副問合せの部分のWHERE句で「主問合せの表の列名 = 副問合せの表の列名」を指定することでします。エイリアス指定の際のASキーワードは省略可能であり、今回は主問合せの表（アクセスログ表）のエイリアスが"AC"、副問合せの表（非営業日表）のエイリアスが"NS"なので、[d]には「AC.操作年月日 = NS.非営業年月日」が当てはまります。

∴c＝EXISTS
　d＝AC.操作年月日 = NS.非営業年月日

なお、本問でEXISTS句を用いた相関副問合せではなく、IN句で副問合せを記述する方法を用いると次のようになります。
SELECT AC.*
FROM アクセスログ AC
WHERE AC.操作年月日 IN
　(SELECT 非営業年月日 FROM 非営業日 NS)
広告

設問3

図3中のe，fに入れる適切な字句又は式を答えよ。なお，表の列名には必ずその表の別名を付けて答えよ。

解答例・解答の要点

e：AC.操作結果 = 'F' ※順不同
f：US.部署ID <> SV.部署ID ※順不同

解説

〔e、fについて〕
部外者失敗一覧表示は「他部署のファイルサーバ上のファイルへの操作のうち、その操作が失敗したもの」を一覧表示する機能なので、抽出対象となるアクセスログのレコードは以下の2つの条件を満たすものです。
  • 他部署のファイルサーバへのアクセスである
  • ファイル操作が失敗している
この2つをWHERE句の内容として記述することになります。

まず1つ目の「他部署のサーバへのアクセス」ですが、操作を行った利用者の所属する部署とサーバを保有する部署が異なるレコードを選択すればよいと判断できます。このSQL文では、アクセスログ表（AC）、利用者表（US）およびサーバ表（SV）を結合しているので、操作を行った利用者の所属する部署は「US.部署名」で、操作対象となったサーバを保有する部署は「SV.部署名」で参照できます。よって、この2つが異なるときに真となる「US.部署ID <> SV.部署ID」が適切です。

2つ目の「ファイル操作が失敗していること」については、問題文中に「アクセスログ表の操作結果列には，ファイル操作が成功した場合には'S'が，失敗した場合には'F'が入っている。」とあるので、アクセスログ表の操作結果列が'F'になっているレコードを選択すればよいと判断できます。アクセスログ表のエイリアスは"AC"なので、「AC.操作結果 = 'F'」という式が適切です。

∴e、f＝AC.操作結果 = 'F'、US.部署ID <> SV.部署ID（順不同）
広告

設問4

〔アクセスログインポート機能の不具合〕について，(1)，(2)に答えよ。
  • 本文中のgに入れる適切な文字をア～ウの中から選んで答えよ。なお，アクセスログ中の空文字('')はデータベースにNULLとしてインポートされる。
  • 本文中の下線①の対応内容を，35字以内で述べよ。

解答例・解答の要点

  • g：ウ
  • アクセスログ表の利用者ID列に定義された参照制約を削除する (29文字)

解説

  • アクセスログ表の利用者ID列は、利用者表の利用者ID列を参照する外部キーです。外部キーには参照先の列に存在しない値が存在してはいけないという参照制約（外部キー制約）があります。つまり、アクセスログ表の利用者ID列に挿入可能な値は、利用者表の利用者ID列にすでに存在している値かNULLということになります。これは問題文中でも「外部キーには，被参照表の主キーの値かNULLが入る」と説明されています。

    〔アクセスログインポート機能の不具合〕には「ウの行は存在しない利用者ID('ADMIN')を指定してファイル削除を試みた場合のデータである」と記述されています。存在しない利用者IDを使った操作なので、この操作は失敗し、アクセスログ表に失敗の操作ログが記録されることになります。しかし、'ADMIN'は利用者表の利用者IDに存在しない値ですので、アクセスログ表の利用者ID列に挿入しようとしても、参照制約違反となり挿入することができません。このため「ウ」の操作ログがアクセスログ表に記録されることはありません。よって、確認時に存在しなかった操作ログは「ウ」です。

    ∴g＝ウ

  • テーブル定義の変更は(1)の問題を解消するための対応です。整合性維持の観点から考えると、通常は参照制約違反となる値を許すことはありませんが、今回はアクセスログという特性上、不正な操作を含め全ての操作を記録しなくてはならないので、利用者表の利用者ID列に存在しない値をも受け入れるような設計が求められます。

    行の挿入に失敗するのは参照制約が原因なのですから、アクセスログ表の利用者ID列の参照制約（REFERENCES制約）を削除すれば、この問題を解消できます。ちなみに、参照制約を削除するにはALTER TABLE文＋DROP文を使います。

    ∴アクセスログ表の利用者ID列に定義された参照制約を削除する
広告
模範解答

前の問題次の問題

▲
Pagetop