Everything Oracle

Home

Everything Oracle

 

 

 

 

 

 

Introduction

 

It has different names, “Virtual Private Database”, “Fine Grained Access Control”, and “Row-Level Security”, but, whatever it may be called, it’s a database mechanism for ensuring that the rows and columns returned to a user (or the DML operations performed by a user) reflect that user’s privileges.

 

It’s called “fine grained” because, unlike earlier security mechanisms, it’s not an all or nothing affair: it’s not a case of “you can view this table’s data or you cannot”; instead, it’s a case of “you can view some of this table’s rows and some of the column values within these rows”.

 

For example, in terms of rows, for an employee table a manager might be able to view the rows corresponding to the employees that he manages, but not the rows corresponding to the employees managed by other managers.

 

For example, in terms of columns, a HR assistant might be able to see all employee rows if a query includes just the employee name and department name columns, but no rows at all if a query includes the employee salary column.

 

 

Security Policies and Policy Functions

 

Security policies are designed to control access to database tables and views.  Each security policy is implemented using an associated policy function, written in PL/SQL.

 

When a select statement is received by the RDBMS, the RDBMS first determines if a security policy exists for the target table or view.  If it does and the conditions of the security policy apply, then the RDBMS runs the policy function referenced by the security policy.  If the policy function returns a non-null value, then the RDBMS appends a where clause to the select statement.  The text of the where clause equals the value returned by the policy function.  It is this modified select statement that is actually run against the target table or view:

 

In constructing the where clause, the policy function takes into account the user’s attributes, so that the rows and columns that are returned by the select statement reflect the privileges that depend on these attributes.

 

 

Preparation

 

To illustrate VPD functionality, we’ll make use of the sample Oracle schema “Scott”, and the table “emp” that is owned by “Scott”:

 

Table “emp” has two columns that contain financial information, “sal” and “comm”.

 

Let’s create a user named “controller”, who will have access to all “emp” data, and a user named “clerk”, who will be prevented from viewing the contents of the “sal” and “comm” columns:

 

Note that this script must be run from user “sys” in order to grant the execute privilege on package “dbms_rls” (unless “sys” has delegated this privilege to another user).

 

At this stage both users have the same privileges, select on table “emp”, so they can view all rows and all columns belonging to this table.

 

 

Policy Function

 

Let’s create a very simple policy function, “df_restrict_emp”:

 

Policy functions must take the schema and the table name as parameters.  This policy function returns “null” by default, ensuring that no where clause will be added to select statements.  If the context user is “clerk”, then the function returns the where clause “0 = 1”, ensuring that the select statement will return no rows.

 

In practice, we might create an “after logon” database trigger that sets the context using a support function.  This support function would obtain the user name, determine the user attributes, and then set a number of different context variables, such as the user role, department identifier, or manager identifier.  Policy functions defined on different tables in the schema could then make use of the values of these context variables, without reworking the calculations needed to derive the variable values on each occasion.

 

 

Row Restriction

 

Column Independent Policy

 

The following PL/SQL block drops and then creates a security policy that makes use of the policy function “df_restrict_emp”:

 

If we login to SQL*Plus as user “controller”, then we can view the employee name, employee job, and employee salary for all rows in table “emp”:

 

In this case the policy function has returned “null”, so no where clause has been appended to the select statement.

 

But if we login to SQL*Plus as user “clerk” and repeat this query then no rows are returned:

 

In this case the where clause “0 = 1” has been appended to the select statement, so that no rows are returned.

 

Column Dependent Policy

 

The previous policy function is not as “fine grained” as it might be.  It prevents user “clerk” from viewing any rows from table “emp” irrespective of the columns that appear in the select statement.  However, we only need to prevent user “clerk” from viewing the data in the “sal” and “comm” columns. 

 

The following version of “df_restrict_emp”:

 

has two additional parameters.  Parameter “sec_relevant_cols” takes as its value a space or comma delimited list of column names, in this case column names “sal” and “comm”.  When this parameter is present the security policy is only applied, and the security function called, if one or more of the specified column names appear in the select statement.

 

If we login as user “clerk” and attempt to view just the employee name and employee job columns, then we can see the data for all table rows:

 

Since neither the column name “sal” nor the column name “comm” appears in the select statement the security policy has not been applied.

 

However, if we modify the select statement to include either of the restricted columns, then no rows are returned by the select statement:

 

In these two cases the security policy is applied, the policy function is called, and the where clause “0 = 1” is appended to the select statements.

 

 

Column Masking

 

The column dependent policy that we examined in the previous section is more fine grained than its predecessor, but it suffers from the disadvantage in an ad hoc reporting context in that a user may construct an inappropriate select statement and then be puzzled as to why no rows are returned, or, at best, will realize the reason for their absence and have to construct a second select statement to view a subset of the data.  An alternative approach that addresses this issue is to return all rows, but to mask the values of the columns that the user is not permitted to see.

 

The following version of “df_restrict_emp”:

 

is the same as its predecessor, except for the value of parameter “sec_relevant_cols_opt”.  The “ALL_ROWS” reference implements column masking, in which all rows from the table are displayed, but with the values of the restricted columns set to “null”.

 

If we login as user “clerk” and attempt to view the employee name, employee job, and employee salary:

 

then we can see the values of the employee name and employee job for all rows, but the values of the corresponding employee salaries have been set to null.

 

 

Policy Function Returning Rows

 

To simplify the illustrations of the different types of policies we’ve used a policy function that produces a where clause that returns no rows.  In practice, the where clause will usually return some rows.

 

For example, let’s assume that user “clerk” is only entitled to view the salaries of salesmen.  So let’s modify the predicate assignment in the policy function to read:

 

v_predicate := 'job = ''SALESMAN''';

 

With this change, when column masking is not applied we get:

 

Instead of seeing no rows, we see the rows for salesmen, but no information for employees who are not salesmen.

 

When column masking is applied with this predicate we get the best of both worlds.  We obtain data from every row for the non-restricted columns and data for all columns for the non-restricted rows:

 

We can see the employee names and employee jobs for all rows, and, in addition, the employee salaries for those employees who are salesmen.

 

 

 

Everything Oracle

Home

Everything Oracle

 

Copyright © 2009 PWG Consulting, All Rights Reserved