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

On Aug 13, 6:36*am, Andreas Mosmann <mosm... (AT) expires-31-08-2010 (DOT) news-
group.org> wrote:
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/E...htm#SQLRF01302

jg
--
@home.com is bogus.
Happy Stan Chambers Day!

On Aug 13, 3:36*pm, Andreas Mosmann <mosm... (AT) expires-31-08-2010 (DOT) news-
group.org> wrote:
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/0...mas-ora-01031/

Cheers.

Carlos.

Andreas Mosmann schrieb am 13.08.2010 in
<1281706582.28 (AT) user (DOT) newsoffice.de>:

Your links solved my problem. I did not expect that a "create table"
privilege is needed too (and I still do not understand, why).
But after giving this it worked fine.

Thank you.
Andreas

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

On Aug 16, 2:27*pm, Andreas Mosmann <mosm... (AT) expires-31-08-2010 (DOT) news-
group.org> wrote:
"(and I still do not understand, why)."

I think it is because you have to create a SEGMENT for the MW, and it
is created on the table owner schema. Thus the owner needs the 'CREATE
TABLE' (there is no 'CREATE SEGMENT' right.)

Cheers.

Carlos.

Carlos wrote:
Right.