Re: Permissions required for create materialized view dynamically

As soon as I do a set role none; then I can't do anything. I get insufficient privs.

Here is a sample that shows what I am trying to do.

drop table test cascade constraints;

create table test (f1 number, f2 number);

ALTER TABLE EPM.TEST ADD CONSTRAINT TEST_PK PRIMARY KEY (f1)

ENABLE VALIDATE; insert into test values (1,2);

insert into test values (2,3);

commit;

select * from test;

create snapshot ss_test refresh FORCE on demand

as

select f1, f2 from test;

select * from ss_test;

drop snapshot ss_test;

declare

begin

execute immediate 'create view ss_test as select f1, f2 from test';

end;

/

select * from ss_test;

drop snapshot ss_test;

create or replace procedure proc_test_ss as

lv_Sql varchar2(1000);

begin

lv_sql := 'create view ss_test as select f1, f2 from test';

execute immediate lv_sql;

exception when others then raise_application_error(-20001,'arg - ' || sqlerrm);

end proc_test_ss;

/

execute proc_test_ss;

select * from ss_test;

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:113669769.00006e1d.079_at_drn.newsguy.com...
> 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
Received on Tue Dec 21 2004 - 17:49:55 CST