Grant READ privilege Oracle Database 12C release 1 (12.1)

New privileges READ, READ ANY TABLE have appeared  in Oracle Database 12c.

They are available since release 12.1.0.2.

They work almost the same as standard SELECT and SELECT ANY TABLE except SELECT and SELECT ANY TABLE can do additionally

--acquires exclusive lock on a table
LOCK TABLE <TABLE_NAME> IN EXCLUSIVE MODE;

--acquires row lock on a table rows
SELECT … FROM <TABLE_NAME> FOR UPDATE;

So simple grant SELECT lets users to make harm(can block other users) in a database just by executing above commands. It’s time to start using READ and READ ANY TABLE to avoid it and improve security.

You can grant/revoke them to the same objects like SELECT:

  • tables
  • views
  • materialized views
  • synonyms
GRANT READ ON test_table TO tomasz;
GRANT READ ON test_view TO tomasz;
GRANT READ ANY TABLE TO tomasz;

REVOKE READ ON test_view FROM tomasz;
REVOKE READ ANY TABLE FROM tomasz;

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *