Re: Grant select on columns

Look up Tom Kite's excellent article on 'Fine Grained Access Control' - http://osi.oracle.com/~tkyte/

catherine_devlin_at_purpleturtle.com wrote:

>
>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 Web -----
> 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

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!  Check out our new Unlimited Server. No Download or Time Limits! -----== Over 80,000 Newsgroups - 19 Different Servers! ==----- Received on Thu Sep 20 2001 - 13:52:43 CDT