READ vs SELECT Privilege

Are you aware of the READ object privilege in Oracle? If yes, what was your first thought of it when you saw it? Mine was that it’s most likely the same as the SELECT object privilege, it’s just a synonyms for SELECT. But then I got curious and started to look into it, to better understand it.

Here is a question, that I would have answered NO to it, for sure! Did you know that if you grant SELECT privilege on a table to a user, that user not only can select (read) the data from the table, but can also lock the rows in the table and even lock the entire table?

This is a bit problematic, it could prevent other sessions, that have DML access to the table to not be able to perform the updates/deletes/inserts. I was totally not aware of this!

In 12.1, Oracle introduced the READ privilege, which only grants read only access on the object, no lock privilege on the object!

Now, that you are aware of this difference, make sure you grant really what is only needed. If the user requesting the access only needs R/O then grant them READ privilege! If you know they need to be able to also lock rows, then grant SELECT!

-- Instead of:
grant SELECT on test_tbl to hr;

-- Try this:
grant READ on test_tbl to hr;

So if this is a fighting match between READ and SELECT, who do you thing it’s the winner? From a security standpoint, the winner is READ! From a power standpoint, the winner is SELECT (it is more powerful)!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $13.99 CAD !