Grant select on columns

From: <catherine_devlin_at_purpleturtle.com>
Date: 20 Sep 2001 17:53:24 GMT
Message-ID: <9odaek$2he$1@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 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

Received on Thu Sep 20 2001 - 12:53:24 CDT