Row-Level Security with Virtual Private Database

Oracle’s Virtual Private Database technology provides a logical and elegant method for applying security to the data within database tables. This article explains how VPD can be used to provide row-level security. It was excerpted from chapter 11 of Effective Oracle Database 10g Security by Design, written by David C. Knox (McGraw-Hill/Osborne, 2004; ISBN: 0072231300).

For many years, Oracle’s customers repeatedly asked for a logical and elegant method for applying security policies to the data within database tables. And for many years, Oracle responded by saying, “Use views with functions.” As Chapter 10 illustrates, in many circumstances this is just not a practical solution. Oracle, recognizing their customer’s needs, introduced Virtual Private Database (VPD) technology with Oracle Database 8.1.5.

This chapter illustrates how VPD can be used to provide row-level security. The chapter begins with a “Quick Start” section that introduces you to the ease and power that VPD can provide. This section can also serve as a refresher for you later.

Next, the chapter examines the various aspects of VPD including how to enable the row-level security features as well as providing examples of how it works with different DML statements—select, insert, update, and delete. One of the most valuable sections is “Debugging RLS Policies,” which offers tips and tricks for troubleshooting the VPD implementation. The transparency that works as your ally for security reasons also works against you when things go wrong. The chapter explores common mistakes to avoid as well as ways to help you track down the source of your errors.

Oracle Database 10g introduces a new feature to VPD called Column Sensitive polices, and you will see how to use this. The VPD Performance section suggests how to maintain high-performing security policies and provides examples of the new caching methods introduced in the Oracle Database 10g release.

The Need for Virtual Private Databases

When I first began working for Oracle, I was asked to work on a Department of Defense (DoD) project that was using a special version of Oracle called Trusted Oracle. Trusted Oracle ran on special “trusted” operating systems. I was familiar with Oracle, and I was familiar with UNIX operating systems, but working with Trusted Oracle was really bizarre. A lot of what I had learned about access controls and security was somehow deficient in this world.

The one behavior that I quickly realized was distinctly different was that Trusted Oracle transparently filtered data records. I found out that the DoD security requirements dictated mandatory separation of records based on a user’s authorizations. In this case the users were authorized for access at different sensitivity levels—SECRET, CONFIDENTIAL, and UNCLASSIFIED. The data was intermingled within tables at various sensitivity levels. One user accessing the data would see one set of records, and a different user with different authorizations would see a different set of records.

The interesting part was that the security was implemented so that it was transparent and could not be subverted. The manner in which Trusted Oracle behaved and the requirements from customers in other industries gave Oracle the idea of abstracting the row-level security features from Trusted Oracle into a framework that would support practically any data model and security policy. This was the genesis of the Virtual Private Database technology.

Officially, the phrase “Virtual Private Database (VPD)” refers to the use of row-level security (RLS) and the use of application contexts. (Application contexts were discussed in detail in Chapter 9.) However, the term “VPD” is commonly used when discussing the use of the row-level security features irrespective of implementation.

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.

{mospagebreak title=RLS In-Depth}

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.

Benefits

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.

Setup

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.

Setting the Application Context

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

{mospagebreak title=Creating the Policy Function}

All RLS policy functions are passed two parameters by the database when they are invoked. The first is the name of the schema that owns the object for which the RLS policy is being invoked. The second is the name of the object for which the RLS policy is being invoked. These two parameters are helpful because a single policy function (PL/SQL program) may be applied to multiple objects in multiple schemas. The parameters can then be used to determine specifically which object the policy is being invoked.

A good practice is to create your policy functions with the parameter values defaulted to null. This will allows you to test the function directly:

sec_mgr@KNOX10g> — Create policy function.
sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION dept_only (
 
2    p_schema  IN  VARCHAR2 DEFAULT NULL,
 
3    p_object  IN  VARCHAR2 DEFAULT NULL)
 
4    RETURN VARCHAR2
 
5  AS
 
6  BEGIN
 
7    RETURN ‘deptno = sys_context
   (”people_ctx”,”deptno”)’;
 
8  END;
 
9  /
Function created.

Notice that this policy function is created in the security manager’s schema. This is important because access to the policy function should be guarded.

NOTE

Your users should not have execute privileges on the policy function, nor should they be able to alter or drop the function.

This last requirement generally implies the function is separated from the data schema to which it will be applied. In some cases, you can see the predicate string that will be used by displaying the return value of the function:

sec_mgr@KNOX10g> — Test policy function. sec_mgr@KNOX10g> col predicate format a50
sec_mgr@KNOX10g> SELECT dept_only predicate FROM DUAL;
PREDICATE
——————————————-
deptno = sys_context(‘people_ctx’,’deptno’)

This policy function is quite simple; in fact, it could just as easily have been implemented in a view. You are using VPD instead of views because this policy is only relevant to inserts and updates.

Applying the Insert/Update Policy

The security requirement implemented by the preceding code is supposed to be in effect on all inserts and updates. After running the following statement, the database will call the DEPT_ONLY function in the SEC_MGR schema whenever someone inserts or updates the SCOTT.PEOPLE table:

sec_mgr@KNOX10g> — apply RLS policy to table
sec_mgr@KNOX10g> BEGIN
 
2    DBMS_RLS.add_policy
  3       (object_schema   => ‘SCOTT’,
  4       object_name      => ‘PEOPLE’,
  5       policy_name      => EOPLE_IU’,   
  6       function_schema  => ‘SEC_MGR’,
  7       policy_function  => ‘Dept_Only’,
  8       statement_types  => ‘
INSERT,
   UPDATE
‘,
  9       update_check     => TRUE);
 10  END;
 11  /
PL/SQL procedure successfully completed.

The combination of object schema, object name, and policy name has to be unique. A good practice for the policy name is to choose something that describes the intended use of the policy. PEOPLE_IU is my way of indicating a policy on the PEOPLE table for insert and update operations. Likewise, choosing a function name that is descriptive about what the function results are is also good practice. The DEPT_ONLY implies the function restricts records at the department level. As a result of executing the ADD_POLICY procedure, the following SQL

update people
  set ename = ‘<NEW_VALUE>’

will be effectively transformed into

update people
 
set ename = ‘<NEW_VALUE>’
 
where deptno = sys_context(‘people_ctx’,’deptno’)

Figure 11-1 depicts how this occurs.

FIGURE 11-1.  Queries are modified transparently by 
                           the RLS policy.

Testing VPD Protected Insert and Updates

A quick test validates that the policy is working. For the test, simply update a record within your department (Department 20 for SCOTT), and update a record outside your department. The following query returns a record from each so you can properly issue your update statements:

sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> SELECT username, deptno
 
2    FROM people
  3   WHERE username < ‘C’;
USERNAME      DEPTNO
———- ———-
ALLEN             30
BLAKE             30
ADAMS             20

Testing the updates should show that updates to records in Department 20 will be successful. Updates to records outside Department 20 should not succeed:

scott@KNOX10g> — Update to department 20 user
scott@KNOX10g> — This should succeed. scott@KNOX10g> UPDATE people
 
2    SET username = ‘GRIZZLY’
 
3  WHERE username = ‘ADAMS’;
1 row updated.
scott@KNOX10g> — Update to department 30 user
scott@KNOX10g> — This should not update anything.
scott@KNOX10g> UPDATE people
 
2     SET username = ‘BOZO’
  3  
WHERE username = ‘BLAKE’;
0 rows updated.

Note that zero errors are thrown for the second update. The modified SQL simply excludes all possible records. The update statement was effectively augmented and resolved to the following:

UPDATE people
   SET ename = ‘Bozo’
 WHERE ename = ‘BLAKE’
   AND deptno = SYS_CONTEXT (‘people_ctx’, ‘deptno’)

The SYS_CONTEXT function will resolve to the number 20 for the user SCOTT. Because there is no BLAKE record with a deptno = 20, zero records are updated.

To test the inserts, try to insert a new record for Department 20, then insert a record outside Department 20:

scott@KNOX10g> — This insert should work as deptno = 20
scott@KNOX10g> INSERT INTO people
 
2          (username, job, salary, deptno)
  3    VALUES (‘KNOX’, ‘Clerk’, ‘3000’, 20);
1 row created.
scott@KNOX10g> — This insert should not work
scott@KNOX10g> INSERT INTO people
 
