SELECT vs READ - Difference - Pros - Cons - 12c New Feature

March 17, 2020  

A new privilege GRANT READ privilege has been introduced in oracle 12c.  While the function of GRANT READ privilege is almost same as that of GRANT SELECT privilege, but with a difference .

GRAND READ PRIVILEGE RESTRICT USERS FROM LOCKING THE TABLES, WITHOUT AFFECTING THE USUAL SELECT PRIVILEGE.

Do you think "SELECT" privilege is very innocent? Maybe we should consider that one more time.

Lets compare the difference between both the privilege.

GRANT SELECT vs GRANT READ:
=============================

GRANT SELECT:
------------------------

Creating a user and give priviliges which can select "hr.employees" table.

SQL> create user myuser identified by usr123;

User created.

SQL> grant connect, resource to myuser;

Grant succeeded.

SQL> grant select on hr.employees to myuser;

Grant succeeded.

Connect with new user and select employees table with "FOR UPDATE" option:

SQL> conn myuser
Enter password:
Connected.

SQL> show user
USER is "MYUSER"

SQL> select * from hr.employees for update;
.
.
.
107 rows selected.

As you see I could lock whole table only with "SELECT" privilege.

It means if you give "SELECT" privilege for a table to a specific user, that user is able to lock against DML transactions from other sessions.

So, it means you should be very careful while giving "SELECT" privilege or you can user "READ" privilege with 12c.

Let's try "READ" privilege

GRANT READ:
--------------------

SQL> create user myuser2 identified by usr123; 

User created.

SQL> grant connect, resource to myuser2;

Grant succeeded.

SQL> grant read on hr.employees to myuser2;

Grant succeeded.


Now, try to lock the table with "READ" privilege

SQL> conn myuser2;
Enter password:

SQL> show user;
USER is "MYUSER2"

SQL> select * from hr.employees for update;

select * from hr.employees for update
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

With oracle 12, its time to revoke GRANT SELECT and use GRANT READ privilege

Similar to GRANT SELECT ANY , we can use GRANT READ ANY  privilege

Note: If you are using Oracle Database 12c, it is much more better to give "READ" privilege to your users if they only need to see table's data.

Source: Oracle Internal