Dienstag, 18. September 2012

side channel attack on ORA-00942

Oracle Databases has a powerful set of grants and permissions. One of the easy philosophies behind it is just to hide anything a user is not allowed to see. Technically this leads to an error message
ORA-00942: table or view does not exist.
More precisely it should give a text like table or view does not exist or you are not allowed to access it.
For an ordinary user/schema separation there might be no big difference: If user A can not read table B.TAB it's of no value for user A whether the object does not exist or is just not accessible. But from a security point of view it might be of some interest if there exists a particular object within the database. Even it's not exploitable yet, maybe it's worth to try to reach it, or there is a combined vector of attack together with other objects.
To gain information about a system which should be kept hidden a well known method for physical cryptosystems is the side channel attack. At this attack extra information about the system is gained by measuring external channels like timings, power consumption and so on.
I am showing a similar method to get many informations about objects within a database a user should not know in general.

The described attack combines 2 well known concepts of every Oracle database: row cache and session statistics.

The row rache (or data dictionary cache) ... is a collection of database tables and views containing reference information about the database, its structures, and its users. - It's important to know this cache is filled by SQL-statements against the data dictionary tables.

session statistics just increase a counter for every session every time an action is executed.

Now let's check if we can get some hidden information out of my test system.
I did a alter system flush shared_pool; to make it slightly easier for the testcase, but even with a pre-filled row cache I am sure these informations can get collected with only little more effort.

My measurement query is
select ms.sid, ms.statistic#, sn.name, ms.value 
from v$mystat ms, V$STATNAME sn 
where sn.name in ('recursive calls', 'execute count') and ms.statistic# = sn.statistic# order by 2;
with a result like

       SID STATISTIC# NAME                      VALUE
---------- ---------- -------------------- ----------
       221          9 recursive calls            3108
       221        588 execute count               947

Now let's see where we can go. Something easy for the beginning. A select from a table which does not exist:

SQL> select * from gfdsaqwert;
select * from gfdsaqwert
ERROR at line 1:
ORA-00942: table or view does not exist
with the measurement

       SID STATISTIC# NAME                      VALUE
---------- ---------- -------------------- ----------
       221          9 recursive calls            3111
       221        588 execute count               950
this means, there where 3 recursive calls and 3 execute counts.

Now for something more interesting - let's check if a user exists:

SQL> select * from zzz.uztrfghj;
select * from zzz.uztrfghj
ERROR at line 1:
ORA-00942: table or view does not exist
20 recursive calls
 3 execute count

SQL> select * from a.uztrfghj;
select * from a.uztrfghj
ERROR at line 1:
ORA-00942: table or view does not exist
21 recursive calls
 3 execute count

In my test-DB no user ZZZ exists, but a user A exists. (but no table A.UZTRFGHJ) There is one more recursive call if the user exists. Not much, but a difference.

SQL> select * from a.m ;
select * from a.m
ERROR at line 1:
ORA-00942: table or view does not exist
26 recursive calls
 7 execute count

Now there are even more recursive calls and execute count. The Table A.M exists in my system.

I'd say this shows the concept. The official answer from the database was ORA-00942: table or view does not exist all the time. But v$mystat gave us some more information we did not get officially.


Gokhan Atil hat gesagt…

Hi Martin,

Interesting idea but when I performed similar tests (on (, I noticed that the results are effected by oracle cache mechanism. So they are not reliable.

I did several test and here are the increments of recursive calls and execute count according to if table exists or not:

Own schema / table not exists: 1,1
Own schema / table exists returns 1 row: 2,0
Other schema / table not exists: 1,1
Other schema / table exists: 1,1

first number shows increment of "execution count", second one shows increment of "recursive calls".

I flushed shared pool and buffer cache, then test again:

Own schema / table not exists: 3,3
Own schema / table exists returns 1 row: 14,47
Other schema / table not exists: 2,2
Other schema / table exists: 12,28

After second run (even I disconnect/re-connect my user), I see the same results of the first test.



Martin Berger hat gesagt…

Hi Gokhan,

thank you for stopping by, and doing you own tests!
You show a perfect working row cache.
Your first set shows values with a filled cache.
After a flush shared pool your 2nd set shows somehow 'usable' values for a side channel attack. Afterwards the row cache is filled again.
As the row cache resides in SGA it is persistent even for disconnect/reconnect.
Maybe it's worth to mention the row cache also caches the non-existance of an user/object/...

You are right, the row cache affects the results.
I also did not provide a walkthrough to attack a system, that was never my intention; I tried to show there are side channels. Maybe it's possible to poison the row cache ( I have not tested this) or use even more statistics to gain better results.