Section 5.1. Introduction to RLS

5.1. 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 creation of PL/SQL functions that encapsulate complex rules logic and that are much easier to manage than views.

At a high level, RLS consists of three main components:

Policy

A declarative command that determines when and how to apply restrictions on a user's access during queries, insertions, deletions, updates, or combinations of these operations. For example, you may want only UPDATEs to be restricted for a user, while SELECTS remain unrestricted, or you may want to restrict access for SELECTs only if the user queries a certain column (e.g., SALARY), not others.

Policy function

A stored function that is called whenever the conditions specified by the security policy are met.

Predicate

A string that is generated by the policy function, and then transparently and automatically appended by Oracle to the WHERE clause of a user's SQL statements.

RLS works by automatically applying the predicate to the SQL statement issued by the user, regardless of how that statement was executed. The predicate filters out rows based on the condition defined in the policy function. If you create the condition in such a way that it excludes all rows that should not be seen by a user, you will effectively be establishing security at the row level. Oracle's automatic application of the predicate to a user's SQL statement is a key aspect of what makes RLS so secure and comprehensive.

5.1.1. Why Learn About RLS?

From this initial description of RLS, you might be thinking that it is a rather specialized security function, one that you are not likely to use in your daily work as a DBA. In fact, the benefits of RLS extend beyond security. I'll take a quick look here at the reasons that DBAs find RLS helpful and discuss these in greater detail throughout the chapter.

Enhance security

Certainly, RLS's primary purpose is to enhance security within your organization. For many organizations, RLS is helpful in meeting the new security and privacy initiatives and guidelines (e.g., Sarbanes-Oxley, HIPAA, Visa Cardholder Information Security Program) with which they must now comply. These days, security is no longer an afterthought, relegated to the cubicles of auditors far out in the corporate jungle. It has become an important part of the overall system design and development life cycle. Today, everyone from the most junior developers to the most senior DBAs must be knowledgeable about the security tools and technologies available to them. Oracle offers many advanced and add-on security features and options, but RLS is built into the Oracle database engine and is the first tool you should turn to when you are faced with the need to implement access policies. Whether you are a new DBA or a veteran with several years of PL/SQL development under your belt, you will quickly find that a thorough knowledge of RLS will help you to smoothly integrate security features into your database.

Simplify development and maintenance

RLS allows you to centralize your policy logic in a set of packages built around highly structured PL/SQL functions. Even if you could implement your row-level security requirements with views, would you even want to? SQL syntax can be quite convoluted when it comes to complex business requirements. And as your company puts into place new or evolved privacy policies, and as the government puts new laws into effect, you have to figure out how to translate that into SQL syntax for your views. It is far easier to make changes to PL/SQL functions in a small number of packages than to leave it to Oracle to automatically apply your rules to the specified tablesregardless of how they are accessed.

Simplify canned applications

Related to the ease of development is RLS's role in simplifying the adoption of third-party canned applications. Even if it were feasible for you to undertake changing every query in your application, you couldn't do this for canned applications, because you would not have the source code to modify. You would need the assistance of the application vendor. This problem is particularly true for legacy systems: most organizations are afraid to change anything in such systems, even something as simple as an additional predicate. RLS comes to the rescue here because it requires no code changes. You can go beneath the third-party application code, bypassing its logic entirely, and add your own policies to the tables with which that code works.

Control write activity

RLS offers a flexible, quick, and easy way to make tables and views read-only and read/write on the fly, and to make them so based on the credentials of the user. Oracle's native administration commands allow you to define only tablespaces as a whole to be read-only or read/write. You can use RLS to fill this gap and apply the same rules to individual tables.

5.1.2. 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 asks for data from the EMP table, Oracle (via the RLS mechanism) will automatically apply the restriction I want. For this to happen, I have to tell Oracle about my requirements.

First I need to write a function that builds and returns this predicate as a string. Given the simplicity of this requirement, I will use a standalone function; in production applications, you should define your predicate functions and related functionality in packages. Connecting as HR, I create the function authorized_emps shown here.

     CREATE OR REPLACE FUNCTION authorized_emps (        p_schema_name   IN   VARCHAR2,        p_object_name   IN   VARCHAR2     )        RETURN VARCHAR2     IS     BEGIN        RETURN 'SAL <= 1500';     END; 

