SQL> sho user
USER is "SYS"
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL>
Next we can create a sample user VV and grant some permissions to do READ and WRITE:
SQL> create user vv identified by vv;
User created.
SQL> grant create session to vv;
Grant succeeded.
SQL> grant select,insert on orablog.customer to vv;
Grant succeeded.
SQL> grant execute on orablog.custa to vv;
Grant succeeded.
SQL>
Next connect to the new user and test that we can do read and write:
SQL> connect vv/vv@//192.168.56.18:1521/freepdb1
Connected.
SQL>
-- test
SQL> select count(*) from orablog.customer;
COUNT(*)
----------
3
SQL> select * from orablog.customer;
FULLNAME FIRSTNAME
------------------------------ ------------------------------
LASTNAME
------------------------------
Pete Finnigan Pete
Finnigan
Zulia Finnigan Zulia
Finnigan
Eric Finnigan Eric
Finnigan
SQL> insert into orablog.customer (fullname,firstname,lastname) values ('EmilFinnigan','Emil','Finnigan');
1 row created.
SQL> set serveroutput on
SQL> exec orablog.custa('Finnigan');
name:=[Pete Finnigan]
name:=[Zulia Finnigan]
PL/SQL procedure successfully completed.
SQL>
Now we can connect to SYS and change our sample user VV to be READ ONLY:
SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> alter user vv read only;
User altered.
SQL>
For interest this setting of READ ONLY can also be done at creation time of a user:
SQL> sho user
USER is "SYS"
SQL> create user vx identified by vx read only;
User created.
SQL>
Connect as vv and redo the select statements:
SQL> connect vv/vv@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from orablog.customer;
COUNT(*)
----------
4
SQL> select * from orablog.customer;
FULLNAME FIRSTNAME
------------------------------ ------------------------------
LASTNAME
------------------------------
Pete Finnigan Pete
Finnigan
Zulia Finnigan Zulia
Finnigan
EmilFinnigan Emil
Finnigan
FULLNAME FIRSTNAME
------------------------------ ------------------------------
LASTNAME
------------------------------
Eric Finnigan Eric
Finnigan
SQL>
These work correctly. What if we try an insert statement that VV has the right to do via grants:
SQL> insert into orablog.customer(fullname,firstname,lastname) values ('TestUser','Test','User');
insert into orablog.customer(fullname,firstname,lastname) values ('TestUser','Test','User')
*
ERROR at line 1:
ORA-28194: Can perform read operations only
SQL>
So, good, READ ONLY works as we are not allowed to do the INSERT even though we have permissions to do an INSERT on that table.
Now try the PL/SQL procedure:
SQL> set serveroutput on
SQL> exec orablog.custa('Finnigan');
name:=[Pete Finnigan]
name:=[Zulia Finnigan]
PL/SQL procedure successfully completed.
SQL>
Hmmm, so we only do READ on ORABLOG.CUSTOMER and not INSERT but we executed PL/SQL. Lets try a different example:
SQL> exec dbms_output.put_line('Test test');
Test test
PL/SQL procedure successfully completed.
SQL>
Hmmm, so we can run PL/SQL so it's not just READ ONLY to me, we can do EXECUTE?
Can we make common users read only. Connect to the PDB and try:
SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> alter session set container=cdb$root;
Session altered.
SQL> create user c##vv identified by c##vv read only;
create user c##vv identified by c##vv read only
*
ERROR at line 1:
ORA-28192: Enabling or disabling read-only property is not allowed on a common
user.
SQL>
So we cannot add READ ONLY to COMMON users although a COMMON READ ONLY user may be useful
Can we do an INSERT via PL/SQL
SQL> sho user
USER is "SYS"
SQL> grant create procedure to vv;
Grant succeeded.
SQL> connect vv/vv@//192.168.56.18:1521/freepdb1
Connected.
SQL> create procedure run as
2 begin
3 insert into orablog.customer(fullname,firstname,lastname) values ('TestName','Test','Name');
4 end;
5 /
create procedure run as
*
ERROR at line 1:
ORA-28194: Can perform read operations only
SQL>
Very interesting. So we cannot create a procedure as we are READ ONLY as the procedure is written to the database and violates READ ONLY. Can we create a dynamic procedure:
SQL> get dyn.sql
1 -- test dyn proceudre to test read only
2 -- --------------------------------------------------
3 -- Create the "procedure"
4 -- --------------------------------------------------
5 var lv_str varchar2(2000);
6 var curnum number;
7 -- null the string
8 exec :lv_str:='';
9 declare
10 begin
11 :curnum:=dbms_sql.open_cursor;
12 dbms_sql.parse(:curnum,'declare bv varchar2(2000):=:ipstr; begin dbms_output.put_line(bv);end;',dbms_sql.native);
13 end;
14 /
15 print curnum
16 -- -------------------------------------------------------
17 -- end of declaring the "procedure"
18 -- -------------------------------------------------------
19 -- -------------------------------------------------------
20 -- Call the dynamic procedure
21 -- -------------------------------------------------------
22 -- prime the string to print it
23 exec :lv_str:='hello world';
24 declare
25 n number;
26 begin
27 -- bind the variable
28 dbms_sql.bind_variable(:curnum,':ipstr',:lv_str);
29 n:=dbms_sql.execute(:curnum);
30 end;
31 /
32 -- --------------------------------------------------------
33 -- End of dynamic procedure call
34* -- --------------------------------------------------------
35 .
SQL>
SQL> @dyn
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
CURNUM
----------
1923939752
PL/SQL procedure successfully completed.
hello world
PL/SQL procedure successfully completed.
SQL>
So we can get around the restriction of creating a procedure BUT can we do an insert in this procedure. Lets modify dyn.sql:
SQL> get dyn.sql
1 -- test dyn proceudre to test read only
2 -- --------------------------------------------------
3 -- Create the "procedure"
4 -- --------------------------------------------------
5 var curnum number;
6 declare
7 begin
8 :curnum:=dbms_sql.open_cursor;
9 dbms_sql.parse(:curnum,'begin insert into orablog.customer(fullname,firstname,lastname) values(''TestUser'',''Test'',''User'');end;',dbms_sql.native);
10 end;
11 /
12 print curnum
13 -- -------------------------------------------------------
14 -- end of declaring the "procedure"
15 -- -------------------------------------------------------
16 -- -------------------------------------------------------
17 -- Call the dynamic procedure
18 -- -------------------------------------------------------
19 declare
20 n number;
21 begin
22 n:=dbms_sql.execute(:curnum);
23 end;
24 /
25 -- --------------------------------------------------------
26 -- End of dynamic procedure call
27* -- --------------------------------------------------------
28 .
SQL> @dyn
declare
*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1169
ORA-06512: at line 4
CURNUM
----------
declare
*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1792
ORA-06512: at line 4
SQL>
No, we cannot do the INSERT via a dynamic block of PL/SQL. ORA-29471 is a security issue so it is blocked.
Turn READ ONLY off for the user VV to show how to reverse it:
SQL> sho user
USER is "SYS"
SQL> alter user vv read write;
User altered.
SQL>
Try the dynamic procedure again after we made the user READ WRITE:
SQL> connect vv/vv@//192.168.56.18:1521/freepdb1
Connected.
SQL> @dyn
PL/SQL procedure successfully completed.
CURNUM
----------
1166850649
PL/SQL procedure successfully completed.
SQL>
What about storage of the READ ONLY status for users. We need to be able to check the database and test which users are READ ONLY or not:
SQL> desc dba_users
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
LOCAL_TEMP_TABLESPACE VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(128)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(17)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED VARCHAR2(1)
INHERITED VARCHAR2(3)
DEFAULT_COLLATION VARCHAR2(100)
IMPLICIT VARCHAR2(3)
ALL_SHARD VARCHAR2(3)
EXTERNAL_SHARD VARCHAR2(3)
PASSWORD_CHANGE_DATE DATE
MANDATORY_PROFILE_VIOLATION VARCHAR2(3)
PROTECTED VARCHAR2(3)
READ_ONLY VARCHAR2(3)
DICTIONARY_PROTECTED VARCHAR2(3)
SQL>
Now we can check the READ_ONLY column and see which users of the database are READ ONLY:
SQL> select username from dba_users where read_only='YES';
USERNAME
--------------------------------------------------------------------------------
VX
SQL>
This is correct as we made VV into READ WRITE above and created VX as READ ONLY
The flag is stored as a bit flag in the SPARE1 column of SYS.USER$:
decode(bitand(u.spare1, 524288), 524288, 'YES', 'NO'),
-- READ_ONLY
In summary, READ ONLY allows SELECT and blocks INSERT/UPDATE and DELETE but we can still do EXECUTE but where the PL/SQL didn't try and change data. I don't know the extent of what "is / is not" allowed in PL/SQL when a user is READ ONLY. More testing would show this further
This is a useful feature to block change BUT if an attacker wants to steal all of the Personal Information or credit card details from your database then he doesn't need change, he only needs read!!
#23c #oracleace #dbsec #oracle #security #databreach #readonly
