needed privileges for mat views

From: Andreas Mosmann <mosmann_at_expires-31-08-2010.news-group.org>
Date: Fri, 13 Aug 2010 15:36:22 +0200
Message-ID: <1281706582.28_at_user.newsoffice.de>



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
Received on Fri Aug 13 2010 - 08:36:22 CDT

Original text of this message