Oracle Tips by Burleson

Chapter 13 Oracle 10g Enhancements

where user_id = sys_context(‘app_ctx’, ‘l_user_id’);

The logon trigger sets the attribute l_user_id in the context app_ctx when the user logs into the database. Note, here the part of the predicate to the left of the equality sign, i.e. user_id is fixed. The right side is fairly static; it only changes when the user disconnects and reconnects; but then it becomes a different session. Hence the change is not really a change, but another invocation of the policy function.

This type of policy, where the function executes only once, not re-executed every time or at every context change, is known as a Static policy. Since the policy function is not executed more than once per session, these types of policies are extremely fast.

The policy type is indicated while defining the policy. Taking the example of the code segment provided in Chapter 5, Virtual Private Database, to add the policy to the table:

begin
dbms_rls.add_policy (
    object_schema    => 'CLAIM_SCHEMA',
    object_name      => 'CLAIMS',
    policy_name      => 'CLAIM_VIEW_POLICY',
    function_schema  => 'CLAIM_SCHEMA
    policy_function  => 'GET_AUTH_PROVIDERS',
    statement_types  =>
        'INSERT, UPDATE, DELETE, SELECT',
    update_check     => TRUE,
    enable           => TRUE,