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

Contexts as Predicates in RLS

So far you have learned that a procedure must be used to set a context value, which is akin to a global package variable. You might be tempted to ask, “How is that useful? Doesn’t it increase the complexity rather unnecessarily without achieving any definite purpose?”

No. Because the trusted procedure is the only way to set a context attribute’s value, you can use it to maintain execution control. Inside the trusted procedure, I can place all types of checks to ensure that the variable assignments are valid. I can even completely eliminate the passing of parameters and set the values from predetermined values without any input (and therefore influence) from the user. Going back to the requirement for employee access, for instance, I know that we need to set the application context value to a string of department numbers, picked from the table EMP_ACCESS, not passed in by the user.

To accomplish this, I will use the application context in the policy function itself. First, I need to modify the policy function.

      1 CREATE OR REPLACE FUNCTION authorized_emps (
      2     p_schema_name   IN   VARCHAR2,
      3     p_object_name   IN   VARCHAR2
      4  )
      5     RETURN VARCHAR2
      6  IS
      7     l_deptno       NUMBER;
      8     l_return_val   VARCHAR2 (2000);
      9  BEGIN
     10     IF (p_schema_name = USER)
     11     THEN
     12        l_return_val := NULL;
     13     ELSE
     14        l_return_val := SYS_CONTEXT ('DEPT_CTX', 'DEPTNO_LIST');
     15     END IF;
     16
     17     RETURN l_return_val;
     18  END;

Here the policy function expects the department numbers to be passed through the attribute ...

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