Re: Question: DMBS_RLS Row-level Security Policies

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 - 11:03:13 CDT