Re: puzzling over problem creating view

Ed Stevens 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>
You've got me a bit concerned. You should not be doing this type of activity as SYSTEM and you should not solve problems such as this with SELECT ANY TABLE. The correct solution is to connect as the table owner and: GRANT SELECT ON <object_name> TO <schema_name> where the view will be built.

Then log on as the schema where the view will be built and: CREATE SYNONYM <object_name> FOR <schema_name.object_name> CREATE VIEW ... You should not violate system security for all tables and views just to grant access to one.

Daniel Morgan Received on Mon Mar 03 2003 - 19:57:39 CST