この記事は ウェブクルー Advent Calendar 2017の10日目の記事です。
昨日は@keigo_magamiさんの「少し工夫してGoogle Cloud Storage連携した話」でした。
はじめに
ユーザ部門からシステム部門宛てに、単発の集計依頼がくることがあります。
その集計依頼というのが、特定のセグメントを切って抽出するだけのものが多いのですが、他の作業もあるため、作業を中断したり、すぐに回答できなかったりすることが多々ありました。
そこで外部顧問の方に相談したところ、他社では非エンジニアであるユーザ部門の人が、SQLを習得し、自身でデータを集計・抽出しているという話を聞き、今回、自社でも取り入れてみようと思い実施しました。
非エンジニアがSQLを覚えることで得られるメリットは下記のようなものが挙げられます。
メリット① エンジニアの手間が減る
データの集計や抽出が、エンジニアを介さずに対応できる為、エンジニアが自分の作業に集中ができる。
メリット② 結果を得られるまでの時間が短縮される
エンジニアに依頼することなくデータの集計や抽出を行える為、内容によっては数分で結果を得られる。
メリット③ より深い分析や施策を考えられるようになる。
SQLを習得する事で、データベースの中身をある程度、理解することができ、PV・UUなどの数値が変化した際の要因分析をする視野が広がる。
またユーザの情報や行動履歴など、細かい情報も追えるようになる為、数値から改善策を考えることも可能になったり、足りない情報を取得したい際にも、データ視点でシステム部門に依頼を行うことができるようになる。
環境を準備する
クライアント側の環境構築
まずは、データベースにアクセスする為のクライアント環境を構築します。
弊社ではOracleをメインのデータベースとして使っていますが、エンジニアは、好きなソフトを自由にインストールして使用している為、ユーザ部門に展開する上では標準化した方がよいということで、以下の条件を満たすツールを選定しました。
- Oracleに対応
- インストールが不要(OracleClient含む)
- できれば日本語に対応
- GUIで操作できる
その結果、「A5:SQL Mk-2」というツールがすべての条件を満たしたため、採用しました。
MySQLやPostgresSQLにも対応しているところが便利ですね。
サーバ側の環境構築
データベースにアクセスする環境を構築していく上で、解決しなければならないセキュリティの課題がいくつかあります。
課題① サービスに影響を与えない
サーバに高負荷をかけてもサービスに影響を与えない環境を準備します。
弊社のデータベース構成をざっくり説明すると、東京にメインのデータベースがあり、同じデータセンター内にバックアップサーバがあります。
また、大阪にはディザスタリカバリ用のデータベースがあり、ぞれぞれが定期的にデータ同期をしています。
今回、東京にあるバックアップサーバのデータ同期を深夜帯のみにして、日中は読み取り専用でオープンすることで、アクセスさせることにしました。問題点は、データが昨日時点であることですが、まぁ仕方ないでしょう。
課題② 操作ログの残す（いわゆる監査ログ）
セキュリティ事故が発生した時に備えての操作ログを記録する必要があります。
これはOracleで標準で備えているAudit機能を利用しました。
とりあえずは非エンジニアの人には、SELECT権限しか与えないので、これだけで十分でしょう。
-- バックアップサーバのOracleに監査設定を有効にする
$ sqlplus / as sysdba
SQL> ALTER SYSTEM SET AUDIT_TRAIL = XML, EXTENDED SCOPE = SPFILE;
SQL> ALTER SYSTEM AUDIT_FILE_DEST="<出力先ディレクトリ名>" SCOPE = SPFILE;
-- インスタンス再起動
SQL> shutdown immediate;
SQL> startup
-- 監査ログの設定
SQL> AUDIT SELECT TABLE BY "<ユーザ名>" BY ACCESS;
課題③ アクセス制限
アクセスするユーザを制限する方法として、FWやリスナーのアクセス制御を活用するのですが、中には共有PCで作業をしている人もいるので、ログイントリガーも併用してアクセス制御しました。
共有PC対策として、Oracleの外部認証を利用してもよかったんですが、ついでにログインできる時間帯も制御したかったので。
まずは共有PC対策として、指定のPCからアクセスできるユーザを（OSユーザ）制限するトリガーを作成します。
// 指定のPCからアクセスできるユーザを制限する
CREATE OR REPLACE TRIGGER "TRIG_TARO.HOKEN_CONTROL" AFTER LOGON ON DATABASE
DECLARE
ip_adress VARCHAR2(30);
os_user VARCHAR2(30);
session_user VARCHAR2(30);
BEGIN
SELECT sys_context('USERENV','IP_ADDRESS') INTO ip_adress FROM DUAL;
SELECT sys_context('USERENV','OS_USER') INTO os_user FROM DUAL;
SELECT sys_context('USERENV','SESSION_USER') INTO session_user FROM DUAL;
IF ip_adress = '10.xxx.xxx.xxx' AND ((upper(session_user) NOT IN ('TARO.HOKEN')) OR (upper(os_user) <> 'TARO.HOKEN')) THEN
RAISE_APPLICATION_ERROR(-20009, 'このユーザはアクセスが出来ません。');
END IF;
END;
/
次に、指定のPCからログインできる曜日、時間帯を制限するトリガーを作成します。
// DBユーザを使えるPC、時間帯を制限する
CREATE OR REPLACE TRIGGER "TRIG_TARO.HOKEN_LOGONTIME" AFTER LOGON ON DATABASE
DECLARE
ip_adress VARCHAR2(30);
os_user VARCHAR2(30);
session_user VARCHAR2(30);
BEGIN
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO ip_adress FROM DUAL;
SELECT SYS_CONTEXT('USERENV','SESSION_USER') INTO session_user FROM DUAL;
// 'TARO.HOKEN'ユーザは'10.xxx.xxx.xxx'からのPCのみアクセス許可をする。また月曜日～金曜日のAM9:00～18：00の時間帯のみアクセス許可をする
IF UPPER(session_user) = 'TARO.HOKEN' AND (ip_adress NOT IN ('10.xxx.xxx.xxx') OR (TO_CHAR(SYSDATE, 'HH24MI') NOT BETWEEN '0900' AND '1800') OR (TO_CHAR(SYSDATE, 'D') NOT BETWEEN '2' AND '6')) THEN
RAISE_APPLICATION_ERROR(-20008, 'このユーザはアクセスが出来ません。');
END IF;
END;
/
今後について
環境の構築が完了したので、次は実際に活用してもらうフェーズです。
非エンジニアにどのようにしてSQL言語を浸透させていくか、まだまだ課題は山積みですので、これからゆっくり考えていきます。
明日は @hiro_moriさんです。よろしくお願いします！
最後に
ウェブクルーでは一緒に働いていただける方を随時募集しております。
お気軽にエントリーくださいませ。