Prev: external table problems
Next: RMAN Mounting Database
From: Andreas Mosmann on 13 Aug 2010 09:36
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
From: joel garry on 13 Aug 2010 13:10
On Aug 13, 6:36 am, Andreas Mosmann <mosm...(a)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
--
@home.com is bogus.
Happy Stan Chambers Day!
 | 
Pages: 1
Prev: external table problems
Next: RMAN Mounting Database