2          (username, job, salary, deptno)
  3   VALUES (‘ELLISON’, ‘CEO’,’90000′, 30); INSERT INTO people
           
*
ERROR at line 1:
ORA-28115: policy with check option violation

Note the insert operation fails with an error. This operation fails because you set the UPDATE_CHECK=TRUE in the ADD_POLICY procedure call. The default value for UPDATE_ CHECK is FALSE. If you had not specified TRUE, the insert would have succeeded. This behavior is consistent with that of a view with a check option.

The Delete RLS Policy Example

The delete policy says that the user can only delete their record. This function can be implemented as follows:

sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION user_only (
  2    p_schema  IN  VARCHAR2 DEFAULT NULL,
 
3    p_object  IN  VARCHAR2 DEFAULT NULL)
  4    RETURN VARCHAR2
  5  AS
  6  BEGIN
  7    RETURN ‘username = sys_context
   (”userenv”,”session_user”)’;
  8  END;
  9  /
Function created.

To apply the delete policy, specify DELETE statements in the ADD_POLICY procedure and provide the USER_ONLY function for the POLICY_FUNCTION:

sec_mgr@KNOX10g> — apply delete policy to table
sec_mgr@KNOX10g> BEGIN
 
2    DBMS_RLS.add_policy
  3         (object_schema  => ‘SCOTT’,
  4         object_name     => ‘PEOPLE’,
  5         policy_name     => People_Del’, 
  6         function_schema => ‘SEC_MGR’,
  7         policy_function => ‘user_only’,
  8         statement_types =>
‘DELETE’);
  9  END;
 10  /
PL/SQL procedure successfully completed.

Testing the delete policy, you’ll notice that a delete statement that attempts to delete all records actually results in only one actual deletion—the user’s very own record:

scott@KNOX10g> DELETE FROM people;
1 row deleted.
scott@KNOX10g> SELECT * FROM people
  2   WHERE username = ‘SCOTT’;
no rows selected

Again, the database has transparently augmented the SQL statement. The policy function restricts delete operations to the user’s record. Therefore, the most users can ever delete is their record.

{mospagebreak title=The RLS Layer of Security}

If you want to prevent users from accessing all records (including their own), first use the object privileges to enforce this to ensure the user doesn’t have the privileges on the table.

To strengthen the security, you can also use RLS. RLS provides an additional layer of security and will even prevent someone with the DBA role or an ANY system privilege—SELECT ANY TABLE—from accessing your protected table(s). Note this doesn’t imply the table is protected from a DBA with malicious intentions. The DBA can alter the policy function, drop the table, disable the RLS policy, and so on. The shear fact of being a DBA doesn’t override the RLS capability. In the previous example, a DBA who can read records from your table still can’t delete any records because the delete policy restricts access to the records matching USERNAME with the schema name:

scott@KNOX10g> CONN system/manager Connected.
system@KNOX10g> — user has SELECT ANY TABLE so he can see all records
system@KNOX10g> SELECT COUNT(*) FROM scott.people;
  COUNT(*)
———-
        14
system@KNOX10g> — user cannot delete records as RLS prevents this
system@KNOX10g> DELETE FROM scott.people;
0 rows deleted.

Returning Zero Records

The most effective way to prevent records from being accessed within an RLS policy function is to return a string that consists of something that can never happen. An easy example is the string “1=0”. Be aware—returning a null or a zero length string has the opposite effect, and all rows are returned.

CAUTION

Returning null from the policy function allows all records to be accessed.

You might find it useful to create a function that eliminates all records. You can then use it anytime you want to quickly lock down a table:

sec_mgr@KNOX10g> — Create a function to be used with RLS
sec_mgr@KNOX10g> — that will always eliminate all records.
sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION no_records (
 
2    p_schema  IN  VARCHAR2 DEFAULT NULL,
 
3    p_object  IN  VARCHAR2 DEFAULT NULL)
 
4    RETURN VARCHAR2
 
5  AS
 
6  BEGIN
 
7    RETURN ‘1=0’;
 
8  END;
 
9  /
Function created.

There are several ways to create a READ ONLY table or view; here you can see another. Adding the NO_RECORDS function to an RLS policy for inserts, updates, and deletes effectively makes the table READ ONLY.

scott@KNOX10g> CREATE TABLE people_ro AS SELECT * FROM emp;
Table created.
scott@KNOX10g> conn sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> BEGIN
  2    DBMS_RLS.add_policy
  3        (object_schema   =>  ‘SCOTT’,
  4         object_name     =>  ‘PEOPLE_RO’,
  5         policy_name  => ‘PEOPLE_RO_IUD’,
  6         function_schema =>  ‘SEC_MGR’,
  7         policy_function => ‘No_Records’,
  8         statement_types => ‘
INSERT, 
   UPDATE,DELETE
‘,
  9         update_check    => TRUE);
 10  END;
 11  /
PL/SQL procedure successfully completed.

As indicated by the tests here, the RLS policy helps to ensure the table maintains its READ ONLY status:

sec_mgr@KNOX10G> scott@KNOX10g> — User can read all records
scott@KNOX10g> SELECT COUNT (*) FROM people_ro;
  COUNT(*)
———-
        14
scott@KNOX10g> — Cannot update any records scott@KNOX10g> UPDATE people_ro
  2     SET ename = NULL;
0 rows updated.
scott@KNOX10g> — Cannot delete records
scott@KNOX10g> DELETE FROM people_ro;
0 rows deleted.
scott@KNOX10g> — Cannot insert new records scott@KNOX10g> INSERT INTO people_ro (ename) VALUES (‘KNOX’);
INSERT INTO people_ro (ename) VALUES (‘KNOX’)
           
*
ERROR at line 1:
ORA-28115: policy with check option violation

RLS Exemption

While the RLS provides wonderful security, it can be problematic when doing database administration tasks such as performing data backups. As you have seen, even the DBAs and the data owner cannot bypass the RLS policy. If you perform an export as the data owner or another administrator while an RLS policy was enabled, you may very well end up with a dataless backup file.

For this reason (and a few others), there is an EXEMPT ACCESS POLICY privilege. This privilege allows the grantee to be exempted from all RLS functions. An administrator who has to perform data backups can use this privilege to ensure backup ability for all the data in the tables. The following example illustrates how this privilege is granted and the affect it has on enabled RLS policies:

system@KNOX10g> CONN system/manager Connected.
system@KNOX10g> — Show system is affected by RLS policy.
system@KNOX10g> — No records should be deleted.
system@KNOX10g> DELETE FROM scott.people_ro;
0 rows deleted.
system@KNOX10g> — grant privilege to bypass RLS policies
system@KNOX10g> GRANT EXEMPT ACCESS POLICY TO SYSTEM;
Grant succeeded.
system@KNOX10g> — Show system is no longer affected by RLS policy.
system@KNOX10g> — All records should be deleted.
system@KNOX10g> DELETE FROM scott.people_ro;
14 rows deleted.
system@KNOX10g> ROLLBACK ;

EXEMPT ACCESS POLICY is a very powerful privilege. The privilege isn’t specific to a schema or policy; it applies to all schemas and all policies. Care should be given to ensure that the privilege is well guarded. By default, users with SYSDBA privileges are exempt from RLS policies. You can determine who has been granted the EXEMPT ACCESS POLICY privilege by checking system privileges, as shown here:

sec_mgr@KNOX10g> — Show users that are exempt from RLS policies
sec_mgr@KNOX10g> SELECT grantee
  2    FROM dba_sys_privs
    WHERE PRIVILEGE = ‘EXEMPT ACCESS 
   POLICY’;
GRANTEE
———————————-
SYSTEM

NOTE

The EXEMPT ACCESS POLICY system privilege allows privileged users to bypass RLS  policies. This may be necessary for database backup and recovery, but it can also be a security risk. Guard this privilege tightly.

Audit Exempt Access Policy

You can’t prevent privileged users from abusing their privileges, you can only catch them doing it. Auditing is an effective way to ensure the RLS exemption privilege is not being abused. The following shows how to enable auditing for this privilege. Once enabled, you should test to ensure that everything is being audited as you think. For this to occur, the system privilege has to be exercised. This too is shown:

