Re: ansi outer join syntax in Oracle allows access to any data

[Greg Williamson <greg () saintly com au>]

Tested as a user with some privs (but not DBA or SELECT ANY TABLE) as below

SQL> select username, user_id, password from sys.dba_users;
select username, user_id, password from sys.dba_users
                                            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from v$version
  2  ;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
PL/SQL Release 8.1.6.3.0 - Production
CORE    8.1.6.0.0       Production
TNS for Solaris: Version 8.1.6.3.0 - Production
NLSRTL Version 3.4.0.0.0 - Production

SQL> 
 

Not sure if ANSI syntax is required (not testable in 8.1.6) and I don't have
a 9i DB to test it on.

Greg.
> ------------- Begin Forwarded Message -------------

> The point is that I can see the dba_users view owned by SYS as a user
> with only CREATE SESSION privilege. This is only possible because of the
> bug in the ANSI outer join syntax. This bug allows access to any table
> without any granted privileges to any user!
>
> The example you show below doesn't show which user you are logged in as
> or what privileges that user has. I assume its a user that is either a
> DBA or has select privileges on the catalog or SELECT ANY TABLE or
> select explicitly on that view.
>
> Try the exact SQL i showed and check for yourself that it doesn't work
> in 8.1.6. but will work in 9.0.1
>
> cheers
>
> Pete