Re: Conditional Views

From: L. Carl Pedersen <carl.pedersen_at_dartmouth.edu>
Date: 21 Oct 92 02:07:47 GMT
Message-ID: <carl.pedersen-201092215509_at_kip-sn-10.dartmouth.edu>


In article <1992Oct19.162139.967_at_ee.gatech.edu>, flur_at_eecom.gatech.edu (Peter W. Flur) wrote:
>
> Can someone suggest a good way to make conditional views? What
> I mean is to have a view that allows an oracle user to only
> access the rows of a table that they somehow "own" (maybe by
> a "username" field or something).
>
> I'd like to create a view something like
> create mytable as select * from bigtable where myoracleid=mytable.userid;

 create view mytable as
 select * from bigtable
  where bigtable.username = user
   with check option;

 grant select, update, delete, insert on mytable to public;

 create public synonym mytable for mytable;

The "with check option" is not necessary if you only grant "select" privilege.

One drawback is that on inserts, the client must insert their own username.

Aside from this, I've used this approach and it works just fine. Totally secure. It's much like each user has their own private "mytable", but the entire table can be looked at by the owner in one select.

A more exotic variant is as follows:

 create view mytable as
 select * from bigtable
  where ( bigtable.username = user and inserted_on = sysdate )

     or rowid is not null
   with check option;

 grant select, insert on mytable to public;

This sets up "mytable" so that anybody can insert into it, but not without identifying themselves and recording the exact date and time. At the same time, anybody can look at any existing row anytime.

The above might stop working in future versions of ORACLE, but ORACLE 7 provides neater ways to achieve the same effects - using DEFAULT and CHECK clauses. Received on Wed Oct 21 1992 - 03:07:47 CET

Original text of this message