sec_mgr@KNOX10g> — Audit the exempt policy system privilege
sec_mgr@KNOX10g> AUDIT EXEMPT ACCESS POLICY BY ACCESS;
Audit succeeded.
sec_mgr@KNOX10g> — Test audit by exercising the system privilege
sec_mgr@KNOX10g> CONN system/manager Connected.
system@KNOX10g> DELETE FROM scott.people_ro;
14 rows deleted.
system@KNOX10g> — Rollback will not erase the audit record
system@KNOX10g> ROLLBACK ;
Rollback complete.
system@KNOX10g> CONN sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> — show audited operation
sec_mgr@KNOX10g> BEGIN
  2    FOR rec IN (SELECT *
  3                  FROM dba_audit_trail)
  4    LOOP
  5      DBMS_OUTPUT.put_line (‘————
    ————-‘);
  6      DBMS_OUTPUT.put_line (‘Who: ‘ ||
    rec.username);
  7      DBMS_OUTPUT.put_line (  ‘What: ‘
  8                  || rec.action_name
  9                  || ‘ on ‘
 
10                  || rec.owner
 11                  || ‘.’
 12                  || rec.obj_name);
 13      DBMS_OUTPUT.put_line ( ‘When: ‘
 14                  || TO_CHAR
 15                     (rec.TIMESTAMP,
 16                    ‘MM/DD HH24:MI’));
 
17      DBMS_OUTPUT.put_line (‘How: “‘ ||
   rec.sql_text || ‘”‘);
 18      DBMS_OUTPUT.put_line (‘Using: ‘ ||
   rec.priv_used);
 19    END LOOP;
 20  END;
 21  /
——————–
Who:   SYSTEM
What:  DELETE on SCOTT.PEOPLE_RO
When:  04/04 14:22
How:   “DELETE FROM scott.people_ro”
Using: DELETE ANY TABLE
——————–
Who:   SYSTEM
What:  DELETE on SCOTT.PEOPLE_RO
When:  04/04 14:22
How:   “DELETE FROM scott.people_ro”
Using: EXEMPT ACCESS POLICY
PL/SQL procedure successfully completed.

The audit trail shows two records because the SYSTEM user exercised two system privileges when the delete statement was issued. The first privilege, DELETE ANY TABLE, allowed access to the table. The second privilege, EXEMPT ACCESS POLICY, allowed access within the table.

{mospagebreak title=Debugging RLS Policies}

When an RLS policy fails, the users can no longer access the protected objects. I have found that the chances of this happening are in direct proportion to the complexity of the policy function. When this does happen, having a sound method for troubleshooting the problem is critical to minimizing the down time associated with the error.

There are generally two reasons for a policy error. First, the policy function is invalid and will not recompile or execute. For example, an error will occur if the policy function queries a table that no longer exists. A policy error will also occur if the policy function doesn’t exist. This is usually because the function has been dropped or the function has been incorrectly registered in the ADD_POLICY procedure.

The second reason for policy error occurs when the policy function returns a string that, when added to the original SQL, produces an invalid SQL statement. There are many possible reasons why the function can fail. The first step to debugging is to ensure that the function is working.

Broken Policy Functions

Errors caused by an invalid policy function can easily make the VPD transparency disappear. To show this, the following example creates a simple table with a simple policy function. The policy function is dependent on the table.

scott@KNOX10g> — Create a dependency. This table will
scott@KNOX10g> — be called by the RLS policy function.
scott@KNOX10g> CREATE TABLE t AS SELECT * FROM DUAL;
Table created.
scott@KNOX10g> — Create policy function. Function
scott@KNOX10g> — is dependent on table T. scott@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (
 
2    p_schema  IN  VARCHAR2 DEFAULT NULL,
  3    p_object  IN  VARCHAR2 DEFAULT NULL)
  4    RETURN VARCHAR2
  5  AS
  6   l_total_recs  NUMBER;
  7  BEGIN
  8   SELECT COUNT (*)
  9     INTO l_total_recs
 
10     FROM t;
 11   RETURN ‘1 <= ‘ || l_total_recs;
 12 END;
 13 /
Function created.

The point here is to create and then break a dependency. The policy function is dependent on the table T. Add the policy to your table and check to see that no errors occur on access:

scott@KNOX10g> CONN sec_mgr/oracle10g
Connected.
sec_mgr@KNOX10g> — Add RLS policy to EMP table;
sec_mgr@KNOX10g> BEGIN
 
2    DBMS_RLS.add_policy
  3         (object_schema     => ‘SCOTT’,
  4          object_name       => ‘EMP’,
  5          policy_name       => ‘debug’,
  6          function_schema   => ‘SCOTT’,
  7          policy_function   =>
   ‘pred_function’);
  8  END;
  9  /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> — Everything initially works fine
scott@KNOX10g> SELECT COUNT(*) FROM emp;
  COUNT(*)
——–
     
14

Dropping the table T will invalidate the policy function. The first indication that something is wrong may come when you try to query the EMP table:

scott@KNOX10g> — This drop breaks the policy function
scott@KNOX10g> DROP TABLE t;
Table dropped.
scott@KNOX10g> — Policy function is invalid and will not recompile;
scott@KNOX10g> SELECT COUNT(*) FROM emp; SELECT COUNT(*) FROM emp
                     *
RROR at line 1:
ORA-28110: policy function or package SCOTT.PRED_FUNCTION has error

Recovering from this is very easy with Oracle Database 10g. You can simply use the Flashback Drop to restore the table T. Once this is done, access to the EMP table is also restored because the policy function will be able to successfully execute:

scott@KNOX10g> — Recover table scott@KNOX10g> FLASHBACK TABLE t TO BEFORE DROP;
Flashback complete.
scott@KNOX10g> SELECT COUNT(*) FROM emp;
  COUNT(*)
———-
        14

Handling Policy Function Exceptions

When the query on EMP was executed after the T table was dropped and before it was recovered, the database threw an error indicating precisely why the query failed. This is very helpful, but it may not be desirable because two potentially sensitive things were revealed. First, it indicated that there is an RLS policy on the table; second, it gave the name of the policy function guarding the table.

You may want to consider suppressing the policy function exceptions to prevent this information from being displayed to the users. The best approach to this requires the use of dynamic SQL that hides the function’s database object dependencies. The function still has to return a value. Returning null will allow the user access to all the records. Failing secure means that you should return zero records if an exception is thrown. This example shows how to fail secure:

scott@KNOX10g> — User dynamic SQL and exception handling
scott@KNOX10g> — to mask policy function errors
scott@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (
 
2   p_schema   IN   VARCHAR2 DEFAULT NULL,  
  3   p_object   IN VARCHAR2 DEFAULT NULL)
  4   RETURN VARCHAR2
 
5  AS
  6   l_total_recs NUMBER;
  7  BEGIN
  8    — Dynamic SQL hides the dependency 
    on table T
  9    EXECUTE IMMEDIATE ‘SELECT COUNT (*) 
    FROM t’
 10                INTO l_total_recs;
 11    RETURN ‘1 <= ‘ || l_total_recs;
 12  EXCEPTION
 13    WHEN OTHERS
 14    THEN
 15      — Fail Secure: remove all rows
 16      RETURN ‘1=0’;
 17  END;
 18  /
Function created.
scott@KNOX10g> SELECT COUNT(*) FROM emp;
 
COUNT(*)
———-
       
14
scott@KNOX10g> — This drop breaks the policy function
scott@KNOX10g> DROP TABLE t;
Table dropped.
scott@KNOX10g> — Policy fails secure. No records are displayed or
scott@KNOX10g> — exception messages given to user.
scott@KNOX10g> SELECT COUNT(*) FROM emp;
 
COUNT(*)
———-
         0

The downside to this approach is that it will be more difficult to debug the policy yourself. The solution to this is to comment out or remove the exception handling code, but you should only do this while debugging.

{mospagebreak title=Invalid SQL}

A second possible reason a policy may fail is because the SQL returned by the policy function creates an invalid SQL statement. Recall the policy’s return value is added to the original SQL. The database will try to parse and execute this final SQL statement. If the SQL is invalid, the policy will throw an error.

Unfortunately, the error message does not indicate how the SQL is malformed. (It’s not even clear what the problem is.) For complex policies, or objects that have multiple policies, this can create some frustration.

