データベーススペシャリスト試験 2016年 午後1 問03

キーワード・論点整理

• 「(3) テーブル又はビューのアクセス権限は,ユーザID, ロールに付与される。」
• 「(4) ロールは,ユーザID に付与され, 別のロールにも付与されることがある。」
• セキュリティ要件②：「部門長は、部下がアクセスできる全ての情報にアクセスできる。」
• ロール階層を使って，「営業部ロール(B10)」→「営業１課ロール(B11)」「営業２課ロール(B12)」という親子関係を構築

解答がこのようになる理由

1. 営業１課ビューへのアクセス権限を持つロールは「B11」
2. 営業２課ビューへのアクセス権限を持つロールは「B12」
3. 部門長(L氏)は「部下がアクセスできる全ての情報にアクセスできる」ため、
   自分のユーザID(E111)に直接 B11,B12 を付与するのではなく、
   「営業部ロール(B10)」に対して B11,B12 を付与し、
   L氏には既に付与している B10 を通じて間接的に権限を得る

• a = B11
• b = B12
• c = B10

GRANT ROLE B11, B12 TO B10;
GRANT SELECT ON 営業１課ビュー TO B11;
GRANT SELECT ON 営業２課ビュー TO B12;

受験者が誤りやすいポイント

• ユーザID への直接付与 vs ロールへの付与
  セキュリティ要件②から，部門長(E111)へ直接 B11,B12 を付与しても要件は満たせますが，​
  「ロールは別のロールにも付与される」という仕様(4)を活かすことで管理性を高めています。
• SQL アの対象(c)を E111 と誤認しない
  「GRANT ROLE … TO ＜ユーザID＞」と思い込みやすいですが，文脈上「ロールに他のロールを付与」する操作です。

試験対策として覚えておくべきポイント

1. ロール階層の活用
   ロールにロールを付与すると，下位ロールの権限をまとめて上位ロールに持たせられる。
2. GRANT ROLE の構文
   • ユーザIDにも，ロールにも付与可能
   • 付与先が「ユーザID」の場合も「ロール」の場合も同じ構文
3. セキュリティ要件から設計を読み取る
   「部門長は部下と同じ権限を持つ」とある場合，ロール階層を用いることで運用負荷を下げる工夫が必要
4. 問題文の仕様(1)～(4)を正確に引用し，意図を把握する訓練
   問題文中の「ロールは別のロールにも付与される」などの一文は，解答の鍵になります。

キーワードと論点整理

解答がこの順序になる理由

1. ロールの定義は最初
   RDBMS の仕様より，「ロールは，ユーザID に付与され，別のロールにも付与されることがある」 (仕様(4)) ため，まずロール自体を作成しておかないと，後続の GRANT 文がエラーになります。
   → (オ) → (カ) → (キ)
2. ユーザへのロール付与
   次に，「ロールは，ユーザID に付与」(仕様(4)) することで，社員がそのロールに紐づく権限を獲得します。
   • 課長や部下社員に，それぞれ属するロール（B10, B11, B12）を付与
     → (イ) → (ウ) → (エ)
   • 続いて「部門長は，部下がアクセスできる全ての情報にアクセスできる」 (セキュリティ要件②) を実現するために，部門長 E111 に部下課のロールを追加付与します。
     → (ア)
3. ビューへの SELECT 権限付与
   セキュリティ要件③「社員にはビューを使わせ，テーブルには直接アクセスさせない」に基づき，各ロールに対して対応するビューの SELECT 権限を付与します。
   → (ク) → (ケ)

(オ) → (カ) → (キ) → (イ) → (ウ) → (エ) → (ア) → (ク) → (ケ)

受験者が誤りやすいポイント

1. ロール作成順を後回しにする
   「GRANT ROLE … TO …」を先に書いてしまうと，対象のロールが未定義でエラーになります。
2. ビュー権限付与とロール付与の順序混同
   ビューへの GRANT SELECT は「ロールに対して」付与するので，ロール自体を作成し，さらにユーザにロールを与えたあとに行うのが自然です。
3. 部門長へのロール付与漏れ
   部門長が下位ロールを持たないと，“部下がアクセスできる情報”をすべて参照できず，セキュリティ要件②を満たせません。

試験対策として覚えておくべきポイント

• ロールと権限付与の基本フロー
  1. ロールを CREATE ROLE
  2. ロールに権限（テーブル／ビューへの SELECT, INSERT…）を GRANT
  3. ユーザID や他のロールにそのロールを GRANT ROLE
• 役割階層の実装
  部門長など上位者に，下位ロールをまとめて付与することで，権限を継承させる設計パターンを押さえる。
• SQL 文のエラー回避
  • ロール未作成のまま GRANT を実行するとエラー
  • 存在しないユーザID に権限を付与するとエラー

