Re: ORA-01031: insufficient privileges on view creation. 10.2.0.4

Have you done the usual check for privileges that need to be granted directly and not via a role (or PUBLIC). This will include select on the underlying tables for the SHELF view and execute on any functions included in the SHELF view.

Niall

On Mon, Mar 7, 2011 at 11:07 AM, David Aldridge <david_at_david-aldridge.com>wrote:

> Does anyone have any ideas why a username that can both create a view and
> can run a particular SELECT could not create a view based exactly on that
> SELECT?
>
>
> 10.2.0.4
>
> So, I can successfully run this query:
>
> select
> s.shelfid ,
> cns.nodeid
> from
> cramer_node_snapshot cns,
> shelf s
> where
> cns.nodeid = s.shelf2node
> /
>
> Also I can create a view that does not reference SHELF:
>
> create or replace view
> view1
> as
> select
> cns.nodeid
> from
> cramer_node_snapshot cns
> /
>
>
> BUT!
>
> I cannot create this view because of "ORA-01031: insufficient privileges":
>
> create or replace view
> view1
> as
> select
> s.shelfid ,
> cns.nodeid
> from
> cramer_node_snapshot cns,
> shelf s
> where
> cns.nodeid = s.shelf2node
> /
>
> SHELF is one of nearly 22,000 public synonyms on this system. Referencing
> the SHELF object directly instead of through the public synonym does not
> change the error. No DB links involved.
>
>
> *scratches-head*
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l