Re: column level privilege - resolved

Rahul,

Glad to know that it worked for you. Yes, I have implemented that and it works quite well in production.

Just make sure you understand that the columns are not really hidden, just "masked" - which means they may convey wrong impressions if not used properly. In my case, I have avoided using it in number fields; since in character fields it's easy to put a value like 'XXXXXXX', or 'NOT ALLOWED'. In the number field, a value of 0 may be misconstrued, especially in aggregation functions. Another options is to use NULLs, but they will lend a hand in indexing.

So, use this with caution. By the way, it's "Arup", not Arun :)

Arup Nanda
www.proligence.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, August 26, 2003 11:44 AM

> Arun, thanks for suggesting this, this is what i did.
>
> 1. created a table to store table names associated column names
> and a "security level" for a that column
>
> EMP emp_name 3
> EMP emp_sal 5
>
> 2. then i granted every users a security level also,
>
> 3. then i created a view on the name table
>
> select decode(sec_func('EMP','EMP_NAME'),1,emp_name,'x' emp_name),
> decode(sec_func('EMP','EMP_SAL'),1,emp_sal,0 emp_sal)
>
> the sec_func is passed table name and the column name and it checks the
> security_level of that column againet the security level of the user
> quering the table. if the security level is equal or lower, then 1 is
> returned, else 0 is returned, and the decode in the view will do the rest
>
> this way i can show/hide columns based on the security level (or roles
> granted to users) ...
>
> ok, i have only tested it, not implemented it, will it work in a real
> application environment ? has anyone used this type of column privs ?
>
> -TIA
>
>
> =====================
> On Sun, 24 Aug 2003 16:24:36 -0800, "Arup Nanda" <orarup_at_hotmail.com>
> wrote :
>
> > This is a multi-part message in MIME format.
> >
> >
> > Rahul,
> >
> > I'm not sure if this is too late; but here is a strategy you could
follow
> to achieve what you want. True, VPD does not have a mechanism to suppress
> columns; and using a view for each user is impractical. Someday, I hope,
> VPD will have that capability; but until then you could try the following.
> >
> > Suppose you have a table called SAVINGS, for savings account holders as
> follows:
> >
> > ACCTNO NUMBER
> > CLEARED_BALANCE NUMBER
> > UNCLEARED_BALANCE NUMBER
> >
> > The records in the table are as follows:
> >
> > ACCTNO CLEARED_BALANCE UNCLEARED_BALANCE
> > ---------- --------------- -----------------
> > 1 1000 1100
> > 2 1100 1200
> > 3 1300 1500
> >
> > Cleared balance is the amount the the customer can draw from the bank.
If
> there are checks outstanding, the balance is shown in uncleared. Let's
> start with a simple example - you have users who are allowed to see the
> uncleared balance of the customers and the others are not. Intead of
hiding
> the column completely, which how VPD operates, you would want to show then
> as zero, if not authorized to see that; otherwise the actual value is
shown.
> >
> > You would create a context as follows:
> >
> > create context sec_ctx using sec_ctx_pkg;
> >
> > The trusted function can be created as:
> >
> > create procedure sec_ctx_pkg
> > (
> > p_attribute_name in varchar2,
> > p_attribute_value in varchar2
> > ) is
> > begin
> > dbms_session.set_context(
> > 'sec_ctx',
> > p_attribute_name,
> > p_attribute_value);
> > end;
> > /
> >
> > In the after-login trigger, you would set the context value
automatically
> for user using
> >
> > set_Ctx_pkg ('cleared', 'yes'); or set_Ctx_pkg ('cleared', 'no');
> depending on whether the user is cleared to see the balance or not. In
real
> life, you may have a table that lists all users and whether or not they
are
> cleared. The after-logon trigger could read that table and set the context
> attribute properly.
> >
> > Next, you would craete a view.
> >
> > create or replace view vw_savings
> > as
> > select acctno, cleared_balance,
> > decode(sys_context('sec_ctx','cleared'),'yes',
> > uncleared_balance, 0) uncleared_balance
> > from savings
> > /
> >
> > Note: there is only ONE view, not one per user. Regardless of how many
> users you have, there will be only one view.
> >
> > Now to test the setup. Assume user RAHUL is allowed to see the
> uncleared_balance. The after-logon trigger will set the context
> attribute "cleared" to "yes" when the user logs in. When the user selects:
> >
> > select * from vw_savings;
> >
> > He sees:
> >
> > ACCTNO CLEARED_BALANCE UNCLEARED_BALANCE
> > --------- --------------- -----------------
> > 1 1000 1100
> > 2 1100 1200
> > 3 1300 1500
> >
> > Which is the correct value. Now, user ARUP logs in, who does not have
the
> authority to see the uncleared balance. The logon trigger will set the
> attribute to "no" and the same select will now produce:
> >
> > ACCTNO CLEARED_BALANCE UNCLEARED_BALANCE
> > ---------- --------------- -----------------
> > 1 1000 0
> > 2 1100 0
> > 3 1300 0
> >
> > Note: How the uncleared balance is 0.
> >
> > This model can be extended to any column and any number of values for
the
> attribute "cleared". You could even specify levels of users who are
allowed
> to see the balances under certain amount; not above that. In case of
> character values; it's even simpler; just mask it by some value such
> as "XXXX", or "NOT CLEARED TO SEE".
> >
> > All the users are granted select privileges on the view, not the table.
> The context setting procedure is owned by a secured user; SYS would do,
but
> you should have a separate username, say, SECUSER, for it. In doing so,
you
> prevent the user from setting the context directly.
> >
> > This is not VPD and not supposed to be; but I think it will work nice
for
> your purpose. Please let us know the devlopment at your side.
> >
> > Hope this helps.
> >
> > Arup Nanda
> > www.proligence.com
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Sunday, August 24, 2003 4:34 AM
> > RL
> >
> >
> > > 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: rahul
> > > > INET: rahul_at_infotech.co.id
> > > >
> > > > 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).
> > > >
> > > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: rahul
> > > INET: rahul_at_infotech.co.id
> > >
> > > 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).
> > >
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: rahul
> INET: rahul_at_infotech.co.id
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: orarup_at_hotmail.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).