DBMS_RLS - strange behaviour

I am developing a security management system for an application, and intend to use the 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

1. Set context for FUNC1 from SQLPLUS
2. Exec a PL/SQL procedure to query the table. It returns Singapore data
3. Set context for FUNC2 from SQLPLUS
4. 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

1. Set context for FUNC1
2. Query emp_table - it returns Singapore data
3. Set context for FUNC2
4. 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 Received on Wed Oct 27 1999 - 09:54:24 CDT