Re: Can't create cursor on dba_synonyms, what's wrong?

Here's my two cents' worth:
The USER_* views show only objects which you own. The ALL_* views show only the objects that either you own or have been granted access privileges by the owner.
The DBA_* views are used by SYS and SYSTEM and show *all* objects in the database.

You might want to write some standardized SELECT stored procedures against these views, then ask your DBA to compile them (as SYS) and grant you EXECUTE. If you gather them into a package you'll only have to annoy your DBA once. For example, I wrote a stored procedure which shows all indexes on all tables named (whatever) in any schema. I also wrote a package that reverse-engineers all schema objects.

The benefit here is now you can get information about any object in any schema.

• Dan Clamage dclamage_at_idcomm.com

Thomas Kyte <tkyte_at_us.oracle.com> wrote in article <342fc031.3666802_at_newshost>...
> Roles are not enabled in procedures. The owner of a procedure must have
 the
> privelege needed to perform operations directly (or gotten them from
 public).
>
> In the following, you undoubtably have access to dba_synonyms via the DBA
 role,
> while you have access to user_synonyms via a public grant.
> Once you get sys to grant access on dba_synonyms to the owner of the
 procedure,
> it'll work.
> On Mon, 29 Sep 1997 02:20:52 GMT, gennick_at_worldnet.att.net (Jonathan
 Gennick)
> wrote:
> >I am trying to create a cursor, in a PL/SQL procedure, on
> >the dba_synonyms view, but can't seem to make it work. See
> >below for some code which reproduces the error. However, I
> >can create a cursor on user_synonyms. What's going on here?
> >Why can I see one view and not the other? Is there some
> >simple, boneheaded thing, which I am not doing correctly?
Received on Mon Sep 29 1997 - 00:00:00 CDT