Re: Permissions required for create materialized view dynamically

I will try the alter sessions and see where that gets me. I don't know how to 'flush the shared pool' and the rest is going over my head. I posted in the previous thread asample that generates the issue for myself as well as for a dba friend at another organization.

thanks for your time

jack

"Sybrand Bakker" <sybrandb_at_hccnet.nl> wrote in message news:e7bhs0pt5j3earvn7ot17lpie0t8l56ssn_at_4ax.com...
> 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 DBA
Received on Tue Dec 21 2004 - 17:51:33 CST