1. キーワード・論点の整理

• 内部結合（INNER JOIN／JOIN）
  ２つのテーブルの「共通するキー値を持つ行のみ」を取り出す結合。
• 外部結合（LEFT OUTER JOIN／LEFT JOIN）
  左側のテーブルのすべての行を保持し、右側に対応する行がなければNULLを補う結合。
• “NULL チェック”による絞り込み（アンチ結合）
  LEFT OUTER JOIN 後に WHERE 右側の結合キー IS NULL とすることで、「右側に対応行がない行」を抽出する手法。

2. なぜその解答になるのか

照会1 社員が過去に登録した訪問予定のうち, その社員が予定日に訪問しなかった顧客の顧客番号, 顧客名, 社員番号及び訪問予定日を出力する (表3の SQL3 を参照)。

1. 「訪問予定」に登録された行のみ をベースにする
2. そのうち，「訪問実績」に対応する行が存在しないもの を抽出する

• (d) 訪問予定を取り込む際，「訪問予定の行のみ」を残すには 内部結合 (INNER JOIN) を使います。
• (e) 「対応する訪問実績がないものを抽出」するには，
  • まず LEFT OUTER JOIN で訪問実績を結合し，
  • WHERE HJ.訪問実施日 IS NULL として「訪問実績が存在しない行」を絞り込みます。

SELECT
  K.顧客番号,
  K.顧客名,
  HY.社員番号,
  HY.訪問予定日
FROM
  顧客 K
  INNER JOIN 訪問予定 HY
    ON K.顧客番号 = HY.顧客番号
  LEFT OUTER JOIN 訪問実績 HJ
    ON HY.顧客番号   = HJ.顧客番号
   AND HY.社員番号   = HJ.社員番号
   AND HY.訪問予定日 = HJ.訪問実施日
WHERE
  HJ.訪問実施日 IS NULL;

3. 誤りやすいポイント

4. 試験対策として覚えておくべきポイント

1. 内部結合 vs. 外部結合
   • 「両者に存在するデータだけ」を使う → INNER JOIN
   • 「片方にしかないデータ」も扱いたい → OUTER JOIN
2. アンチ結合のパターン
   • LEFT OUTER JOIN ... WHERE 右側の列 IS NULL
   • 対応する行がないもの（除外したい条件）を簡潔に表現できる。
3. 実務／試験での注意
   • 結合の向き（LEFT vs. RIGHT）を誤らない
   • NULL 判定の位置（WHERE か ON か）で結果が変わる
   • 結合順序と絞り込み順序を意識してSQLの実行結果をイメージする

1. 模範解答のキーワード・論点整理

HAVING COUNT(*) > ?

2. なぜその解答になるのか（問題文引用で論理展開）

年初からの訪問回数が N 回以上の社員 … ここで，N は実行時に与えられ，SQL 文の動的パラメタの ? に設定される

F さんは … SQL 文を簡単にするために，それぞれビューを使うことにした。

HAVING COUNT(*) >= ?

3. 受験者が誤りやすいポイント・ひっかけ

4. 試験対策まとめ

1. VIEW 定義は 静的 SQL
   • バインド変数（?, :1 など）は書けない
   • 変化する条件は、ビューの外側で再 SELECT する
2. “動的 SQL” と “静的 SQL” の違い
   • アプリ側で値を差し替える → 動的
   • 一切パラメタを持たない → 静的（ビュー向き）
3. HAVING 句は 集計結果に対するフィルタ
   • ビュー側で固定閾値を書くのは可
   • プレースホルダは不可
4. 試験のひっかけ
   • “?” を日付リテラルや列名と混同しない
   • プレースホルダを消すだけ／別値に置換する誤答に注意
5. 抜け・漏れチェック
   • CREATE VIEW で使う SELECT は ORDER BY 句を含まないか（製品依存）
   • GROUP BY 列と選択リストの整合を確認する

キーワードと論点整理

• ビュー（社員別訪問回数ビュー）を利用して集計結果を取得する
• 動的パラメータ ? による検索条件の適用
• 集計結果（訪問回数）に対しての絞り込みはビュー外で WHERE を使う

解答に至る論理的説明

1. 【問題文】のSQL4では、次のように社員ごとの訪問回数を集計し、HAVING で動的パラメータ ? 以上の社員を抽出しています。

   「HAVING COUNT(*) ≥ ?」

   SELECT S.社員番号, S.社員名, COUNT(*) 訪問回数
     FROM 社員 S
     INNER JOIN 訪問実績 HJ ON S.社員番号 = HJ.社員番号
    WHERE HJ.訪問実施日 >= ISODATE('2016-01-01')
   GROUP BY S.社員番号, S.社員名
   HAVING COUNT(*) >= ?
   

