Oracle PL/SQL Programming, 5th Edition

Row-Level Security

Row-level security (RLS) is a feature introduced in Oracle8i Database that allows you to define security policies on tables (and specific types of operations on tables) that have the effect of restricting which rows a user can see or change in a table. Much of the functionality is implemented with the built-in package DBMS_RLS.

Oracle has, for years, provided security at the table level and, to some extent, at the column level. Privileges may be granted to allow or restrict users to access only some tables or columns. For example, you can grant privileges to specific users to insert only into certain tables while allowing them to select from other tables. Or you can allow users to update certain columns of specific tables. Using views, you can also restrict how the tables get populated from the views, using INSTEAD OF triggers (described in Chapter 19). All of these privileges are based on one assumption; you can achieve security simply by restricting access to certain tables and columns. But when that access is granted, the users have access to see all the rows of the table. What if you need to limit the visibility of rows in a table, based on criteria such as the identity of the user or other application-specific characteristics?

Consider, for example, the demonstration table provided with the database—EMP in schema HR. The table has 14 rows of data, with primary keys (employee numbers) ranging from 7369 to 7934.

Suppose that you have given a user named Lora access ...