第40回：Oracle23aiの新機能 READ ONLY USERについて

1. READ ONLY USERの概要

Oracle23aiの新機能として実装された「READ ONLY USE」では、ユーザー単位で読み取り専用を設定できるようになりました。

実際に、読み取り専用ユーザーの動きについて検証してみた結果をレポートします。

2. 検証結果

まず、検証に使用するユーザーを作成します。

SQL> create user test1 identified by test1
  2  default tablespace users temporary tablespace temp
  3  quota unlimited on users;
ユーザーが作成されました。

SQL> create user test2 identified by test2
  2  default tablespace users temporary tablespace temp
  3  quota unlimited on users;
ユーザーが作成されました。

SQL> grant create session , create table , create sequence to test1,test2;
権限付与が成功しました。

test1ユーザーで接続し、テスト用のテーブルとシーケンスを作成します。

SQL> conn test1/test1@FREEPDB1
接続されました。
SQL> create table testtab (col1 number , col2 varchar2(50));
表が作成されました。

sql> create sequence testseq start with 1
  2      increment by 1
  3      maxvalue 9999
  4      minvalue 1
  5      cycle
  6      cache 20
  7      order;
順序が作成されました。

テスト用テーブルにデータを insert します。

SQL> insert into testtab values (testseq.nextval , 'test');

1行が作成されました。
SQL> insert into testtab values (testseq.nextval , 'test');

1行が作成されました。
SQL> commit;
コミットが完了しました。

select文で insert された内容を確認します。

SQL> select * from testtab;

      COL1 COL2
---------- --------------------------------------------------
         1 test
         2 test

systemユーザーに切り替え、test1ユーザーをread onlyに変更します。

SQL> conn system/system@FREEPDB1
接続されました。
SQL> alter user test1 read only;

ユーザーが変更されました。

テスト用ユーザーで接続し、テスト用テーブルにデータを insert しようとするとエラーになり insert できません。

SQL> conn test1/test1@FREEPDB1;
接続されました。
SQL> insert into testtab values (testseq.nextval , 'test');
insert into testtab values (testseq.nextval , 'test')
*
行1でエラーが発生しました。:
ORA-28194: 読取り操作のみを実行できます ヘルプ:
https://docs.oracle.com/error-help/db/ora-28194/


ユーザーが変更されました。

select 文を実行すると、以下の通り正常に実行可能です。

SQL> select * from testtab;

      COL1 COL2
---------- --------------------------------------------------
         1 test
         2 test

select 文で sequence の nextval の値を取得することは可能です。

SQL> select testseq.nextval from dual;

   NEXTVAL
----------
         3

grant 文で他のユーザーに対して権限付与を行うことはできません。

SQL> grant select on testtab to test2 ;
grant select on testtab to test2
*
行1でエラーが発生しました。:
ORA-28194: 読取り操作のみを実行できます ヘルプ:
https://docs.oracle.com/error-help/db/ora-28194/

systemユーザーで接続し、test1ユーザーをread writeに戻します。

SQL> conn system/system@FREEPDB1
接続されました。
SQL> alter user test1 read write;

ユーザーが変更されました。

test1ユーザーで接続し、テーブルとシーケンスに対する権限を付与します。

SQL> conn test1/test1@FREEPDB1
接続されました。
SQL> grant insert , select , update , delete on testtab to test2;

権限付与が成功しました。
SQL> grant select on testseq to test2;

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

systemユーザーで接続し、test1ユーザーをread onlyにします。

SQL> conn system/system@FREEPDB1
接続されました。
SQL> alter user test1 read only;

ユーザーが変更されました。

test2ユーザーで接続し、test1ユーザーのテーブルに対して insert を行うと正常に実行されます。
test1ユーザーはread onlyですが、権限を持つtest2ユーザーであれば insert は可能となります。

SQL> conn test2/test2@FREEPDB1;
接続されました。
SQL> insert into test1.testtab values (3,'test2');

1行が作成されました。
SQL> commit;

コミットが完了しました。
SQL> select * from test1.testtab;

      COL1 COL2
---------- --------------------------------------------------
         1 test
         2 test
         3 test2

3. まとめ

READ ONLYはユーザーの機能を使うことにより、自身の所有するテーブルに対しても更新不可な参照用ユーザーを簡単に作成することができます。
必要に応じて本機能を利用してみてください。