Oracle 12c – READ Privilege

The READ privilege is new in Oracle Database 12c 12.1.0.2. Why do we need a READ privilege when there is SELECT privilege? Well, the SELECT privilege includes few more privilege than a pure read-only privilege! With the SELECT privilege, in addition to reading (or “selecting” from) the table, you are also able to do

  • LOCK TABLE<table_name> IN EXCLUSIVE MODE;
  • SELECT … FROM<table_name> FOR UPDATE;

So, for better security to enforce pure read-only privilege Oracle needed another privilege, which is READ privilege. You can use the READ object privilege or READ ANY TABLE system privilege. Both operate similar to their SELECT counterpart, except for the privileges listed above.

No, there is no READ statement to go with the READ privilege. You will still be using the SELECT statement to read from the table or view. You use the READ object privilege to enable users query database tables, views, materialized views, and synonyms. The READ ANY TABLE privilege enables to query any table or view in the database.

So, from now onwards, you may start granting the READ or READ ANY TABLE privilege instead of SELECT or SELECT ANY TABLE, to the users requiring only the query privilege on the table or view for better security.

Examples of grants and revoke:

SQL> GRANT READ ON XX.XXLE_CUSTOMERS_T TO smith;

SQL> GRANT READ ON XX.XXLE_SUPPLIERS_VW TO smith;

SQL> GRANT READ ANY TABLE TO peter;

SQL> REVOKE READ ON XX.XXLE_SUPPLIERS_VW FROM smith;

SQL> REVOKE READ ANY TABLE FROM peter;

Leave a Reply

%d bloggers like this: