USE GRANT READ instead of GRANT SELECT in Oracle 12c

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:

dummy account

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;

OR

SQL> select * from DUMMY_RECORDS for update;

exclusive lock.jpg

Now, let us revoke (GRANT SELECT) and use (GRANT READ) on the table

grant read 1

grant read 2

as shown above, after logging with the account we were not able to exclusively  lock the table and ORA-01031 was thrown.

Important Remarks:

  • 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)

 

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s