Re: 9iR2, grant select on a column (without using views) using RL

Hi!

> how would i write a policy which retuns selected columns if the user has
> issued select * from tab ???

You can't. Because in describe phase of query, sys.col$ is queried to get column names and datatypes. And this always returns all columns that physically exist in a given table (except columns set as "unused"). So, unless you implement some kind of RLS on sys.col$ table, which I doubt is gonna ever work, you'll always see all the fields of a row of a given table.

Your options are either code the column viewing security to application, or create views - not for every user, but for every security profile. There's probably lot less security profiles than users. Then make a grant and a private synonym for appropriate view to every user's schema (if you are using Oracle authentication mechanism). That way your application can always access synonym X which points to view1, view2... etc..

Tanel.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).