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

[Arup Nanda]

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

From: "rahul" <[EMAIL PROTECTED]>

To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>

Sent: Sunday, August 24, 2003 4:34 AM

Subject: RE: 9iR2, grant select on a column (without using views) using 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"
> <[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: rahul
> >   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).
> >
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: rahul
>   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).
>