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 220.127.116.11 (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 firstname.lastname@example.org 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.