RLS In-Depth - Oracle

The preceding example can help you get started, but there is much more to RLS than this. The security policies can be much more complex, and the policy functions typically return dramatically different strings based on user authorizations.

There are some similarities between RLS and some of the view examples you read about in Chapter 10. Row-level security is enforced by a PL/SQL function, and the role of the PL/SQL function is the same as it was when used in the view examples. The difference between RLS and views is how the PL/SQL is invoked.

The policy function’s job is to return a string (varchar2) that will serve as a predicate or a where clause to the original query. In effect, the original query is modified, the predicate string is attached, and the query is executed. For example, a simple query select * from EMP might be augmented by an RLS policy function that returns the predicate ename = USER. The effective SQL that is then executed will be select * from EMP where ename = USER. You’ll see a diagram of this process later in Figure 11-1.

The PL/SQL functions are registered to tables, views, or synonyms by invoking the DBMS_ RLS.ADD_POLICY procedure. The DBMS_RLS package is not granted to everyone; administrators will require direct execute privileges on the package. The ADD_POLICY procedure requires, at minimum, the name of the object to which the policy will be applied, a name for the policy, and the name of a PL/SQL function that will implement the security policy.

The policies can be applied to SELECT, INSERT, UPDATE, DELETE, and INDEX statements. The index affects CREATE INDEX and ALTER INDEX DDL commands. Whenever a user directly or indirectly accesses a protected table, view, or synonym, the RLS engine is transparently invoked, the PL/SQL function registered will execute, and the SQL statement will be modified and executed.

RLS is very flexible and very granular. By default, the policy applies to all DML statements. The ADD_POLICY procedure accepts a STATEMENT_TYPES parameter that allows the administrator to specify which DML operations the policy is to apply. This granularity also allows the database to apply separate policies based on the DML type. For example, the database can easily support a policy to allow all records for SELECT statements; an INSERT, UPDATE policy to restrict records to a user’s department on insert and update operations; and a DELETE policy that restricts DELETE operations to only the user’s record.

Multiple policies also can be applied to the same object: the database logically ANDs the policies together. That is, if there is one policy that returns ename = USER and another policy (on the same object for the same DML) that returns sal > 2000, the database will automatically add both policies, effectively generating where ename = USER and sal > 2000.

The security from VPD derives from the fact that the predicates are used to restrict records returned by the original query, regardless of how the query was issued or who issued the query. This record filtering provides consistent row-level security that is guaranteed to work irrespective of the applications interacting with the data. The entire process is transparent to the application originally issuing the query. One of the strongest arguments for VPD is that the security is tightly fixed to the data it protects—it’s consistent, centrally managed, and it can’t be bypassed.

To understand why this is desirable, look at an alternative security model in which the application implements the record filtering. A particular challenge arises when the same data is required by multiple applications. In this case, the security about the data has to be replicated to all the applications. Varying programming languages, COTS applications, and design models often make this an arduous job at best.

The database’s ability to support security for the data at both an object level and within the object (intra-object) are crucial for ensuring consistent and constant security. Programming languages are born and die within a few years. Applications change in functionality, design, and use even more frequently than that. A well-defined database schema will invariably outlive both. Therefore, a proper security model at the database is paramount to ensuring overall data security. By using features like VPD, the database implements the security policies and thus any application using the data will have the security policies automatically applied.

You now have the background to understand why VPD is a good tool, now you will see more examples of how to use it. In the next example, you’ll solve the challenge posed in the “Viewing Problems” section in Chapter 10: a user is allowed to see all records; to insert and update records only within their department; and to delete only their individual record. You could build this with three views, but your application code would have to switch between views based on the type of operation it wishes to perform, and you would have to write Instead-of triggers. VPD will allow you to enforce two policies. Thus, any currently written SQL doesn’t have to be altered as the security policy is created, altered, or deleted.

