LATEST TOPICS

Oracle 12c (12.1.0.2): Time to revoke SELECT (we have READ to offer)

In today’s post, I am going to discuss yet another enhancement that is introduced with Oracle database 12c (12.1.0.2). Prior to Oracle 12c (12.1.0.2), whenever we had a requirement to create read only users, we used to grant those users the SELECT privilege on the underlying tables. However, the SELECT privilege also has the ability to lock a table using the SELECT..FOR UPDATE option. This ability of SELECT command exposes the tables to be blocked by unprivileged users even though those users do not have the ability to modify the tables.

Therefore, granting SELECT privilege to read only users always has the risk associated with it as the read only users still have the ability to block (deny) transactions on the tables on which they have read access.

Lets quickly go through an example to validate these facts.

----//
----// create a user (test_ro) //----
----//
SQL> create user test_ro identified by test_ro;

User created.


----//
----// create a table T_TEST_RO under MYAPP schema //----
----//
SQL> create table MYAPP.T_TEST_RO as select * from all_users;

Table created.

----//
----// grant connect to test_ro user //----
----//
SQL> grant connect to test_ro;

Grant succeeded.


----//
----// grant select access to test_ro user on MYAPP.T_TEST_RO table //----
----//
SQL> grant select on MYAPP.T_TEST_RO to test_ro;

Grant succeeded.

We have created a user TEST_RO and granted it read only access to table MYAPP.T_TEST_RO by means of SELECT privilege. Lets login as this read-only user and see what operations it can do.

----//
----// login as test_ro user  //----
----//
SQL> conn test_ro/test_ro@mypdb_01
Connected.
SQL> show user
USER is "TEST_RO"

----//
----// note the SID of logged in session of test_ro user //----
----//
SQL> SELECT sys_context('USERENV', 'SID') SID  FROM DUAL;

SID
----------
28


----//
----// select from table MYAPP.T_TEST_RO //----
----//
SQL> select * from MYAPP.T_TEST_RO where username='SYS';

USERNAME      USER_ID CREATED   COM O
---------- ---------- --------- --- -
SYS                 0 13-SEP-15 YES Y

----//
----// select from table MYAPP.T_TEST_RO with for update option //----
----//
SQL> select * from MYAPP.T_TEST_RO where username='MYAPP' for update;

USERNAME      USER_ID CREATED   COM O
---------- ---------- --------- --- -
MYAPP              63 17-SEP-15 NO  N

----//
----// update not allowed as the user test_ro doesn't have update privs on the table //----
----//
SQL> update MYAPP.T_TEST_RO set common='YES' where username='MYAPP';
update MYAPP.T_TEST_RO set common='YES' where username='MYAPP'
             *
ERROR at line 1:
ORA-01031: insufficient privileges

The read-only user can select from the table (expected) as well as can lock the table by means of SELECT..FOR UPDATE (not intended) even though it doesn’t have the privilege to modify the table.

Lets connect to another session as a privileged user and try to modify the record which is accessed by the read-only user (TEST_RO) with SELECT..FOR UPDATE command.

----//
----// login in another session as a different (privileged) user //----
----//
SQL> show user
USER is "MYAPP"

SQL> SELECT sys_context('USERENV', 'SID') SID  FROM DUAL;

SID
----------
125

----//
----// try to perform a update on the table row which is selected by  //----
----// read only user test_ro in previous database session //----
----//
SQL> update MYAPP.T_TEST_RO set COMMON='YES' where username='MYAPP';

----// update will hang //-----

We are not able to modify the record in this session and the UPDATE command just hangs there. If we query the blocking sessions, we can see the read only user is blocking the update operation as shown below.

----//
----// update (session:125) is blocked by select (session:28) //----
----//
SQL> select WAITING_SESSION,HOLDING_SESSION,LOCK_TYPE,MODE_HELD,MODE_REQUESTED from dba_waiters;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE     MODE_HELD     MODE_REQUESTE
--------------- --------------- ------------- ------------- -------------
            125              28 Transaction   Exclusive     Exclusive

As we can see, although the read-only user only has the SELECT access, it still has the ability to block transactions from a privileged user by means of SELECT..FOR UPDATE statement. In that sense, the SELECT privilege is never a pure read only privilege and is always granted with the understanding that the grantee will not misuse the access.

Unfortunately, we did not have any other choice for granting read only access other than granting SELECT privilege until Oracle 12c (12.1.0.2). Oracle 12c (12.1.0.2) has introduced a pure read only privilege that can be granted to users which are intended to have only read access on application tables. These users can no longer block transactions on the underlying tables. With Oracle 12c (12.1.0.2), we can now grant READ or READ ANY TABLE privileges to provide pure read only access to database users.

Lets go through a quick example to validate how this new privilege works.

----//
----// create a user (test_ro) //----
----//

SQL> drop user test_ro;

User dropped.

SQL> create user test_ro identified by test_ro;

User created.

----//
----// grant connect to test_ro user //----
----//
SQL> grant connect to test_ro;

Grant succeeded.

----//
----// grant read access to test_ro user on MYAPP.T_TEST_RO table //----
----//
SQL> grant read on MYAPP.T_TEST_RO to test_ro;

Grant succeeded.

Lets login as the read only user (TEST_RO) and validate if it can still lock the underlying tables.

----//
----// login as test_ro user  //----
----//
SQL> conn test_ro/test_ro@mypdb_01
Connected.

SQL> show user
USER is "TEST_RO"

----//
----// try to perform a SLECT..FOR UDPATE on MYAPP.T_TEST_RO table  //----
----//
SQL> select * from MYAPP.T_TEST_RO where username='MYAPP' for update;
select * from MYAPP.T_TEST_RO where username='MYAPP' for update
                    *
ERROR at line 1:
ORA-01031: insufficient privileges

----//
----// perform a select from MYAPP.T_TEST_RO table  //----
----//
SQL> select * from MYAPP.T_TEST_RO where username='MYAPP';

USERNAME      USER_ID CREATED   COM O
---------- ---------- --------- --- -
MYAPP              63 17-SEP-15 NO  N

As we can see from this example, with the new READ access; database users can only select from the tables and can no longer lock the table by means of SELECT..FOR UDPATE which was the case prior to 12c (12.1.0.2) as there was no pure READ privilege.

No more mess up from read-only users. They are now pure read only. Time to start with the privilege clean up (revoke select)!!

No Responses
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed