Re: Can't create view

"Jan Schaefer" <schaefer_at_kksl.uni-leipzig.de> wrote in message news:9et3v6$pta$1_at_news.uni-leipzig.de...
> I've created a table and granted SELECT to a user. After this the user was
> able to create a view basing on my table in his schema.
>
> Then I granted SELECT to a role and granted the role to the user after
> revoking the direct SELECT grant. Now the user was unable to create the
 same
> view on my table. Oracle gives a error message that states: ORA-00942:
 Table
> or View does not exist. But the SELECT statement that creates the view
 still
> works.
>
> Does anybody know why it's not possible to create the view only with
 SELECT
> rights over a role?
>
> Thanks in advance,
>
> Jan
>
>

Views are compiled, and roles are not observed during compilation as roles are volatile. In short: if it does work at compilation, there's no guarantee it will continue to work.
You need to use direct grants.

Hth,

Sybrand Bakker, Oracle DBA Received on Mon May 28 2001 - 06:02:36 CDT