Re: puzzling over problem creating view

On Mon, 03 Mar 2003 17:57:39 -0800, DA Morgan <damorgan_at_exesolutions.com> wrote:

>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

OK. Point taken, sort of.

I'm not sure I understand why I shouldn't be doing this as SYSTEM. We typically don't grant CREATE anything to schema owners, forcing the developers to come to DBA when they need new objects. I do understand having the table owner grant SELECT on the object to ODBCUSER, (instead of the SELECT ANY TABLE system priv) which is our general practice.

Second, I'm not sure I understand why I should create a synonym. If ODBCUSER has been granted the object privilege to SELECT a specific table, and he owns a view that selects on that table, what purpose does the synonym serve?

SQL> connect MVXJDTA/****
Connected.
SQL> grant select on MILOIN to odbcuser;

Grant succeeded.

SQL> grant select on MITMAS to odbcuser;

Grant succeeded.

SQL> grant select on MILOMA to odbcuser;

Grant succeeded.

SQL> connect system/****
Connected.
SQL> CREATE OR REPLACE VIEW odbcuser.VW_EQUIPMENT <snip>

View created.

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

  COUNT(*)

        76

1 row selected.

SQL> Received on Tue Mar 04 2003 - 08:46:49 CST