Re: Refresh materialized view by other user then owner
- From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 1 May 2006 11:01:24 +0200
Ahh okay, then there is one part of the confusion we can identify:
SYS is magical, SYS is different.If you connect AS SYSDBA, using any user,
you get into the database as user SYS, and not the actual user you specified
when connecting. And I tend to think that this is the root cause of your
problems. You really, really, really should not use SYS for storing user
data. The same goes for the SYSTEM tablespace, this is reserved for Oracle
database internal data as well.
Could you try issuing the following statements, when you're connected to the
database AS SYSDBA and let me know if this works.
-- we'll first create a tablespace to store the table / mview
-- here you just have to replace the path to a valid folder on your
computer,
-- I'm assuming you're running Windows, please correct me if I'm wrong
create tablespace users datafile 'c:\users.dbf' size 10M;
-- we'll create a user that will own the table and materialized view
create user user_a identified by user_a
quota unlimited on users;
-- grant him the privileges necessary for this test
grant create session, create table, create materialized view to user_a;
-- create the user that will be able to refresh user_a's mview
create user user_b identified by user_b;
grant create session, alter any materialized view to user_b;
-- now connect as user_a to create the objects
connect user_a/user_a
-- connected as user_a we create the table and the mview
create table a_table (x int primary key);
create materialized view a_mview as select * from a_table;
-- connect as user_b and refresh it
connect user_b/user_b
begin
dbms_mview.refresh('USER_A.A_MVIEW','c');
end;
/
Stefan
Other related posts:
