ICTSC Tech Blog

ICTSC2019 一次予選 問題解説まとめ

 /
カテゴリー

ICTSC2019 の一次予選に参加してくださった皆さん、お疲れ様でした。一次予選で出題した問題の解説記事をテーマ別にまとめました。これらのテーマの問題は、二次予選、本選でも出題される予定ですので解説記事を参考に対策していただければと思います。

コンテナ

コンテナが作れない

監視できない！

ウェブ

APIが飛ばないんですけど…

メール

メールは届いてるけど…

トンネリング

OpenVPNが急に繋がらなくなった！

パケットフィルタ

郷に入っては郷に従え

SSHできなくなっちゃった

ルーティング

接続が不安定になっちゃった

外に出れません・・。

IPv6

VyOSの設定が吹き飛んだ！

データベース

ストアドプロシージャってやつでなんとかして！！！！

ICTSC2019 一次予選 問題解説: ストアドプロシージャってやつでなんとかして！！！！

 /
カテゴリー

問題文

あなたはA社に今年入社した新入社員です。
A社は人手不足でAさんの他に上司のBさんしか情報システム部門にいません。
ある日突然Bさんが人事のCさんに、「簡単に指定のデータを取り出せるようにしてほしい」と言われました。
ですが、Bさんは次の日からバカンスに行く予定があったため、あなたに任せて出国してしまいました。
Bさんの代わりにCさんを助けてあげてください。

問1(q01)

  • 性別名が”男”の従業員のみを取得してください。
  • カラムは(employee_id, name, birthday, sex_name)を表示してください。

問2(q02)

  • 性別名が”女”で年齢が女性従業員の平均年齢以上の従業員のみを取得してください。
  • カラムは(employee_id, name, age, sex_name)を表示してください。
  • age(年齢)に関してはbirthdayから求めて答えてください。

問3(q03)

  • 部署も権限もすでに決まっている(NULL以外である)従業員のデータのみを取得してください。
  • カラムは(employee_id,name,age,department_name,permit_name)
  • age(年齢)に関してはbirthdayから求めて答えてください。

条件

Employees以外のテーブル内にあるカラム”〇〇_id”は今後内容が変更されることがあるので注意すること。

ゴール

問1,2,3で指定したデータを取得するクエリを、q01,q02,q03という名前でストアドプロシージャとして登録する。

情報

問題サーバー

  • IPアドレス: 192.168.0.1
  • ユーザー: admin
  • パスワード: 6pfmqje365Ed
  • DBユーザー: root
  • DBパスワード: 無し

解説・解答例

解説

　この問題は指定されたデータのみをストアドプロシージャを利用してCall (小問id)で簡単に呼び出せるようにしてあげるというものでした。
なので、最初に必要なデータを取得するためのクエリを用意しないといけません。
用意する際は以下の点に気をつけなければいけません。

  • 問1(q01)
    • 性別名が男なので、sexテーブルのsex_nameが男と一致してなければいけない
      • JOIN句を用いてsexテーブルとEmployeesテーブルを結合したあとにWhere句を用いてsex_nameが男のレコードのみを抽出する。
  • 問2(q02)
    • ageを求めるためにbirthdayから変換しないといけない
      • 現在の日付と誕生日の差を取り、その年数を求めることで年齢を求められる
  • 問3(q03)
    • q02と同様にageをbirthdayから求めないといけない
    • Join句を2回用いていなければいけない。
      • 順番はDepartmentsテーブルからでもPermitテーブルからのどちらからでも問題ない
  • 共通
    • クエリの内容でWHERE句とHAVING句を混同するなど動作が未定義な用法をしている場合は減点対象になっている

ここまでで用意したクエリをそれぞれプロシージャとして保存しなければいけませんでした。
クエリの最初にCREATE PROCEDURE [小問id]()をつけて実行することでそのクエリを保存することができます。
ここまでの作業を行ったうえでCall [小問id]();を実行することで簡単に指定したデータを取り出せるようになります。

解答例

問1(q01)

CREATE PROCEDURE q01() SELECT employee_id, name, birthday, sex_name FROM Employees LEFT OUTER JOIN Sex ON Employees.sex_id = Sex.sex_id WHERE sex_name = "男";

問2(q02)

CREATE PROCEDURE q02() SELECT employee_id, name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age, sex_name FROM Employees LEFT OUTER JOIN Sex ON Employees.sex_id = Sex.sex_id WHERE sex_name= "女" AND TIMESTAMPDIFF(YEAR, birthday, CURDATE())>= (SELECT AVG(TIMESTAMPDIFF(YEAR, birthday, CURDATE())) FROM Employees LEFT OUTER JOIN Sex ON Employees.sex_id = Sex.sex_id WHERE sex_name="女");

問3(q03)

CREATE PROCEDURE q03() SELECT employee_id,name,TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age,department_name,permit_name FROM Employees AS Emp JOIN Departments AS Dep ON Emp.department_id=Dep.department_id JOIN Permit AS Per ON Emp.permit_id=Per.permit_id;

講評

　今回は一次予選で最近出題が少なかったデータベースの問題ということで、どちらかというとオペレーションの簡略化とミス防止、ストアド・プロシージャという機能を知っているかということを意識して作問しました。
　問1～3まで通して、ストアドプロシージャという機能を使えるかということを中心に、問題文の条件にも書いてあるようにテーブル内のデータの増減や変更などに耐えられるクエリがかけているかということを中心に採点を行いました。
　今回の解答を見ている中で惜しいミスがいくつかあり、例えばsex_id=0など、Employeesテーブル以外のsex_idとsex_nameが今後変化することがあるかもしれないのに抽出条件に書いてしまっている解答が多く見られました。
また、解答では正答を書いているにも関わらず問題環境でオペレーションを行っていないチームが多数ありました。
こちらについては、問題の本質について理解しているということで各小問50%減点で採点をさせていただきました。
減点の理由としては問題の本質について理解していても、オペレーションの時間を他の問題に費やせていたかもしれないという考えからです。
　今回、採点をした感想としては上位層はかなり高い割合で問題を完答しており、DBの基本的なところがしっかり理解していそうだなと感じました。
今回出題した機能は、SQLの入門書の最後の方に出てくるかどうかという機能でした。
ストアドプロシージャ以外にもMySQLやMariaDBには様々な機能が備わっているのでぜひ調べて見てください！

ICTSC 2019 一次予選 問題解説: SSHできなくなっちゃった

 /
カテゴリー

問題文

踏み台サーバのテストをするためにLinuxの機能を用いていろいろいじっていたところ、SSHが繋がらなくなってしまった。
どうやらVNCはそのまま繋がるようなので、原因を特定して繋がるようにしてほしい。

ゴール

  • SSHで接続できる

情報

  • IPアドレス: 192.168.0.10
  • ユーザー名: admin
  • パスワード: tWuVEsPiCLiP
  • VNCパスワード: tWuVEsPiCLiP

解説

/etc/hosts.deny によって他のホストからの接続が制限されていることが原因。

/etc/hosts.deny の中身を消すと接続できるようになる。 ncコマンドで確認しても見かけ上はopenになっているので注意。

/etc/hosts.allowでsshdや踏み台サーバのIPアドレスを許可することでSSHの接続を可能にする方法もある。ただし、IPアドレスで指定を行う場合は内部のネットワークアドレスである 192.168.0.10 を指定する必要がある。

VNCに接続する方法の一例としてsshやncコマンドを用いたポートフォワードが挙げられる。例えば下記のコマンドを実行すると、localhostの5901ポートにアクセスすることでVNCサーバーにつなげることができる。

ssh -L 5901:192.168.0.10:5901 [email protected][踏み台のIPアドレス]

ICTSC2019 一次予選 問題解説: Openvpnが急に繋がらなくなった！

 /
カテゴリー

問題文  

自宅に置いてあるサーバ同士でOpenVPNを貼っていてある通信をOpenVPN越しにやりとりしていたのだが、 ある日突如通信ができなくなってしまった。この問題の原因を究明し、OpenVPNを利用して通信がまたできるようにしてほしい。

問題構成

  • OpenVPN Server:

    • OS: Ubuntu 18.04
    • SSH用IPアドレス: 192.168.0.10
    • SSHユーザ: admin
    • SSHパスワード: tP6xqe3f
    • パッケージ
      • OpenVPN 2.4.4
      • OpenSSL 1.1.1
      • PKI CAとして easyrsa3.0.6 を利用している
        • /easy-rsa-3.0.6 以下のディレクトリにある
        • CAのパスワードは ictsc-ca-pass で設定されている

  • OpenVPN Client:

    • OS: Vyos 1.1.8
    • SSH用IPアドレス: 192.168.0.20
    • SSHユーザ: admin
    • SSHパスワード: tP6xqe3g

  • 通信要件

    • OpenVPN Server/Client 共にeth1のインタフェースからトンネルを貼っている
    • OpenVPN Serverにはループバックインタフェース(172.25.0.1/32)があり、OpenVPN ClientはVPN越しにこのループバックのアドレスに通信ができること

