Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL for DBAs

Static RLS Policies

So far the examples have demonstrated the use of a static policy, which means that the value returned by the predicate function does not change even if the circumstances under which it is called change.

Given this fact, RLS need not execute the function every time a query is issued against the table. The value can be determined only once, cached, and then reused from the cache as many times as needed. To make a policy behave that way, you can define it as a static policy to RLS by passing TRUE for the static_policy argument:

      1  BEGIN
      2     DBMS_RLS.ADD_POLICY (
      3        object_name      => 'EMP',
      4        policy_name      => 'EMP_POLICY',
      5        function_schema  => 'HR',
      6        policy_function  => 'AUTHORIZED_EMPS',
      7        statement_types  => 'INSERT, UPDATE, DELETE, SELECT',
      8        update_check     => TRUE,
      
      9        static_policy    => TRUE
     10     );
         11 END;

The default value of the static_policy parameter is FALSE: that value makes the policy dynamic rather than static, and it causes the policy function to be called for each operation on the table. I’ll describe dynamic policies later in the chapter in the section "Defining a Dynamic Policy.” Policy types, in addition to static and dynamic, are supported in Oracle Database 10g. See the section "Other Classes of Dynamism" for more information.

There are many situations that call very precisely for a static policy. Consider a merchandise warehouse that is servicing several customers. Here, a predicate might be used to limit the entries to only the relevant records for that customer. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page