14 Using Virtual Private Database to Implement Application Security Policies

14.1.1.1 Column-Level VPD

Column-level VPD enables you to enforce row-level security when a security-relevant column is referenced in a query. You can apply column-level VPD to tables and views, but not to synonyms. By specifying the security-relevant column name with the sec_relevant_cols parameter of the DBMS_RLS.ADD_POLICY procedure, the security policy is applied whenever the column is referenced, explicitly or implicitly, in a query.

For example, users outside of the HR department typically are allowed to view only their own Social Security numbers. When a sales clerk initiates the following query:

SELECT fname, lname, ssn FROM emp;

The function implementing the security policy returns the predicate ssn='my_ssn' and the database rewrites the query and executes the following:

SELECT fname, lname, ssn FROM emp WHERE ssn = 'my_ssn';

14.1.1.2 Column-Level VPD with Column-masking Behavior

If a query references a sensitive column, then the default behavior of column-level VPD restricts the number of rows returned. With column-masking behavior, which can be enabled by using the sec_relevant_cols_opt parameter of the DBMS_RLS.ADD_POLICY procedure, all rows display, even those that reference sensitive columns. However, the sensitive columns display as NULL values.

To illustrate this, consider the results of the sales clerk query, described in the previous example. If column-masking behavior is used, then instead of seeing only the row containing the details and Social Security number of the sales clerk, the clerk would see all rows from emp, but the ssn column values would be returned as NULL. Note that this behavior is fundamentally different from all other types of VPD policies, which return only a subset of rows.

14.1.1.3 VPD Security Policies and Applications

The security policy is applied within the database itself, rather than within an application. This means that the use of a different application will not bypass the security policy. Security can thus be built once, in the database, instead of being implemented again in multiple applications. VPD therefore provides far stronger security than application-based security, at a lower cost of ownership.

It may be desirable to enforce different security policies depending on which application is accessing data. Consider a situation in which two applications, Order Entry and Inventory, both access the ORDERS table. You may want to have the Inventory application use a policy that limits access based on type of product. At the same time, you may want to have the Order Entry application use a policy that limits access based on customer number.

In this case, you must partition the use of fine-grained access by application. Otherwise, both policies would be automatically ANDed together, which is not the desired result. You can specify one or more policy groups, and a driving application context that determines which policy group is in effect for a given transaction. You can also designate default policies that always apply to data access. In a hosted application, for example, data access should always be limited by subscriber ID.

14.2.1.1 Security Policies Based on Tables, Views, and Synonyms

Attaching security policies to tables, views, or synonyms rather than to applications provides greater security, simplicity, and flexibility.

14.2.1.2 Multiple Policies for Each Table, View, or Synonym

You can establish several policies for the same table, view, or synonym. Suppose, for example, you have a base application for Order Entry, and each division of your company has its own special rules for data access. You can add a division-specific policy function to a table without having to rewrite the policy function of the base application.

Note that all policies applied to a table are enforced with AND syntax. If you have three policies applied to the CUSTOMERS table, then each policy is applied to any access of the table. You can use policy groups and a driving application context to partition fine-grained access control enforcement so that different policies apply, depending upon which application is accessing data. This eliminates the requirement for development groups to collaborate on policies and simplifies application development. You can also have a default policy group that is always applicable (for example, to enforce data separated by subscriber in a hosting environment).

14.2.1.3 Grouping of Security Policies

Because multiple applications with multiple security policies, can share the same table, view, or synonym, it is important to identify those policies that should be in effect when the table, view, or synonym is accessed.

For example, in a hosting environment, Company A can host the BENEFIT table for Company B and Company C. The table is accessed by two different applications, Human Resources and Finance, with two different security policies. The Human Resources application authorizes users based on ranking in the company, and the Finance application authorizes users based on department. To integrate these two policies into the BENEFIT table would require joint development of policies between the two companies, which is not a feasible option. By defining an application context to drive the enforcement of a particular set of policies to the base objects, each application can implement a private set of security policies.

