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 AFEECTING THE USUAL SELECT PRIVILEGE.

    Lets compare the difference between both the privilege. 

 

GRANT SELECT VS GRANT READ :

GRANT SELECT:

— create a user with grant select privilege

create user TEST_USR1 identified by TEST_USR1;

grant create session to TEST_USR1;

grant SELECT ON  dba_raj.testing to test_usr1;

 

with this select privilege, it can run select for update also.

SESSION 1( connect with test_usr1)

connect test_usr1/test_usr1

select * from dba_raj.testing for update;

 

SESSION 2 ( Open another session as owner of the table):

SQL> connect dba_raj/dba_raj
Connected.
SQL> show user
USER is “DBA_RAJ”

SQL> update dba_raj.testing set OBJECT_NAME=’TEST’ where owner=’SYS’;

<<< it is stuck>>

 

SESSION 3 ( check the blocking session ) :

Get the monitoring script DB MONITORING SCRIPT

SQL> @blocker

INST_ID BLOCKING_SESSION SID SERIAL# SECONDS_IN_WAIT
———- —————- ———- ———- —————
1 246 321 2709 30

SQL> @csql
Enter value for 1: 321
old 2: (select sql_hash_value from gv$session where sid=&1)
new 2: (select sql_hash_value from gv$session where sid=321)

SQL_TEXT
——————————————————————————–
update dba_raj.testing set OBJECT_NAME=’TEST’ where owner=’SYS’
SQL> @csql
Enter value for 1: 246
old 2: (select sql_hash_value from gv$session where sid=&1)
new 2: (select sql_hash_value from gv$session where sid=246)

SQL_TEXT
——————————————————————————–
select * from dba_raj.testing for update

 

We have noticed that despite the user  TEST_USR1   has only GRANT SELECT privilege on dba_raj.testing table, it is able to lock the table .

 

GRANT READ :

— create user with grant read privilege 

create user TEST_USR2 identified by TEST_USR2;

grant create session to TEST_USR2;

grant READ ON  dba_raj.testing to test_usr2;

 

this user will be able to do select operation as usual:

SQL> connect TEST_USR2/TEST_USR2
Connected.
SQL> select count(*) from dba_raj.testing;

COUNT(*)
———-
85812

Lets try the same command select for update.( It wont allow this operation) 
SQL> select * from dba_raj.testing for update;
select * from dba_raj.testing 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