Refresh materialized view by other user then owner 2006-04-30 - By varciasz
>>>> 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 (See ORA-01031.ora-code.com): insufficient privileges
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512 (See ORA-06512.ora-code.com): 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
--
http://www.freelists.org/webpage/oracle-l
|
|
