Re: Grant select on columns

They will work, but for a more complete and, I think, more manageable and elegant solution try Tom Kyte's method of 'fine grained access control'

look at it here :

http://osi.oracle.com/~tkyte/article2/index.html

hth

catherinedevlin_at_girlgeekmail.com wrote:

>
>Never mind! I've got it, I've got it!
>
>Of COURSE there are things I can use to point to various different views;
>they're called "synonyms". Duh.
>
>To implement column-level SELECT security, I create a view for each type of
>user, which points to the main view (or table).
>
>CREATE OR REPLACE VIEW viewowner.view_for_omniscient
>AS
>SELECT col1,
> col2,
> col3
>FROM main_view;
>
>CREATE OR REPLACE VIEW viewowner.view_for_the_mushroom_managed
>AS
>SELECT col1,
> 'Mind your own business!' AS col2,
> NULL AS col3
>FROM main_view;
>
>Then I create private synonyms for each user, pointing to the view that they
>should be able to see.
>
>(in the account of Zrxygorph, the seven-brained Alpha-Centaurian datagod)
>create synonym front_end_view for viewowner.view_for_omniscient;
>
>(in the account of Ned, the undernourished data weasel)
>create synonym front_end_view for viewowner.view_for_the_mushroom_managed;
>
>Now anyone or anything looking at 'front_end_view' will see exactly what
>they're supposed to see!
>
>I've got row-level security implemented in the main_view, but maybe I'll move
>it out into the specialized views... well, either way will work.
>
>I'm so happy!
>
>Thanks for bearing with my babbling. Synonyms are my friend...
>
>- Catherine
>http://profiles.yahoo.com/arcticturtle
>
>In article <9odaek$2he$1_at_news.netmar.com>, <catherine_devlin_at_purpleturtle.com>
>writes:
>>
>>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 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 Fri Sep 21 2001 - 13:00:12 CDT