Oracle11gへの接続が特定のアカウントだとなぜか接続ができない場合、リスナーログ（listener.log）を確認する

以下のケースでは関係ない

• Oracle 11gでは、アカウントごとに特定のIPアドレスからの接続のみを許可・拒否する機能は、データベース自体の標準機能としては備わっていない。
• リスナー設定 listener.oraで「どのIPアドレスやポートで接続を受け付けるか」は指定できるが、アカウント単位ではなく、サーバー全体やリスナー単位の制御。
• OSやネットワークレベルのファイアウォールで、特定IPアドレスからの接続のみを許可することはあるが、アカウント単位ではなく、サーバーやポート単位の制御。
• アプリケーションがDB接続時に、IPアドレスやユーザー名をチェックして制御することは可能だが、DB直接の接続の場合はアプリケーションは経由しないので関係ない。

まずはログファイルを確認

リスナーログ（listener.log）

• クライアントからの接続要求や接続エラー（TNS-エラーなど）が記録される。

$ORACLE_BASE/diag/tnslsnr/<ホスト名>/<リスナー名>/trace/listener.log

以下は失敗のログの例

19-NOV-2020 02:10:01 * (CONNECT_DATA=(SERVICE_NAME=hogepdb)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.104)(PORT=64500)) * establish * hogepdb * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

以下は成功例（最後に 0 がある。）

19-NOV-2020 02:08:33 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=hogepdb)(CID=(PROGRAM=sqlplus@ol77)(HOST=ol77)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.103)(PORT=64384)) * establish * hogepdb * 0

アラートログ（alert log）認証エラー時

• データベースの重大なイベントやエラー（ORA-01017エラーなど）が記録される

$ORACLE_BASE/diag/rdbms/<DB名>/<インスタンス名>/trace/alert_<インスタンス名>.log

ログイントリガで接続をはじく方法もありはする

• PL/SQLのAFTER LOGONトリガとV$SESSIONなどを使い、接続時にIPアドレスやユーザー名をチェックし、条件に合わなければRAISE_APPLICATION_ERRORで切断する方法がある。
• ただし、これは公式サポート外の運用。

CREATE OR REPLACE TRIGGER valid_ip
AFTER LOGON ON DATABASE
DECLARE
    v_ip   VARCHAR2(30);
BEGIN
    -- V$SESSIONから現在のセッションのIPアドレスを取得
    SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO v_ip FROM DUAL;

    -- ユーザーごとにIPアドレスチェック
    IF (USER = 'TARGET_USER' AND v_ip NOT IN ('192.168.1.100', '127.0.0.1')) THEN
        RAISE_APPLICATION_ERROR(-20001, 'このIPアドレスからの接続は許可されていません');
    END IF;
END;
/

設定の確認

SELECT OWNER, TRIGGER_NAME, STATUS, DESCRIPTION
FROM ALL_TRIGGERS
WHERE TRIGGER_TYPE = 'AFTER LOGON ON DATABASE';

SELECT TRIGGER_NAME, STATUS, DESCRIPTION
FROM USER_TRIGGERS
WHERE TRIGGER_TYPE = 'AFTER LOGON ON SCHEMA';