Re: puzzling over problem creating view

On Mon, 03 Mar 2003 15:51:20 -0600, Ed Stevens <nospam_at_noway.nohow> wrote:

>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>
As far as I know it's the same for views as it is for stored procedures: if you reference an object in a view you need to have direct privileges on the object and not by means of a role. In your case, I think odbcuser has a select privilege on the mvxjdta objects by means of a role, so you can do a select count(*) on them but cannot references them in a view.

Jaap. Received on Wed Mar 05 2003 - 03:30:38 CST