READ privilege in Oracle 12c

Starting with Oracle 12c we can now use READ object privilege to enable the users to query database tables, views, materialized views and synonyms. With this new READ object privilege, users can still query the objects like SELECT object privilege, but no more locks on the objects are possible.

 

 

demo@ORCL> create table t as select * from scott.dept;

 

Table created.

 

demo@ORCL> grant READ ON t to scott;

 

Grant succeeded.

 

demo@ORCL> conn scott/tiger

Connected.

scott@ORCL> select count(*) from demo.t;

 

  COUNT(*)

----------

         4

 

scott@ORCL> lock table demo.t in exclusive mode;

lock table demo.t in exclusive mode

                *

ERROR at line 1:

ORA-01031: insufficient privileges

 

 

scott@ORCL> begin

  2     for x in (select * from demo.t for update of deptno)

  3     loop

  4             null;

  5     end loop;

  6  end;

  7  /

        for x in (select * from demo.t for update of deptno)

                                     *

ERROR at line 2:

ORA-06550: line 2, column 31:

PL/SQL: ORA-01031: insufficient privileges

ORA-06550: line 2, column 12:

PL/SQL: SQL Statement ignored

 

 

scott@ORCL>

 

Now let’s remove the READ privilege and grant the SELECT privilege.

 

demo@ORCL> revoke READ on t from scott;

 

Revoke succeeded.

 

demo@ORCL> grant SELECT on t to scott;

 

Grant succeeded.

 

With this SELECT privilege, users can still query the objects.

 

demo@ORCL> conn scott/tiger

Connected.

scott@ORCL> select count(*) from demo.t;

 

  COUNT(*)

----------

         4

 

But lock the rows in the table are possible through SELECT privileges.

 

scott@ORCL> lock table demo.t in exclusive mode;

 

Table(s) Locked.

 

scott@ORCL> rollback;

 

Rollback complete.

 

scott@ORCL> begin

  2     for x in (select * from demo.t for update of deptno)

  3     loop

  4             null;

  5     end loop;

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

scott@ORCL> rollback;

 

Rollback complete.

 

scott@ORCL> conn demo/demo

Connected.

demo@ORCL> select grantor,table_name,grantee,privilege

  2  from user_tab_privs_made

  3  where grantor='DEMO'

  4  and table_name ='T'

  5  /

 

GRANTOR    TABLE_NAME           GRANTEE    PRIVILEGE

---------- -------------------- ---------- ----------

DEMO       T                    SCOTT      SELECT

 

demo@ORCL> revoke select on t from scott;

 

Revoke succeeded.

 

demo@ORCL> grant read on t to scott;

 

Grant succeeded.

 

demo@ORCL> select grantor,table_name,grantee,privilege

  2  from user_tab_privs_made

  3  where grantor='DEMO'

  4  and table_name ='T'

  5  /

 

GRANTOR    TABLE_NAME           GRANTEE    PRIVILEGE

---------- -------------------- ---------- ----------

DEMO       T                    SCOTT      READ

 

demo@ORCL>