| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Grant select on columns
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
CREATE OR REPLACE VIEW viewowner.view_for_the_mushroom_managed
AS
SELECT col1,
'Mind your own business!' AS col2,
NULL AS col3
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...
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
 |
 |