Oracle環境での開発者の失敗を未然に防ぐ「参照ユーザー」設定術

ごあいさつ

こんにちは、「エイジ＠フジワーランド」です
フリーエンジニアでパッケージシステムOEM供給やってますが、最近暇なので時給でSESやってます
SESやると色々勉強になりますね、お金もらって勉強になって現場に喜んでもらえていい仕事です
さて今回は開発者の失敗を未然に防ぐ「参照ユーザー」設定の話です

開発者の身を守るための参照専用ユーザー

開発環境（ステージング環境）と同じDBサーバーに、本番環境や結合テスト用検証環境などが共存しているケースはよく見られます

別環境のデータを参照しながら開発環境でテストデータを作成しているうちに、どちらが開発環境か本番環境か分からなくなり、誤って本番データを更新してしまった……という事態は決して珍しい話ではありませんよね

ここで役立つのが「参照ユーザー」です
参照ユーザーは一般的に通常のユーザーが誤って本番データを更新しないようにするための仕組みと考えられがちですが、実際に本番データを更新してしまうリスクが最も高いのは管理者権限を持つ開発者だったりします（これはあくまで個人の感想ですが）

ただし、別のユーザーでログインするとテーブルを参照する際にスキーマ名を毎回付ける必要があり、手間がかかることがあります（スキーマ名を指定しないと参照できないことで本番環境を意識するようになるというメリットもありますが、これについてはまた別の機会に）

本記事では、参照ユーザー運用をより効率的に行うためのヒントを共有します
この記事が少しでもお役に立てば幸いです

この記事でわかること

ORACLEで「カレントスキーマ設定」と「ログイントリガー」を使って
手間なく別スキーマのテーブルを参照できる設定を解説します
急ぐ方は 「3. 毎回自動でデフォルトスキーマを設定する方法」を参照ください

1. サンプルDDL

以下の例では、データを格納するアプリケーションスキーマAPP_SCHEMAと、参照専用のユーザREAD_ONLY_USERを作成します。

スキーマ作成とサンプルテーブルの作成

※管理者権限ユーザーで実行してください

-- アプリケーションスキーマの作成
CREATE USER APP_SCHEMA IDENTIFIED BY app_password;
GRANT CONNECT, RESOURCE TO APP_SCHEMA;

-- サンプルテーブルの作成
CREATE TABLE APP_SCHEMA.EMPLOYEES (
    EMPLOYEE_ID NUMBER PRIMARY KEY,
    EMPLOYEE_NAME VARCHAR2(100),
    DEPARTMENT_ID NUMBER
);

-- サンプルデータの挿入
INSERT INTO APP_SCHEMA.EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT_ID) VALUES (1, 'Alice', 10);
INSERT INTO APP_SCHEMA.EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT_ID) VALUES (2, 'Bob', 20);
COMMIT;

参照専用ユーザの作成

※管理者権限ユーザーで実行してください

-- 参照専用ユーザの作成
CREATE USER READ_ONLY_USER IDENTIFIED BY readonly_password;
GRANT CONNECT TO READ_ONLY_USER;

-- すべてのテーブルへの参照権限を付与
GRANT SELECT ANY TABLE TO READ_ONLY_USER;

2. スキーマ名なしでデータを照会する方法

シノニム使えば好きな名前で参照できますが
本記事ではデフォルトスキーマを設定する方法で実現いたします

※READ_ONLY_USERで実行してください

-- 参照専用ユーザでセッション中にデフォルトスキーマを設定
ALTER SESSION SET CURRENT_SCHEMA = APP_SCHEMA;

-- データを照会
-- (スキーマ名を省略して参照可能)
SELECT * FROM EMPLOYEES;

3. 毎回自動でデフォルトスキーマを設定する方法

毎回 ALTER SESSION しなくても、ログイントリガーを作成しておけば自動で実行されます

※管理者権限ユーザーで実行してください

-- トリガーの作成
CREATE OR REPLACE TRIGGER set_default_schema_on_login
AFTER LOGON ON DATABASE
BEGIN
    -- READ_ONLY_USER がログインした場合にのみ実行
    IF USER = 'READ_ONLY_USER' THEN
        EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = APP_SCHEMA';
    END IF;
END;
/

おわりに

今回は Oracleで「カレントスキーマ設定」と「ログイントリガー」を使って手間なく別スキーマのテーブルを参照できる設定を解説いたしました
参照ユーザーをうまく活用して開発者の失敗が少しでもなくなることを願います(自戒を込めて)

最後まで読んでいただきありがとうございました