2. 小問(2)では、同じ集計結果をあらかじめ定義したビュー 社員別訪問回数ビュー(社員番号, 社員名, 訪問回数) から取得することが求められています。
   • ビュー定義の中で既に GROUP BY と COUNT(*) AS 訪問回数 を行っているため、呼び出し側（SQL5）では再度 GROUP BY は不要です。
   • 結果として、ビュー外では集計済みの列 訪問回数 に対して 通常のWHERE句 で絞り込みを行います。
3. したがって、SQL5の空欄には、

   「HAVING COUNT(*) ≥ ?」 に対応させた条件として、
   訪問回数 ≥ ?
   を記述します。

4. 模範解答のとおり、SQL5は以下のように完成します。

   SELECT 社員番号, 社員名, 訪問回数
     FROM 社員別訪問回数ビュー
    WHERE 訪問回数 >= ?
   

受験者が誤りやすいポイント

試験対策として覚えておくべきポイント

1. ビューと集計
   • ビュー定義内で GROUP BY + COUNT(*) などを行った場合、呼び出し側のSQLでは集計関数を使わず、通常の列として扱う。
2. WHERE句とHAVING句の使い分け
   • WHERE句：集計前の行フィルタリングに用いる（集計関数は使えない）。
   • HAVING句：GROUP BY後のグループ単位の絞り込みに用いる。
3. 動的パラメータ?の位置
   • 集計済みの列に対する比較条件は、ビュー外ではWHERE句で指定する。
4. ビューを活用したSQLの簡素化
   • 複雑なJOINやGROUP BYはビューにまとめ、呼び出し側ではシンプルなSELECT文で済ませることで、可読性と保守性を向上できる。

キーワード・論点整理

• チームメンバテーブル：セキュリティ要件強化で新設されたテーブル
• 登録項目：部門番号、チーム番号、社員番号、担当開始日、担当終了日
• 主キー：同一社員が同一チームに複数期間所属できるため、担当開始日を含む複合キー
• 設計要件引用：
  • 「営業課内のチームの社員ごとに, 担当開始日及び担当終了日を設定した行を登録する」
  • 「当該テーブルへのアクセス権限…を課長に与え…」

解答の導出過程

1. 【問題文】の〈セキュリティ要件の強化〉において、
   「(1) 営業支援システムに, 新たに “チームメンバ” テーブルを追加する。…
   (b) 営業課内のチームの社員ごとに, 担当開始日及び担当終了日を設定した行を登録する。」
   とあることから、テーブルのカラムは以下の5つであることがわかります。
   • 部門番号
   • チーム番号
   • 社員番号
   • 担当開始日
   • 担当終了日
2. 主キー設定の考え方
   • 「同じチームの社員ごとに 期間を区切って 登録できる」ため、
     担当開始日を含めなければ過去と現在の重複を区別できません。
   • 担当終了日は後から変更したりNULLを許容したりするため、
     主キーから外すのが一般的です。
3. 以上より、主キーは複合キーとして
   部門番号＋チーム番号＋社員番号＋担当開始日
   の4列を選択します。

テーブル構造（解答例）

主キー：部門番号, チーム番号, 社員番号, 担当開始日

受験者が誤りやすいポイント

• 担当終了日を主キーに含める
  →終了日は変更・NULL許容のため、一意性維持に不向き
• チーム番号だけを主キーにする
  →同一チーム内の複数社員や同一社員の複数期間を区別できない
• サロゲートキー（自動採番）を要求されたと誤解
  →設問は「構造を示せ」とあるため、与えられた要件の列と複合キーを答える

試験対策ポイント

• 複合主キーの設計基準
  • 期間管理が必要な履歴テーブルは日時を主キーに含める
  • NULL許容列は主キーに含めない
• 設問文の要件を正確に読み取る
  • 「～ごとに…行を登録する」の表現から必須カラムを抽出
  • 主キー設定の理由付けに「設計要件」を引用して解答
• テーブル設計の基本
  • 意味のあるユニーク性を保つ複数列の組み合わせ
  • 更新や変更の要件から主キー／ユニーク制約を再検討

キーワードと論点整理

• SQL6 の結合条件

  … 
  FROM 顧客 K 
    INNER JOIN チームメンバ T
      ON K.担当部門番号 = T.部門番号 
     AND K.チーム番号     = T.チーム番号
  WHERE T.社員番号 = CURRENT_USER 
    AND … 
  

  → T.社員番号 = CURRENT_USER を満たす行だけビューに現れる
• チームメンバ テーブル未登録者は結果に現れない
  → 部長・課長はチーム編成をシステム外で管理していたため、チームメンバ表に登録されておらず、SQL6 の条件を満たせない
• 対象社員（図1 より）

  社員番号	役割
  E111	営業部長
  E112	営業1課長
  E116	営業2課長

解答の論理的説明

