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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
