Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!logbridge.uoregon.edu!newsfeed.berkeley.edu!ucberkeley!sn-xit-02!sn-xit-06!sn-post-01!supernews.com!news.supernews.com!not-for-mail From: postmaster@cjc.org (Cheng-Jih Chen) Newsgroups: comp.databases.oracle.server Subject: Re: Grant permissions on views with WITH clauses Date: Tue, 09 Sep 2003 22:12:31 -0000 Organization: CJC Organization Message-ID: References: <3F5E0DD3.4F83D484@remove_spam.peasland.com> Sender: postmaster@cjc.org Reply-To: postmaster@cjc.org X-Newsreader: trn 4.0-test76 (Apr 2, 2001) Originator: cjc@cjc.org (Cheng-Jih Chen) X-Complaints-To: abuse@supernews.com Lines: 25 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:242734 In article , Jonathan Lewis wrote: > >Just as a little refinement on the problem. > >If you grant select on the underlying tables, >then queries against the view __MAY__ fail >'randomly' with error 32036 when executed >by the grantee. > >The problem may not occur with all variations >of using subquery factoring and I haven't tried >identifying exact boundaries of the problem, but the >point at which the issues appears is when the >optimiser decides to INLINE the factored subquery >rather than generating a temporary table for it. We saw 32036 errors when we tried to GRANT SELECT on some of the production views that have the WITH clause, much less trying to query against the view. The test case I posted was simple, mainly to illustrate the basic problem of not being able to do the SELECT with the second user. Thanks for the extra testing. We weren't sure where the 32036s were coming from, or how related they were to the permission issue.