Security flaw fixed

Unfortunately, I didn't have enough time to post this note earlier – I was totally booked, and that was much more important than Internet, web forums and blogs...

At October 17th Oracle officially released eighth Critical Patch Update (CPU). This CPU fixes about one hundred Oracle bugs, and its advisory is currently available at:
http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpuoct2006.html

Among others, it finally suggests a fix to the dire security hole I came across in April:
http://andrewmax.blogspot.com/2006/04/yet-another-security-alert.html

I have applied October CPU and, according to tests I've made, the problem is really resolved – any attempt of unauthorized data modification now fails with ORA-01031 (insufficient privileges) error.
My corresponding Metalink SR got finally closed.

In "Credits" section of CPU advisory, Oracle kindly credited me as well as other persons and organizations for bringing security problems to Oracle’s attention:

The following people discovered and brought security vulnerabilities addressed by this Critical Patch Update to Oracle's attention: Johannes Fahrenkrug; Sacha Faust of S.P.I. Dynamics, Inc.; Esteban Martinez Fayo of Application Security, Inc.; Jeff Kayser of Database Doctor, Inc.; Alexander Kornbrust of Red Database Security GmbH; David Litchfield of Next Generation Security Software Ltd.; and Andrew Maksimenko of COMEC-92 (me).

I'm very glad to realize that the story is over – having such hole unfixed for a long time is like sitting on a ticking bomb: sooner or later, it goes.
In short - I’d recommend applying October CPU as soon as possible, or else you leave your Oracle database vulnerable, both data integrity and security can be easily compromised.

Andrew.

A silent but important change

While learning Oracle and working with it, sometimes I’m getting really surprised. Especially when I discover odd differences between reality and what documentation states.

Few months ago I noticed one silent change in Oracle locking strategy that was introduced since Oracle 9.2.0.6.0 (and 10.1.0.4.0, for 10g family). I called it “silent” because it’s not reflected anywhere in Oracle documentation yet despite the fact that it may break logic of some applications where manual locking statements (e.g. LOCK TABLE) are used. And what is more confusing, there are only two Metalink bug notes (4115353 and 4252394), from which one can conclude that locking behavior was changed intentionally, to fix some locking-related bug in RAC environments.

Here are details. One can repeat this small test in Oracle 9.2.0.5.0 and earlier:

SQL> show release
release 902000500
SQL>
SQL> create table t(x) as
  2  select * from dual;
 
Table created.
 
SQL> select * from t for update;
 
X
-
X
 
SQL> select type, lmode
  2    from v$lock
  3   where request = 0 and
  4   sid = (select sid
  5            from v$mystat
  6           where rownum = 1);
 
TY      LMODE
-- ----------
TX          6
TM          2

So everything is as expected: SELECT ... FOR UPDATE applied TX lock in mode 6 as well as TM lock in mode 2 (also called "row share" lock).

But look what will happen if we rerun our small test in 9.2.0.6.0:

SQL> show release
release 902000600
 
...
< rerun "create table" and 
  "select ... for update" here >
...
 
SQL> select type, lmode
  2    from v$lock
  3   where request = 0 and
  4   sid = (select sid
  5            from v$mystat
  6           where rownum = 1);
 
TY      LMODE
-- ----------
TX          6
TM          3

Well, that was a bit surprising. Now Oracle applied TM mode 3 lock (e.g. "row exclusive" table-level lock) as we issued SELECT ... FOR UPDATE statement.

The impact of this change on existing applications is not so critical unless they have implemented some manual locking strategy. For example, look what may happen in 9.2.0.6.0 and above:

Session #1> show release
release 902000600
Session #1>
Session #1> lock table t in share mode;
 
Table(s) Locked.
 
Session #2> show release
release 902000600
Session #2>
Session #2> select * from t for update nowait;
select * from t for update nowait
              *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SELECT ... FOR UPDATE NOWAIT statement in session 2 failed, but it succeeds in Oracle 9.2.0.5.0 and earlier (10.1.0.3.0 and earlier).

Small change, but it may cause a bit of headache to someone who doesn’t know about it.

Andrew.

It's neat and useful

Things change rapidly.
In world of Oracle Database technologies things change so rapidly so it's hardly possible to keep track of them all. But I believe that one should learn something new about Oracle each day - it's the only way to become successful developer or DBA.

Recently, I was reading and enjoying new Jonathan's book, an outstanding work of art for everyone who wants to explore Oracle CBO in detail. While reading it, I noticed small but pretty fascinating remark about a feature I never saw before.

