Normally when we want to grant an oracle database account access to read records form certain tables, we use the SQL command (GRANT SELECT), however this is found to be not the best security practice. And, new security feature has been introduced in Oracle 12c which is GRANT READ.
To illustrate more,
I have created a dummy account named “dummy_test” with the following basic privileges:
And created a dummy table with random values called “DUMMY_RECORDS”, and executed the below SQL statement to grant the user access to read records from the table:
SQL> grant select on DUMMY_RECORDS to dummy_test ;
Now….the interesting part is the following…..i will be able to exclusively LOCK the table !!!
either by executing the following:
SQL> lock table DUMMY_RECORDS in exclusive mode;
SQL> select * from DUMMY_RECORDS for update;
Now, let us revoke (GRANT SELECT) and use (GRANT READ) on the table
as shown above, after logging with the account we were not able to exclusively lock the table and ORA-01031 was thrown.
- this security feature is only available in 12c release.
- some applications could frequently use (select* from table for update) frequently so you need to test the consequences of using the GRANT READ permission.
- the purpose of this security feature is that it will prevent the hacker who stole the credentials of the account to lock the table which will block transactions and impact the running the application ! (denial of service)