Notice that the two arguments (schema and object names), are not used inside the function. They are still required by the RLS architecture. Every predicate function, in other words, must pass those two arguments; this topic is explained in more detail later in the chapter.

When the function is executed, it will return my predicate: "SAL <= 1500". Let's confirm that using the following test script:

     DECLARE        l_return_string   VARCHAR2 (2000);     BEGIN        l_return_string := authorized_emps ('X', 'X');        DBMS_OUTPUT.put_line ('Return String = ' || l_return_string);     END; 

The output is:

     Return String = SAL <= 1500 

Now that I have a function that returns the required predicate, I can take the next step: set up a security policy, also known as an RLS policy or, simply, a policy. This policy defines when and how the predicate will be applied to SQL statements. To define row-level security for the table EMP, I'll use the following code:

       1  BEGIN       2     DBMS_RLS.add_policy (object_schema  => 'HR',       3         object_name          => 'EMP',       4         policy_name          => 'EMP_POLICY',       5         function_schema      => 'HR',       6         policy_function      => 'AUTHORIZED_EMPS',       7         statement_types      => 'INSERT, UPDATE, DELETE, SELECT'       8         );       9  END; 

Let's look more carefully at what is going on here: I am adding a policy named EMP_POLICY (line 4) on the table EMP (line 3) owned by the schema HR (line 2). The policy will apply the filter coming out of the function AUTHORIZED_EMPS (line 6) owned by schema HR (line 5) whenever any user performs INSERT, UPDATE, DELETE, or SELECT operations (line 7).

Once I define my policy, I can immediately test it with a query against the EMP table:

     SQL>SELECT * FROM hr.emp;     EMPNO ENAME      JOB         MGR HIREDATE     SAL       COMM DEPTNO     ------ ---------- --------- ----- --------- ------ ---------- ------       7369 SMITH      CLERK      7902 17-DEC-80    800                20       7521 WARD       SALESMAN   7698 22-FEB-81  1,250        500     30       7654 MARTIN     SALESMAN   7698 28-SEP-81  1,250       1400     30       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       7934 MILLER     CLERK      7782 23-JAN-82  1,300                10 

Note how only 7 rows are selected, not all 14 of them. If you look closely, you'll notice that all the rows selected have a SAL value less than or equal to 1,500, which was the predicate function.

Similarly, if users try to delete from or update all the rows in the table, they will only remove those rows made visible by the RLS policy:

     SQL> DELETE hr.emp;     7 rows deleted.     SQL> UPDATE hr.emp SET comm = 100;     7 rows updated. 

And because Oracle applies this filtering at a very low level, users are not aware of the filtering; they don't, in effect, know what they are missingand that is another valuable feature of RLS from the standpoint of security.

Figure 5-1 illustrates the basic flow of data and filtering as implemented by RLS.

Figure 5-1. How row-level-security filters work

When a user operates on a table that is under the control of row-level security, the SQL statement is intercepted and rewritten by the database engine by adding the output of the policy function. As long as the policy function returns a valid predicate clause, it is applied to the user's original statement.

Policies are not database schema objects: in other words, no user owns them. Any user with the EXECUTE privilege on the DBMS_RLS package can create a policy. Similarly, any user with that EXECUTE privilege can drop any policy. Therefore, it's important that you grant authority to run DBMS_RLS very carefully. If someone has granted the EXECUTE privilege on the package to PUBLIC, it should be revoked immediately.

You can write policy functions that are arbitrarily complex to meet virtually any application requirements. All those functions must, however, conform to these rules:

• A policy function must be a schema-level or packaged function, never a procedure.

• It must return a VARCHAR2 value, which will be applied as the predicate.

• It must have exactly two input parameters in the following order.

  1. The schema that owns the table on which the policy has been defined

  2. The object name (table or view) to which the policy is being applied

To see the policies defined on a table, you can check the data dictionary view DBA_POLICIES, which shows the name of the policy, the object on which it is defined (and its owner), the policy function name (and its owner), and much more. See Appendix A for a complete list of the columns in this view.

If you want to drop an existing RLS policy, you can do so using the DROP_POLICY program in the DBMS_RLS package. You will see examples of using this program later in this chapter.

RLS Policies in a Nutshell

• A policy is a set of instructions used to place a table under row-level security. It is not a schema object, and no user owns it.

• Oracle uses the policy to determine when and how to apply a predicate to all SQL statements that reference the table.

• The predicate is created by and returned from the policy function.