How many ways to obtain statement execution plan do you know? I'm not going to discuss them all, but I think that EXPLAIN PLAN statement and AUTOTRACE feature are most widely used. For sure, they are simple and convenient but one should be careful when using them, because they can be misleading to some extent. The most important thing to remember is that EXPLAIN PLAN is always doing hard parse. Therefore, for example, side-effects of bind variable peeking will be probably missed.

And if we want to look at "real" execution plan, which opportunities do we have? I think these are most familiar ones:
1. Using SQL_TRACE and TKPROF, and then analyzing produced trace files.
2. Querying V$SQL and V$SQL_PLAN dynamic views.

Both options are good, but they require some extra efforts. Well, maybe I'm too lazy, but from Jonathan's book I knew exactly what I was dreaming about. In first release of Oracle 10g, several important enhancements were made to DBMS_XPLAN package, and in particular, new function DISPLAY_CURSOR was added.

It's nice, and easy to use:

SQL> show release
release 1001000200
SQL> 
SQL> create table test(x primary key, y) as
  2  select rownum, rpad('*', 100, '*')
  3    from dual
  4  connect by level <= 1000;
 
Table created.
 
SQL> begin
  2    dbms_stats.gather_table_stats
  3    (ownname => user,
  4     tabname => 'TEST',
  5     estimate_percent => null,
  6     cascade => true);
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> var x number
SQL> 
SQL> exec :x := 1000
 
PL/SQL procedure successfully completed.
 
SQL> explain plan for
  2  select * from test
  3   where x < :x;
 
Explained.
 
SQL> -- Now, EXPLAIN PLAN tells us
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------
Plan hash value: 633647876
 
------------------------------------------------------------ ...
| Id  | Operation                   | Name        | Rows  |  ...
------------------------------------------------------------ ...
|   0 | SELECT STATEMENT            |             |    50 |  ...
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |    50 |  ...
|*  2 |   INDEX RANGE SCAN          | SYS_C005782 |     9 |  ...
------------------------------------------------------------ ...
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("X"<TO_NUMBER(:X))
 
14 rows selected.
 
SQL> -- Ok, let's run it...
SQL> select * from test
  2   where x < :x;
 
         X Y
---------- -------------------------------------------------- ...
         1 ************************************************** ...
         2 ************************************************** ...
       ... ...
       ... ...
       999 ************************************************** ...
 
999 rows selected.
 
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID  5r7q3102zwvh3, child number 0
-------------------------------------
select * from test  where x < :x
 
Plan hash value: 3836831076
 
------------------------------------------------------------- ...
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU ...
------------------------------------------------------------- ...
|   0 | SELECT STATEMENT  |      |       |       |     6 (100 ...
|*  1 |  TABLE ACCESS FULL| TEST |   999 |   101K|     6   (0 ...
------------------------------------------------------------- ...
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("X"<:X)
 
  
18 rows selected.

However, one thing to remember: you should disable SERVEROUTPUT before using this feature or else you'll be slightly surprised:

SQL> set SERVEROUTPUT ON
SQL> 
SQL> select * from test
  2   where x < :x;
 
         X Y
---------- -------------------------------------------------- ...
         1 ************************************************** ...
         2 ************************************************** ...
       ... ...
       ... ...
       999 ************************************************** ...
 
999 rows selected.
 
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------- ...
SQL_ID  9babjv8yq8ru3, child number 0
 
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
 
NOTE: cannot fetch plan for SQL_ID 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify SQL_ID and CHILD_NUMBER;  maybe the plan is
      no longer available in V$SQL_PLAN
 
 
 
8 rows selected.

Neat feature. I liked it.

Andrew.

Too late

Recently I’ve decided to remove my previous post because things turned out to be worse than I expected. Briefly speaking, April 2006 CPU released by Oracle Corp. does not solve the problem: unfortunately, that serious vulnerability is still relevant.


Now, I must stress: at the moment I published my previous post, I was pretty sure that problem is nearly fixed: Oracle Support told me that "it’s well-known security vulnerability that will be solved by April CPU". That CPU is already released, however, as I already mentioned, it does not suggest a fix to that issue. Seems that Oracle support guys didn’t get me right from the very beginning, but I’m not going to speculate about possible reasons...


And now, I feel that I’m in a tricky situation of some kind: being sure that the problem is about to be fixed soon, I published pretty critical information here. Though I didn’t mention any details, it’s still critical I think.


So I decided to remove it once and forever, but then I eventually discovered that it’s just too late. The same web page, http://www.red-database-security.com/advisory/oracle_modify_data_via_views.html, was updated days ago with information from my blog. For sure, I don’t know Alexander Kornbrust personally (a man who seem to head up that company) but I believe that he understands responsibility and won’t publish any critical details on his website.


It seems that there’s no way back, and it obviously does not make me happy. So I am leaving my previous post "as it is", with sincere hope for better future.


Andrew.

Yet another security alert

Days ago I found the following on the Web:
http://www.red-database-security.com/advisory/oracle_modify_data_via_views.html
where it says few words about serious security vulnerability which affects all versions of Oracle from 9.1.0.0 to 10.2.0.3. Briefly speaking, the main issue is that any user with SELECT privilege on another user’s table can insert/delete/update rows in that table via specially constructed view. They also mentioned Metalink note 363848.1 which is not accessible now for public user, probably due to security reasons.
As an immediate workaround, it was recommended to sanitize the CONNECT role by removing the CREATE VIEW (and CREATE DATABASE LINK) privileges from that role as well as revoking unnecessary CREATE VIEW privilege from user accounts.

So at first sight, it may seem that DBAs have an acceptable temporary solution to this problem. But recently I discovered that situation is much more disastrous. I was “lucky” to construct INSERT/UPDATE/DELETE statements which make it possible to update another user’s table without even creating any views. Thus, no special privileges are needed: a malicious user with a single CREATE SESSION privilege can modify tables in database while having only SELECT grants on those tables.

Unfortunately, it’s not a fake and the following test script proves that. Those DML statements were removed because I think this kind of knowledge should never be published for everyone’s sight.


SQL*Plus: Release 9.2.0.6.0 - Production on Sun Apr 16 17:49:00 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL>
SQL> create user user1 identified by user1
2 default tablespace users
3 quota 10M on users;

User created.

SQL> grant create session, create table to user1;

Grant succeeded.

SQL> create user hacker identified by hacker;

User created.

SQL> -- Only CREATE SESSION is granted to hacker
SQL>
SQL> grant create session to hacker;

Grant succeeded.

SQL> conn user1/user1
Connected.
SQL>
SQL> -- Create table in USER1 schema
SQL>
SQL> create table test (x int primary key, y varchar2(10));

Table created.

SQL> insert into test
2 select 1, 'One'
3 from dual
4 union all
5 select 2, 'Two'
6 from dual;

2 rows created.

SQL> -- Grant SELECT on table to "hacker"
SQL>
SQL> grant select on test to hacker;

Grant succeeded.

SQL> conn hacker/hacker
Connected.
SQL>
SQL> select * from user1.test;

X Y
---------- ----------
1 One
2 Two

SQL> -- This will fail, as expected
SQL>
SQL> update user1.test
2 set y = 'I AM HERE!'
3 where x = 1;
update user1.test
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> -- Ouch...
SQL>
SQL> update
2 <... censored ...>
...

1 row updated.

SQL> select * from user1.test;

X Y
---------- ----------
1 I AM HERE!
2 Two

SQL> rollback;

Rollback complete.

SQL> select * from user1.test;

X Y
---------- ----------
1 One
2 Two

SQL> -- This will fail, too
SQL>
SQL> delete from user1.test
2 where x = 1;
delete from user1.test
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> -- Ouch-ouch...
SQL>
SQL> delete from
2 <... censored ...>
...

1 row deleted.

SQL> select * from user1.test;

X Y
---------- ----------
2 Two

SQL> rollback;

Rollback complete.

SQL> select * from user1.test;

X Y
---------- ----------
1 One
2 Two

SQL> -- This will fail, of course
SQL>
SQL> insert into user1.test
2 values (3, 'I AM HERE!');
insert into user1.test
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> -- But...
SQL>
SQL> insert into
2 <... censored ...>
...

1 row created.

SQL> select * from user1.test;

X Y
---------- ----------
1 One
2 Two
3 I AM HERE!


Being sure that Oracle Corp. should fix this issue as soon as humanly possible, I’ve opened “severity 1” TAR on Metalink. Oracle Support personnel replied immediately that they are aware of that problem and it will surely be solved by April 2006 CPU planned for next week.

Note: I published this info here not to shed a light on Oracle Corp. and their products. It’s here just to warn DBAs and management staff that extremely high precautions should be taken until this issue is fixed.

Andrew.

So I've decided to blog...

Hi everybody!