| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> column level privilege - resolved
Arun, thanks for suggesting this, this is what i did.
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
> 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
> > > ----------------------------------------------------------------------
-- 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).Received on Tue Aug 26 2003 - 10:44:26 CDT
 |
 |