Oracle PL/SQL for DBAs

A Simple Example

Let’s start with a simple example of using RLS. Here is the definition of the table EMP in the schema HR, created from the example script provided with Oracle software in $ORACLE_HOME/sqlplus/demo/demobld.sql.

    SQL> DESC emp
     Name              Null?    Type
     ----------------- -------- ------------
     EMPNO             NOT NULL NUMBER(4)
     ENAME                      VARCHAR2(10)
     JOB                        VARCHAR2(9)
     MGR                        NUMBER(4)
     HIREDATE                   DATE
     SAL                        NUMBER(7,2)
     COMM                       NUMBER(7,2)
     DEPTNO                     NUMBER(2)

The table has 14 rows:

    EMPNO ENAME      JOB          MGR HIREDATE     SAL   COMM  DEPTNO
    ----- ---------- ---------- ----- --------- ------ ------ -------
     7369 SMITH      CLERK       7902 17-DEC-80    800             20
     7499 ALLEN      SALESMAN    7698 20-FEB-81  1,600    300      30
     7521 WARD       SALESMAN    7698 22-FEB-81  1,250    500      30
     7566 JONES      MANAGER     7839 02-APR-81  2,975             20
     7654 MARTIN     SALESMAN    7698 28-SEP-81  1,250  1,400      30
     7698 BLAKE      MANAGER     7839 01-MAY-81  2,850             30
     7782 CLARK      MANAGER     7839 09-JUN-81  2,450             10
     7788 SCOTT      ANALYST     7566 09-DEC-82  3,000             20
     7839 KING       PRESIDENT        17-NOV-81  5,000             10
     7844 TURNER     SALESMAN    7698 08-SEP-81  1,500      0      30
     7876 ADAMS      CLERK       7788 12-JAN-83  1,100             20
     7900 JAMES      CLERK       7698 03-DEC-81    950             30
     7902 FORD       ANALYST     7566 03-DEC-81  3,000             20
     7934 MILLER     CLERK       7782 23-JAN-82  1,300             10

I’ll start with a very simple requirement: I want to restrict users to seeing only employees with a salary of 1,500 or less. Using these assumptions, let’s suppose that a user enters the query

    SELECT * FROM emp;

I would like RLS to modify this query transparently to:

    SELECT * FROM emp WHERE sal <= 1500;

That is, whenever a user ...