For this example, the RLS policy will be applied to the PEOPLE table. To enforce the security just described, two policies will be needed: the first will manage the insert and update operations restricting records to the user’s department; the second will manage delete operations to ensure the user can only delete their own records.

Restricting insert and updates to the user’s department requires knowing what department the user belongs to. To make this as efficient as possible, the user’s department number will be stored in an application context. While this is being done for illustrative purposes, application contexts are not required to implement RLS. A lookup table is created to support the population of the context values. The application context setup will involve three steps: creating an application context for the department number, building the namespace manager program to populate the context with the appropriate value, and invoking the namespace manager automatically when the user logs on.

The following code sample for setting up the application context is similar to an example given in Chapter 9. More details about why the code is written as it is are given in that chapter. The code is shown here again as a convenience.

The security manager will set the context values. As such, select privileges are required on the PEOPLE table. The security manager then creates the lookup table.

scott@KNOX10g> -- Recreate the people table to include all rows from EMP
scott@KNOX10g> DROP TABLE people;
Table dropped.
scott@KNOX10g> CREATE TABLE people
  2  AS SELECT ename username, job, sal salary, deptno
  3    FROM emp;
Table created.
scott@KNOX10g> GRANT SELECT ON people TO sec_mgr;
Grant succeeded.
scott@KNOX10g> CONN sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> -- Create table to populate application context values
sec_mgr@KNOX10g> CREATE TABLE lookup_dept
  2  AS SELECT username, deptno FROM scott.people;
Table created.

The namespace manager program will set the context based on the user’s department number as stored in the LOOKUP_DEPT table:

sec_mgr@KNOX10g> -- Create namespace for application context
sec_mgr@KNOX10g> CREATE CONTEXT people_ctx USING sec_mgr.people_ctx_mgr;
Context created.
sec_mgr@KNOX10g> -- Create namespace manager program for modifying context.
sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE people_ctx_mgr
  2  AS
  3    PROCEDURE set_deptno;
  4    PROCEDURE clear_deptno;
  5  END;
  6  /
Package created.
sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE BODY people_ctx_mgr
  2  AS
  3  ---------------------------------------
  4  PROCEDURE set_deptno
  5  AS
  6   l_deptno NUMBER;
  7  BEGIN
  8    SELECT deptno
  9      INTO l_deptno
 10      FROM lookup_dept
 11     WHERE username =
 12           SYS_CONTEXT ('userenv',
 13                        'session_user'); 
 14    DBMS_SESSION.set_context
 15          (namespace    => 'people_ctx',
 16          ATTRIBUTE     => 'deptno',
 17          VALUE         => l_deptno);
 18  END set_deptno;
 19 ---------------------------
 20  PROCEDURE clear_deptno
 21  AS
 22  BEGIN
 23    DBMS_SESSION.clear_context
 24           (namespace   => 'people_ctx',
 25           ATTRIBUTE    => 'deptno');
 26  END clear_deptno;
 27 ----------------------------
 28 END people_ctx_mgr;
 29 /
Package body created.
sec_mgr@KNOX10g> -- do NOT have to grant execute on namespace manager

To populate the context value automatically, a logon trigger will be used:

sec_mgr@KNOX10g> -- do NOT have to grant execute on namespace manager
sec_mgr@KNOX10g> CREATE OR REPLACE TRIGGER set_user_deptno
  2    AFTER LOGON ON DATABASE
  3  BEGIN
  4    sec_mgr.people_ctx_mgr.set_deptno;
  5  EXCEPTION
  6    WHEN NO_DATA_FOUND
  7    THEN
  8      -- If user is not in table,
  9      -- a no_data_found is raised
 10      -- If exception is not handled,
    then users not in table
 11      -- will be unable to log on
 12      NULL;
 13  END;
 14  /
Trigger created.

Test the context by logging in as the SCOTT user:

sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> COL deptno format a8 scott@KNOX10g> SELECT SYS_CONTEXT ('people_ctx',
  2                      'deptno') deptno
  3    FROM DUAL;
DEPTNO
-------
20