For this example, the policy function has been modified to support a policy that restricts DML to just the user’s record. Unfortunately, the SQL column NAME should really be ENAME. Because the return value is a string, this error does not manifest itself until runtime:

scott@KNOX10g> — Create policy function that returns
scott@KNOX10g> — records just for the user. scott@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (
 
2    p_schema  IN  VARCHAR2 DEFAULT NULL,
  3    p_object  IN  VARCHAR2 DEFAULT NULL)
  4    RETURN VARCHAR2
  5  AS
  6  BEGIN
  7    — Restricting records by mapping
   ENAME
  8    — to authenticated database user’s 
   name  
  9    RETURN ‘
name = user’;
 10  END;
 11  /
Function created.
scott@KNOX10g> SELECT COUNT(*) FROM emp; SELECT COUNT(*) FROM emp
                     *
ERROR at line 1:
ORA-28113: policy predicate has error

An ORA-28113 is the indication that the SQL is bad. It’s impossible for the database to know why this is true. Your policy function could have derived the predicate string in an infinite number of ways. The only thing you know is that the resulting SQL is invalid. To debug it, first try to inspect the value returned by the policy function:

scott@KNOX10g> SELECT pred_function “Policy Predicate” FROM DUAL;
Policy Predicate
——————-
name = user

Note you can query the DBA_POLICIES view (as shown later in the “Viewing the Original SQL and Predicate” section) to see the PREDICATE but only if the policy doesn’t throw an error.

At this point, your best alternative may be to access a trace file. To do this easily, create a simple view (based on another Tom Kyte contribution from asktom.oracle.com) that can determine the trace filename the database will be using:

sec_mgr@KNOX10g> — Create view to display session’s current trace file
sec_mgr@KNOX10g> CREATE OR REPLACE VIEW get_trace_filename
  2  AS
  3    SELECT    c.VALUE
  4           || decode(instr
   (c.value,”),0,’/’,”)
  5           || INSTANCE
 
6           || ‘_ora_’
  7           || LTRIM (TO_CHAR (a.spid,
   ‘fm99999’))
  8           || ‘.trc’ filename
  9      FROM v$process a,
 10           v$session b,
 11           v$parameter c,
 12           v$thread c
 13     WHERE a.addr = b.paddr
 14       AND b.audsid = USERENV
   (‘sessionid’)
 15       AND c.NAME = ‘user_dump_dest’;
View created.
sec_mgr@KNOX10g> GRANT SELECT ON get_trace_filename TO PUBLIC;
Grant succeeded.
sec_mgr@KNOX10g> CREATE PUBLIC SYNONYM get_trace_filename
  2                 FOR get_trace_filename;
Synonym created.

Connect as a user and reproduce the query that caused the policy error. This is done here as SCOTT:

scott@KNOX10g> — Enable tracing scott@KNOX10g> ALTER SESSION SET EVENTS
  2    ‘10730 trace name context forever, level 12’;
Session altered.
scott@KNOX10g> — Reproduce error. scott@KNOX10g> — Issue query; note only selecting ENAME
scott@KNOX10g> SELECT ename FROM emp;
SELECT ename FROM emp
                     
*
ERROR at line 1:
ORA-28113: policy predicate has error
scott@KNOX10g> — view trace file scott@KNOX10g> COL filename format a50 scott@KNOX10g> SELECT * FROM get_trace_filename;
FILENAME
——————————————–
C:ORACLEADMINKNOX10GUDUMPknox10g_ora_3740.trc
scott@KNOX10g> — fix the policy function scott@KNOX10g> — Create policy function that returns
scott@KNOX10g> — records just for the user. scott@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (
 
2    p_schema  IN  VARCHAR2 DEFAULT NULL,  
  3    p_object  IN  VARCHAR2 DEFAULT  NULL)   
  4    RETURN VARCHAR2
  5  AS
  6  BEGIN
  7   — Restricting records by mapping 
   ENAME
  8   — to authenticated database user’s
   name
  9   RETURN ‘
ename = user‘;
 10  END;
 11  /
Function created.
scott@KNOX10g> — Re-query to verify policy works
scott@KNOX10g> SELECT ename FROM emp;
ENAME
———
SCOTT

Viewing the contents of the trace file with the name returned from GET_TRACE_FILENAME provides helpful information. The relevant information from the trace file just generated from the previous session is displayed here:

——————————————–
Logon user     : SCOTT
Table/View     : SCOTT.EMP
Policy name    : DEBUG
Policy function: SCOTT.PRED_FUNCTION
RLS view :
SELECT “EMPNO”,”ENAME”,”JOB”,”MGR”,”HIREDATE”,”SAL”,
“COMM”,”DEPTNO” FROM
SCOTT”.”EMP” “EMP” WHERE (name = user)
*** 2004-04-05 13:32:02.874
——————————————–
Error information for ORA-28113:
Logon user     : SCOTT
Table/View     : SCOTT.EMP
Policy name    : DEBUG
Policy function: SCOTT.PRED_FUNCTION
RLS predicate:
name = user
ORA-00904: “NAME”: invalid identifier
—————————————
Logon user     : SCOTT
Table/View     : SCOTT.EMP
Policy name    : DEBUG
Policy function: SCOTT.PRED_FUNCTION
RLS view :
SELECT “EMPNO”,”ENAME”,”JOB”,”MGR”,”HIREDATE”,”SAL”,
“COMM”,”DEPTNO” FROM
SCOTT”.”EMP” “EMP” WHERE (ename = user)

 

This file is very useful, especially the section that displays information on the ORA-28113 error. The RLS predicate record (bolded) pinpoints the erroneous SQL.

Be careful, there is a very misleading fact printed, too.

CAUTION

The RLS view is not the actual SQL the database executes.

Many incorrectly believe it is. This should be obvious because the original SQL statement asked only for the ENAME column; the RLS view would have you believe all columns were selected.

Viewing the Original SQL and Predicate

Another view you may find helpful in inspecting your VPD implementations is the V$VPD_ POLICY view. This will tell you what policies have been successfully applied to your SQL statements. The PREDICATE column will actually show the SQL string that was appended to the query. You can join this record with V$SQL, which has the original SQL statement as follows:

sec_mgr@KNOX10g> — Nothing in pool – a fresh database
sec_mgr@KNOX10g> SELECT * FROM v$vpd_policy;
no rows selected
sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> — this will seed the v$vpd_policy view
scott@KNOX10g> SELECT COUNT (*) FROM emp;
COUNT(*)
——–
      
1
scott@KNOX10g> CONN sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> — show VPD Policy results sec_mgr@KNOX10g> COL object_owner format a12 sec_mgr@KNOX10g> COL object_name format a12 sec_mgr@KNOX10g> COL policy format a6 sec_mgr@KNOX10g> COL sql_fulltext format a26 sec_mgr@KNOX10g> COL predicate format a12 sec_mgr@KNOX10g> SELECT object_owner,
 
2         object_name,
  3         policy,
  4         sql_fulltext,
  5         predicate
  6    FROM v$vpd_policy p, v$sql s
  7   WHERE p.sql_id = s.sql_id
  8    AND predicate IS NOT NULL;

OBJECT_OWNER OBJECT_NAME POLICY SQL_FULLTEXT PREDICATE

———— ———— —— ————————-

SCOTT EMP DEBUG SELECT COUNT (*) FROM emp ename = user


This is a good way of inspecting the policies, but there is one important caveat: no records are recorded if the policy throws an error. For policy errors, you will have to use tracing, and even then the real SQL is not given.

{mospagebreak title=Null Application Context Values and Recursive Lookups}

There are two areas that consistently trap newcomers to VPD. The problem occurs when an RLS policy function references an application context that is null. The reason the application context is null is because the namespace manager is trying to set the context on the table to which the RLS policy is being applied.

This example begins by illustrating the working context. Recall the user’s department number is set by a logon trigger. The trigger calls the namespace manager, which sets the value by querying the LOOKUP_DEPT table:

sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> — Context value is set on logon
scott@KNOX10g> COL deptno format a6 scott@KNOX10g> SELECT SYS_CONTEXT (‘people_ctx’,
 
2                      ‘deptno’) deptno
  3   FROM DUAL;
DEPTNO
——
20
scott@KNOX10g> CONN sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> — Current view on table sec_mgr@KNOX10g> SELECT * FROM lookup_dept
 
