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.
Let’s create a table T and grant SELECT right on it to TEST:
SQL> connect system/oracle@sol01 Connected. SQL> create table t(a) as select 1 from dual; Table created. SQL> SQL> grant create session to test identified by test; Grant succeeded. SQL> SQL> grant select on t to test; Grant succeeded.
Let’s connect as TEST and see what we can do:
SQL> connect test/test@sol01
Connected.
SQL>
SQL> select * from system.t for update;
A
----------
1
SQL>
SQL> lock table system.t in exclusive mode;
Table(s) Locked.
SQL>
We were just able to lock the table in exclusive mode with just a select privilege. “Nice!” (note the quotation marks)
So how can we work around it? The first thing that comes into my mind is creating a read only view on the table and granting select just on that view. Lets try it:
SQL> connect system/oracle@sol01
Connected.
SQL>
SQL> revoke select on t from test;
Revoke succeeded.
SQL>
SQL> create view v as select * from t with read only;
View created.
SQL>
SQL> grant select on v to test;
Grant succeeded.
SQL>
SQL> connect test/test@sol01
Connected.
SQL>
SQL> select * from system.v for update;
A
----------
1
SQL>
SQL> lock table system.v in exclusive mode;
Table(s) Locked.
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!
So, what did actually get locked?
SQL> select sid,type,id1,id2,lmode,request from v$lock where sid = ( select sid from v$mystat where rownum = 1 );
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
470 TM 71149 0 6 0
470 TX 458794 6239 6 0
SQL> select owner, object_name from dba_objects where object_id = 71149;
OWNER OBJECT_NAME
------------------------------ -----------------------------------------
SYSTEM T
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:
SQL> connect system/oracle@sol01
Connected.
SQL>
SQL> create or replace view v as select * from t union all select * from t where 1=0 with read only;
View created.
SQL>
SQL> connect test/test@sol01
Connected.
SQL>
SQL> select * from system.v for update;
select * from system.v for update
*
ERROR at line 1:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
Nice! (without quotation marks this time :-)
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:
SQL> connect system/oracle@sol01 Connected. SQL> SQL> alter table t disable table lock; Table altered. SQL> SQL> connect test/test@sol01 Connected. SQL> SQL> lock table system.v in exclusive mode; lock table system.v in exclusive mode * ERROR at line 1: ORA-00069: cannot acquire lock -- table locks disabled for T
So, when TM table locks are disabled (or dml_locks is set to 0) you can not lock it.
There are few gotchas with disabled table locks though – you need to reenable them before truncating or doing any DDL on them:
SQL> connect system/oracle@sol01
Connected.
SQL>
SQL> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for T
SQL>
SQL> alter table t enable table lock;
Table altered.
SQL> truncate table t;
Table truncated.
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:
SQL> alter session set statistics_level=all;
Session altered.
SQL> select * from system.v where a = 1;
A
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID g3qy3gtcu7s70, child number 1
-------------------------------------
select * from system.v where a = 1
Plan hash value: 606164380
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 1 | VIEW | V | 1 | 55 | 1 |00:00:00.01 | 3 |
| 2 | UNION-ALL PARTITION| | 1 | | 1 |00:00:00.01 | 3 |
|* 3 | TABLE ACCESS FULL | T | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 4 | FILTER | | 1 | | 0 |00:00:00.01 | 0 |
|* 5 | TABLE ACCESS FULL| T | 0 | 1 | 0 |00:00:00.01 | 0 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"=1)
4 - filter(NULL IS NOT NULL)
5 - filter("A"=1)
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)
