Re: mview over private database link
From: Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
Date: Thu, 6 Oct 2011 11:38:17 +0200
Message-ID: <4E8D7709.9040706_at_nordea.com>
Now all is clear to me:
Testcase pasted below
here excerpt from previous posting:
"let's assume we have a schema MIDDLE within the DEST database on which we defined a private remote link to remote database SRC. I am able to create in the MIDDLE schema materialized views and views based on tables from the SRC db.
I have also another schema TARGET, where I am able to create materialized view on materialized views from MIDDLE schema. I am not able however to create mviews based on views from the MIDDLE schema, even though I am able to select those views as the TARGET user.
create user src identified by src quota unlimited on users; grant connect to src;
grant create table to src;
create table src.test(a number, b varchar2(20)) tablespace users; insert into src.test values(1,'test');
commit;
create user middle identified by middle default tablespace users quota unlimited on users;
grant connect to middle;
grant create database link, create view, create materialized view, create table to middle;
connect middle/middle
CREATE DATABASE LINK DL_SRC_SRC
CONNECT TO SRC IDENTIFIED BY "src"
USING '(DESCRIPTION =(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = 1521)))(CONNECT_DATA=(SID = <sid>)))'; create view middle.v_test1 as select * from src.test_at_DL_SRC_SRC; create view middle.v_test2 as select * from test_at_DL_SRC_SRC; create materialized view middle.mv_test1 tablespace users as select * from middle.v_test1;
create materialized view middle.mv_test2 tablespace users as select * from middle.v_test2;
create user target identified by target default tablespace users quota unlimited on users;
grant connect to target;
grant create materialized view, create table to target; grant select on middle.v_test1 to target; grant select on middle.v_test2 to target; connect target/target
create materialized view target.mv_test1 tablespace users as select * from middle.v_test1;
create materialized view target.mv_test2 tablespace users as select * from middle.v_test2;
Date: Thu, 6 Oct 2011 11:38:17 +0200
Message-ID: <4E8D7709.9040706_at_nordea.com>
Now all is clear to me:
Testcase pasted below
here excerpt from previous posting:
"let's assume we have a schema MIDDLE within the DEST database on which we defined a private remote link to remote database SRC. I am able to create in the MIDDLE schema materialized views and views based on tables from the SRC db.
I have also another schema TARGET, where I am able to create materialized view on materialized views from MIDDLE schema. I am not able however to create mviews based on views from the MIDDLE schema, even though I am able to select those views as the TARGET user.
So it seems the TARGET user can select on rights of the MIDDLE user, but can not select for building a mview.
and
user SRC
create user src identified by src quota unlimited on users; grant connect to src;
grant create table to src;
create table src.test(a number, b varchar2(20)) tablespace users; insert into src.test values(1,'test');
commit;
user MIDDLE
create user middle identified by middle default tablespace users quota unlimited on users;
grant connect to middle;
grant create database link, create view, create materialized view, create table to middle;
connect middle/middle
CREATE DATABASE LINK DL_SRC_SRC
CONNECT TO SRC IDENTIFIED BY "src"
USING '(DESCRIPTION =(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = 1521)))(CONNECT_DATA=(SID = <sid>)))'; create view middle.v_test1 as select * from src.test_at_DL_SRC_SRC; create view middle.v_test2 as select * from test_at_DL_SRC_SRC; create materialized view middle.mv_test1 tablespace users as select * from middle.v_test1;
create materialized view middle.mv_test2 tablespace users as select * from middle.v_test2;
user TARGET - last 2 command end with errors - anyway the point is that it seems views from MIDDLE are performed on TARGET connections thus failing - even though direct call to those views is performed properly on connection by MIDDLE
create user target identified by target default tablespace users quota unlimited on users;
grant connect to target;
grant create materialized view, create table to target; grant select on middle.v_test1 to target; grant select on middle.v_test2 to target; connect target/target
create materialized view target.mv_test1 tablespace users as select * from middle.v_test1;
create materialized view target.mv_test2 tablespace users as select * from middle.v_test2;
TIA, Remigiusz
-- Pole nakazi ---------------------------------------------------------------------- Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com> pos : DBA at DIiUSI addr : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland phone : +48 58 667 17 43 mobile: +48 602 42 42 77 Nordea Bank Polska S.A. z siedzibą w Gdyni, ul. Kielecka 2, 81-303 Gdynia, wpisaną do Rejestru Przedsiębiorców Krajowego Rejestru Sądowego pod numerem: 0000021828, dla której dokumentację przechowuje Sąd Rejonowy Gdańsk - Północ w Gdańsku, VIII Wydział Gospodarczy Krajowego Rejestru Sądowego, o kapitale zakładowym i wpłaconym w wysokości: 277.493.500,00 złotych, NIP: 586-000-78-20, REGON: 190024711-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 06 2011 - 04:38:17 CDT