2   WHERE username = ‘SCOTT’;
USERNAME      DEPTNO
———- ———–
SCOTT             20

Normally, the lookup table would not exist. It was created specifically to bypass this problem. Nevertheless, the mistake comes next when an RLS policy is added to the LOOKUP_DEPT table. To help with this example, an update policy will be added to the EMP table using the same policy function. Most often the context will be set from the same table the RLS policy will be enforced from which, as you will see, can’t be done:

sec_mgr@KNOX10g> — Add Dept level policy to lookup table.
sec_mgr@KNOX10g> — Policy references people_ctx context.
sec_mgr@KNOX10g> BEGIN
 
2    DBMS_RLS.add_policy
  3          (object_schema   => SEC_MGR’,
  4           object_name     => ‘lookup_
   dept’,   
  5           policy_name     => ‘lookup_
   dept_sel’,
  6           policy_function => 
   ‘dept_only’,
  7           statement_types => ‘SELECT’);
 
8  END;
 
9  /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> — Add Dept level policy to EMP table.
sec_mgr@KNOX10g> — Policy references people_ctx context.
sec_mgr@KNOX10g> BEGIN
  2    DBMS_RLS.add_policy
  3         (object_schema   => ‘SCOTT’,
  4          object_name     => ‘EMP’,
  5          policy_name     => ‘EMP_UPD’,
  6          policy_function => ‘dept_
   only’,  
  7          statement_types => ‘UPDATE’);
  8  END;
  9  /
PL/SQL procedure successfully completed.

Querying from the lookup table shows the expected results. If you are the security administrator, you may feel nothing is wrong:

sec_mgr@KNOX10g> — rows are no longer visible
sec_mgr@KNOX10g> — since SEC_MGR has not context
sec_mgr@KNOX10g> SELECT * FROM lookup_dept;
no rows selected

Connecting as the SCOTT user, you can see the application context wasn’t set. The ramifications of this are that any RLS policies that are based on this context will not behave properly:

sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> — Context no longer exists scott@KNOX10g> COL deptno format A6 scott@KNOX10g> SELECT SYS_CONTEXT (‘people_ctx’,
 
2                      ‘deptno’) deptno
  3    FROM DUAL;
DEPTNO
——
scott@KNOX10g> — Any RLS policies that used the context value will not
cott@KNOX10g> — behave correctly. scott@KNOX10g> — This update would normally succeed for all dept 20 records scott@KNOX10g> UPDATE emp
 
2     SET ename = ename;
0 rows updated.

Recall the policy function being added restricts the user’s records to the department in which they work. The department number is stored in the context that is populated from the same table being restricted by the RLS policy. This recursive condition prevents the application context from being populated.

The solution to this is to create a different object to be used for populating the application context. The object can be a table or a view. A view is the best because there is no data synchronization that needs to occur. If you create a view, the application context lookup should occur on the table, and the RLS policy should be applied to the view. If you did this the other way, it wouldn’t work because the RLS on the base table would still be in effect when the table was accessed by way of the view.

Partitioned Fine-Grained Access Control

Oracle introduced partitioned fine-grained access control in Oracle9Database, which allows you to group together multiple policies and then enable and disable them easily. This is a useful concept when multiple policies exist on the same objects but have conflicting return values.

Recall that Oracle logically ANDs all RLS policies. That is, if there are two policies, A and B, for the same object, then the resulting SQL will be the output of A and B. If policy A returns “deptno = 20” and policy B returns “deptno = 10”, then the resulting query will be “deptno = 20 and deptno = 10”. Because the deptno is either 10 or 20, this query results in zero rows.

Partitioned fine-grained access control allows you to define which RLS policies you want to enable for the user session. This is done by setting the policy name into an application context. The database will then reference this policy name by looking at the value stored in the user’s application context and applying that RLS policy.

Normally, I would include an example that illustrates some nuance of the technology. Fortunately, the Oracle Database Security Guide 10g has an excellent example of partitioned fine-grained access control. Including a different example here would be redundant.

Column Sensitive VPD

Oracle Database 10g offers a new feature to VPD called Column Sensitive VPD. The objective of this feature is to invoke the security policy when a specific column is referenced.

Let’s augment the security on the PEOPLE view to allow users to see only their own salaries. You do still want the user to see other columns of other user records. You can use the same PL/SQL function, USER_ONLY, for this new policy. The added parameter is SEC_RELEVANT_COLS.

sec_mgr@KNOX10g> BEGIN
 
2    DBMS_RLS.add_policy
  3     (object_schema => ‘SCOTT’,
  4      object_name   => ‘PEOPLE’,
  5      policy_name   => ‘people_sel_sal’,
  6      function_schema => ‘SEC_MGR’,
  7      policy_function => ‘user_only’,
  8      statement_types => ‘SELECT’,
  9      sec_relevant_cols => ‘SALARY’);
 10  END;
 11  /
PL/SQL procedure successfully completed.

Testing this code, you see a different result than you did with our view. When the SALARY column is queried, the VPD policy is invoked, and only one record is returned; when the SALARY column is not queried, then the policy isn’t invoked and all records are returned:

sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> — User can see all records when SALARY column is not
scott@KNOX10g> — queried. Show first five records only.
scott@KNOX10g> SELECT username FROM people
  2   
WHERE ROWNUM <= 5;
USERNAME
———-
SMITH
ALLEN
WARD
JONES
MARTIN
scott@KNOX10g> — Adding the salary column causes
scott@KNOX10g> — the RLS policy to activate scott@KNOX10g> SELECT username, salary FROM people;
USERNAME       SALARY
——— ———–
SCOTT            3000

To make VPD behave like the column masking view example you saw in Chapter 10 you can use another new parameter to the ADD_POLICY procedure: SEC_RELEVANT_COLS_OPT. Oracle’s implementation is consistent with what was done in the view. That is, the values for the sensitive columns are null values. All the other columns and rows are returned:

sec_mgr@KNOX10g> BEGIN
 
2    — Remove current policy
  3    DBMS_RLS.drop_policy
  4          (object_schema   => ‘SCOTT’,
  5           object_name     => ‘PEOPLE’,
  6           policy_name     =>
   ‘people_sel_sal’);
 
7    — Add policy again but now add the
   SEC_RELEVANT_COLS_OPT
  8    DBMS_RLS.add_policy
  9         (object_schema    => ‘SCOTT’,
 
10          object_name      => ‘PEOPLE’, 
 11          policy_name      =>
   ‘people_sel_sal’,
 12          function_schema  => ‘SEC_MGR’,
 13          policy_function  =>
   ‘user_only’,
 14          statement_types  => ‘SELECT’, 
 15          sec_relevant_cols => ‘SALARY’,
 16          sec_relevant_cols_opt =>
   DBMS_RLS.all_rows
);
 17  END;
 18  /
PL/SQL procedure successfully completed.

The final query then results with null salary values for all but the invoking users:

scott@KNOX10g> — all_rows added as sec_relevant_cols_opt
scott@KNOX10g> — Just showing dept20 records for brevity.
scott@KNOX10g> SELECT username, salary FROM people
 
WHERE deptno = 20;
USERNAME     SALARY
——— ———
SMITH
JONES
SCOTT          3000
ADAMS
FORD

NOTE

The SEC_RELEVANT_COLS_OPT is applicable only to select statements.

The column sensitive option is ideal for privacy requirements. The column may be anything sensitive that you want to conceal—salary, a credit card number, patient diagnosis, financial status, and so on. This option allows you to store the sensitive information and the nonsensitive information together with the assurance that anytime someone requests the sensitive data, RLS will remove or mask the values.

{mospagebreak title=VPD Performance}

A clear and obvious concern when implementing any type of security is performance. While VPD provides the best security protection—it’s consistent and constant—it is not magical. That is, the modified query will be executed and overall performance will be based on this final query string.

One of the easiest ways to ensure high performing VPD is to create indexes on the predicate values. If your policy function returns “username = USER”, then an index on the USERNAME column will increase VPD execution.

Bind Variables

Bind variables help to ensure high performance by allowing the database to save valuable computing resources when queries differ only by variable values.

The first area to investigate is the performance of the returned predicate. Because the actual SQL to be executed includes not only the original SQL, but also the SQL returned from the predicate, you have to ensure that this SQL string performs well.

