联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle read only用户—23ai新特性:只读用户
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
23ai版本支持用户级别设置read only特性,对于在某些情况下,为了数据的一致性,是一个比较方便的特性,而不是以前版本通过权限控制实现只读,比如授权create session+表/视图等查询权限
下面创建一个用户u_readonly,并授权dba权限,创建一个表进行测试
[oracle@xifenfei ~]$ ss
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Sat Jan 11 21:12:09 2025
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
SQL>
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud an
d Engineered Systems
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XIFENFEI MOUNTED
SQL> alter session set container=xifenfei;
Session altered.
SQL> alter database open;
Database altered.
SQL> create user u_readonly identified by oracle;
User created.
SQL> grant dba to u_readonly;
Grant succeeded.
SQL> conn u_readonly/oracle@127.0.0.1/xifenfei
Connected.
SQL> create table t_xff as select * from dba_objects;
Table created.
SQL> select count(1) from t_xff;
COUNT(1)
----------
70951
修改用户为只读特性,然后进行dml/ddl操作会报ORA-28194: Can perform read operations only
SQL> conn / as sysdba
Connected.
SQL> alter session set container=xifenfei;
Session altered.
SQL> alter user u_readonly read only;
User altered.
SQL> conn u_readonly/oracle@127.0.0.1/xifenfei
Connected.
SQL> delete from t_xff;
delete from t_xff
*
ERROR at line 1:
ORA-28194: Can perform read operations only
SQL> insert into t_xff select * from dba_objects;
insert into t_xff select * from dba_objects
*
ERROR at line 1:
ORA-28194: Can perform read operations only
SQL> select count(1) from t_xff;
COUNT(1)
----------
70951
SQL> create table t1 as select * from dba_users;
create table t1 as select * from dba_users
*
ERROR at line 1:
ORA-28194: Can perform read operations only
直接使用create user命令创建一个只读用户
SQL> conn / as sysdba Connected. SQL> alter session set container=xifenfei; Session altered. SQL> create user u_readonly2 identified by oracle read only; User created. SQL> grant dba to u_readonly2; Grant succeeded. SQL> conn u_readonly2/oracle@127.0.0.1/xifenfei Connected. SQL> create table t_xifenfei as select * from dba_objects; create table t_xifenfei as select * from dba_objects * ERROR at line 1: ORA-28194: Can perform read operations only
修改只读用户为读写模式
SQL> conn / as sysdba Connected. SQL> alter session set container=xifenfei; Session altered. SQL> alter user u_readonly2 read write; User altered. SQL> conn u_readonly2/oracle@127.0.0.1/xifenfei Connected. SQL> create table t_xifenfei as select * from dba_objects; Table created. SQL> delete from t_xifenfei where rownum<100; 99 rows deleted. SQL> commit; Commit complete.
查看用户是否处于只读状态
SQL> select username,read_only from dba_users where created>sysdate-1; USERNAME READ_O ------------------------------ ------ U_READONLY2 NO U_READONLY YES
在只读用户中，使用动态plsql直接直接dml也报ORA-28194: Can perform read operations only
SQL> conn u_readonly/oracle@127.0.0.1/xifenfei
Connected.
SQL> select count(1) from t_xff;
COUNT(1)
----------
70951
SQL> delete from t_xff;
delete from t_xff
*
ERROR at line 1:
ORA-28194: Can perform read operations only
SQL> DECLARE
2 v_sql VARCHAR2(1000);
3 BEGIN
4 v_sql := 'delete from t_xff where rownum<1000';
5 EXECUTE IMMEDIATE v_sql;
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-28194: Can perform read operations only
ORA-06512: at line 5
判断用户是否只读的底层基表属性user$.spare1
SQL> conn / as sysdba Connected. SQL> alter session set container=xifenfei; Session altered. SQL> COL NAME FOR A30 SQL> select name,decode(bitand(spare1, 67108864), 67108864, 'YES', 'NO') 2 read_only from user$ where name like 'U_READONLY%' 3 / NAME READ_O ------------------------------ ------ U_READONLY YES U_READONLY2 NO
