Hi,

If I have a Table X in Schema A with 1000 rows spilt between 3
companies (company_id 1,2,3).

I then create a view called Y in Schema B which is a Read Only View of
table X but only for rows where company_id = 3.

To create this view Schema B must have a SELECT privilege on Table X.

The problem is that, by assigning this select priv Schema B can now
view ALL rows in Table X directly and bypass the view.

Is there any way to prevent this by allowing access only to the view
and not the underlying table with company 1,2 data that is
confidential.

I'm using Oracle 8.1.7.4

Thanks for the help

Johne_uk wrote:
> If I have a Table X in Schema A with 1000 rows spilt between 3
> companies (company_id 1,2,3).
>
> I then create a view called Y in Schema B which is a Read Only View of
> table X but only for rows where company_id = 3.
>
> To create this view Schema B must have a SELECT privilege on Table X.
>
> The problem is that, by assigning this select priv Schema B can now
> view ALL rows in Table X directly and bypass the view.
>
> Is there any way to prevent this by allowing access only to the view
> and not the underlying table with company 1,2 data that is
> confidential.
>
> I'm using Oracle 8.1.7.4
>
> Thanks for the help

Create the view in schema A and then grant select on that view to
schema B.

Hi,

Yes I see what you are saying. By creating the views in the same schema
there is implict access to the data tables. Then granting access to the
views only for Schema B ensures that the underlying data tables cannot
be read directly by B.

The scenario relates to a production schema (Schema A) and Schema B was
to consist of a resticted number of Views. I was hoping to be able to
seperate the views to Schema B but this does not seem to be possible
judging by your suggestion and my testing.

Thank you very much for your help.
John