Bind variables are the staple of performance in an Oracle database. Bind variables allow the database to reuse SQL between database sessions; that is, the database can share a single parsed plan for multiple open cursors. The performance is achieved because the database doesn’t have to reparse the SQL.

In some of the previous examples, the returned strings consisted of the SYS_CONTEXT function. This is critical because the policy function could have resolved the SYS_CONTEXT function and returned that string. The following will return “deptno = 20” for the SCOTT user:

CREATE OR REPLACE FUNCTION dept_only (
  p_schema IN VARCHAR2 DEFAULT NULL,
  p_object IN VARCHAR2 DEFAULT NULL)
 
RETURN VARCHAR2
AS
BEGIN
 
— Return predicate with value resolved.
  RETURN ‘deptno = ‘ || sys_context(‘people_ctx’,’deptno’)
;
END; /

This implementation is functionally equivalent to the one implemented before, but it is not equivalent from a performance perspective. Ensuring VPD performance comes from the fact that the SYS_CONTEXT is treated as a bind variable. If you do not use bind variables, but rather return the actual resolved value, for example, “deptno = 20”, the database will spend a lot of time reparsing the SQL statements.

Performance as measured here is not based on how the SQL is generated. In the example, the VPD policy is invoked, and the predicate is produced. However, the application could have produced the same or similar SQL. The point is that it doesn’t matter how the SQL was generated—if you want to achieve stellar performance, you have to produce good SQL; bind variables are generally a good way to go.

Code Location

Another question on the design revolves around whether the SQL should be modified at the database or at the application. Essentially, from a performance perspective, it does not matter. The same process will have to occur regardless of where it occurs. That is, some procedural logic will fire, check some things, and then determine how to reform the SQL query, thus securing the data for the user.

From a security perspective, the database implementation is much better. It guarantees that the SQL, and thus security, will always be enforced. This has value when the data may be needed by other applications and also helps provide defense in depth in the case that the web application is successfully attacked. In the latter case, the security of the application itself has been compromised, and it is only the database security that will now ensure that an attacker does not gain access to unauthorized data.

Policy Caching

Another question arises on the performance regarding the time required for the database to invoke         VPD—a.k.a. “overhead.” Because VPD invokes a function each time a statement or cursor is issued, performance can be a concern.

To help ensure things are running extremely fast, the database allows you to cache the VPD policy. In Oracle 9i Database, this capability was introduced with a new STATIC_POLICY parameter. When set to TRUE while registering the policy with the DBMS_RLS.ADD_POLICY procedure, the database will cache, on the first execution of the VPD policy, the results from your policy function. This can result in significant performance improvements because the PL/SQL code implementing your VPD policy will not be called in further queries.

You can still create a VPD policy and set the STATIC_POLICY parameter to true; however, that parameter is deprecated in Oracle Database 10g. The STATIC_POLICY was a good start, but Oracle realized there are still some enhancements that can be done. In Oracle Database 10g, the RLS package supports five new variations of policy caching. The replacement is a parameter called POLICY_TYPE, which allows you to set the caching to one of five different values:

  • STATIC  Equivalent to the STATIC_POLICY=TRUE setting in Oracle9i Database. The policy function is executed once, and the resulting string (the predicate) is stored in the Shared Global Area (SGA).
  • SHARED_STATIC  Allows the predicate to be cached across multiple objects that use the same policy function.
  • CONTEXT_SENSITIVE  The server always executes the policy function on statement parsing. The server will only execute the policy function on statement execution if it detects context changes. This makes it ideal for connection pooling solutions that share a database schema and use application contexts to actually perform the user identity switching (see Chapter 6 for an example of how to do this).
  • SHARED_CONTEXT_SENSITIVE  The same as CONTEXT_SENSITIVE except that the policy can be shared across multiple objects that use the same policy function.
  • DYNAMIC  The default, which makes no assumptions about caching. The policy will be invoked every time the SQL statement is parsed or executed.

You will see three examples of the caching: STATIC, SHARED_STATIC, and SHARED_ CONTEXT_SENSITIVE. You will not see DYNAMIC because that means no caching, and the CONTEXT_SENSITIVE caching is a subset of the SHARED_CONTEXT_SENSITIVE.

STATIC Caching Example

The first caching example will use the STATIC option, which is equivalent to setting the STATIC_ POLICY parameter to TRUE in the Oracle 9i Database procedure. To test the caching, the policy function will be modified to incorporate an artificial latency. The DBMS_LOCK.SLEEP procedure simulates a policy function that takes two seconds to execute and will make the caching visible to you. The policy function owner will require execute privileges on the DBMS_LOCK package. Because the RLS will call this function, you can measure the execution time of your query to determine the latency this function causes.

sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION user_only (
 
2    p_schema  IN  VARCHAR2 DEFAULT NULL,
 
3    p_object  IN  VARCHAR2 DEFAULT NULL)
 
4    RETURN VARCHAR2
 
5  AS
 
6  BEGIN
 
7    — stall for 2 seconds
  8    DBMS_LOCK.sleep (2);
  9    RETURN ‘username = sys_context
   (”userenv”,”session_user”)’;
 10  END;
 
11  /
Function created.

Recall this is the policy function guarding the SALARY column of the PEOPLE table. You can test the caching by querying the salaries from the table. The first query will be done prior to enabling the caching:

scott@KNOX10g> SELECT username, salary FROM people
  2   WHERE deptno = 20;
USERNAME      SALARY
———- ———
SMITH
JONES
SCOTT           3000
ADAMS
FORD
Elapsed: 00:00:04.10

It took four seconds, not two. The policy is invoked once during the SQL parse phase and once during the statement execution. You can alter the RLS policy to employ the use of the “static” caching for the column-sensitive policy previously defined for the SALARY column of the PEOPLE table:

sec_mgr@KNOX10g> BEGIN
 
2    — Remove current policy
  3    DBMS_RLS.drop_policy
  4         (object_schema      => ‘SCOTT’,
  5          object_name        => ‘PEOPLE’,
  6          policy_name =>
   ‘people_sel_sal’);
 
7    — Add policy again but now with 
   Caching
  8    DBMS_RLS.add_policy
  9        (object_schema    => ‘SCOTT’,
 
10         object_name      => ‘PEOPLE’,
 11         policy_name      =>
   ‘people_sel_sal’,
 12         function_schema  => ‘SEC_MGR’, 
 13         policy_function  => ‘user_only’,
 14         statement_types  => ‘SELECT’,
 15         sec_relevant_cols => ‘SALARY’, 
 16         policy_type    =>
   DBMS_RLS.STATIC
);
 17  END;
 18  /
PL/SQL procedure successfully completed.

Enabling the timing feature of SQL*Plus, you can see the benefit to using static caching. The first execution will invoke the policy, and the predicate will be stored in the Shared Global Area:

scott@KNOX10g> SELECT username, salary FROM people
  2  
WHERE deptno = 20;
USERNAME      SALARY
———- ———
SCOTT           3000
Elapsed: 00:00:02.05

The two seconds were introduced by PRED_FUNCTION. Any subsequent execution by any user will use the cached policy. To illustrate this, rerun the query. Because the cached predicate is stored in the SGA, the caching can be used for all user sessions across all schemas:

scott@KNOX10g> SELECT username, salary FROM people
  2   WHERE deptno = 20;
USERNAME      SALARY
——— ———-
SCOTT           3000
Elapsed: 00:00:00.01
scott@KNOX10g> — Reconnect. Establish a different session for same user scott@KNOX10g> conn scott/tiger
Connected.
scott@KNOX10g> set timing on
scott@KNOX10g> SELECT username, salary FROM people
 
2   WHERE deptno = 20;
USERNAME      SALARY
———- ———
SCOTT           3000
Elapsed: 00:00:00.01
scott@KNOX10g> — Connect as a different user
scott@KNOX10g> conn system/manager Connected.
system@KNOX10g> SET timing on system@KNOX10g> SELECT username, salary FROM scott.people
scott@KNOX10g> — Connect as a different user scott@KNOX10g> conn system/manager Connected.
system@KNOX10g> SET timing on system@KNOX10g> SELECT username, salary FROM scott.people
 
2   WHERE deptno = 20;
no rows selected
Elapsed: 00:00:00.02

