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

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 ...
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