Re: Question: DMBS_RLS Row-level Security Policies

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