Pete Finnigan's Oracle Security Weblog

I saw an interesting thread on the ORACLE-L mailing list yesterday about how to create a read only user in Oracle. The poster said in the thread "Read Only User" the application and database had not been designed to accommodate this. She had created a role and granted SELECT access to the role on relevant tables but she was having issues with how to deal with access procedures written in PL/SQL that are presumably used by the application. She identified that she could create wrapper procedures to only expose the read functions or procedures of these packages.

Some of the posters suggested some good ideas, these ranged from create wrapper PL/SQL code, using triggers on each table to disallow any DML access and allow only query access. This, as the original poster suggested would be a big maintenance issue but the code could be generated where wrapper functions could not. Using triggers would allow execute privileges to be simply given on the packages as the triggers would prevent DML from succeeding. The original poster said that there could be performance issues with having a lot of triggers on the OLTP application / database.

Someone else made a good suggestion about using Row Level Security as one policy function can be created to prevent any access other than read access. Again there will be a slight performance hit but not as bad as triggers.

I suggested reading four previous entries in this blog. I said:

"I don't know if these are of use to you or not but I wrote a few entries
in my Oracle security weblog on this subject and similar. These are the
links:

"Creating read only users" -
/weblog/archives/00000009.htm

"creating read only tables" -
/weblog/archives/00000033.htm

"Allowing a user read-only access to stored procedure source code" -
/weblog/archives/00000052.htm

"Another good paper by Howard Rogers on read-only tables" -
/weblog/archives/00000063.htm"

The original poster hit on the real issue when she said:

"Thanks for your links - some useful material here. I like your comment: "The whole process requires
planning and forethought not quick fixes". This is exactly not what I've got here! Management tell
me "I just need to tick a box to make my Excel spreadsheet read-only" and wonder why I can't just do
the same for the database."

This is an interesting and I said in reply:

"Glad you found them useful. I think you have found the universal problem
in security, the fact that management do not appreciate how easy or hard
some things are to achieve in an Oracle environment..:-)

I am not sure if I have said this before in writing, I think I have, I
think Oracle should look seriously at letting customers easily specify a
read only user without resorting to wrapper code or any other non
trivial hacks. I can appreciate that this may not be easy to do but it
sure would be a useful feature. I don't mean just SELECT ANY TABLE but
being able to access the data through the same developed interfaces that
already have been coded."

My view with this issue of creating read only users which the original poster now says is for developers to view data in the production database. If possible it is better to not allow developers access to production systems anyway. I understand that this is often needed to resolve bugs but if so it needs to be done so that it can be disabled after use, either by revoking the privileges granted or by locking the accounts. The best approach is to plan ahead, design these users and roles in advance. If possible access the base data or views rather than go to the trouble of creating wrapper procedures around application code - this simplifies the issue. Most of all remember that access to ALL data is very very rarely needed. So don't grant access to all data. Review each request in advance or create users in advance that can access each logical business area of an application.