Re: Permissions required for create materialized view dynamically

In article <1D0yd.563162$%k.365998_at_pd7tw2no>, Jack Addington says...
>
>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
>

can you show us a cut and paste of:

user foo logs in
user foo issues "set role none;"
user foo creates MV.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation