Re: Create a View across schemas

On Tue, 12 Jun 2007 20:40:41 -0000, simpsonjohnj_at_gmail.com wrote:

>Hi all,
>
>I am having trouble creating a view in a schema that references tables
>in another schema. For example. I want to create view x_view in
>schema A that selects fields from Table b_tab in schema B. Schema A
>has all view privileges granted to it (create any view, drop any view,
>under any view) as well as the select any table permission. When I
>run the following query, I get an insufficient privileges error. Can
>someone please help me out?
>
>CREATE OR REPLACE VIEW a.x_view AS SELECT field1, field2 FROM b.b_tab;
>
>and the error:
>ERROR at line 1:
>ORA-01031: insufficient privileges
>
>Thanks,
>John

ora-1031 on compiled objects (a view is compiled): *roles* are *disregarded* during compilation as roles are volatile. This has been posted here at least a million times. Did you search Google or this forum? I bet not.

-- 
Sybrand Bakker
Senior Oracle DBA