1. ビュー定義（SQL6）の確認
   問題文より

   「CREATE VIEW 営業課ビュー AS … INNER JOIN チームメンバ T … WHERE T.社員番号 = CURRENT_USER …」
   とあり、

   • ビュー参照時には必ずチームメンバ表に現行ユーザの行が存在し、
   • 担当開始日 ≤ CURRENT_DATE かつ 担当終了日 ≥ CURRENT_DATE OR NULL の条件を満たす必要がある。
2. 登録漏れ社員の抽出
   図1 より、SQL6 実行時に閲覧権限を持つのは営業部長・各課長・各チームの社員であるが、
   • 営業部長（E111）
   • 営業1課長（E112）
   • 営業2課長（E116）
     は「チームメンバ」テーブルに行がまだ登録されていないため、
     T.社員番号 = CURRENT_USER の条件を満たさず、個人情報が取得できない。
3. よって解答は
   • 社員番号：E111, E112, E116

解決策（チームメンバ テーブルへの具体的行操作）

• 部長を全チームに、課長を各チームにメンバとして登録する行を INSERT する

全チームに部長を、各チームに課長をメンバ登録する。

受験者が誤りやすいポイント

• 「システム外で管理」「課長が登録操作可能」 の記述に注目し過ぎて、実際にチームメンバ表への登録漏れが起こることに気づかない。
• ビューで CURRENT_USER を使った場合、権限・ロールだけでなく主キー制約で存在しないと何も返さない点を見落としがち。

試験対策として覚えておくべき知識

1. ビューに CURRENT_USER や SESSION_USER を使う場合、必ずJOIN 先のテーブルに当該ユーザの行を用意する必要がある。
2. セキュリティ要件で「即時反映」を求める場合、RBAC（Role-Based Access Control）だけでなくデータ操作による行レベル制御も漏れなく設計する。
3. 複数階層の組織（部長・課長・メンバ）を扱う場合、最上位者も下位テーブルに登録しないとビューから漏れるリスクがあることを意識する。

1. 模範解答のキーワード・論点整理

• 「課長は，社員の行の担当終了日を更新することで直ちにアクセスを制限できる」
• 「課長は，DBA にロールの剥奪を 1 営業日前までに依頼する必要がない」

2. 解答の論理的な説明

1. セキュリティ要件④では，「個人情報にアクセスする必要がなくなった社員については，そのことを反映するためのアクセス制限を直ちに実施する」ことが求められています。
2. 対応案A（チーム別ロール）では，課長が社員を異動・解任するたびに「DBA にロールの付与・剥奪を 1 営業日前までに依頼」しなければならず，最短でも 1 日のタイムラグが発生します。
   • 問題文：「課長が 1営業日前までにデータベース管理者(DBA)に依頼する。DBAは…ロールの付与・剥奪を RDBMS に対して実施する。」
3. 対応案Bでは，「チームメンバ」テーブルの 担当終了日 を課長自身が随時更新でき，ビュー側では T.担当終了日 >= CURRENT_DATE OR T.担当終了日 IS NULL の条件によってアクセス権限を動的に判定します（SQL6）。
   • SQL6 抜粋：

     ... WHERE T.社員番号 = CURRENT_USER 
       AND T.担当開始日 <= CURRENT_DATE
       AND (T.担当終了日 >= CURRENT_DATE OR T.担当終了日 IS NULL)
     

   • この仕組みでは，課長が担当終了日を「前日までの日付」や「当日以前の日付」に書き換えれば，その瞬間から対象社員はビューに行を見られなくなります。
4. したがって，対応案Bは「直ちにアクセス制限を実施できる」とともに，「DBA への依頼が不要で運用負荷が低い」という２つの利点を同時に満たします。

3. 受験者が誤りやすいポイント

• 「CURRENT_USER」「CURRENT_DATE」などビルトイン関数を使った動的条件の意味を取り違える
  → ビュー内の条件とテーブル更新を組み合わせることで，権限変更の即時反映が可能になる点を押さえること。
• 対応案Aの手順だけを想像し，「ロールを剥奪すれば瞬時にアクセス制限できる」と考えてしまう
  → 問題文で「1営業日前までにDBAに依頼」という運用ルールが必ず介在する点を見逃さないこと。

4. 試験対策として覚えておくべきポイント

• ビューに組み込んだ日付・ユーザ判定条件と運用ルールを組み合わせることで，DBA 介在を不要にし，アクセス制御の即時性を高められる
• 「ロールによる権限付与・剥奪」は管理者（DBA）への依頼フローがボトルネックになる場合がある
• 動的ビューとジョイン条件を使った行レベルセキュリティ（Row‐Level Security）の考え方
• CURRENT_USER／CURRENT_DATE といったビルトイン関数の役割と，アクセス制御への応用方法