Re: Permissions required for create materialized view dynamically

Sybrand,

Thanks for your reply. As for your questions to the need:

My system provides a data entry mechanism for research projects. The data entry module provides a mechanism to build a 'questionnaire'. The answers to these questions can be numbers, text, dates, memos, or even attachments. To allow for a dynamic set of questionnaires to be created the data is captured in 1 row for each question, allowing different result types. So instead of one table with each answer a column for each questionnaire, the system is dynamic.

However when the time comes to do data analysis it is impossible to analyse the data in rows, it must be transposed into columns (like excel). So, I have written some simple code that loops through the definition of the questionnaire and transposes the data into a useful excel type format. Because this is a commercial product and not a single inhouse project there is not someone around to create a snapshot everytime they modify a questionnaire.

The snapshot then provides a very simple source for them to use for data extracting.

As for the permissions issue, I have searched all day for an answer in google and online. I only have a permission issue when it comes to the execute immediate.

I had no role, just a single schema, the schema owner (with DBA rights) and still had no luck. I have tried creating a role and giving it execute on the package, the create materialized view. As someone else replied:

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?

thx

"Sybrand Bakker" <sybrandb_at_hccnet.nl> wrote in message news:8fvgs0t8h3dvd0h9tskp63htr9bh0d1hsn_at_4ax.com...
> Comments embedded
>
> On Tue, 21 Dec 2004 19:23:01 GMT, "Jack Addington"
> <jaddington_at_shaw.ca> wrote:
>
>>I am trying to write a stored procedure that dynamically drops/creates a
>>snapshot.
> Which obviously is a bad idea. Objects shouldn't be created on the
> fly. What business need do you have you think you 'need' this
> 'feature'
>
> I got all the sql right (I think) but I am running into a
>>insufficient privileges message when it fires. If I take the same code an
>>run it directly it creates the MV just fine. I am running as the schema
>>owner right now. I even tried altering the code to do a create view and I
>>got the same error message.
>>
>>What permissions could I be missing?
>
> This question must have been answered a million times, the answer has
> been provided at least as often, and yet people still fail to read
> documentation and/or search Google, prior to asking this boring
> question *again* for the umpteenth time. Why would there be Google
> anyway?
> The answer is your privileges are through a role.
> For the rest of the answer either search Google on 'insufficient
> privileges' or read up on 'definer's rights' and 'invokers rights' in
> the documentation.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Tue Dec 21 2004 - 15:31:41 CST