Re: HELP with view access

Lynda Sohl (lyndas_at_email.mot.com) wrote:

> Please HELP. I have a two tables, each named the same but owned by
> different owners. For example, own1.customer and own2.customer. I
> have a third owner which was granted select access to these two
> tables. owner3 then built a view on the two base tables. The view is
> stated as: "create view customer as (select * from own1.customer union
> select * from own2.customer);". Owner3 can access the view and query
> against it. I than granted select to the view and the two base tables
> to user1. user1 can query the two base tables but when he tries to
> query or describe the view, I get an ORA-942 Tbale or view does not
> exist. Support seems to be stumped. Can anyone help direct me to
> what may be wrong here?

Please format your lines to under 80 characters! Reading 250 character lines on an 80 character terminal is a pain.

If a user (eg user1) creates a view which references a table belonging to another user (eg user2) and then wishes to grant access to the view to yet a third user (eg user3), you need:

  connect user2/<password>
  grant select on table to user1 WITH GRANT OPTION

  connect user1/<password>
  grant select on view to user3

(you may also need to grant user3 access to user2's table directly, but the real problem is including that WITH GRANT OPTION)

--
  _________________________   ______________________________________
 /  Tommy Wareing          \ /  And I dream about movies            \
|  p0070621_at_brookes.ac.uk   X   They won't make of me when I'm dead  |
 \  0865-483389            / \      - Jon Bon Jovi, Keep the Faith  /
  ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~