Re: ORA-00942 on view containing ANSI join
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Dec 2009 07:39:00 -0500
there are no public synonyms for the tables involved.
On Wed, Dec 9, 2009 at 6:07 PM, chet justice <chet.justice@xxxxxxxxx> wrote:
> Are you 100% sure there aren't any public synonyms?
>
>
>
> On Wed, Dec 9, 2009 at 5:25 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
>
>> 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:
