puzzling over problem creating view

From: Ed Stevens <nospam_at_noway.nohow>
Date: Mon, 03 Mar 2003 15:51:20 -0600
Message-ID: <o2j76vkkmt2m8e8ie0jkvglc9uld7vulrh@4ax.com>

Platform: Oracle 8.1.7

Connect as SYSTEM, I try to create a view under a user schema. View is selecting on yet another user's table. Response is that the tables being selected in the view do not exist.

Check privileges for this user; he has no object privileges and does not have SELECT ANY TABLE

Connect as the user under which the view is to be created and attempt to SELECT against the offending table. Success. This in itself is puzzling since he does *not* have SELECT ANY TABLE, and the table is *not* in his own schema.

Connect as SYSTEM and grant SELECT ANY TABLE to the user who is to own the view.

Create the view -- success.

I don't understand.

Session log follows:

SQL> connect system/****
Connected.
SQL> revoke select any table from odbcuser; revoke select any table from odbcuser
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'ODBCUSER'

SQL> CREATE OR REPLACE VIEW odbcuser.VW_EQUIPMENT   2 (Company,
<snip>

 73  From  MVXJDTA.MILOIN MILOIN,
 74        MVXJDTA.MITMAS MITMAS,
 75        MVXJDTA.MILOMA MILOMA

 76 where MILOIN.LICONO = MITMAS.MMCONO
<snip>

/

      MVXJDTA.MILOMA MILOMA
              *

ERROR at line 75:
ORA-00942: table or view does not exist

SQL> connect odbcuser/****
Connected.
SQL> select count(*) from MVXJDTA.MILOMA;

  COUNT(*)

---

        76

1 row selected.

SQL> connect system/****
Connected.
SQL> grant select any table to odbcuser;

Grant succeeded.

SQL> CREATE OR REPLACE VIEW odbcuser.VW_EQUIPMENT   2 (Company,
<snip>

 73  From  MVXJDTA.MILOIN MILOIN,
 74        MVXJDTA.MITMAS MITMAS,
 75        MVXJDTA.MILOMA MILOMA

 76 where MILOIN.LICONO = MITMAS.MMCONO
<snip>

/

View created.

SQL> Received on Mon Mar 03 2003 - 15:51:20 CST