Re: Permissions required for create materialized view dynamically

:-) Happiness!!! It worked.

I thank both of you sooo much! Isn't there a song about seeing clearly now. So having 'DBA' accessing is simply a role that has all the grants, I think that is what screwed me up. I had no roles defined by me (app_role) so I couldn't understand how I could have role based access. I think I was missing the create table or create view that is needed.

I will probably have to think hard about this as a deployment issue as only a select group of users will need this functionality.

jack

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:113673473.0000962d.018_at_drn.newsguy.com...
> In article <DE2yd.539954$Pl.370054_at_pd7tw1no>, Jack Addington says...
>>
>>As soon as I do a set role none; then I can't do anything. I get
>>insufficient privs.
>>
>
> that means you are getting the privs to do what you need
>
> FROM A ROLE :)
>
> it is all about the role -- you do NOT have the direct privs necessary to
> create
> materialized views.
>
>
> do you meet the prereq's documented for creating them?
>
> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_63a.htm#2063839
>
>
> (cut and pastes of what you are attempting to do -- with the accompanying
> error
> messages are better than "this is what I'm running" -- it is like we can
> see
> your monitor)
>
>
> (when others should be outlawed....)
>
>>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
> 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 - 18:42:16 CST