Mac De SQL Developer #7 Tweet

Oracle SQL Developer 1.0 for MacOSXネタというより、OTN-Jで公開していたCodeTips #33関連のネタで、あるユーザが特定のアプリケーションでのみデータベースに接続できるようにしたいというもの。　無理矢理 SQL Developerネタにしてみた。（笑）

尚、OTN-JのCodeTipsを参照するには、OTN-Jにユーザ登録する必要がある。（無料）

CodeTips #33は、JOB QUEUEにジョブを登録し即座に実行、該当する接続を切断するというサンプルである。

JOB QUEUEに登録して実行するという方式であるため　”即座”とは言っても、LOGONクライアントイベントと完全に同期して接続を切断している訳ではない。

JOB QUEUEに関して、Oracle8iまでは、JOB_QUEUE_INTERVALというパラメータでCJQ0プロセス（コーディネータ・ジョブ・プロセス）が、ジョブキューをスキャンする間隔を制御できたが、9i以降廃止されたためこのスキャン間隔を調整することはできない。

また、多数のジョブがエンキューされた場合、登録されたJOBの実行が多少遅延する可能性もある。（JOB_QUEUE_PROCESSESパラメータで、ジョブを実行するJnnn（ジョブ・キュー・プロセス。nnnは数字で000から999まで）プロセス数を調整し、遅延を少なくするという方法はあるが、ただ多くすればいいってものでもない。）

ということで、あるユーザが接続してから接続が切断されるまでには、ある程度のタイムラグが発生する可能性がある。データベースサーバーの負荷が低い状態であれば、ログインとほぼ同時に、接続は切断されることが多いが、当然、そうならない場合もある。

尚、バックグランド・プロセスについては、マニュアル「データベース管理者ガイド　Oracleプロセスの管理」及び、「データベース概要の第三部 Oracleプロセスの概要」、
　　データベーストリガーについては、マニュアル「アプリケーション開発者ガイド　基礎編」、
　　v$　表に関しては、マニュアル「データース・リファレンス」などを参照すると良いだろう。

その結果、接続が切断されるまでの間にいくつかのDML文などを実行することも可能なのである。
この投稿では、あるユーザが　SQL*Plus以外で接続してきた場合、何も出来ないようにしたいと言うことだったため、CodeTips#33のような ジョブキューを利用する方式では要件に合わなかったということなのである。（CodeTipsに載せたコメントに、”ログオンしたとたんに・・・接続を切る”と書いてしまったことも混乱させた原因ではあると思うが。。。。反省）

そこで、私が考えた方法は、接続されてしまってから何とかしようという方式ではなく、接続自体を拒否してしまえばよいのでは？
と思い、以下の方法を提示した。

以下の内容は、OTN-Jの会議室に載せた内容に加えて、Oracle SQL Developerによる接続が拒否され接続エラーとなった画面のスナップショットや、該当トリガーを管理する専用ユーザの作成例も載せておく。

この例では、ログオントリガー管理専用ユーザを作成することにする。

SQL*Plus: Release 10.2.0.1.0 - Production on 水 4月 12 23:30:16 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn / as sysdba
接続されました。
SQL> create user caretaker identified by xxxxx
  2  default tablespace users
  3  temporary tablespace temp
  4  quota 1m on users;

ユーザーが作成されました。

SQL> grant create session to caretaker;

権限付与が成功しました。

SQL> grant create trigger to caretaker;

権限付与が成功しました。

SQL> grant select any dictionary to caretaker;

権限付与が成功しました。

SQL> grant administer database trigger to caretaker;

権限付与が成功しました。

作成したcaretakerユーザに接続しトリガーを作成する。

SQL> conn caretaker/xxxxxxx

接続されました。
SQL> CREATE OR REPLACE TRIGGER restrict_logon
  2  --
  3  -- administer database triggerシステム権限を持つユーザでは
  4  -- LOGONクライアントイベントは発生しないのでご注意ください。
  5  --
  6    AFTER LOGON
  7    ON DATABASE
  8  DECLARE
  9    CURSOR cs_session
 10    IS
 11      SELECT
 12        username,
 13        program
 14      FROM
 15        v$session
 16      WHERE
 17        audsid = (
 18                   SELECT
 19                     USERENV('SESSIONID')
 20                   FROM
 21                     DUAL
 22                 )
 23        AND username IS NOT NULL;
 24  --
 25     c_sqlplus CONSTANT VARCHAR2(7) := 'SQLPLUS';
 26     c_target_user CONSTANT VARCHAR2(30) := 'SCOTT';
 27  --
 28  BEGIN
 29    FOR session_rec IN cs_session LOOP
 30      IF  session_rec.username = c_target_user
 31      AND INSTR(UPPER(NVL(session_rec.program, ' ')), c_sqlplus) = 0 THEN
 32        RAISE_APPLICATION_ERROR(-20000,'SQL*Plus以外からの接続はできません。');
 33      END IF;
 34    END LOOP;
 35  END;
 36  /

トリガーが作成されました。

SQL> 

以下、Oracle SQL Developer 1.0でトリガーを作成している画面。（トリガー名やコードの一部が異なりますが気にしないでください）



SQL*Plusから接続し操作できるかを確認する。

SQL> conn / as sysdba
接続されました。
SQL> select * from dual;

DU
--
X

SQL> conn system/xxxxxx@catfish
接続されました。
SQL> select * from dual;

DU
--
X

SQL> conn scott/tiger
接続されました。
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> conn caretaker/xxxxxx@catfish
接続されました。
SQL> select * from dual;

DU
--
X

SQL>
 

以下、SQL*Plus以外から接続した場合は

ORA-00604: 再帰SQLレベル1でエラーが発生しました。 
ORA-20000: SQL*Plus以外からの接続はできません。 
ORA-06512: 行24

のようなエラーが返され接続できなくなっていることを確認。
以下 Oracle SQL Developer 1.0から接続した際のエラー画面。（MacOSX以外のプラットフォームの画面もあります）

尚、SQL Developerの”接続”設定は、新規作成で接続が確立できないと保存できない為、ログオントリガーを無効化した状態で作成し、その後、ログオントリガーを有効化してテストしたものである。

Oracle SQL Developer 1.0 for MacOSX版のスナップショット



Oracle SQL Developer 1.0 for Linux版のスナップショット



Oracle SQL Developer 1.0 for Windows版のスナップショット

おまけ
Oracle 9i R2 EE for Linuxでも同じトリガーを作成し、SQL Developerから接続するとエラーが発生し接続できないことも確認できた。

---

尚、このトリガーは、OTN-JのCodeTips#1531 でも公開しています。