【Oracle Database 23ai 検証】読取り専用モードの機能拡張

Oracle Database 23ai（以下、23ai）では、データベースやユーザーを参照専用にすることでオブジェクトの作成や更新を防ぐ「読取り専用モード」の機能が拡張されています。

今回は、23aiから追加された「ハイブリッド読取り専用モード（Hybrid read-only mode）」と「読取り専用ユーザー／セッション（Read only users and sessions）」を紹介します。
メンテナンス操作やアプリケーションユーザーの権限管理にお役立てください。

Oracle Database 19c以前のPDBでは、オープンするモードは読取り／書込み（Read/Write）または、読取り専用（Read-only）のいずれかでした。

23aiでは、一般ユーザー（PDBのローカルユーザー）での接続のみPDBを読取り専用として動作させ、SYSTEMなどのCDB共通ユーザーでは更新可能とする、ハイブリッド読取り専用モードが新たに追加されています。
では、早速動作を見ていきましょう。

1. PDBのモードを確認

SQL> conn /as sysdba
接続されました。
SQL> desc V$CONTAINER_TOPOLOGY

 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 INSTANCE_NUMBER                                    NUMBER
 CON_NAME                                           VARCHAR2(128)
 OPEN_MODE                                          VARCHAR2(10)
 CPU_COUNT                                          NUMBER
 CON_ID                                             NUMBER
 RESTRICTED                                         VARCHAR2(3)
 IS_HYBRID_READ_ONLY                                VARCHAR2(3)  <=★19cにはない列が追加

SQL> SELECT * FROM v$container_topology WHERE con_name='FREEPDB1';

INSTANCE_NUMBER CON_NAME        OPEN_MODE             CPU_COUNT     CON_ID RESTRI IS_HYB
--------------- --------------- -------------------- ---------- ---------- ------ ------
              1 FREEPDB1        READ WRITE                    2          3 NO     NO <=★NO

2. PDBをハイブリッド読取り専用モードで起動

SQL> ALTER PLUGGABLE DATABASE freepdb1 CLOSE;
プラガブル・データベースが変更されました。

SQL> ALTER PLUGGABLE DATABASE freepdb1 OPEN HYBRID READ ONLY;
プラガブル・データベースが変更されました。

SQL> SELECT * FROM v$container_topology WHERE con_name='FREEPDB1';

INSTANCE_NUMBER CON_NAME        OPEN_MODE             CPU_COUNT     CON_ID RESTRI IS_HYB
--------------- --------------- -------------------- ---------- ---------- ------ ------
              1 FREEPDB1        READ WRITE                    2          3 NO     YES <=★YESに変更

3. 管理ユーザー（共通ユーザー）、一般ユーザーでそれぞれオブジェクトの作成／更新

SQL> conn system/oracle@freepdb1
接続されました。

SQL> show con_name

CON_NAME
------------------------------
FREEPDB1

SQL> CREATE TABLE testuser.test_tab (c1 number);
表が作成されました。

SQL> INSERT INTO testuser.test_tab VALUES(1);
1行が作成されました。

SQL> conn testuser/test@freepdb1
接続されました。

SQL> CREATE TABLE testuser.test_tab (c1 number);
CREATE TABLE testuser.test_tab (c1 number)
*
行1でエラーが発生しました。:
ORA-16000: 読取り専用アクセスでオープンされているデータベースまたはプラガブル・データベースを変更しようとしています。
ヘルプ: https://docs.oracle.com/error-help/db/ora-16000/

SQL> INSERT INTO testuser.test_tab VALUES(1);
INSERT INTO testuser.test_tab VALUES(1)
*
行1でエラーが発生しました。:
ORA-16000: 読取り専用アクセスでオープンされているデータベースまたはプラガブル・データベースを変更しようとしています。
ヘルプ: https://docs.oracle.com/error-help/db/ora-16000/

ハイブリッド読取り専用モードでは、PDBローカルユーザーでの接続でオブジェクトの作成や更新を行おうとするとORA-16000エラーで失敗することがわかります。※小ネタですが、23aiからはエラー発生時にマニュアルのリンクも出力されるようになっています

夜間バッチでのメンテナンス操作は管理ユーザーで行い、アプリケーションからの接続はPDBローカルユーザーにするように使い分けることで、意図しない更新などを抑止できそうです。

なお、上記ではPDBのオープン時に”hybrid read only”を指定しましたが、初期化パラメータ”HYBRID_READ_ONLY”でも動的に変更できます。

SQL> ALTER SYSTEM SET hybrid_read_only=true;
システムが変更されました。

SQL> SELECT * FROM v$container_topology WHERE con_name='FREEPDB1';

INSTANCE_NUMBER CON_NAME        OPEN_MODE             CPU_COUNT     CON_ID RESTRI IS_HYB
--------------- --------------- -------------------- ---------- ---------- ------ ------
              1 FREEPDB1        READ WRITE                    2          3 NO     YES

SQL> conn testuser/test@freepdb1
接続されました。

SQL> INSERT INTO testuser.test_tab VALUES(1);
INSERT INTO testuser.test_tab VALUES(1)
*
行1でエラーが発生しました。:
ORA-16000: 読取り専用アクセスでオープンされているデータベースまたはプラガブル・データベースを変更しようとしています。
ヘルプ: https://docs.oracle.com/error-help/db/ora-16000/

