Row-Level Security Quick Start

Many examples you see using VPD involve the use of application contexts and/or several data tables with esoteric column names and complicated referential integrity constraints. I find that these elements, while truthful in their representation of many database schemas, tend to confuse and mislead the reader about how the row-level security technology works and precisely what is needed to enable it. Using RLS is easy, and the purpose of this section is to prove this very point.

VPD’s row-level security allows you to restrict access to records based on a security policy implemented in PL/SQL. A security policy, as used here, simply describes the rules governing access to the data rows. This process is done by creating a PL/SQL function that returns a string. The function is then registered against the tables, views, or synonyms you want to protect by using the DBMS_RLS PL/SQL package. When a query is issued against the protected object, Oracle effectively appends the string returned from the function to the original SQL statement, thereby filtering the data records.

Quick Start Example

This example will focus on the process required to enable RLS. The intention is to keep the data and security policy simple so as not to distract from how to enable an RLS solution.

The RLS capability in Oracle requires a PL/SQL function. The function accepts two parameters, as shown next. The database will call this function automatically and transparently. The string value returned from the function (called the predicate) will be effectively added to the original SQL. This results in an elimination of rows and thus provides the row-level security.

The security policy for this example will exclude Department 10 records from queries on SCOTT.EMP. The PL/SQL function to implement this will look as follows:

sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION no_dept10 (   2    p_schema  IN  VARCHAR2,   3    p_object  IN  VARCHAR2)   4    RETURN VARCHAR2   5  AS   6  BEGIN   7    RETURN 'deptno != 10';   8  END;   9  / Function created.

To protect the SCOTT.EMP table, simply associate the preceding PL/SQL function to the table using the DBMS_RLS.ADD_POLICY procedure:

sec_mgr@KNOX10g> BEGIN   2    DBMS_RLS.add_policy   3               (object_schema      => 'SCOTT',   4                object_name        => 'EMP',   5                policy_name        => 'quickstart',   6                policy_function    => 'no_dept10');   7  END;   8  / PL/SQL procedure successfully completed.

That’s it; you are done! To test this policy, log on as a user with access to the SCOTT.EMP table and issue your DML. The following shows all the department numbers available in the table. Department 10 is no longer seen because the RLS policy transparently filters out those records:

scott@KNOX10g> -- Show department numbers. scott@KNOX10g> -- There should be no department 10. scott@KNOX10g> SELECT DISTINCT deptno FROM emp;     DEPTNO ----------         20         30

The important point is that row-level security can be trivial to implement.

Note 

RLS has no requirements or dependencies on the use of application contexts, the user’s identity, or the predicate referencing the table’s columns.

Changing the security implementation is trivial, too. Suppose the security policy is changed so that no records should be returned for the user SYSTEM:

sec_mgr@KNOX10g> -- change policy implementation to sec_mgr@KNOX10g> -- remove all records for the SYSTEM user sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION no_dept10 (   2    p_schema  IN  VARCHAR2,   3    p_object  IN  VARCHAR2)   4    RETURN VARCHAR2   5  AS   6  BEGIN   7    RETURN 'USER != ''SYSTEM''';   8  END;   9  / Function created. sec_mgr@KNOX10g> -- Test by counting records as SCOTT sec_mgr@KNOX10g> -- then by counting records as SYSTEM sec_mgr@KNOX10g> CONN scott/tiger Connected. scott@KNOX10g> SELECT COUNT(*) Total_Records FROM emp; TOTAL_RECORDS -------------            14 scott@KNOX10g> CONN system/manager Connected. system@KNOX10g> SELECT COUNT(*) Total_Records FROM scott.emp; TOTAL_RECORDS -------------             0

Notice that the security policy implemented by the function can change without requiring any re-registration with the DBMS_RLS package.