| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: column level privilege - resolved
Reginald,
No, FGAC (or VPD or RLS, depending on who you ask) is not about columns; it's about restricting rows. Within the selected rows, all the columns are visible. Rahul wanted to mask columns without creating views for each user. This is the only way to do that.
Hope this helps.
Arup
>
> Rahul:
>
> It sounds like your implementation is very similar, in theory, to Oracle's
> Fine Grain Access. Are you re-inventing the wheel?
>
>
>
>
> 08/26/2003
> 10:44 AM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> 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:
> INET: Reginald.W.Bailey_at_jpmorgan.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).
>
-- 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).Received on Tue Aug 26 2003 - 11:49:29 CDT
 |
 |