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

From: <fitzjarrell_at_cox.net>
Date: Mon, 2 Jun 2008 11:53:33 -0700 (PDT)
Message-ID: <27e1cbea-1e58-406a-b521-1f489447c711@x41g2000hsb.googlegroups.com>

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 Received on Mon Jun 02 2008 - 13:53:33 CDT