To do this, you can organize security policies into groups. By referring to the application context, the Oracle Database server determines which group of policies should be in effect at run time. The server enforces all the policies that belong to that policy group.

14.2.1.4 High Performance

With fine-grained access control, each policy function for a given query is evaluated only once, at statement parse time. Also, the entire dynamically modified query is optimized and the parsed statement can be shared and reused. This means that rewritten queries can take advantage of the high performance features of Oracle Database, such as dictionary caching and shared cursors.

14.2.1.5 Default Security Policies

While partitioning security policies by application is desirable, it is also useful to have security policies that are always in effect. In the previous example, a hosted application can always enforce data separation by subscriber_ID, whether you are using the Human Resources application or the Finance application. Default security policies allow developers to have base security enforcement under all conditions, while partitioning of security policies by application (using security groups) enables layering of additional, application-specific security on top of default security policies. To implement default security policies, you add the policy to the SYS_DEFAULT policy group.

14.3.1.1 Specifying Attributes for Each Application

Each application can have its own context with its own attributes. Suppose, for example, you have three applications: General Ledger, Order Entry, and Human Resources. You can specify different attributes for each application:

• For the General Ledger application context, you can specify the attributes SET_OF_BOOKS and TITLE.

• For the Order Entry application context, you can specify the attribute CUSTOMER_NUMBER.

• For the Human Resources application context, you can specify the attributes ORGANIZATION_ID, POSITION, and COUNTRY.

In each case, you can adapt the application context to your precise security needs.

14.3.1.2 Providing Access to Predefined Attributes Through the USERENV Namespace

Oracle database server provides a built-in application context namespace (USERENV) that provides access to predefined attributes. These attributes are session primitives, which is information that the database captures regarding a user session. Examples include the user name, the IP address from which the user connected, and a proxy user name if the user connection is proxied through a middle tier.

Predefined attributes are useful for access control. For example, a three-tier application creating lightweight user sessions through OCI or thick JDBC can access the PROXY_USER attribute in USERENV. This attribute enables you to determine if the user session was created by a middle tier application. Your policy function could allow a user to access data only for connections where the user is proxied. If users connect directly to the database, then they would not be able to access any data.

You can use the PROXY_USER attribute within VPD to ensure that users only access data through a particular middle-tier application. As a different approach, you can develop a secure application role to enforce your policy that users access the database only through a specific proxy.

You can access predefined attributes through the USERENV application context, but you cannot change them. They are listed in Table 14-1.

Use the following syntax to obtain information about the current session.

SYS_CONTEXT('userenv', 'attribute')

Table 14-2 lists the attributes of namespace USERENV that have been deprecated. Oracle suggests that you use the alternatives suggested in the Comments column.

14.3.1.3 Externalized Application Contexts

Many applications store attributes used for fine-grained access control within a database metadata table. For example, an EMPLOYEES table could include cost center, title, signing authority, and other information useful for fine-grained access control. Organizations also centralize user information for user management and access control in LDAP-based directories, such as Oracle Internet Directory. Application context attributes can be stored in Oracle Internet Directory and assigned to one or more enterprise users. They can be retrieved automatically upon login for an enterprise user and then used to initialize an application context.

14.3.2.1 Secure Data Caching

Accessing an application context inside your fine-grained access control policy function is like writing down an often-used phone number and posting it next to your phone, where you can find it easily rather than looking it up every time you need it.

For example, suppose you base access to the ORDERS_TAB table on customer number. Rather than querying the customer number for a logged-in user each time you need it, you could store the number in the application context. In this way, the customer number is available in the session when you need it.

Application context is especially helpful if your security policy is based on multiple security attributes. For example, if a policy function bases a predicate on four attributes (such as employee number, cost center, position, spending limit), then multiple subqueries must execute to retrieve this information. Instead, if this data is available through application context, then performance is much faster.

