Re: grant on view

From: Dan Clamage <clamage_at_mime.dw.lucent.com>
Date: 1997/10/30
Message-ID: <01bce516$533f2660$54110b87@clamagent>#1/1

>We have a user SI2 who has about 300 user tables.
>we have created a view in this account which does a join
>between a user table and all_tab_columns.
>If we select from the view in account SI2 we see the info
>but if we select in SI2USR we see nothing.
>Now when i read an oracle textbook, it says that for views
>when you grant select , you have also to give grant on
>the underling base table.
>I can do this for my_table,
>but all_tab_columns is a synonym
>and am i suppose to give grants to all 300 tables
>in account SI2.

ALL_TAB_COLUMNS is a public synonym for a view on data dictionary tables owned by SYS. Part of this view's definition is to select objects that are owned by the current user. Since SI2USR doesn't own SI2's table you see nothing.
To get this to work, do the following:

1. 	As SI2, Grant select on my_table to SYS with grant option;
2. 	As SYS, create or replace view v_test as

select a.table_name,a.column_name,b.id
from dba_tab_columns a,

     SI2.my_table b
where b.table_name=a.table_name;

3. 	As SYS, grant select on v_test to SI2USER;
4. 	As SI2USER, select * from SYS.v_test; and see all the data; OR
5. 	As SI2USER, create synonym v_test for sys.v_test;
6. 	As SI2USER, select * from v_test; and see all the data

This works! Note that SI2's grant to SYS must be direct, not via a role, in order for SYS to create the view (SYS gets ANY privileges via a DBA role). SYS' grant to SI2USER could be via a role. SYS could create a public synonym for sys.v_test if you prefer.

So what do I get for a prize?
- Dan Clamage Received on Thu Oct 30 1997 - 00:00:00 CST