| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: ALL_VIEWS in a stored procedure
> From a schema that SELECT ANY TABLE privilege, I can SELECT from the
> ALL_VIEWS view. However, I tried creating a cursor that did a join on
> the ALL_VIEWS table, and I got:
>
> PLS-00356: 'ALL_VIEWS' must name a table to which the user has
> access
>
> What's up with that? Do I have access or not? I thought everybody had
> access to ALL_VIEWS, whether they could see any rows or not.
Stored procedures are a little special, because they are objects you own
and on which you can grant the EXECUTE privilege to somebody else;
remember WITH GRANT OPTION? If you could have a 'standard' privilege on
a table, create a stored proc and grant execute on this proc to somebody
else, you would by-pass the GRANT OPTION. This is why when somebody
needs to use an object which belongs to somebody else in a stored
procedure, the somebody else should grant the required privileges WITH
GRANT OPTION to the first one. Otherwise you end up with procedures
which run perfectly well as anonymous PL/SQL blocks, and fail miserably
as procedures. Especially funny with dynamic SQL, because you discover
it at runtime. Connect as SYS, and grant SELECT on ALL_VIEWS WITH GRANT
OPTION to the user, it will work better.
--
Regards,
Stéphane Faroult
Oriole Corporation
 |
 |