While the policy will be cached for all users, the returned records are not necessarily the same. In the previous example, the policy always returns the same predicate. When the SYSTEM user executes the query, the policy is cached and different results are returned because the SYS_CONTEXT function returns a different user identity.

{mospagebreak title=SHARED_STATIC Caching}

The next cache example uses the SHARED_STATIC setting, which allows the predicate to be cached across multiple objects. This is practical because it’s likely that you will be using the same policy function for object references in the same application. This example requires the use of the EMP and DEPT tables and assumes the security policy governing access to these tables is identical. Before you can add the policy, it’s important to ensure all other policies have been dropped. The following script will drop all policies governing select statements on the EMP and DEPT tables in the SCOTT schema:

— disable all select policies for EMP and DEPT DECLARE l_str VARCHAR2 (100); BEGIN FOR rec IN (SELECT * FROM dba_policies WHERE object_owner = ‘SCOTT’ AND object_name IN (‘EMP’, ‘DEPT’) AND sel = ‘YES’) LOOP l_str :=

The next cache example uses the SHARED_STATIC setting, which allows the predicate to be cached across multiple objects. This is practical because it’s likely that you will be using the same policy function for object references in the same application. This example requires the use of the EMP and DEPT tables and assumes the security policy governing access to these tables is identical. Before you can add the policy, it’s important to ensure all other policies have been dropped. The following script will drop all policies governing select statements on the EMP and DEPT tables in the SCOTT schema:

— disable all select policies for EMP and DEPT
DECLARE
 
l_str VARCHAR2 (100);
BEGIN
 
FOR rec IN (SELECT *
               
FROM dba_policies
               
WHERE object_owner = ‘SCOTT’
                
AND object_name IN
                       
(‘EMP’, ‘DEPT’)
                
AND sel = ‘YES’)
 
LOOP
   
l_str :=
        
‘begin DBMS_RLS.drop_policy(”SCOTT”,”’
     
|| rec.object_name
     
|| ”’,”’
     
|| rec.policy_name
     
|| ”’); end;’;
   
DBMS_OUTPUT.put_line (l_str);
   
EXECUTE IMMEDIATE l_str;
 
END LOOP;
END;
/

To cache across objects, the objects have to be sharing the same policy function. This example will use a function in the security manager schema that again sleeps for two seconds. Because the policy predicate used in the previous example referenced the ENAME column, which is not a column in the DEPT table, the policy function has to be modified to prevent an error. Because in this example you are only interested in cache performance, the policy function will sleep for two seconds and then simply return null:

sec_mgr@KNOX10g> — VPD function, injects 2 second delay
sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (
 
2    p_schema IN VARCHAR2 DEFAULT NULL,
  3    p_object IN VARCHAR2 DEFAULT NULL)
  4    RETURN VARCHAR2
 
5  AS
  6  BEGIN
  7    DBMS_LOCK.sleep (2);
  8    — return all records
  9    RETURN NULL;
 
10  END;
 11  /
Function created.

This predicate function is then applied to both the EMP and DEPT tables:

sec_mgr@KNOX10g> BEGIN
 
2    — Add policy on EMP table
  3    DBMS_RLS.add_policy
  4       (object_schema    => ‘SCOTT’,
  5        object_name      =>
‘EMP’,
  6        policy_name      =>
   ‘EMP_SEL_CACHE’,
  7        function_schema  => ‘SEC_MGR’,
  8        policy_function  =>
   ‘PRED_FUNCTION’,
  9        statement_types  => ‘SELECT’,
 10        policy_type      =>
   DBMS_RLS.shared_static
);
 11    — Add policy on DEPT table
 12    DBMS_RLS.add_policy
 13       (object_schema    => ‘SCOTT’,
 14        object_name      => ‘DEPT’,
 15        policy_name      =>
   ‘DEPT_SEL_CACHE’,
 16        function_schema  => ‘SEC_MGR’,
 17        policy_function  =>
   ‘PRED_FUNCTION’,
 18        statement_types  => ‘SELECT’,
 19        policy_type      =>
   DBMS_RLS.shared_static
);
 20  END;
 21  /
PL/SQL procedure successfully completed.

Connect as SCOTT and query either of the tables. Notice the first query will cache the predicate for both tables:

scott@KNOX10g> SET timing on
scott@KNOX10g> — Query one of the tables scott@KNOX10g> SELECT COUNT (*) FROM emp;
 
COUNT(*)
———-
        14
Elapsed: 00:00:02.34
scott@KNOX10g> — run again to see if predicate is cached
scott@KNOX10g> /
  COUNT(*)
——–
     
14
Elapsed: 00:00:00.01
scott@KNOX10g> — Query a different table that uses same policy function scott@KNOX10g> — The database should use the cached predicate
scott@KNOX10g> SELECT COUNT (*) FROM dept;
 
COUNT(*)
———-
        
4
Elapsed: 00:00:00.01

The behavior with this is consistent with the STATIC example in that the caching exists across sessions and schemas:

scott@KNOX10g> — Query as a different session
scott@KNOX10g> — Cache is shared across sessions
scott@KNOX10g> conn system/manager Connected.
system@KNOX10g> SELECT COUNT (*) FROM scott.dept;
  COUNT(*)
———-
         4
Elapsed: 00:00:00.01

The benefit gained from this caching technique is directly proportional to the number of tables sharing the policy function.

SHARED_CONTEXT_SENSITIVE

The final caching example uses the SHARED_CONTEXT_SENSITIVE setting. This will allow caching for the user’s session up until a user-defined application context is changed. To begin, first drop the current policies by running the script given in the beginning of the previous example. The output is shown here:

begin DBMS_RLS.drop_policy(‘SCOTT’,’DEPT’,’DEPT_SEL_CACHE’); end; begin DBMS_RLS.drop_policy(‘SCOTT’,’EMP’,’EMP_SEL_CACHE’); end;

Next, add the RLS policy to EMP and DEPT, changing the policy type to SHARED_ CONTEXT_SENSITIVE:

sec_mgr@KNOX10g> — add shared_context_sensitive policies sec_mgr@KNOX10g> BEGIN
 
2    — Add policy on EMP table
  3    DBMS_RLS.add_policy
  4           (object_schema   => ‘SCOTT’,
 
5            object_name     => ‘EMP’,
  6            policy_name     =>
   ‘EMP_SEL_CACHE’,
  7            function_schema => ‘SEC_MGR’,
  8            policy_function =>
   ‘PRED_FUNCTION’,
  9            statement_types => ‘SELECT’,
 10            policy_type     =>

   DBMS_RLS.shared_context_sensitive);
 11    — Add policy on DEPT table
 12    DBMS_RLS.add_policy
 13           (object_schema   => ‘SCOTT’, 
 14            object_name     => ‘DEPT’,
 15            policy_name     =>
   ‘DEPT_SEL_CACHE’,
 16            function_schema =>    
   ‘SEC_MGR’,  
 17            policy_function =>
   ‘PRED_FUNCTION’,
 18            statement_types => ‘SELECT’, 
 19            policy_type     =>
    DBMS_RLS.shared_context_sensitive); 20  END;
21  /
PL/SQL procedure successfully completed.

The database will cache the policy until a user-defined application context is manipulated. It doesn’t have to be a context that has any direct relationship to the tables the policy protects. Therefore, the application context defined earlier will be adopted for this example by the addition of a new SET_DEPTNO procedure. The new package specification and new procedure code are listed:

sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE people_ctx_mgr
 
2 AS
  3   PROCEDURE set_deptno;
  4   
PROCEDURE set_deptno(p_deptno in
   number);
 
5   PROCEDURE clear_deptno;
  6 END;
  7 / 

Package created.

The following code is for the new procedure only:

21   PROCEDURE set_deptno (p_deptno IN 
  NUMBER)

22   AS
23   BEGIN
24     DBMS_SESSION.set_context
25            (namespace    => ‘people_ctx’,
26             ATTRIBUTE    => ‘deptno’,
27             VALUE        => p_deptno);
28   END set_deptno;
29

Execute privileges are granted to SCOTT to allow him to directly manipulate the application context. This is done to illustrate how the caching works, and you wouldn’t grant privileges to execute on this namespace manager to users if the context values it set were to be used for security purposes:

sec_mgr@KNOX10g> GRANT EXECUTE ON people_ctx_mgr TO scott;
Grant succeeded.

