Re: DBMS_RLS - strange behaviour

A copy of this was sent to "Arati Vijay" <vijayj00_at_singnet.com.sg> (if that email address didn't require changing) On Wed, 27 Oct 1999 22:54:24 +0800, you wrote:

>I am developing a security management system for an application,
>and intend to use the DBMS_RLS.
>

please read my paper on fine grained access control found at http://osi.oracle.com/~tkyte/article2/index.html

In particular, you will want to read the "important caveat" section -- it shows that what you are seeing is actually expected, why it is expected and how to make sure you do not get burned by it.

The rest of the paper shows how to successfully implement a secure DBMS_RLS policy. You will find it of use to develop with dbms_rls.

>Our requirement is that a user has a default set of branches whose
>data can be viewed (ours is a centralized system handling multiple
>branches). Further, some application functions will allow him access
>only to a subset of his default set of branches.
>
>For example, he might be allowed Singapore, Bangkok and Hong
>Kong branch access by default. But he can only approve Singapore
>payments, and view payments only in Singapore and Hong Kong.
>
>I plan to use fine grained security, and set the predicate in the
>application context depending on the function. However, this is
>exhibiting funny behaviour.
>
>Consider two functions FUNC1 (allowed for Singapore) and FUNC2
>(allowed for Singapore and HongKong).
>
>My table, say emp_table has branch as one of its columns.
>
>When I do the following
>
>a) Set context for FUNC1 from SQLPLUS
>b) Exec a PL/SQL procedure to query the table. It returns Singapore data
>c) Set context for FUNC2 from SQLPLUS
>d) Exec the SAME PL/SQL procedure as in step b. It still returns only
>Singapore data. (It should return Singapore and HongKong). My new
>predicate has not taken effect.
>
>Anybody out there who knows why this happens?
>
>Then it gets more bizarre
>
>When I do the all the above steps from within a PL/SQL procedure, I don't
>experience any problems. To elaborate my PL/SQL procedure does
>the following
>
>a) Set context for FUNC1
>b) Query emp_table - it returns Singapore data
>c) Set context for FUNC2
>d) Query emp_table - the same query. It returns HongKong and Singapore
>data, as expected.
>
>Again, any clues?
>
>Unless I can get a predictable response from this feature, I cannot use it -
>as I have a user requirement for dynamic access rights.
>
>Is there any option or init parameter for SQLPLUS / Oracle RDBMS that
>I have to set / reset. Further, I will be using PowerBuilder as the
>client -
>are you aware of any precautions that I need to take?
>
>Thanx,
>VJ
>

--
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 Oct 27 1999 - 10:18:58 CDT