Re: Grant select on columns

Hi Catherine,

have you considered using packages/procedures instead of views for your tables?
You can return a VARRAY or REF CURSOR so the procedure looks to Forms as if it´s a table. All logic for security, column access and dynamic building of statements is
done in the package. Very nice. I did this a few years ago in a project where
column based security was a must and in some cases even content of the columns had to
be críteria if a user would see the row/column or not. Furthermore by using dynamic sql
you can code very efficiently and reduced network traffic is the result.

hth
Andy

<catherine_devlin_at_purpleturtle.com> schrieb in im Newsbeitrag: 9odaek$2he$1_at_news.netmar.com...
>
> Hi, everybody! I just wanted to resurrect an
> ancient c.d.o.s discussion to look at a question
> in more depth.
>
> >From: Ms. D.H. Harvey (qq45_at_liverpool.ac.uk)
> >Subject: Re: grant select on columns
> >Date: 1999/07/23
> >
> >Rüdiger J. Schulz (r.schulz_at_berlin.de) wrote:
> >: hi all,
> >: I try to grant select on columns to a user with
> >: GRANT select (field1, field2) ON schema1.table1 TO user;
> >: but oracle dont want to give the grant.
> >: what's wrong?
> >: R.J. Schulz
> >
> >According to manual (Release 7.3 SQL Reference) 'You can only specify
> >columns when granting the INSERT, REFERENCES, or UPDATE privilege'.
> >
> >Helen
>
> And it's still true today.
>
> But my question is: what if I really WANT to? Excuse me as I think out
loud,
> hoping that some super-Oracle person will be intrigued by the problem and
> come to the rescue.
>
> Views won't do for my case -
> - Different users should see subsets of big column's tables
> - Would need 25 (?) different views
> for 25 different subsets of tables
> - Also making optional Oracle Form for data entry/viewing
> - must access table via a view (for row-level security)
> - would need 25 different versions of Form,
> each using different view
> - Yuck!
> - security should remain on the database side
> (not the Form) for ad-hoc queries, etc.
>
> So how can I limit which columns my users can see?
>
> Thoughts I've suffered so far...
>
> 1. Is there a way to create an 'envelope' view, something like
> CREATE VIEW envelope_view AS
> SELECT * FROM julie_view if you're Julie, or
> SELECT * FROM bill_view if you're Bill
>
> ?
> I doubt it, because then the view's definition
> would not have a fixed list of columns. But
> maybe if it weren't a view, as such? Something
> like a pointer? My C roots are showing.
>
> 2. CREATE VIEW overall_view AS
> SELECT column_for_everybody,
> func_column_show( column_for_julie ),
> func_column_show( column_for_bill )
> FROM big_table
> WHERE row-level security here...
>
> func_column_show is a function that returns either
> the column value (if they're entitled to see it)
> or a recognizable dummy placeholder (if they're not)
>
> Then INSTEAD OF triggers are needed for update,
> insert, etc, because by putting functions in, I
> made those columns of the view non-updatable.
>
> I think this might work, but it seems almost as
> painful and inelegant as the 25-different-views
> solution.
>
> Is anyone still reading? If you have any ideas, I'd
> be very grateful for them! Thank you very much.
>
> - Catherine
> http://profiles.yahoo.com/arcticturtle
>
>
> ----- Posted via NewsOne.Net: Free (anonymous) Usenet News via the
eb -----
> http://newsone.net/ -- Free reading and anonymous posting to 60,000+
groups
> NewsOne.Net prohibits users from posting spam. If this or other posts
> made through NewsOne.Net violate posting guidelines, email
abuse_at_newsone.net Received on Fri Sep 21 2001 - 04:08:50 CDT