Re: CREATE VIEW: Table or view does not exist (ORA-00942)

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
> >>
> >>
> >
>
Received on Tue Nov 26 2002 - 09:45:13 CST