Pete Finnigan's Oracle Security Weblog

I just posted my previous blog entry on read only users and I checked the mailing list its based on (Oracle-l) and found a new post by Gints. This is an excellent post so I decided to post an update here about it as his comments and ideas are important.

Gints reminds us that programmed interfaces such as those described by the poster on oracle-l may not just query the data but also create audit records into some application log or system tables. This could be done to record what data application users are viewing or changing during normal operations or could be used to log connection attempts or when procedures are called or even for performance metrics. He goes on to suggest that the select procedures may possibly do inserts or updates of audit records even just for read access.

He also mentions in a similar vein to me that read only requirements should not be generalised. Access to data should be segregated for insert, update, delete and select and even segregated on data subsets. He even suggests that access to historical and current data may need to be segregated.

Gints finishes on a similar note to me in saying that it a pity this is a late requirement as it would have been better to design this sort of access properly in at the start of a project.

These are some excellent comments on the issues of read only users. I had not remembered about audit records generated at the application level. Good call. If audit is enabled at the table level either through normal audit trail or via table or view level triggers then no matter the access method - either direct or via programmed interface the audit will be created. The user recorded may vary though based on whether the programmed interface is definer or invoker.

Fine grained audit also will not be fooled by a read only user created above the application. Any policies created will still fire no matter the type of access used.

Application based audit though needs to be considered properly when read only users are created that do not access the data through the approved API.