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 ...
Get Oracle PL/SQL for DBAs now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.