ORA-00942 on view containing ANSI join

From: Neil Kodner <nkodner_at_gmail.com>
Date: Wed, 9 Dec 2009 17:25:09 -0500
Message-ID: <ae2c46ba0912091425g161af6f6s32d0ac059dc8bf57_at_mail.gmail.com>


User A owns a handful of tables, User B has select privileges on the tables through both PUBLIC and role SELRAWEREP. User B contains no synonyms to any of User As tables or views. It is confirmed that User B can query the necessary tables when qualifying with the table owner.

User A creates a somewhat complex view (view 1) with a few nested subqueries, ANSI-style-outer-joined to the other tables in the view's FROM clause. User A then grants select on the view to both User B directly, and to SELRAWEREP. User B tries to select from the view and receives ORA-00942.

I then create a new version of the view and fully-qualify each table name with the table owner(view 2). User B can query the view just fine.

I remove the references to the table owner and then rework the ANSI Outer-join to a standard Oracle-style inner join.(view 3). User B can query that view just fine.

I then create view4 which is a copy of view 3 but outerjoining the subquery, which matches the intent of the original view. User B is back to receiving ORA-00942 when trying to query it.

Finally, I modify the original view and change the ANSI outer join into an ANSI equi-join, user B still gets ORA-00942.

My views may be found at http://www.neilkodner.com/views.txt

I can't find the documentation that explains what's going on here. I can't tell if this is an outer-join issue, an issue with combining ANSI-style joins with traditional Oracle joins, or something else. Does anyone have an idea, or can at least cite the documentation that explains all of this?

Received on Wed Dec 09 2009 - 16:25:09 CST

Original text of this message