ORA-00942 on view containing ANSI join
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Dec 2009 17:25:09 -0500
Version 10.2.0.4
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?
Other related posts:
