Re: Question: DMBS_RLS Row-level Security Policies

There's a new feature in 9i called Partitioned Fine Grained Access (implemented via a Policy Group), and it was expressly designed to allow multiple policies to exist on a table *without them being AND'ed*. In other words, the implication was that in 8i, multiple policies *were* AND'ed (though I never got my hands dirty enough to find out what that meant in practice).

The pertinent paragraph in the 9i documentation reads: "When more than one Fine Grained Access Control has been defined on a table, a row must satisfy all requirements before being visible. The FGACs are effectively "AND"-ed. This is not desirable if the table is to be used by users from separate groups, each group with a different FGAC. Prior to Oracle9i, one complicated FGAC that included all user groups' requirements was defined."

Regards
HJR "Mike Jay" <mikejay_at_mitre.org> wrote in message news:3BBB36C1.4554E72_at_mitre.org...
> Off to the book store ;)
>
> Using 'ALTER SYSTEM' in my test instance, I found that only one policy
> function per statements_type would fire.
>
> That is, for MyOwner.MyTable the user MyUser will SELECT against
> the table, but be subject to only one policy with respect to
> the SELECT statement_type EVEN THOUGH two policies pertaining to
> SELECT are defined.
>
> Does this make sense based on y'all's experience?
>
> Again, I am using 8.1.6 on Solaris 5.7 configuration.
>
> Right now we are doing the brute force solution of just having only
> one policy per statement type, but that seems rather limiting.
>
> Thanks,
> mikejay
>
> Jonathan Lewis wrote:
> >
> > You mean you haven't got a copy of my book !
> > I don't know where else it is documented - although
> > there is likely to be something in
> > $ORACLE_HOME/rdbms/mesg/oraus.msg
> >
> > alter session set events '10730 trace name context forever, level 1';
> >
> > This results in a dump describing the object,
> > policy, user, and predicate when you parse
> > a new SQL statement that includes objects
> > protected by RLS policies.
> >
> > --
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Host to The Co-Operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> > Author of:
> > Practical Oracle 8i: Building Efficient Databases
> >
> > Screen saver or Life saver: http://www.ud.com
> > Use spare CPU to assist in cancer research.
> >
> > Mike Jay wrote in message <3BBA26FD.51F401DE_at_mitre.org>...
> > >What is the meaning of this event number?
> > >
> > >What manual and chapter number?
> > >
> > >The .trc files contained some info, but only when the d_pred value had
> > >invalid syntax.
> > >
> > >The V_$SYSTEM_EVENT had no numbering and the V_$EVENT_NAME had only 211
> > >entries.
> > >
> > >Some details regarding DB and OS:
> > >Oracle Version 8.1.6
> > >Solaris SunOS 2.7
> > >OFA directory structure
> > >TOAD is the developmental tool
> > >Alas, NOT an option is Oracle Enterprise Manager
> > >
> > >Thanks,
> > >mikejay
> > >
> > >Jonathan Lewis wrote:
> > >>
> > >> Try setting event 10730 and checking the
> > >> resulting trace files.
> > >>
> > >> Depending on the version of Oracle this will
> > >> either give you just the predicate generated,
> > >> or - in the latest version - the text defining
> > >> the in-line view that replaces the basic table
> > >> reference. This may help you figure out
> > >> what is going wrong.
> > >>
> > >> --
> > >> Jonathan Lewis
> > >> http://www.jlcomp.demon.co.uk
> > >>
> > >> Host to The Co-Operative Oracle Users' FAQ
> > >> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> > >>
> > >> Author of:
> > >> Practical Oracle 8i: Building Efficient Databases
> > >>
> > >> Screen saver or Life saver: http://www.ud.com
> > >> Use spare CPU to assist in cancer research.
> > >>
> > >> Mike Jay wrote in message <3BB9FD8D.CEAF3199_at_mitre.org>...
> > >> >Hi folks,
> > >> >
> > >> >For a given object_schema.object_name (e.g., myuser.mytable)
> > >> >with a 'SELECT' statement_types parameter to DMBS_RLS.ADD_POLICY,
> > >> >will every policy fire for two or more policies?
> > >> >
> > >> >That is, if mytable has the two policies:
> > >> >
> > >> >DMBS_RLS.ADD_POLICY(myuser,
> > >> > mytable,
> > >> > mypolicy_select_A,
> > >> > mypackageuser,
> > >> > mypackage.myfunction_A,
> > >> > 'SELECT')
> > >> >
> > >> >DMBS_RLS.ADD_POLICY(myuser,
> > >> > mytable,
> > >> > mypolicy_select_B,
> > >> > mypackageuser,
> > >> > mypackage.myfunction_B,
> > >> > 'SELECT')
> > >> >
> > >> >will both mypolicy_select_A and mypolicy_select_B fire?
> > >> >
> > >> >If so, are the two dynamic predicates joined by AND rather than OR
> > >> >logic?
> > >> >
> > >> >Having read:
> > >> >
> > >> >Oracle8i (8.1.6) Concepts, A76965-01, Chapter 27, Privileges, Roles,
and
> > >> >Security Policies
> > >> >
> > >> >Oracle8i (8.1.6) Supplied PL/SQL Packages Reference, A76936-01,
Chapter
> > >> >44, DBMS_RLS
> > >> >
> > >> >I had expected that both should fire and that both d_pred values
> > >> >returned must be true for a given row to be selected by a given
user.
> > >> >
> > >> >Also, in my particular example, one policy works as expected, but
the
> > >> >other fails.
> > >> >
> > >> >Using DBMS_OUTPUT.PUT_LINE to check package functions (i.e.,
> > >> >myfunction_A and myfunction_B), the d_pred returned is the correct
> > >> >VARCHAR2 dynamic predicate for my business rules with respect the
> > >> >package functions.
> > >> >
> > >> >Yet, I still am not certain how the functions work at run time with
a
> > >> >SQL SELECT statement's WHERE clause for such things as precedence
and
> > >> >syntactical grouping, that is, I may need parenthesis where I
thought I
> > >> >didn't.
> > >> >
> > >> >Does anyone have a keyword--I have tried row-level security--for use
in
> > >> >searches on technet as the Row-Level Security involves more than
PL/SQL
> > >> >packages per se?
> > >> >
> > >> >Thanks,
> > >> >mikejay
Received on Wed Oct 03 2001 - 15:39:04 CDT