Chapter 5. Implementing VPD with Fine Grained Access Control

Data security has always been a questionable criterion for a solution which promises data repository. "How much is my data secure or vulnerable?" We make a lot of effort on the concepts of data storage, fetch optimization, and its integrity to ensure and evolve a concrete database philosophy. But the last question hovers every time the database design and security paradigms are planned. The Oracle database offers multiple solutions in variety of areas to enforce best security strategies on the data. Some of the focused areas have been securing user accounts, their authentication, their roles and privileges, data encryption, data audit, and data vaulting.

In this chapter, we will discuss one of the data security features which builds up a protocol or mechanism to impose latent dynamic querying criteria on the data selection. The security feature comes up with the name Fine Grained Access Control which is also known as Virtual Private...

Fine Grained Access Control

In this section, we will discuss about Fine Grained Access Control.

Overview

The authoritative rules (as referred to above) enable the security at a table row or a column. This feature limits the access to the secured data only for the users who are authorized for it. The feature is known as Fine Grained Access Control (FGAC). The FGAC security feature imposes row-level and column-level security so that only privileged users can see them. The feature creates a private window of a table for the currently logged in user who can view only the data for which he is authorized.

Prior to FGAC, it used to be a cumbersome activity to decide the data authorization barriers and segregate the schema or the data, as required. Maintenance of multiple users and their multiple schemas used to be a DBA's overhead.

In a multiuser database environment, users are required to access the information authorized for them. One way could be that admin holds the schema and all database objects...

VPD implementation—outline and components

The development steps of VPD implementation is demonstrated in the flowchart as follows:

• Creation of an application context: The application context is a collection of variables whose values, once set, remain the same and are available in the same session. The variables are known as attributes. Each session accessing the same application context attributes can have different values.

• Create the context key and value: Create the trusted program to set the context using DBMS_SESSION.SET_CONTEXT. During the creation of the context, Oracle does not verify the existence of the program being specified. It relies on the trust of the user to create and use a program with the same name to set the context attributes. Therefore, the program is referred to as trusted.

• Set the context key explicitly or through a Logon trigger: The context must be set for the whole session or just before each call. System-level Logon triggers are also capable of scheduling...

VPD implementation—demonstrations

We will demonstrate two ways of VPD implementation. While the first method is simple association of a policy, the second demonstration uses an application context for the same. Before we get into the actual implementation, let us have a look at the employees table. Notice the explain plan for the SELECT query:

/*Select the EMPLOYEES table data*/
SQL> SELECT * 
  FROM employees
/

/*Generate the explain plan for the above query*/
SQL> EXPLAIN PLAN FOR SELECT * FROM employees;

Explained.

/*Display the explain plan*/
SQL>SELECT * FROM TABLE(dbms_xplan.display);

We will take up the same scenario of employees where the employee often viewed other department's salaries and argued with their management. As a result, management took a heavy step by limiting the access in its host department only.

Assignment 1—implementing VPD using simple security policy

In our first assignment, we will see the implementation of a dynamic VPD policy on a user owned database...

VPD policy metadata

The Oracle Server maintains the association information between the security policies and schema objects in data dictionary views. The data dictionaries available for this information is USER_DEPENDENCIES, ALL_DEPENDENCIES, and DBA_DEPENDENCIES. The structure of the dictionary view is as follows:

SQL> DESC USER_POLICIES

  Name                      Null?      Type
  -------------------------------------------------
  OBJECT_NAME               NOT NULL   VARCHAR2(30)
  POLICY_GROUP              NOT NULL   VARCHAR2(30)
  POLICY_NAME               NOT NULL   VARCHAR2(30)
  PF_OWNER                  NOT NULL   VARCHAR2(30)
  PACKAGE                              VARCHAR2(30)
  FUNCTION                  NOT NULL   VARCHAR2(30)
  SEL                                  VARCHAR2(3)
  INS                                  VARCHAR2(3)
  UPD                                  VARCHAR2(3)
  DEL                                  VARCHAR2(3)
  IDX                                  VARCHAR2...

Policy utilities—refresh and drop

Policy utility activities such as refreshing or dropping can be done through DBMS_RLS package subprograms. Refreshing a policy pushes an enabled VPD protocol to inherit the latest changes done to the policy and its dependents. Policy refresh is required when the underlying referenced objects of the policy undergo changes. So as to invalidate its dependent objects operationally during the policy refresh process, all the cached statements associated with the policy are parsed again.

Notice that a disabled VPD policy cannot be refreshed.

The subprogram requires the policy owner, the table or view name, and the policy name as the input parameters:

SQL> EXEC DBMS_RLS.REFRESH_POLICY('ORADEV','EMPLOYEES','EMP_DEPT_20');

PL/SQL procedure successfully completed.

Dropping a policy lifts the data restriction and full data is visible to all the users. Like REFRESH_POLICY, the dropping subprogram also requires the policy owner, the table or view name, and the policy...

Summary

In the chapter, we learned an efficient security concept called Fine Grained Access Control. We started with the concept understanding and its components. Within the prescribed scope of the chapter, we had a walk-through demonstration on the VPD implementation with and without using the application contexts. Since VPD has been emerging as one of the promising security features, we will recommend further reading from the following links to touch other areas too:

• Oracle documentation: http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm

• OTN Network: http://www.oracle.com/technetwork/database/security/index-088277.html

In the next chapter, we will handle one of the major application storage bottlenecks that is, storage of large objects. We will understand various large objects' data types and their management.

Practice exercise

1. Identify the correct statements about the working of Fine Grained Access Control:

   1. A table can have only one security policy.

   2. Different policies can be used to protect SELECT, INSERT, UPDATE, and DELETE statements on a table but not one.

   3. The policy function returns the predicate information as WHERE <Column> = <Value>.

   4. Once associated, the FGAC policy cannot be revoked from the table.

2. A security policy can be associated to group of objects by the DBA. State true or false.

   1. True

   2. False

3. Chose the correct statement about DBMS_RLS:

   1. DBMS_RLS is used only for Row Level Security policies.

   2. The package is owned by SYS.

   3. It can create/drop/refresh policies and create/drop policy groups.

   4. Using DBMS_RLS to set the policy degrades the application performance.

4. Identify the correct statements about the context of an application:

   1. A user who holds the CREATE CONTEXT privilege can create a context.

   2. It is owned by the user SYS.

   3. A user can check context metadata in USER_CONTEXTS.

   4. The trusted package...