RE: Refresh materialized view by other user then owner
- From: "varciasz" <varciasz@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 30 Apr 2006 21:56:37 +0200
>>>> This table and materialized view have been made by user System
>>What about if it's owned by a regular user? This sounds like a very
>>marginal case with a lot that could go wrong.
Hi there,
When user creates his own MView then there is no problem with refreshing it,
but I still have no idea how refresh MView of other user
FOR EXAMPLE:
User "SYSTEM":
CREATE USER User2
IDENTIFIED BY ThisIsMySecretPassword;
GRANT CREATE SESSION TO User2;
GRANT ALTER ANY MATERIALIZED VIEW to User2;
GRANT SELECT ANY TABLE to User2;
GRANT CREATE ANY TABLE to User2;
GRANT CREATE ANY MATERIALIZED VIEW to User2 ;
GRANT DROP ANY MATERIALIZED VIEW to User2;
ALTER USER "USER2" QUOTA UNLIMITED ON "SYSTEM";
-- END OF SYSTEM
User "USER2"
create table My_Table (aa integer primary key);
create materialized view My_View as select * from My_Table;
begin
DBMS_MVIEW.REFRESH('My_View','c');
end;
--END OF USER2
This example works fine but when TABLE and MATERIALIZED VIEW are made by
"SYSTEM" and user "USER2" trying to refresh it by:
begin
DBMS_MVIEW.REFRESH('sys.My_View','c');
end;
then some privileges are needed and error is shown:
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 2
I don't have any idea what privileges are needed because I already tried
almost all of then. What is a meaning of this lines in error (794, 851,
832)? Is there any way to track what privileges are missing?
It not suppose to be so difficult! What's wrong with this Oracle?
Thanks for any help
varciasz
--
//www.freelists.org/webpage/oracle-l
Other related posts:
