Re: needed privileges for mat views
From: Carlos <miotromailcarlos_at_netscape.net>
Date: Sun, 15 Aug 2010 23:41:28 -0700 (PDT)
Message-ID: <f966a3b1-5ac4-44ea-b81b-4ce407a9572c_at_v41g2000yqv.googlegroups.com>
On Aug 13, 3:36 pm, 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
Date: Sun, 15 Aug 2010 23:41:28 -0700 (PDT)
Message-ID: <f966a3b1-5ac4-44ea-b81b-4ce407a9572c_at_v41g2000yqv.googlegroups.com>
On Aug 13, 3:36 pm, 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
In order to PUBLISHER to create MVIEW on DATA, DATA must have CREATE TABLE privs. Otherwise "insufficient privileges" will raise (but it's about DATA's privileges, not PUBLISHER's privileges) as explained here (in spanish):
http://carlosal.wordpress.com/2008/02/20/creando-vistas-materializadas-en-otros-esquemas-ora-01031/
Cheers.
Carlos. Received on Mon Aug 16 2010 - 01:41:28 CDT