Oracle PL/SQL for DBAs

Introduction to 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. You can grant privileges to specific users to insert only into certain tables while allowing them to select from other tables. For example, a user John can be granted SELECT access to the table EMP owned by Scott, which allows John to select any row from the table, but not to update, delete, or insert. Object-level privileges satisfy many requirements, but sometimes they are not granular enough to meet the various security requirements that are often associated with a company’s data. A classic example arises from Oracle’s traditional human resources demonstration tables. The employee table contains information about all the employees in the company, but a departmental manager should only be able to see information about employees in his department.

Historically, database administrators have relied on the creation of views on top of underlying tables to achieve a degree of row-level security. Unfortunately, this approach can result in a multitude of views, which are difficult to optimize and manage, especially because the rules restricting access to rows can change often over the lifetime of an application.

This is where RLS comes into play. Using RLS, you can very precisely restrict the rows in a table that a user can see, and that control can be accomplished through the ...