Re: mview over private database link

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 28 Sep 2011 08:24:14 -0700
Message-ID: <CAORjz=MrdN_n6tq5oLV3YBeOLswFVPw-ZQX7TRrkMzxXEhENcQ_at_mail.gmail.com>



Further attempts to troubleshoot this Amount to Guessing<http://www.battleagainstanyguess.com/> If you don't mind, please include DDL that can be used to reproduce the problem.

Or to at least reproduce the schemas. :)

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

On Wed, Sep 28, 2011 at 5:23 AM, Remigiusz Sokolowski < remigiusz.sokolowski_at_nordea.com> wrote:

> SELECT grants are provided directly (do I need more then select?) - I've
> just checked it.
> If schema TARGET has its own private link then all goes well, if not
> then while creating a mview the rdbms throws
> 00942. 00000 - "table or view does not exist".
>
> This error is when in the view in the MIDDLE schema there is no
> qualification (ie. there is from some_view_at_SRC and not
> some_user.some_view_at_SRC). If I add it then I 've got
> 04052. 00000 - "error occurred when looking up remote object %s%s%s%s%s"
>
> thus it seems that when I call the MIDDLE view from TARGET session in a
> query, it is done entirely on MIDDLE objects, but when I try to build a
> mview the definition is somthing like copied to the mview definition and
> then performed - then in a wrong context.
>
>
> W dniu 27.09.2011 20:31, Jared Still pisze:
> > On Tue, Sep 27, 2011 at 1:16 AM, Remigiusz Sokolowski
> > <remigiusz.sokolowski_at_nordea.com
> > <mailto:remigiusz.sokolowski_at_nordea.com>> wrote:
> >
> >
> > So it seems the TARGET user can select on rights of the MIDDLE user,
> but
> > can not select for building a mview.
> >
> > Do You know how looks the big picture behind this?
> >
> >
> > It would appear that the rights for TARGET are granted through a role.
> > TARGET needs explicit SELECT grants to the tables in the MIDDLE schema.
> >
> >
> > Jared Still
> > Certifiable Oracle DBA and Part Time Perl Evangelist
> > Oracle Blog: http://jkstill.blogspot.com
> > Home Page: http://jaredstill.com
>
>
> --
> 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 siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia,
> wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod
> numerem: 0000021828,
> dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc w Gdansku,
> VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego,
> o kapitale zakladowym i wplaconym w wysokosci: 277.493.500,00 zlotych,
> NIP: 586-000-78-20, REGON: 190024711
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 28 2011 - 10:24:14 CDT

Original text of this message