Re: Grant select on columns - oops

I , of course, meant Tom Kyte, the Oracle guru, not Tom Kite the great golfer...

TurkBear <noone_at_nowhere.com> wrote:

>
>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! ==-----

-----= 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 - 14:16:21 CDT