Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Permissions required for create materialized view dynamically
On Tue, 21 Dec 2004 21:31:41 GMT, "Jack Addington"
<jaddington_at_shaw.ca> wrote:
>It doesn't make sense to me either. To recap-
>
>- you have a user, 'foo'
>
>- foo owns the procedure creating the MV
>
>- foo owns all of the tables in the MV (no synonyms, etc)
>
>- foo has the create MV privilege granted directly, not through a role
>(Justin had a great point, above)
>
>- foo can create the MV in a sql editor
>
>- foo cannot create the MV through the procedure.
>
>Any thoughts other than weeding through the million google hits?
I can imagine the following course of actions to isolate it.
- flush the shared pool
- enable tracing (alter session set events='10046 trace name context
forever, level 12)
- alter session set events='1031 errorstack context forever, level 3'
execute the procedure
This should dump the exact statement in error (but I guess it's the
create view)
It should also allow you to see all the recursive sql (ie checks
against the datadictionary) including the values of bind variables,
and hopefully one of these statements has err=1031 in it, and one of
these statements is selecting from the grn$ table.
At this point, not seeing the statement, you don't call any pl/sql functions in your view definition outside this schema? Those would also need direct grants.
-- Sybrand Bakker, Senior Oracle DBAReceived on Tue Dec 21 2004 - 17:13:25 CST