You can see from the following test results that this type of caching is indeed functionally different from the previous two. The test first queries the EMP table, which causes the predicate to be cached for the EMP and DEPT tables.

scott@KNOX10g> SET timing on
scott@KNOX10g> — Query one of the tables. Predicate is not yet cached
scott@KNOX10g> SELECT COUNT (*) FROM emp;
 
COUNT(*)
———-
       
14
Elapsed: 00:00:02.05
scott@KNOX10g> — Query a different table that uses same policy function scott@KNOX10g> — The database should use the cached predicate
scott@KNOX10g> SELECT COUNT (*) FROM dept;
 
COUNT(*)
———-
         4
Elapsed: 00:00:00.01

You might think the Client Identifier, which is a type of application context, could be used to trigger a switch in the caching, but it cannot. This is important because you may be relying only on the Client Identifier switch when using a shared schema application pool. If you are, consider securing it using the technique described in Chapter 6.

scott@KNOX10g> — note that changing the Client Identifier has no
scott@KNOX10g> — effect on cache scott@KNOX10g> EXEC dbms_session.set_identifier(‘Some Value’); PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
scott@KNOX10g> — Predicate is still cached. scott@KNOX10g> SELECT COUNT (*) FROM emp;
 
COUNT(*)
———-
       
14
Elapsed: 00:00:00.01

To invalidate the cache, change the value in a user-defined application context. The new SET_DEPTNO procedure does this:

scott@KNOX10g> — Execute the namespace manager procedure.
scott@KNOX10g> — This changes a context value which tells the database scott@KNOX10g> — to invalidate the cached predicate.
scott@KNOX10g> EXEC sec_mgr.people_ctx_mgr.set_deptno(10);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
scott@KNOX10g> SELECT COUNT (*) FROM emp;
 
COUNT(*)
———-
       
14
Elapsed: 00:00:02.05

A difference between this and the previous example is that the caching is session specific. This is because the cache invalidation is based on (local) application contexts, which are always going to be different across sessions. Reconnect to the database and query and you will see the cache is automatically invalidated:

scott@KNOX10g> — Query using a different session.
scott@KNOX10g> — Cache is not shared across sessions
scott@KNOX10g> conn scott/tiger
Connected.
scott@KNOX10g> SELECT COUNT (*) FROM scott.dept;
  COUNT(*)
———-
        
4
Elapsed: 00:00:02.05

The policy caching capabilities can increase performance by bypassing the policy invocation. However, there are some situations in which this is not the solution to use.

{mospagebreak title=Caching Caution}

A word of caution: cached policies may not prove effective in all situations. The policy function is executed once, and the result is cached. The policy function will never be re-executed, which means any logic used within the function will never be re-executed. There are some situations when this is undesirable. The most obvious is when the predicate changes based on the logic in the policy function. In the previous example, the predicate is constant, while the value returned by the application context changes. This is very desirable and allows for a cached VPD policy.

The implementation of the policy function also influences whether the policy can be cached. For example, the predicate function in the following meets the requirement of restricting access between the hours of 9 A.M. and 5 P.M. However, if this policy is cached, the first access will cause the function to execute, the result will be cached and applied to everyone. If the function first executes at 10 A.M., then the access will be permitted even after 5 P.M.

— Wrong implementation for caching
CREATE OR REPLACE FUNCTION pred_function_9_to_5 (
  p_schema  IN  VARCHAR2 DEFAULT NULL, 
  p_object  IN  VARCHAR2 DEFAULT NULL)
  RETURN VARCHAR2
AS
BEGIN
  IF TO_CHAR (SYSDATE, ‘HH24’) BETWEEN 9 AND 17
  THEN
   
RETURN ‘1=1’;
  ELSE
    RETURN ‘1=0’;
  END IF;
END;

To correct this, the policy function must either not be cached, or the value must be evaluated each time. The following forces the condition to be evaluated each time:

— Correct implementation for caching.
CREATE OR REPLACE FUNCTION pred_function_9_to_5 (
  p_schema  IN  VARCHAR2 DEFAULT NULL, 
  p_object  IN  VARCHAR2 DEFAULT NULL)  
  RETURN VARCHAR2
AS
BEGIN
  RETURN ‘to_char(sysdate,”HH24”) between 9 and 17’;
END;
/

The point is that the policy function’s implementation cannot be done without regard to the caching strategy. In most cases, caching should be considered and the code should be written to ensure security is always enforced. You should first test without caching to ensure your performance is acceptable. Then enable caching and test the policy with the appropriate use cases to ensure that security is working as desired.

Comparing VPD Performance to View-Based RLS

In the view chapter, you saw a performance test that compared the time required to execute RLS in a view that used a function to filter the records. For even comparisons, you can now build a VPD policy that emulates that security functionality and then test the performance:

sec_mgr@KNOX10G> sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION owner_admin (
  2   p_schema  IN  VARCHAR2 DEFAULT NULL,
  3   p_object  IN  VARCHAR2 DEFAULT NULL)
  4   RETURN VARCHAR2
  5 AS
  6 BEGIN
 
7   IF (SYS_CONTEXT (‘userenv’, ‘isdba’) =
   ‘TRUE’)
 
8   THEN
  9     RETURN NULL;     — returns all rows
 10   ELSE
 11     RETURN ‘OWNER = USER’;
 12   END IF;
 13 END;
 14 /
Function created.

You’ll create a new view over your BIG_TAB table. Add your VPD policy to the view you create:

sec_mgr@KNOX10g> CREATE OR REPLACE VIEW big_vpd_view
  2  AS
  3    SELECT * FROM big_tab;
View created.
sec_mgr@KNOX10g> BEGIN
  2    DBMS_RLS.add_policy
  3       (object_name    => ‘BIG_VPD_VIEW’,
  4        policy_name    =>
   ‘BIG_VPD_VIEW_SIUD’,
  5       policy_function => ‘owner_admin’);
  6  END;
  7  /
PL/SQL procedure successfully completed.

Now for the tests, query once on the base table where you specify the security predicate directly. Query once on the function-based view that was created in the “Functions in Views for Row-Level Security” section in Chapter 10, and finally, query the VPD-based view:

sec_mgr@KNOX10g> SET timing on sec_mgr@KNOX10g> — time with security built into SQL
sec_mgr@KNOX10g> SELECT COUNT (*)
 
2    FROM big_tab
  3   WHERE 1 = DECODE (owner, USER, 1, 0)
  4      OR SYS_CONTEXT (‘userenv’, ‘isdba’)
   = ‘TRUE’;
  COUNT(*)
———-
     
1184
Elapsed: 00:00:07.48
sec_mgr@KNOX10g> — time with RLS built into view
sec_mgr@KNOX10g> SELECT COUNT (*)
  2    FROM big_view;
sec_mgr@KNOX10g> — time with RLS built into view sec_mgr@KNOX10g> SELECT COUNT (*)
  2 FROM big_view;
 
COUNT(*)
———-
     
1184
Elapsed: 00:01:05.97
sec_mgr@KNOX10g> — time with VPD sec_mgr@KNOX10g> SELECT COUNT (*)
  2    FROM big_vpd_view;
 
COUNT(*)
———-
     
1184
Elapsed: 00:00:06.99

The query on the VPD view performs on par with the modified SQL because the SQL is modified by the VPD policy before it is executed.

Summary

Virtual Private Database (VPD) helps resolve some of the challenges associated with views. An RLS policy is defined as a mapping from a PL/SQL implemented security function to a table, view, or synonym. The actual PL/SQL implementation that enforces the VPD can be based on whatever is relevant—IP address, time of day, application context values. The policies also are transparent to queries on the protected objects.

New to Oracle Database 10g is the ability to support column-sensitive policies, which allows a more selective invocation of the RLS mechanisms. This is very practical and allows you to more easily store data with different sensitivities within the same table. One of the challenges to implementing VPD is debugging faulty implementations. You saw various best practice techniques for helping to mitigate the debug challenge.

To ensure high performance, the RLS mechanism has been written to modify the SQL before it is parsed and executed. This allows the database to use indexes and optimization plans to ensure fast access to data. Using bind variables and application contexts and enabling policy caching can significantly improve RLS performance.

In Chapter 12, you will explore an implementation (or perhaps an augmentation) of VPD called Oracle Label Security.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye