Re: are the ALL_* views usually viewable by all users?

On Jun 2, 2:53�pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> Comments embedded.
> On Jun 2, 12:41�pm, m..._at_pixar.com wrote:
>
> > I'm currently querying the USER_* views to do introspection
> > on a user's schema. �But, in order to handle aliases, I need
> > to use DBMS_UTILITY.NAME_RESOLVE and the ALL_* views.
>
> > Will I enounter any unexpected issues where a random user
> > won't be able to execute �DBMS_UTILITY.NAME_RESOLVE
>
> I haven't found any, so no.
>
> > �or
> > access the ALL_* views?
>
> No, as the ALL_* views report all of the oibjects the currently
> connected user can access.
>
> > If so, what's the role they should have to access these?
>
> Even a freshly created user with only CREATE SESSION privilege can
> execute DBMS_UTILITY.NAME_RESOLVE so no special privileges need be
> granted:
>
> SQL> create �user blerp identified by blorp;
>
> User created.
>
> SQL> grant create session to blerp;
>
> Grant succeeded.
>
> SQL> grant select on test to blerp;
>
> Grant succeeded.
>
> SQL> connect blerp/blorp@###########
> Connected.
> SQL> declare
> � 2 � � �sch � �varchar2(40);
> � 3 � � �p1 � � varchar2(40);
> � 4 � � �p2 � � varchar2(40);
> � 5 � � �dblink varchar2(40);
> � 6 � � �p1t number;
> � 7 � � �objnum number;
> � 8 �begin
> � 9 � � dbms_utility.name_resolve('BING.TEST',2,sch, p1,p2, dblink,
> p1t, objnum);
> �10 � � � � �dbms_output.put_line(sch||' � '||p1||' '||p2||' '||
> dblink||' '||p1t||' '||objnum);
> �11 �end;
> �12 �/
> BING � TEST � 2 68806
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> > Will that be a common case?
>
> It should be common that you shouldn't have issues, in my opinion.
>
> > Many TIA,
> > Mark
>
> > --
> > Mark Harrison
> > Pixar Animation Studios
>
> David Fitzjarrell

I will just add to David's response that by default Oracle grants execute on dbms_utility to public via catproc. If you site has removed some of the public grants you may need to restore the privilege or provide the privilege via a role.

Also as mentioned the ALL_ views are sensitive to the querying user's privilege set. If you are going to code any of the queries into stored code you could be changing the result set unless you use current user authorization.

HTH -- Mark D Powell -- Received on Tue Jun 03 2008 - 11:59:21 CDT