環境
・Oracle Database 23ai
※DBエディションやOSによって差異はなし
やれること
読み取りしかできないDBユーザを作成することができるようになりました。
データの変更・削除や、表の削除ができないため、安全にデータの参照をさせることができます。
DBユーザ自体を読み取り専用とすることもできますし、
通常は読み書き可能にしておいて、セッションを読み取り専用にすることも可能です。
なお、読み取り専用になるのは「そのDBユーザで接続したときの操作」です。
他の管理者権限をもったようなDBユーザから、読み取り専用DBユーザが所有するテーブルデータの操作は可能です。
そのため、「そのDBユーザで接続したときに誤ったことができないようにする」というのが主な目的になるかと思います。
DBユーザを読み取り専用にする構文
ALTER USER testuser READ ONLY;
読み書き可能の戻す構文
ALTER USER testuser READ WRITE;
実行例
(1) DBユーザの作成（読み書き可能状態）
まずは 接続とテーブル作成のみ可能なDBユーザを作成してみます。
また、そのDBユーザの読み取り可否を確認します。
ディクショナリ「DBA_USERS」に新しい「READ_ONLY」列が追加されています。
YESの場合、読み取り専用ユーザになっています。デフォルトは読み書き可能のため、「NO」になります。
SQL> connect system/Password_1_2_3@db23
接続されました。
SQL> CREATE USER readtest IDENTIFIED BY oracle QUOTA unlimited ON users;
ユーザーが作成されました。
SQL> GRANT create session,create table TO readtest;
権限付与が成功しました。
SQL> SELECT username,created,read_only FROM dba_users WHERE username = 'READTEST' ORDER BY username;
USERNAME CREATED REA
-------------------------------- -------- ---
READTEST 24-06-30 NO ★NOのため読み書き可能状態です
(2) テストテーブルの作成
テスト用のテーブルを作成し、読み書き可能の動作を確認します。
システム権限「CREATE TABLE」が付与されていれば、TABLEに関する全権があるため
データのINSERT,UPDATE,DELETEも、テーブルのALTER,DROPも可能です。
SQL> connect readtest/oracle@db23
接続されました。
SQL> create table t1 ( id number(1) );
表が作成されました。
SQL> insert into t1 values(1);
1行が作成されました。
SQL> commit;
コミットが完了しました。
SQL> select * from t1;
ID
----------
1
SQL> update t1 set id = 2;
1行が更新されました。
SQL> delete t1;
1行が削除されました。
SQL> rollback;
ロールバックが完了しました。
(3) DBユーザを読み取り専用にする
DBユーザを読み取り専用にするには、ALTER USER文を使用します。
結果は「DBA_USERS」の「READ_ONLY」列で確認可能です。
SQL> connect system/Password_1_2_3@db23
接続されました。
SQL> alter user readtest read only;
ユーザーが変更されました。
SQL> SELECT username,created,read_only FROM dba_users WHERE username = 'READTEST' ORDER BY username;
USERNAME CREATED REA
-------------------------------- -------- ---
READTEST 24-06-30 YES ★YESのため読み取り専用状態です
(4) 読み取り専用ユーザで何ができるか確認する
DML文も、DDL文も、ORA-28194でエラーになります。
SQL> connect readtest/oracle@db23
接続されました。
SQL> insert into t1 values(1);
insert into t1 values(1)
*
行1でエラーが発生しました。:
ORA-28194: 読取り操作のみを実行できます ヘルプ:
https://docs.oracle.com/error-help/db/ora-28194/
SQL> update t1 set id = 2;
update t1 set id = 2
*
行1でエラーが発生しました。:
ORA-28194: 読取り操作のみを実行できます ヘルプ:
https://docs.oracle.com/error-help/db/ora-28194/
SQL> delete t1;
delete t1
*
行1でエラーが発生しました。:
ORA-28194: 読取り操作のみを実行できます ヘルプ:
https://docs.oracle.com/error-help/db/ora-28194/
SQL> create table t2 ( id number(1));
create table t2 ( id number(1))
*
行1でエラーが発生しました。:
ORA-28194: 読取り操作のみを実行できます ヘルプ:
https://docs.oracle.com/error-help/db/ora-28194/
SQL> alter table t1 modify (id number(2));
alter table t1 modify (id number(2))
*
行1でエラーが発生しました。:
ORA-28194: 読取り操作のみを実行できます ヘルプ:
https://docs.oracle.com/error-help/db/ora-28194/
SQL> drop table t1;
drop table t1
*
行1でエラーが発生しました。:
ORA-28194: 読取り操作のみを実行できます ヘルプ:
https://docs.oracle.com/error-help/db/ora-28194/
SQL> create sequence seq1;
create sequence seq1
*
行1でエラーが発生しました。:
ORA-28194: 読取り操作のみを実行できます ヘルプ:
https://docs.oracle.com/error-help/db/ora-28194/
SQL> create view v1 as select * from t1;
create view v1 as select * from t1
*
行1でエラーが発生しました。:
ORA-28194: 読取り操作のみを実行できます ヘルプ:
https://docs.oracle.com/error-help/db/ora-28194/
(5) 読み書き可能に戻す
読み取り専用ユーザが自分自身で読み書き可能にすることはできません。
他のDB管理ユーザで行う必要があります。
SQL> connect readtest/oracle@db23
接続されました。
SQL> alter user readtest read write;
alter user readtest read write
*
行1でエラーが発生しました。:
ORA-28194: 読取り操作のみを実行できます ヘルプ:
https://docs.oracle.com/error-help/db/ora-28194/
SQL> connect system/Password_1_2_3@db23
接続されました。
SQL> SELECT username,created,read_only FROM dba_users WHERE username = 'READTEST' ORDER BY username;
USERNAME CREATED REA
-------------------------------- -------- ---
READTEST 24-06-30 YES
SQL> alter user readtest read write;
ユーザーが変更されました。
SQL> SELECT username,created,read_only FROM dba_users WHERE username = 'READTEST' ORDER BY username;
USERNAME CREATED REA
-------------------------------- -------- ---
READTEST 24-06-30 NO