Oracle PL/SQL for DBAs

Defining a Dynamic Policy

In the previous sections, I talked about a policy that returns a predicate string that is constant—for example, SAL <= 1500. In real life, such a scenario is not very common, except in some specialized applications such as goods warehouses. In most cases, you will need to build a filter based on the user issuing the query. For instance, the HR application may require that users see only their own records, not all records in a table. This is a dynamic requirement, as it needs to be evaluated for each employee who logs in. The policy function can be rewritten as follows.

      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_return_val   VARCHAR2 (2000);
      8  BEGIN
      9     l_return_val := 'ENAME = USER';
     10     RETURN l_return_val;
     11  END;
     12  /

In line 9, the predicate will compare the ENAME column with the USER—that is, the name of the currently logged-in user. If the user Martin (remember that Martin is the name of an employee in the table EMP) logs in and selects from the table, he sees only one row—his own.

    SQL> CONN martin/martin
    Connected.

    SQL> SELECT * FROM hr.emp;

    EMPNO ENAME      JOB          MGR HIREDATE     SAL   COMM DEPTNO
    ------ ---------- --------- ------ --------- ------ ------ ------
      7654 MARTIN     SALESMAN    7698 28-SEP-81  1,250  1,400     30

Now let’s expand this model to let Martin show more records—not just his own, but his entire department’s records. The policy function now becomes the following.

 1 ...