Re: needed privileges for mat views

From: joel garry <joel-garry_at_home.com>
Date: Fri, 13 Aug 2010 10:10:39 -0700 (PDT)
Message-ID: <b1946630-72e0-48cc-8d1f-1421e8b32e4c_at_s17g2000prh.googlegroups.com>



On Aug 13, 6:36 am, Andreas Mosmann <mosm..._at_expires-31-08-2010.news- group.org> wrote:
> Hi ng,
>
> (Oracle 11.2.0.1.0, Linux)
>
> Following Situation:
>
> There are 3 schemas:
> DATA - holds all the data of my database
>
> WORKER - prepares the data for publishing, has select- privilege on all
> tables of DATA with admin option
>
> PUBLISHER - there should be stored the data prepared by WORKER in a
> MatView, has select- privilege on all views/tables of WORKER and system
> privilege CREATE MAT VIEW, CREATE ANY MAT VIEW (and for test a direct
> select privileg on DATA.TABLE1, DATA.TABLE2 ...)
>
> Pseudo:
> WORKER:
> CREATE VIEW MyView as SELECT * FROM DATA.TABLE1 JOIN DATA.TABLE2 ...
>
> PUBLISHER:
> CREATE MATERIALIZED VIEW as SELECT * FROM WORKER.MyView; -- Doesn't
> work, insufficient privileges
>
> No problem for publisher to Select WORKER.MyView, but no chance to
> create a mat view from it, neither by "SELECT * FROM WORKER.MyView" nor
> by "SELECT * FROM DATA.TABLE1 JOIN DATA.TABLE2 ..."
>
> How to find out, which privileges it is missing?
>
> BTW: Spatial Data should be collected, but I suppose, this is not the
> problem, because a select works and a create mat view w/o geometry
> column has the same effect.
>
> Thanks in advance
> Andreas
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de

select any table is way too much power, but if you give that to publisher and it starts working, that could mean either you missed some log privilege or granted through a role instead of directly. Brain in logjam today, I may be missing something else: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_6002.htm#SQLRF01302

jg

--
_at_home.com is bogus.
Happy Stan Chambers Day!
Received on Fri Aug 13 2010 - 12:10:39 CDT

Original text of this message