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

rahul

You can use the standard technique for that -- hide your sensitive columns under a view, something like

...
SELECT pkey

      , DECODE(SYS_CONTEXT('CTX$SEC', 'ROLE')
        , 'CEO', col1
        , 'MANAGER', col1
          NULL
        ) col1

Where ctx$sec role is a application role based security context. You can define whatever context you like. Using this approach you can use one view that covers different user application roles.

Regards,

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

rahul wrote:
> how would i write a policy which retuns selected columns if the user has 
> issued select * from tab ??? 
> 
> using views for each user would work, but then.. i would end up with 
> so many views in the main schema !!! ;-(
> 
> 
> On Sat, 23 Aug 2003 12:24:39 -0800, "Jamadagni, Rajendra" 
> <Rajendra.Jamadagni_at_ESPN.COM> wrote :
> 
> 


>>This message is in MIME format. Since your mail reader does not understand

>>this format, some or all of this message may not be legible.

>>

>>

>>Use RLS ...

>>

>>Raj

>>--------------------------------------------------------------------------

> 
> --
> 


>>----

>>Rajendra dot Jamadagni at nospamespn dot com

>>All Views expressed in this email are strictly personal.

>>QOTD: Any clod can have facts, having an opinion is an art !

>>

>>

>>-----Original Message-----

>>Sent: Saturday, August 23, 2003 2:34 AM

>>To: Multiple recipients of list ORACLE-L

>>

>>

>>list, i'm ikn the process of designing security for a highly sensitive 

>>schema for a bank, 

>>

>>plan:

>>have multiple oracle users, and use roles, and grant minimum required 

>>privs, all the user/role/privs management coded in the application (with 

> 
> in 
> 


>>turn would create the db role and user etc) 

>>

>>probolem:

>>i cannot do a "grant select(col1)on tabname to role1", as select grant on 

> 
> a 
> 


>>column level is not supported, to workaround this i must

>>

>>1) use views and include all the columns granted seleted privs for a 

> 
> user, 
> 


>>then give grant select on this view to user.

>>

>>2) somehow use RLS ?? 

>>

>>TIA

>>

>>-Rahul


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: vladimir.begun_at_oracle.com

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).