Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Permissions required for create materialized view dynamically

Re: Permissions required for create materialized view dynamically

From: Sybrand Bakker <sybrandb_at_hccnet.nl>
Date: Wed, 22 Dec 2004 00:13:25 +0100
Message-ID: <e7bhs0pt5j3earvn7ot17lpie0t8l56ssn@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:13:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US