Re: Virtual Private Database

Thank you once again. Another question comes to mind regarding column-level security applied to a table that has been set up for row-level security using DBMS_RLS.

Assume a PARTS table has been set up for row-level security, by vendor name. ACME CAPACITORS sees only their capacitors, ZENITH SEMICONDUCTOR sees only their chips. User ACME wants to give user ZENITH access to a subset of the columns relating to its capacitors, e.g. everything but the ACTUAL_MFG_COST column; and so user ACME creates a VIEW against the PARTS table:

CREATE VIEW RETAIL_CAPACITORS as
Select PART_NO, DESCRIPTION, SUGGESTED_RETAIL_COST from PARTS

GRANT SELECT ON RETAIL_CAPACITORS TO ZENITH Now what happens when user ZENITH selects against the RETAIL_CAPACITORS view?

    Select * from RETAIL_CAPACITORS

Does user ZENITH get a list of ACME capacitors, or nothing at all?

Tim Romano

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:ubmv5sk75tkujiggdfm17mg83dhp95m24s_at_4ax.com...
> A copy of this was sent to "Tim Romano" <tim_at_ot.com>
> (if that email address didn't require changing)
> On Tue, 21 Dec 1999 14:33:55 -0500, you wrote:
>
> >Thank you for the reply. A followup, please:
> >
> >When the predicate is transparently applied to the DML statement,
according
> >to the definitions in the function one creates, it is insinuated, so to
> >speak, into the DML statement as a "where clause" (but without the
"where")
> >and thus parsed along with the statement. I mean, it's not applied as a
> >secondary filter *after* the DML has been executed. Is that correct?
> >
> >If so, how would the predicate ("where x = <some value>") affect the DML
if
> >it involves a table, secured in this manner, in an *outer* join? Wouldn't
> >there be a conflict of join operators?
> >Tim Romano
> >
>
> Lets say to table T1, you decided to supply the predicate "where c1 = 2"
for a
> given user. Then, given a query such as:
>
>
> select *
> from T1, T2
> where T2.c1 = T1.c1 (+)
>
>
> would be re-written in effect as:
>
>
> select *
> from ( select * from t1 where c1 = 2 ) t1, t2
> where t2.c1 = t1.c1 (+)
> /
>
> It is applied as a 'pre' filter if you will -- before your dml (not really
> before, but you get the point) is executed. In reality - the optimizer
may very
> well be able to merge it right in.
>
>
> For example:
>
> tkyte_at_ORA8IDEV.WORLD> create table t1 ( c1 int );
> tkyte_at_ORA8IDEV.WORLD> create table t2 ( c1 int );
>
> tkyte_at_ORA8IDEV.WORLD> insert into t1 values ( 1 );
> tkyte_at_ORA8IDEV.WORLD> insert into t1 values ( 2 );
> tkyte_at_ORA8IDEV.WORLD> insert into t1 values ( 3 );
> tkyte_at_ORA8IDEV.WORLD> insert into t1 values ( 4 );
> tkyte_at_ORA8IDEV.WORLD> insert into t2 select * from t1;
> tkyte_at_ORA8IDEV.WORLD> insert into t2 values ( 5 );
>
> tkyte_at_ORA8IDEV.WORLD> select *
> 2 from T1, T2
> 3 where T2.c1 = T1.c1 (+)
> 4 /
>
> C1 C1
> ---------- ----------
> 1 1
> 2 2
> 3 3
> 4 4
> 5
>
>
> Now, lets apply the dbms_rls function:
>
> tkyte_at_ORA8IDEV.WORLD> create or replace function
y_security_policy( obj_schema
> in varchar2,
> 2 obj_name in
varchar2 )
> 3 return varchar2
> 4 as
> 5 begin
> 6 return 'c1 = 2';
> 7 end;
> 8 /
>
> Function created.
>
> tkyte_at_ORA8IDEV.WORLD>
> tkyte_at_ORA8IDEV.WORLD> begin
> 2 dbms_rls.add_policy
> 3 ( object_schema => user,
> 4 object_name => 't1',
> 5 policy_name => 'my_policy',
> 6 function_schema => user,
> 7 policy_function => 'my_security_policy',
> 8 statement_types => 'select, insert, update, delete' );
> 9 end;
> 10 /
>
> PL/SQL procedure successfully completed.
>
>
> and re-run the query:
>
> tkyte_at_ORA8IDEV.WORLD> select *
> 2 from T1, T2
> 3 where T2.c1 = T1.c1 (+)
> 4 /
>
> C1 C1
> ---------- ----------
>
> 1
> 2 2
> 3
> 4
> 5
>
>
> It does not 'mess up' the outer join since the predicate is applied to T1
AND
> THEN T1 is outer joined to T2....
>
> >
> >
> >
> >Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
> >news:aaav5ss2rpp90gb1p44086vafs4nvjuqi7_at_4ax.com...
> >> A copy of this was sent to "Tim Romano" <tim_at_ot.com>
> >> (if that email address didn't require changing)
> >> On Tue, 21 Dec 1999 09:22:07 -0500, you wrote:
> >>
> >> >I've searched Technet looking for information on the row-level
security
> >for
> >> >8i (e.g. Customer X sees their orders only) that are provided by the
> >> >"Virtual Private Database" (my search argument) but came up with
nothing.
> >> >
> >> >I'm looking for information on how to implement it, and on how
difficult
> >it
> >> >is to maintain in a dynamic environment, where new customers were
being
> >> >added all the time, to use the example above. How is the User logon ID
> >> >associated with the CustomerID? Do they have to be one and the same?
Or
> >is
> >> >there a lookup table? Or are the associations hard-coded in a SWITCH
> >> >statement in a function?
> >> >
> >> >Thank you
> >> >Tim Romano
> >>
> >>
> >> see the url in my signature for a paper on fine grained access control.
> >>
> >> To answer the "how is the user logon id associcated with the customer
> >id" -- you
> >> would have to model this information, store it in a table and then use
it
> >at
> >> query parse time to determine the appropriate query.
> >>
> >> --
> >> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
> >Oracle8i'...
> >> Current article is "Part I of V, Autonomous Transactions" updated June
> >21'st
> >>
> >> Thomas Kyte tkyte_at_us.oracle.com
> >> Oracle Service Industries Reston, VA USA
> >>
> >> Opinions are mine and do not necessarily reflect those of Oracle
> >Corporation
> >
>
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>
>
Received on Wed Dec 22 1999 - 07:22:25 CST