Home arrow Oracle arrow Page 3 - Row-Level Security with Virtual Private Database

Creating the Policy Function - Oracle

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).

  1. Row-Level Security with Virtual Private Database
  2. RLS In-Depth
  3. Creating the Policy Function
  4. The RLS Layer of Security
  5. Debugging RLS Policies
  6. Invalid SQL
  7. Null Application Context Values and Recursive Lookups
  8. VPD Performance
  9. SHARED_STATIC Caching
  10. Caching Caution
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 33
October 20, 2005

print this article



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)
5  AS
7    RETURN 'deptno = sys_context
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.


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;
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  => '
  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
scott@KNOX10g> SELECT username, deptno
2    FROM people
  3   WHERE username < 'C';
---------- ----------
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'
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)
  5  AS
  6  BEGIN
  7    RETURN 'username = sys_context
  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 =>
  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.

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: