ALTER SYSTEM KILL SESSION…"だけ"の権限付与を12c新機能のCode Based Access Control＋実行者権限プロシージャで実現してみる。(Oracle Database)
前回のエントリ
gonsuke777.hatenablog.com
からの続き。
Qiita の @tlokweng さんから 12c新機能のCode Based Access Control
なるものの存在を教えて頂きますた彡(ﾟ)(ﾟ)
ALTER SYSTEM KILL SESSION…によるユーザーセッションの切断は許可したいけど、
ALTER SYSTEM権限の付与(GRANT)は範囲が広過ぎる、権限が強過ぎる。。。
ALTER SYSTEM KILL SESSIONだけ許可したい……てな要件を、
今回は実行者権限(AUTHID CURRENT_USER)のプロシージャと、
12c新機能のCode Based Access Controlで実現してみるやで彡(ﾟ)(ﾟ)
概要
以下のような構成でDBユーザーとストアド・プロシージャを作成します。
DEFUSER … 権限の強いDBユーザー │ ↓ │ RL_EXE_KILL_SESS … ALTER SYSTEM権限を保持するロール │ │ │ (Code Based Access Controlでシステム権限をプロシージャに対してロール経由で付与) ↓ ↓ DEFUSER.PRC_KILL_SESS … KILL SESSIONを行うストアド・プロシージャ、実行者権限(AUTHID CURRENT_USER)で作成 ↓ 実行権限付与(GRANT EXECUTE…) ↓ EXEUSER ←権限の弱いDBユーザー
DBユーザー作成
プロシージャ所有者(DEFUSER)と実行ユーザー(EXEUSER)をそれぞれ作成します。
-- プロシージャの所有者を作成 CONNECT /AS SYSDBA CREATE USER DEFUSER IDENTIFIED BY xxxxxxxx DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; -- DBAロールを付与 GRANT DBA TO DEFUSER; -- KILL SESSIONを実行するユーザー CREATE USER EXEUSER IDENTIFIED BY xxxxxxxx DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; -- CREATE SESSION権限のみ付与 GRANT CREATE SESSION TO EXEUSER; Connected. User created. Grant succeeded. User created. Grant succeeded.
プロシージャ作成
実行者権限(AUTHID CURRENT_USER)のプロシージャを作成します。
-- 実行者権限(AUTHID CURRENT_USER)のKILL SESSIONプロシージャを作成 CONNECT DEFUSER/xxxxxxxx@yyyyyyyy CREATE OR REPLACE PROCEDURE DEFUSER.PRC_KILL_SESS ( in_sid IN NUMBER , in_serial IN NUMBER ) AUTHID CURRENT_USER IS BEGIN DBMS_OUTPUT.PUT_LINE('Current Schema => ' || SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')); DBMS_OUTPUT.PUT_LINE('Current User => ' || SYS_CONTEXT('USERENV', 'CURRENT_USER')); DBMS_OUTPUT.PUT_LINE('Session User => ' || SYS_CONTEXT('USERENV', 'SESSION_USER')); EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || in_sid || ',' || in_serial || ''''; END; / Connected. Procedure created.
ロール作成＆権限付与 および Code Based Access Controlによる権限付与
プロシージャ所有者でロールを作成して、
ロール経由でALTER SYSTEM権限をプロシージャに付与します。
ユーザーやロールではなく、プロシージャに権限を付与するところがポイントで、
これが12c新機能のCode Based Access Control、、、と理解したやで。彡(ﾟ)(ﾟ)
CONNECT DEFUSER/xxxxxxxx@yyyyyyyy -- ロール作成とロールに対するALTER SYSTEM権限の付与 CREATE ROLE RL_EXEC_KILL_SESS; GRANT ALTER SYSTEM TO RL_EXEC_KILL_SESS; -- ALTER SYSTEM権限をロール経由でプロシージャに付与(Code Based Access Control) GRANT RL_EXEC_KILL_SESS TO PROCEDURE DEFUSER.PRC_KILL_SESS; -- プロシージャの実行権限付与 GRANT EXECUTE ON DEFUSER.PRC_KILL_SESS TO EXEUSER; Role created. Grant succeeded. Grant succeeded. Grant succeeded.
EXEUSERでKILL SESSIONの検証
EXEUSERでKILL SESSIONの検証をしてみます。下記のセッションをKILLしてみます。
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy Connected. SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT'; SID SERIAL# ---------- ---------- 787 62729
まずは ALTER SYSTEM KILL SESSION…から。権限が無いので当然失敗します。
CONNECT EXEUSER/xxxxxxxx@yyyyyyyy Connected. SHOW USER USER is "EXEUSER" ALTER SYSTEM KILL SESSION '787, 62729'; * ERROR at line 1: ORA-01031: insufficient privileges
ストアド・プロシージャ(PRC_KILL_SESS)によるKILL。こちらは成功します。
CONNECT EXEUSER/xxxxxxxx@yyyyyyyy Connected. SHOW USER USER is "EXEUSER" SET SERVEROUTPUT ON SIZE 1000000; EXEC DEFUSER.PRC_KILL_SESS(787, 62729); Current Schema => EXEUSER Current User => EXEUSER Session User => EXEUSER PL/SQL procedure successfully completed.
元のセッション(AYSHIBATユーザ)に戻ってみると……
見事にKILLされています。やったぜ。彡(^)(^)
SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT'; * ERROR at line 1: ORA-00028: your session has been killed SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT'; * ERROR at line 1: ORA-01012: not logged on Process ID: 4160 Session ID: 787 Serial number: 62729
ちなみにALTER SYSTEM権限をロールから剥奪(REVOKE)すると、
実行ユーザーからはKILL SESSIONが実行できなくなります。
Code Based Access Controlが有効に機能していることが解ります彡(ﾟ)(ﾟ)
CONNECT DEFUSER/xxxxxxxx@yyyyyyyy REVOKE ALTER SYSTEM FROM RL_EXEC_KILL_SESS; Connected. Revoke succeeded. CONNECT EXEUSER/xxxxxxxx@yyyyyyyy SET SERVEROUTPUT ON SIZE 1000000; EXEC DEFUSER.PRC_KILL_SESS(798, 56993); Connected. Current Schema => EXEUSER Current User => EXEUSER Session User => EXEUSER BEGIN DEFUSER.PRC_KILL_SESS(798, 56993); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "DEFUSER.PRC_KILL_SESS", line 11 ORA-06512: at line 1
AUTHID DEFINERだと、問答無用で定義者の権限やロールで処理されてしまう所を、
実行者に付与された権限＆ロールできめ細かく制御可能なのが、
この機能(Code Based Access Control)のメリットやね彡(^)(^)
＃今回のケースだと、メリットが全面的には出し辛かったんやけどね。。。彡(-)(-)
マニュアルも読みませう彡(ﾟ)(ﾟ)
Oracle Database新機能ガイド 12cリリース1 (12.1) B71327-05 2.9.2.2 コードベース・セキュリティ https://docs.oracle.com/cd/E57425_01/121/NEWFT/chapter12101.htm#FEATURENO08676 Oracle Databaseセキュリティ・ガイド 12c リリース1 (12.1) B71285-10 定義者権限および実行者権限のコード・ベース・アクセス制御の使用 https://docs.oracle.com/cd/E57425_01/121/DBSEG/dr_ir.htm#GUID-45E77E8E-587F-42AF-A163-D814264341E2
ALTER SYSTEM KILL SESSION…"だけ"の権限付与を定義者権限のプロシージャで実現してみる。(Oracle Database)
ALTER SYSTEM KILL SESSION…によるユーザーセッションの切断は許可したいけど、
ALTER SYSTEM権限の付与(GRANT)は範囲が広過ぎる、権限が強過ぎる。
ALTER SYSTEM KILL SESSIONだけ許可したい……てな要件を、
定義者権限(AUTHID DEFINER)のプロシージャで実現してみるやで彡(ﾟ)(ﾟ)
概要
以下のような構成でDBユーザーとストアド・プロシージャを作成します。
DEFUSER … 権限の強いDBユーザー ↓ DEFUSER.PRC_KILL_SESS … KILL SESSIONを行うストアド・プロシージャ、定義者権限(AUTHID DEFINER)で作成 ↓ 実行権限付与(GRANT EXECUTE…) ↓ EXEUSER ←権限の弱いDBユーザー
DBユーザー作成
権限の強いユーザー(DEFUSER)と弱いユーザー(EXEUSER)をそれぞれ作成します。
CONNECT /AS SYSDBA -- プロシージャの所有者 CREATE USER DEFUSER IDENTIFIED BY xxxxxxxx DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; -- DBAロールとALTER SYSTEM権限を付与 GRANT DBA TO DEFUSER; GRANT ALTER SYSTEM TO DEFUSER; User created. Grant succeeded. Grant succeeded. -- KILL SESSIONを実行するユーザー CREATE USER EXEUSER IDENTIFIED BY xxxxxxxx DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; -- CREATE SESSION権限のみ付与 GRANT CREATE SESSION TO EXEUSER; User created. Grant succeeded.
ストアド・プロシージャ作成＆権限付与
KILL SESSIONを実行するストアド・プロシージャをDEFUSERスキーマに
作成してEXEUSERに実行権限を付与します。
CONNECT DEFUSER/xxxxxxxx@yyyyyyyy CREATE OR REPLACE PROCEDURE DEFUSER.PRC_KILL_SESS ( in_sid IN NUMBER , in_serial IN NUMBER ) AUTHID DEFINER IS BEGIN EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || in_sid || ',' || in_serial || ''''; END; / Procedure created. GRANT EXECUTE ON DEFUSER.PRC_KILL_SESS TO EXEUSER; Grant succeeded.
EXEUSERでKILL SESSIONの検証
EXEUSERでKILL SESSIONの検証をしてみます。下記のセッションをKILLしてみます。
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy Connected. SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT'; SID SERIAL# ---------- ---------- 775 31102
まずは ALTER SYSTEM KILL SESSION…から。権限が無いので当然失敗します。
CONNECT EXEUSER/xxxxxxxx@yyyyyyyy Connected. SHOW USER USER is "EXEUSER" ALTER SYSTEM KILL SESSION '775, 31102'; * ERROR at line 1: ORA-01031: insufficient privileges
ストアド・プロシージャ(PRC_KILL_SESS)によるKILL。こちらは成功します。
CONNECT EXEUSER/xxxxxxxx@yyyyyyyy Connected. SHOW USER USER is "EXEUSER" EXEC DEFUSER.PRC_KILL_SESS(775, 31102); PL/SQL procedure successfully completed.
元のセッション(AYSHIBATユーザ)に戻ってみると……
見事にKILLされています。やったぜ。彡(^)(^)
SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT'; * ERROR at line 1: ORA-00028: your session has been killed SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'AYSHIBAT'; ERROR at line 1: ORA-01012: not logged on Process ID: 4398 Session ID: 775 Serial number: 31102
マニュアルも読みませう彡(ﾟ)(ﾟ)
AUTHID DEFINERでプロシージャ(等)を作成すると、
定義者(今回のケースではDEFUSER)の権限で実行されるってことなんやね。
Oracle Database PL/SQL言語リファレンス 12c リリース2 (12.2) E72879-03 実行者権限および定義者権限(AUTHIDプロパティ) https://docs.oracle.com/cd/E82638_01/LNPLS/plsql-subprograms.htm#GUID-41D23DE7-3C07-41CF-962B-F92B696594B5
Oracle Database の DRCP（データベース常駐接続プーリング）の MINSIZE と MAXSIZE を 1 に設定して、複数セッションから接続してみる。
DRCP は 通常はAPサーバー側で確保するコネクションプール を
Oracle Database側で用意しておく機能やね彡(ﾟ)(ﾟ)
DRCPでMAXSIZEを超える同時接続要求が来た際の挙動を確認してみるんやで。
まずは DRCP の設定を変更して、開始します。
-- DRCPの最大接続数／最小接続数を1に設定 EXECUTE DBMS_CONNECTION_POOL.CONFIGURE_POOL(MINSIZE=>1, MAXSIZE=>1); PL/SQL procedure successfully completed. -- DRCP開始 EXECUTE DBMS_CONNECTION_POOL.START_POOL; PL/SQL procedure successfully completed. -- 確認 SET LINESIZE 100; COLUMN CONNECTION_POOL FORMAT A40; SELECT CONNECTION_POOL, STATUS, MINSIZE, MAXSIZE FROM DBA_CPOOL_INFO; CONNECTION_POOL STATUS MINSIZE MAXSIZE ---------------------------------------- ---------------- ---------- ---------- SYS_DEFAULT_CONNECTION_POOL ACTIVE 1 1
クライアントからDRCPに接続して……(こちらはすぐ繋がります)
11:32:02 SQL> CONNECT AYSHIBAT/xxxxxxxx@127.0.0.1:11521/orcl:POOLED 接続されました。
別のクライアントからも接続します。こちらは待たされます。
そう、待たされるんだよなぁ……彡(ﾟ)(ﾟ)
11:32:05 SQL> CONNECT AYSHIBAT/xxxxxxxx@127.0.0.1:11521/orcl:POOLED （待たされる）
この状態で元クライアントの接続を切断すると……
11:32:08 SQL> 11:32:37 SQL> DISCONNECT Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Productionとの 接続が切断されました。 11:32:37 SQL>
待たされていたクライアントが繋がるんやで彡(ﾟ)(ﾟ)
11:32:05 SQL> CONNECT AYSHIBAT/xxxxxxxx@127.0.0.1:11521/orcl:POOLED 接続されました。 11:32:37 SQL>
DRCP接続の確立が待たされると、V$CPOOL_STATSビューのNUM_WAITSがカウントされるもよう
SET LINESIZE 120; COLUMN POOL_NAME FORMAT A40; SELECT POOL_NAME, NUM_REQUESTS, NUM_HITS, NUM_MISSES, NUM_WAITS FROM V$CPOOL_STATS; POOL_NAME NUM_REQUESTS NUM_HITS NUM_MISSES NUM_WAITS ---------------------------------------- ------------ ---------- ---------- ---------- SYS_DEFAULT_CONNECTION_POOL 2 0 2 1★コレ
例によってマニュアルや各種ドキュメントも見とくんやで彡(ﾟ)(ﾟ)
Oracle Database管理者ガイド 12cリリース2 (12.2) E72998-04
5.2 データベース常駐接続プーリングの理解
https://docs.oracle.com/cd/E82638_01/ADMIN/managing-processes.htm#GUID-BB76E57C-3F16-4C85-AEF6-BA14FC1B4777Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 12c リソース2 (12.2)
E85246-03
35 DBMS_CONNECTION_POOL
https://docs.oracle.com/cd/E82638_01/ARPLS/DBMS_CONNECTION_POOL.htm#GUID-C918B806-48D1-42F6-9B2E-B3F307164873Oracle Database管理者ガイド 12cリリース2 (12.2)
E72998-04
5.4.2.1 データベース常駐接続プーリングの構成パラメータ
https://docs.oracle.com/cd/E82638_01/ADMIN/managing-processes.htm#GUID-ABBE6941-73AC-4343-B320-A1400451B44Fデータベース常駐接続プーリング（DRCP）Oracle Database 11g
テクニカル・ホワイト・ペーパー
http://www.oracle.com/technetwork/jp/topics/oracledrcp11g-132231-ja.pdfOracle Databaseリファレンス 12cリリース2 (12.2)
E72905-03
F バックグラウンド・プロセス
https://docs.oracle.com/cd/E82638_01/REFRN/background-processes.htm#GUID-86184690-5531-405F-AA05-BB935F57B76D
：
Lnnn
プール・サーバー・プロセス
データベース常駐接続プーリングでクライアント要求を処理する
：
Nnnn
接続ブローカ・プロセス
データベース常駐接続プーリングでアイドル状態の接続を監視し、アクティブな接続を渡す
：
こちらの資料も大変参考になります！彡(^)(^)
Oracle常駐接続プーリング(DRCP)を導入した話
https://www.slideshare.net/kenken0807/oracledrcp
Oracle Database 12cR2(12.2)だとTABLEファンクションのTABLE句が省略できるらしいのでDBMS_XPLANで試してみる。
※本エントリで記述されている事項はドキュメント非記載のため、原則非サポートと考えて下さい。
※(2018/12/24追記)マニュアルに載ってました。
Oracle DatabaseデータベースPL/SQL言語リファレンス 18c
12.5.1 テーブル・ファンクションの概要
https://docs.oracle.com/cd/E96517_01/lnpls/plsql-optimization-and-tuning.html#GUID-4E10CBFA-4B6A-4761-8905-83C26C112694
次のように、同等の問合せをTABLE演算子なしで記述できます。
SELECT * FROM table_function_name(parameter_list)
下記ツイートで見かけたOracle Database 12cR2(12.2)の隠し機能彡(ﾟ)(ﾟ)
https://twitter.com/kibeha/status/955557265687359488
Huh? Is this new #OrclDB 12.2 undocumented feature that you can skip TABLE if you just add empty parentheses instead? Or is it just me that didn't know this was possible?
#SQL #PLSQL
12cR2(12.2)だとTABLEファンクションのTABLE句が省略できるらしい？彡(ﾟ)(ﾟ)
TABLEファンクションの代表と云えばDBMS_XPLAN、下記のようTABLE句を付けて使うのがセオリーですが。。。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '9ht3ba3arrzt3')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 9ht3ba3arrzt3, child number 0 ------------------------------------- UPDATE KET$_CLIENT_TASKS SET STATUS = CASE STATUS WHEN 13 THEN 2 ELSE STATUS END, WINDOW_NAME = NULL, CURR_JOB_NAME = NULL, RETRY_COUNT = RETRY_COUNT+1, LT_JOB_LOG_ID = :log, LT_TERM_CODE = 11, LT_PRIORITY = TASK_PRIORITY, LT_ERROR = :err, LT_DATE = :end, LT_DURATION = :ela, LT_CPU_TIME = :cpu WHERE CLIENT_ID = :cid AND OPERATION_ID = :oid AND TARGET_TYPE = :tgt AND TARGET_NAME = :tgn Plan hash value: 3863298075 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 1 (100)| | 1 | UPDATE | KET$_CLIENT_TASKS | | | | |* 2 | INDEX UNIQUE SCAN| KET$_TSK_PK | 1 | 112 | 0 (0)| ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CLIENT_ID"=:CID AND "OPERATION_ID"=:OID AND "TARGET_TYPE"=:TGT AND "TARGET_NAME"=:TGN) 27 rows selected. SQL>
これを12cR2(12.2)環境でTABLE句無しで実行してみると……彡(ﾟ)(ﾟ)
SQL> SELECT * FROM V$VERSION; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 PL/SQL Release 12.2.0.1.0 - Production 0 CORE 12.2.0.1.0 Production 0 TNS for Linux: Version 12.2.0.1.0 - Production 0 NLSRTL Version 12.2.0.1.0 - Production 0 SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '9ht3ba3arrzt3'); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------- SQL_ID 9ht3ba3arrzt3, child number 0 ------------------------------------- UPDATE KET$_CLIENT_TASKS SET STATUS = CASE STATUS WHEN 13 THEN 2 ELSE STATUS END, WINDOW_NAME = NULL, CURR_JOB_NAME = NULL, RETRY_COUNT = RETRY_COUNT+1, LT_JOB_LOG_ID = :log, LT_TERM_CODE = 11, LT_PRIORITY = TASK_PRIORITY, LT_ERROR = :err, LT_DATE = :end, LT_DURATION = :ela, LT_CPU_TIME = :cpu WHERE CLIENT_ID = :cid AND OPERATION_ID = :oid AND TARGET_TYPE = :tgt AND TARGET_NAME = :tgn Plan hash value: 3863298075 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 1 (100)| | 1 | UPDATE | KET$_CLIENT_TASKS | | | | |* 2 | INDEX UNIQUE SCAN| KET$_TSK_PK | 1 | 112 | 0 (0)| ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CLIENT_ID"=:CID AND "OPERATION_ID"=:OID AND "TARGET_TYPE"=:TGT AND "TARGET_NAME"=:TGN) 27 rows selected.
マジやった！TABLE句省略できるやんけ！彡(ﾟ)(ﾟ)
V$SQLとその周辺でER図を描いてみよう！ - JPOUG Advent Calendar 2017 Day 15 -
JPOUG Advent Calendar 2017 の Day 15 の記事となります。
昨日は おおのたかしさん の記事でした彡(ﾟ)(ﾟ)
Oracle Database 12cR2へのアップグレード後に発生するORA-01017 https://www.ashisuto.co.jp/db_blog/article/201712-ora-01017.html
今年はV$SQLとその周辺でER図を描いてみたやで彡(ﾟ)(ﾟ)
Slideshareの元リンクは下記やで彡(ﾟ)(ﾟ)
V$SQLとその周辺でER図を描いてみよう！ https://www.slideshare.net/shibataayumu/vsqler
今回描いたV$SQL廻りのER図全景はこちら。
アプリでもインフラでも役立つER図/モデリングは最強のスキルなんやで彡(^)(^)
JavaのPreparedStatementを使ったバインド変数なSQLで、ワザとORA-1000エラーを起こすソースを書いてみて回避策を探る。
ORA-1000エラーはOracle Databaseのセッションでオープンしている
カーソル数が最大値を超えた際に発生するエラーです。
ORA-1000 最大オープン・カーソル数を超えました。
早速やってみるやで彡(ﾟ)(ﾟ) まずはテーブルの準備から。
CREATE TABLE TBL_A( C1 NUMBER , C2 VARCHAR2(30) );
ORA-1000エラーが起きるJavaソース(※アンチパターン)
PreparedStatementのバインド変数なSQLで、
ワザとORA-1000エラーが起きるようにソースを記述しています。
forループ内にprepareStatementメソッドが有って、その度にカーソルが
新規にオープンされて、エラーになってしまうんですやね彡(ﾟ)(ﾟ)
クソコード要素満載なんやけど、ワザとなんでマサカリ飛ばしたら(ｱｶﾝ 彡(-)(-)
import java.sql.*; import java.util.Date; public class InsertTest { public static void main(String[] args) throws Exception { //DB connection info final String path = "jdbc:oracle:thin:@127.0.0.1:1521/orcl"; //path final String id = "xxxxxxxx"; //ID final String pw = "yyyyyyyy"; //password int i; Connection conn = null; PreparedStatement ps = null; System.out.println(new Date() + " Connect..."); try { //DB Connect conn = DriverManager.getConnection(path, id, pw); //AutoCommit Setting conn.setAutoCommit(false); //Insert Execute System.out.println(new Date() + " Insert..."); for (i = 1; i <= 2000; i++) { //Prepared Statement Set. ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)"); ps.setInt(1, i); ps.setString(2, "A" + String.valueOf(i)); ps.execute(); } //Commit conn.commit(); //Close conn.close(); } catch(SQLException ex) { conn.rollback(); ex.printStackTrace(); System.exit(1); } finally { if (ps != null) { ps.close(); } if (conn != null) { conn.close(); } } //End System.out.println(new Date() + " End..."); } }
実行すると、以下の通りORA-1000エラーが発生するんやで彡(ﾟ)(ﾟ)
$ javac ./InsertTest.java $ java -classpath .:${ORACLE_HOME}/jdbc/lib/ojdbc8.jar InsertTest Thu Nov 30 00:57:01 JST 2017 Connect... Thu Nov 30 00:57:23 JST 2017 Insert... java.sql.SQLException: ORA-01000: maximum open cursors exceeded at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494) at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:910) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780) at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3887) at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1079) at InsertTest.main(InsertTest.java:27) Caused by: Error : 1000, Position : 0, Sql = INSERT INTO TBL_A (C1, C2) VALUES (:1 , :2 ), OriginalSql = INSERT INTO TBL_A (C1, C2) VALUES (?, ?), Error Msg = ORA-01000: maximum open cursors exceeded at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498) ... 14 more $ echo $? 1
回避策1. prepareStatementをforループの外に置く。
prepareStatementをforループの外に置けば、
ORA-1000エラーを回避できます。そりゃそやな？彡(ﾟ)(ﾟ)
： //Insert Execute System.out.println(new Date() + " Insert..."); //Prepared Statement Set. ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)"); for (i = 1; i <= 2000; i++) { ps.setInt(1, i); ps.setString(2, "A" + String.valueOf(i)); ps.execute(); } //Commit conn.commit(); ：
回避策2. オブジェクトのnullチェックをして初回だけprepareStatementを実行する。
オブジェクトのnullチェックをして初回prepareStatementを
実行すれば、ORA-1000エラーを回避できます。
： //Insert Execute System.out.println(new Date() + " Insert..."); for (i = 1; i <= 2000; i++) { //Prepared Statement Set. if (ps == null) { ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)"); } ps.setInt(1, i); ps.setString(2, "A" + String.valueOf(i)); ps.execute(); } //Commit conn.commit(); ：
回避策3. try-with-resources構文を採用する。
回避策1.のバリエーションになるですやろうか彡(ﾟ)(ﾟ)
以前の記事で教えて貰ったtry-with-resources構文を採用すると、
初めのアンチパターン的な書き方を「自ずと」回避して、
ORA-1000エラーは発生しません。「自ずと」ってとこがポイントです。
ソースはすっきりするし、良い事ずくめなんやなぁ彡(ﾟ)(ﾟ)
import java.sql.*; import java.util.Date; public class InsertTest { public static void main(String[] args) { //DB connection info final String path = "jdbc:oracle:thin:@127.0.0.1:1521/orcl"; //path final String id = "xxxxxxxx"; //ID final String pw = "yyyyyyyy"; //password //try-with-resources Statement System.out.println(new Date() + " Connect..."); try ( //DB Connect Connection conn = DriverManager.getConnection(path, id, pw); //Prepared Statement Set. PreparedStatement ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)"); ) { //Initialize int i; //AutoCommit Setting conn.setAutoCommit(false); //Insert Execute System.out.println(new Date() + " Insert..."); for (i = 1; i <= 2000; i++) { ps.setInt(1, i); ps.setString(2, "A" + String.valueOf(i)); ps.execute(); } //Commit conn.commit(); } catch(SQLException ex) { ex.printStackTrace(); System.exit(1); } //End System.out.println(new Date() + " End..."); } }
回避策4. PreparedStatementオブジェクトを毎回close()する。※2017/11/27追記
ループ内でPreparedStatementオブジェクトを
毎回close()すればORA-1000エラーの発生は回避できます。
： //Insert Execute System.out.println(new Date() + " Insert..."); for (i = 1; i <= 2000; i++) { //Prepared Statement Set. ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)"); ps.setInt(1, i); ps.setString(2, "A" + String.valueOf(i)); ps.execute(); ps.close(); } //Commit conn.commit(); ：
この書き方すると毎回Hard Parseしちゃうかな？と思ったんですが、
文キャッシュの機能で毎回Hard Parseは避けられるもよう彡(ﾟ)(ﾟ)
文キャッシュについて
https://docs.oracle.com/cd/E16338_01/java.112/b56281/stmtcach.htm#i1069942
：
・文の解析と作成の繰返しを回避します。
：
おまけ(※2017/11/30追記)
別の人から「アンチパターンのは変数のスコープがそもそもおかしくねぇか？」って
指摘を受けたので、try句で変数を宣言するようにソースを修正すると……
うーん、自然と回避策1.っぽいコードになっちゃいますやね。
try-with-resources構文と云い、お作法大事ですやね彡(ﾟ)(ﾟ)
import java.sql.*; import java.util.Date; public class InsertTest { public static void main(String[] args) { //DB connection info final String path = "jdbc:oracle:thin:@127.0.0.1:1521/orcl"; //path final String id = "xxxxxxxx"; //ID final String pw = "yyyyyyyy"; //password System.out.println(new Date() + " Connect..."); try { //Init int i; //DB Connect Connection conn = DriverManager.getConnection(path, id, pw); //AutoCommit Setting conn.setAutoCommit(false); //Prepared Statement Set. PreparedStatement ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)"); //Insert Execute System.out.println(new Date() + " Insert..."); for (i = 1; i <= 2000; i++) { ps.setInt(1, i); ps.setString(2, "A" + String.valueOf(i)); ps.execute(); } //Commit conn.commit(); //Close ps.close(); conn.close(); } catch(SQLException ex) { ex.printStackTrace(); System.exit(1); } //End System.out.println(new Date() + " End..."); } }
おまけ2. addBatch(), executeBatch() でSQLを実行(2019/8/20)
コメントで addBatch についてコメント頂いたので、おまけのソースを基に改修してみました。
こんな感じで良いのかしら……彡(ﾟ)(ﾟ) addBatch(バッチ更新) のマニュアルはこちら⇒ https://docs.oracle.com/cd/F19136_01/jjdbc/performance-extensions.html#GUID-FEECA64F-44F4-453F-B8A8-AFBF6D29ABA4
import java.sql.*; import java.util.Date; public class InsertTest { public static void main(String[] args) { //DB connection info final String path = "jdbc:oracle:thin:@127.0.0.1:1521/orcl"; //path final String id = "xxxxxxxx"; //ID final String pw = "yyyyyyyy"; //password System.out.println(new Date() + " Connect..."); try { //Init int i; //DB Connect Connection conn = DriverManager.getConnection(path, id, pw); //AutoCommit Setting conn.setAutoCommit(false); //Prepared Statement Set. PreparedStatement ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)"); //Insert Execute System.out.println(new Date() + " Insert..."); for (i = 1; i <= 2000; i++) { ps.setInt(1, i); ps.setString(2, "A" + String.valueOf(i)); ps.addBatch(); } //Execute(batch updates) int[] updateCounts = ps.executeBatch(); System.out.println(new Date() + " Batch Counts..." + updateCounts.length); //Commit conn.commit(); //Close ps.close(); conn.close(); } catch(SQLException ex) { ex.printStackTrace(); System.exit(1); } //End System.out.println(new Date() + " End..."); } }
$ javac ./InsertTest.java $ java -classpath .:/u01/app/oracle/product/version/db_1/jdbc/lib/ojdbc8.jar InsertTest Tue Aug 20 04:18:11 EDT 2019 Connect... Tue Aug 20 04:18:13 EDT 2019 Insert... Tue Aug 20 04:18:14 EDT 2019 Batch Counts...2000 Tue Aug 20 04:18:14 EDT 2019 End... $
SQLNET.AUTHENTICATION_SERVICESをnoneに設定してOS認証を無効化してみる(Oracle Database)
sqlnet.ora の SQLNET.AUTHENTICATION_SERVICES を none に
セットすると、OS認証を無効化することができるんやで彡(ﾟ)(ﾟ)
さっそくやってみます。
設定前(デフォルト)
$ id -a uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba) $ cat $ORACLE_HOME/network/admin/sqlnet.ora ★sqlnet.oraが無い cat: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora: No such file or directory $ sqlplus /nolog SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 8 10:53:41 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. SQL> CONNECT /AS SYSDBA Connected. ★OS認証でログインできる。
設定後(SQLNET.AUTHENTICATION_SERVICESをnoneにセット)
$ id -a uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba) $ cat $ORACLE_HOME/network/admin/sqlnet.ora SQLNET.AUTHENTICATION_SERVICES=(none) $ sqlplus /nolog SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 8 10:59:17 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. SQL> CONNECT /AS SYSDBA ERROR: ★OS認証ではログインできない。 ORA-01017: ユーザー名/パスワードが無効です。ログオンは拒否されました。 SQL> CONNECT SYS/xxxxxxxx AS SYSDBA ★ Connected. ★パスワード認証ではログインできる。
OS認証ではログインできなくなったやで彡(^)(^)
マニュアル
マニュアルは下記、AUTHENTICATION_SERVICESのデフォルト値はallやで彡(ﾟ)(ﾟ)
Oracle Database Net Servicesリファレンス 12cリリース1 (12.1) B71289-04
5 sqlnet.oraファイルのパラメータ
SQLNET.AUTHENTICATION_SERVICES
https://docs.oracle.com/cd/E57425_01/121/NETRF/sqlnet.htm#BIIDAFFD
