Oracle Security, Part 2: Your read only accounts aren’t that read only

Couple of years ago an interesting fact floated up in Oracle-L – a regular user with only SELECT privilege on a table can successfully execute a SELECT FOR UPDATE against it, locking all rows and even lock the whole table using LOCK TABLE command. Locking a table in exclusive mode would stall all changes and selects against that table – effectively hanging all applications using that table. Pete Finnigan wrote a review of the issue in this blog entry.

This means that many of the “read only” accounts used by support or reporting users aren’t really that read only – these accounts could be used for a (hopefully) short denial of service attack and leaves another opportunity for human error to cause trouble in production environments. This issue applies both for direct user sessions and dblinks.

This behaviour came as news to me back then and no real solution for this issue has been proposed so far. So, while I do not provide any new information regarding the problem itself, I do propose a solution for it.

First lets start from reproducing the problem case. Note that I use two users, SYSTEM as the table owner and TEST as the “read only” user.

What?! Despite having select access to the view V only (table T access was revoked), I was still able to select for update from it! Moreover, I was able to issue a LOCK TABLE command against that view!

From above we see that I was able to lock the underlying table of a view even with having no rights directly on the table itself and even when the view had been created with READ ONLY option. The reason for Oracle “ignoring” the READ ONLY option is that the option only affects DML commands, but not the SELECT (and apparently LOCK).

So, is there any way for preventing SELECT FOR UPDATEs against views?

Yes, the best way I’ve came up with so far is adding a dummy query with UNION ALL clause into the view:

Apparently there is a restriction that SELECT FOR UPDATE can not be done against views with UNION ALLs. I’ve tested this in versions up to 11g (though it may well happen that such restriction is lifted in some future version).

As the second query appended using UNION ALL doesn’t return any rows ( thanks to the WHERE 1=0 clause ) then the result set will be not affected, all rows are returned from the underlying table exactly as they are.

Let’s see whether we can still lock the underlying table with LOCK TABLE command?

SQL> lock table system.v in exclusive mode;
Table(s) Locked.

Ugh… The table-level locking still works. But for this one we have another workaround:

I consider this actually a good thing. This prevents you from accidentially dropping or truncating a table. This can also help to save a tiny amount of CPU time as TM enqueue structures do not need to be maintained on tables whenever starting (and ending) a transaction on them. Btw, the positive effect can be even greater on RAC.

When using the disabled table lock approach, the database release procedures just need to have additional steps for enabling table locks when DDL is performed on them.

So this is a working solution (or maybe it’s better to call it a workaround) which allows you to have a really read-only user accounts. The majority of companies out there are probably not interested in implementing this solution (how often do such problems happen anyway, right?). However there definitely are some companies where it is important to eliminate even such “small” risk to their service.The obvious next question is whether this UNION ALL view impacts the query performance through those views. Does this UNION ALL cause the table to be scanned twice?

It’s easy to test out with profiling the query with statistics_level=all, which records the real rowcounts passed up from row sources and the rowsource function call counts:

Check execution plan line 4 (and the predicate NULL IS NOT NULL) below. This shows that the view predicate “WHERE 1=2” was internally translated to equivalent “WHERE NULL IS NOT NULL” (both return FALSE).
Thanks to this FILTER predicate always returning FALSE, Oracle did not even have to execute row source function in plan line 5 once (the Starts column shows 0).

So in this particular example, the UNION ALL addition did not have any adverse effect as it simply was not executed. This addition should always be eliminated from plan execution by Oracle query execution engine. However there may be cases where some query transformation feature or quirk in execution engine causes this additional access to happen. You may not even notice it, but if you do, its just matter of usual SQL tuning to get this fixed.

Update: Neil Chandler has also written a post about this behavior and has also covered the new Oracle 12c READ privilege (that allows selecting but, no select for update)

NB! If you want to move to the "New World" - offload your data and workloads to Hadoop, without having to re-write your existing applications - check out Gluent. We are making history! ;-)

3 Responses to Oracle Security, Part 2: Your read only accounts aren’t that read only

Very interresting, smart analysis, efficiently coded and above all usefull and re-usable.

However, I all sound like an Oracle conception bug when they put the ‘select for update’ into the category of ‘Select’ while it should be treated as an operation of the ‘update familly’ and the grants/role should follow. In short you should have the grant update to issue a ‘select for update’

Yep, I think there could be use for a “select for update” priv, or maybe making a select for update to require both “select” AND “update” privs in order to succeed – as it essentially does both select (retuns data to client) and an sort of an update (updating the lock byte in a row).

what’s even weirder though, is that you can lock a table in exclusive mode just with select priv :)