ただし、PDBを再起動した場合に値がリセットされ、HYBRID_READ_ONLY=FALSEに戻るようですので、起動ごとに都度初期化パラメータを設定する、もしくはPDB起動時にOPEN HYBRID READ ONLYを指定する必要があります。

23aiでは、ユーザーやセッション単位でも読取り専用モードに変更できるようになっています。

従来のバージョンでは、"CREATE SESSION”と"SELECT ANY TABLE”権限のみを与えたユーザーを読取り専用ユーザーとして利用するケースがありましたが、23aiのこの機能拡張を活用すれば、書込み権限を持っているユーザーを、付与済みの権限を保持したまま任意に読取り専用に変更でき、ユーザー管理の柔軟性が向上しています。

・ユーザーを読取り専用モードに変更

SQL> conn sys/oracle@freepdb1 as sysdba
接続されました。
SQL> ALTER USER testuser READ ONLY;
ユーザーが変更されました。

SQL> conn testuser/test@pdb1
接続されました。
SQL> INSERT INTO testuser.test_tab VALUES(1);
INSERT INTO testuser.test_tab VALUES(1)
*
行1でエラーが発生しました。:
ORA-28194: 読取り操作のみを実行できます
ヘルプ: https://docs.oracle.com/error-help/db/ora-28194/

・セッションを読取り専用モードに変更

SQL> conn testuser/test@freepdb1
接続されました。
SQL> ALTER SESSION SET read_only=true;
セッションが変更されました。

SQL> INSERT INTO testuser.test_tab VALUES(1);
INSERT INTO testuser.test_tab VALUES(1)
*
行1でエラーが発生しました。:
ORA-28193: 読取り操作のみを実行できます
ヘルプ: https://docs.oracle.com/error-help/db/ora-28193/

トリガーと組み合わせることで、特定のIPアドレスから特定ユーザーに接続した場合のみ読取り専用とするような使い方もできます。

・TESTUSERに対して192.168.16.150から接続があった場合のみ読取り専用とするトリガー

SQL> conn sys/oracle@freepdb1 as sysdba
SQL> CREATE OR REPLACE TRIGGER test_trig
  2  AFTER LOGON ON testuser.schema
  3      BEGIN
  4          IF UPPER(sys_context('USERENV','IP_ADDRESS')) = '192.168.16.150' THEN
  5          EXECUTE IMMEDIATE 'ALTER SESSION SET READ_ONLY=TRUE';
  6      END IF;
  7  END;
  8  /

今回は23aiでの読取り専用モードの機能拡張をご紹介しました。

サポートセンターでは「意図しないテーブルの更新やオブジェクトの削除を行ってしまったのでバックアップから復旧したい」というお問い合わせをいただくことも少なくありません。

この機能拡張された読取り専用モードの活用により、意図しない更新や削除からデータベースを守ることができますので、Oracle Database 23aiの運用管理では本機能の利用をご検討いただければと思います。

なお、サポートセンターにお問い合わせの多い「特定ユーザーが保持しているテーブルに対してのみ、読取り権限を持っているユーザー」については、引き続き個別に"GRANT SELECT ON <USER>.<TABLE>”権限を付与する必要があります。

たとえば、SCOTTユーザーの持つテーブルに対するSELECT権限のみをREADTESTユーザーに付与したい場合は、以下のようなSQLを実行してGRANT文を作成します。

SQL>  set pages 0
SQL>  SELECT 'GRANT SELECT ON  SCOTT.' ||OBJECT_NAME || ' TO READTEST;'
  2   FROM DBA_OBJECTS WHERE OWNER = 'SCOTT';

GRANT SELECT ON  SCOTT.EMP TO READTEST;
GRANT SELECT ON  SCOTT.DEPT TO READTEST;

本ブログでは引き続き、23aiの様々な機能についてご紹介します。次回記事もお楽しみにお待ちください！

本記事に関連して、他にもOracle Database 23aiの新機能や機能拡張についてご紹介している記事もございます。あわせてご活用ください。

【Oracle Database 23ai 検証】最新のSQLを使いこなそう！ -新機能編- | アシスト

Oracle Database 23aiではSQL関連の機能も数多く追加されています。本ブログでは23aiのSQL機能について「新機能編」と「機能拡張編」の2回にわたってご紹介します。本記事では新機能をご紹介します。

【Oracle Database 23ai 検証】ロックフリー予約で効率的なデータ更新を実現しよう！ | アシスト

Oracle Database 23aiの新機能であるロックフリー予約により、トランザクション同士がブロックすることなく、効率的なデータ更新を実現できます。本記事では、ロックフリー予約の使い方をご紹介します。

Oracle Database 23aiが遂にリリース！バージョンアップで押さえておくべき非推奨と廃止機能とは | アシスト

OCIのBaseDB（PaaS）で先行リリースされていた「23c」が2024年5月に「23ai」として正式リリースされました。オンプレも順次リリース予定です。バージョンアップで慌てないために、事前に考慮しておくべき非推奨と廃止機能を、対処策のヒントを交えてお届けします。