Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: CREATE VIEW: Table or view does not exist (ORA-00942)
"Bernd Sonderkoetter" <B.Sonderkoetter_at_smf.de> wrote in message
news:as058h$ejr$00$1_at_news.t-online.com...
> Yes, the user creating the view has implicitly select rights on C by a
role,
> but he has no explicit select rights.
>
> I think that the role with the select rights on C was assigned after
> creating the (private) synonyms on C for the user creating the view, but
> should this really be relevant?
>
> So I assume that in the described case the Oracle DBMS ignores the select
> rights on C which are *implicitly* granted by a role and only recognizes
the
> rights that are *explicitly* granted. If you explicitly grant select
rights
> on C for the user, he is able to create the view.
>
> The depicted problem does not appear if the user creates the view in the
> schema of the user to who has created table C,
> i.e. be JIM the user who created C the following works without explicity
> select rights:
>
> CREATE VIEW JIM.TEST (A) AS SELECT B FROM C WHERE...
>
> whereas
>
> CREATE VIEW TEST (A) AS SELECT B FROM C WHERE...
>
> raises ORA-00942
>
>
>
> "TurkBear" <john.greco_at_dot.state.mn.us> schrieb im Newsbeitrag
> news:s737uuc5031lb8voo7hrh980keeu0hhrlh_at_4ax.com...
> >
> > To be clear:
> >
> > User creating the view has select rights on C ( and granted by C's owner
> AFTER synonym[ public or private?] was created)?
> > Is that the case?
> >
> > "Bernd Sonderkoetter" <B.Sonderkoetter_at_smf.de> wrote:
> >
> > >No, this is not the problem because a synonym on table c does exist!
> > >
> > >"Guido Konsolke" <Guido.Konsolke_at_Triaton.com> schrieb im Newsbeitrag
> > >news:1038308790.752445_at_news.thyssen.com...
> > >> Bernd wrote ...
> > >> >Hi,
> > >> >
> > >> >does anybody know why it is not sufficient for creating a view to
have
> > >> >granted select rights by a ROLE on a table which is used in the
view?
> > >> >This is under
> > >> >the assumption that the table belongs to another user and the view
is
> > >> >to be
> > >> >created in your own schema.
> > >> >
> > >> >Example:
> > >> >
> > >> >CREATE VIEW TEST ( A ) AS SELECT B FROM C WHERE...
> > >> >results in the error ORA-00942: table or view does not exist
> > >> >if the select right on C is granted by a role and C belongs to
another
> > >> >user.
> > >> >
> > >> >Is this a bug or a feature?
> > >> >
> > >> >Thanks,
> > >> >Bernd
> > >>
> > >> Hi Bernd,
> > >> maybe my (only *this*) thought is totally wrong, couldn't verify it.
> > >> If you're lucky you only have to add the schema name to the
> > >> table C. This will read "Select B from schema.C Where...".
> > >> If that really is your prob, you can also solve it by creating
> > >> a synonym for table C.
> > >>
> > >> Sorry if my suggestions are obvious silly...
> > >> Guido
> > >>
> > >>
> > >
> >
>
Sorry, I've had enough of this thread....Read my lips:
You cannot create a stored object using privileges from a role
Just to clarify:
YOU CANNOT CREATE A STORED OBJECT USING PRIVILEGES FROM A ROLE
(sorry for shouting).
Regards,
Paul
P.S. Try 'set role none;' then do the select, then create the view. Received on Tue Nov 26 2002 - 15:06:59 CST