14.3.2.2 Returning a Specific Predicate (Security Policy)

You can use application context to return the correct security policy, enforced through a predicate.

Consider an order entry application that enforces the following rules: customers only see their own orders, and clerks see all orders for all customers. These are two different policies. You could define an application context with a Position attribute, and this attribute could be accessed within the policy function to return the correct predicate, depending on the value of the attribute. Thus, you can enable a user in the Clerk position to retrieve all orders, but a user in the Customer position to see only those records associated with that particular user.

To design a fine-grained access control policy to return a specific predicate for an attribute, access the application context within the function that implements the policy. For example, to limit customers to seeing only their own records, use fine-grained access control to dynamically modify user queries as from this:

SELECT * FROM Orders_tab

to the following query:

SELECT * FROM Orders_tab 
   WHERE Custno = SYS_CONTEXT ('order_entry', 'cust_num');

14.3.2.3 Providing Attributes Similar to Bind Variables in a Predicate

Continuing with the preceding example, suppose you have 50,000 customers, and you do not want to have a different predicate returned for each customer. Customers all share the same predicate, which prescribes that they can only see their own orders. It is merely their customer numbers that are different.

Using application context, you can return one predicate within a policy function that applies to 50,000 customers. As a result, there is one shared cursor that executes differently for each customer, because the customer number is evaluated at execution time. This value is different for every customer. Use of application context in this case provides optimum performance, as well as row-level security.

Note that the SYS_CONTEXT function works much like a bind variable, only if the SYS_CONTEXT arguments are constants.

14.5.2.1 Limiting Roles Through PRODUCT_USER_PROFILE

DBAs can use PRODUCT_USER_PROFILE to disable certain SQL and SQL*Plus commands in the SQL*Plus environment for each user. SQL*Plus, not the Oracle Database, enforces this security. DBAs can even restrict access to the GRANT, REVOKE, and SET ROLE commands in order to control user ability to change their database privileges.

The PRODUCT_USER_PROFILE table enables you to list roles that you do not want users to activate with an application. You can also explicitly disable the use of various commands, such as SET ROLE.

For example, you could create an entry in the PRODUCT_USER_PROFILE table to:

• Disallow the use of the CLERK and MANAGER roles with SQL*Plus

• Disallow the use of SET ROLE with SQL*Plus

Suppose user Jane connects to the database using SQL*Plus. Jane has the CLERK, MANAGER, and ANALYST roles. As a result of the preceding entry in PRODUCT_USER_PROFILE, Jane is only able to exercise her ANALYST role with SQL*Plus. Also, when Jane attempts to issue a SET ROLE statement, she is explicitly prevented from doing so because of the entry in the PRODUCT_USER_PROFILE table prohibiting use of SET ROLE.

Use of the PRODUCT_USER_PROFILE table does not completely guarantee security, for multiple reasons. In the preceding example, while SET ROLE is disallowed with SQL*Plus, if Jane had other privileges granted to her directly, then she could exercise these using SQL*Plus.

14.5.2.2 Using Stored Procedures to Encapsulate Business Logic

Stored procedures encapsulate use of privileges with business logic so that privileges are only exercised in the context of a well-formed business transaction. For example, an application developer might create a procedure to update employee name and address in the EMPLOYEES table, which enforces that the data can only be updated in normal business hours. Also, rather than grant a human resources clerk the UPDATE privilege on the EMPLOYEES table, a developer (or application administrator) may grant the privilege on the procedure only. Then, the human resources clerk can exercise the privilege only in the context of the procedures, and cannot update the EMPLOYEES table directly.

14.5.2.3 Using VPD for Highest Security

Virtual Private Database (VPD) provides the benefit of strong security policies, which apply directly to data. When you use VPD, you can enforce security no matter how a user gets to the data: whether through an application, through a query, or by using a report-writing tool.