問題のゴール状態  

OpenVPN ClientはVPN越しにこのOpenVPN Serverのループバックのアドレスに通信ができること

トラブルの概要  

証明書の期限が切れていて、通信ができなくなってる

解答例  

この問題ではまずなぜ通信ができなくなったかを確認する必要があります。 問題文には

ある日突如通信ができなくなってしまった

というところを見てヒントになったチームもあるかもしれません。 OpenVPN Serverの/var/log/openvpn/openvpn.logを参照すると下記のようなログが見えます。

Sun Jul 14 22:47:28 2019 192.168.200.20:57004 TLS: Initial packet from [AF_INET]192.168.200.20:57004, sid=672cf5a9 3ecb1b68
Sun Jul 14 22:47:28 2019 192.168.200.20:57004 VERIFY OK: depth=1, CN=Easy-RSA CA
Sun Jul 14 22:47:28 2019 192.168.200.20:57004 VERIFY ERROR: depth=0, error=certificate has expired: CN=vyos-user-4
Sun Jul 14 22:47:28 2019 192.168.200.20:57004 OpenSSL: error:1417C086:SSL routines:tls_process_client_certificate:certificate verify failed

error=certificate has expired

とあるのでこのログを見て、証明書の期限が切れたとわかります。

証明書の期限が切れているので、新しく証明書を発行しOpenVPN Serverに新しく証明書を登録します。

  1. OpenVPN Serverにログインする
  2. 以下のコマンドを打つ

cd /easy-rsa-3.0.6/easyrsa3 ./easyrsa build-client-full vyos-client-2 nopass ictsc-ca-pass

  1. OpenVPN Clientにログインする
  2. 以下のコマンドを打つ

scp [email protected]:/easy-rsa-3.0.6/easyrsa3/pki/issued/vyos-client-2.crt ~/ scp [email protected]:/easy-rsa-3.0.6/easyrsa3/pki/private/vyos-client-2.key ~/ cp * /config/auth/ configure set interfaces openvpn vtun0 tls cert-file /config/auth/vyos-client-2.crt set interfaces openvpn vtun0 tls key-file /config/auth/vyos-client-2.key commit

  1. ping 172.25.0.1 を打って疎通を確認する。通ればOK

ICTSC2019 一次予選　問題解説：接続が不安定になっちゃった！

 /
カテゴリー

問題文

通常用セグメント 192.168.1.0/24 と、管理用セグメント 192.168.2.0/24 を持ったネットワーク上にいくつかのサーバがある。client1をこのネットワークに追加し設定したところ、client1とclient2間の通信が不安定になってしまった。192.168.1.0/24 のセグメントで正常に通信が行えるようにし、今後同じ状況にならないように設定を書き換えて、原因を報告してほしい。 管理用セグメント (192.168.2.0/24) からは正常にアクセスできるため、こちらからsshすること。

トポロジー図

トラブルの概要

本問題では動的割当のホストと静的割当のホストのIPアドレスが重複してしまい、通信が不安定になるトラブルでした。

解説

この問題はVyOSの設定のservice dhcp-server global-parameters ‘ping-check false;’という項目でDHCPによるアドレス割り当ての前にアドレスの使用状況を確認する動作が無効化されていたため、静的割り当てのホストと同じアドレスがDHCPによって払い出されていました。

解答例

VyOS

delete service dhcp-server global-parameters ‘ping-check false;’

Client(動的割り当て)

sudo dhclient -r

sudo dhclient

採点基準

  • 正常に通信を行えるかどうか
  • 原因を特定し、今後同じ状況にならないような設定にしているか

この二つを確認していました。

クライアントのどちらか片方のアドレスをnetplanで静的に書き換えても正常に通信はできますが、問題文に今後同じ状況にならないように設定してください。と記載されているため、DHCPの設定まで直して満点となります。

まとめ

IPアドレスの重複は簡単そうに見えて実際にトラブルに直面すると意外と気づきにくい所であり、DHCPの仕組みを理解していないとVyOSの誤設定に気づけない問題でした。

解答の設定の数もそれほど多くないのですぐ特定できた方は解決まで早かったのではないかと思います。

« Prev

最近の投稿

アーカイブ

カテゴリー

メタ情報