6
Access Controls on Tables, Views, Synonyms, or Rows

The authentication processes described in Chapter 4 validate the identities of the entities using your networks, databases, and applications

The authorization processes described in Chapter 5 provide limits to their access and actions, limits that are linked to their identities and roles.

This chapter describes restrictions associated not with users but with objects, providing protection regardless of the entity who seeks, by whatever means, to access or alter them.

You provide object protections using object-level privileges and views, as well as by designing and using policies to restrict access to specific tables, views, synonyms, or rows. This level of control, which enables you to use application context with fine-grained access control, is called Virtual Private Database, or VPD. Such policies invoke functions that you design to specify dynamic predicates establishing the restrictions. You can also group established policies, applying a policy group to a particular application.

Having established such protections, you need to be notified when they are threatened or breached. Auditing capabilities enable you to receive notification of activities you want watched, and to investigate in advance of or in response to being notified. Given notification, you can strengthen your defenses and deal with the consequences of inappropriate actions and the entities who caused them. Oracle's auditing facilities are introduced in Chapter 8, "Database Auditing: Security Considerations" and described in detail in Chapter 11, "Configuring and Administering Auditing".

This chapter describes Oracle's access control capabilities in the following sections:

• Introduction to Views
• Fine-Grained Access Control
• Security Followup: Auditing as well as Prevention

  See Also: Regarding policies, see Chapter 7, "Security Policies". Regarding application development considerations on policies, see Chapter 12, "Introducing Database Security for Application Developers", Chapter 13, and Chapter 14, "Implementing Application Context and Fine-Grained Access Control" Regarding fine-grained access control, see Chapter 13, "Using Virtual Private Database to Implement Application Security Policies", about developing applications to configure and administer such controls. Regarding security conditions for auditing, see Chapter 8. Regarding how to configure and administer auditing features and mechanisms for both standard users and DBAs, see Chapter 11.

Introduction to Views

A view is a presentation of data selected from one or more tables (possibly including other views). In addition to showing the selected data, a view also shows the structure of the underlying tables, and can be thought of as the result of a stored query.

The view contains no actual data but rather derives what it shows from the tables and views on which it is based. A view can be queried, and the data it represents can be changed. Data in a view can be updated or deleted, and new data inserted. These operations directly alter the tables on which the view is based and are subject to the integrity constraints and triggers of the base tables.

For example, a base table of all employee data may have several columns and numerous rows of information. If you want users to see only specific columns, you can create a view of that table, containing only the allowable columns. You can then grant other users access to the new view, while disallowing access to the base table.

Figure 6-1 shows an example of a view called staff derived from the base table employees. Notice that the view shows only five of the columns in the base table.

Figure 6-1 An Example of a View

Text description of the illustration viewexam.gif

As discussed extensively in Chapter 5, a schema object privilege is a privilege or right to perform a particular action on a specific schema object. Different object privileges are available for different types of schema objects. Privileges related to views are discussed in that chapter, in the View Privileges section. Some schema objects, such as clusters, indexes, triggers, and database links, do not have associated object privileges. Their use is controlled with system privileges. For example, to alter a cluster, a user must own the cluster or have the ALTER ANY CLUSTER system privilege.

All these privileges, including those for tables, views, procedures, types and more, are introduced in Chapter 5's section entitled Introduction to Privileges. The tools and processes for managing these security facilities are discussed in Chapter 10, "Administering User Privileges, Roles, and Profiles".

In some circumstances, a finer level of access control is needed for tables or rows and the possible actions on them, sometimes associated with particular applications. When such controls are needed, Oracle's fine-grained access control capabilities can be used, as described in the next section.

Fine-Grained Access Control

Fine-grained access control enables you to use functions to implement security policies and to associate those security policies with tables, views, or synonyms.

See Also: Using application context with fine-grained access control is called Virtual Private Database, or VPD. See these references: Application Context. Chapter 13, "Using Virtual Private Database to Implement Application Security Policies" Chapter 14, "Implementing Application Context and Fine-Grained Access Control"

The database server automatically enforces your security policies, no matter how the data is accessed, including, for example, through an application by ad hoc queries.

Fine-grained access control enables you to use all of the following capabilities:

• Limit access at the row level, using different policies for SELECT, INSERT, UPDATE, and DELETE.
• Use security policies only where you need them (for example, on salary information).
• Invoke a policy only if a particular column is referenced.
• Restrict access using a combination of row- and column-level controls, by applying a VPD policy to a view.
• Have some policies that are always applied, called static policies, and others that can change during execution, called dynamic policies (see Application Context).
• Use more than one policy for each table, including building on top of base policies in packaged applications.
• Distinguish policies between different applications, by using policy groups. Each policy group is a set of policies that belong to an application.
• Distinguish and control the use of INDEX, in row level security policies.
• Designate an application context, called a driving context, to indicate the policy group in effect. When tables, views, or synonyms are accessed, the fine-grained access control engine looks up the driving context to determine the policy group in effect and enforces all the associated policies that belong to that policy group.

The PL/SQL package DBMS_RLS let you administer your security policies. Using this package, you can add, drop, enable, disable, and refresh the policies (or policy groups) you create.

See Also: The DBMS_RLS chapter in PL/SQL Packages and Types Reference for information about package implementation Chapter 13, "Using Virtual Private Database to Implement Application Security Policies" Chapter 14, "Implementing Application Context and Fine-Grained Access Control" Oracle Database Application Developer's Guide - Fundamentals for information and examples on establishing security policies

The following subsections describe how fine-grained access control works:

• Dynamic Predicates
• Application Context
• Dynamic Contexts

Dynamic Predicates

A dynamic predicate for a table, view, or synonym is generated by a PL/SQL function, which you write and associate with a security policy through a PL/SQL interface.

Dynamic predicates are acquired at statement parse time, when the base table or view is referenced in a query using SELECT or a DML statement.

The function or package that implements the security policy you create returns a predicate (a WHERE condition). This predicate controls access according to the policy you specified. Rewritten queries are fully optimized and shareable.

Here is an example of such a policy:

DBMS_RLS.ADD_POLICY (
   'hr', 'employees', 'emp_policy', 'hr', 'emp_sec', 'select');

Whenever the EMPLOYEES table, under the HR schema, is referenced in a query or subquery (SELECT), the server calls your EMP_SEC function (under the HR schema). This function returns a predicate (called P1 in the following section) defined in the function, which in this example could be specific to the current user for the EMP_POLICY policy. Your policy function can generate the predicates based on the session environment variables available during the function call, that is, from the application context as described in the next section. The policy can specify any combination of security-relevant columns and any combination of these statement types: SELECT, INSERT, UPDATE, DELETE, or INDEX. You can also specify whether the result of an INSERT or UPDATE should immediately be checked against the policy.

The server then produces a transient view, with the text:

SELECT * FROM hr.employees WHERE P1

Here, P1 (for example, where SAL > 10000, or even a subquery) is the predicate returned from your EMP_SEC function. The server treats the EMPLOYEES table as a view and does the view expansion just like the ordinary view, except that the view text is taken from the transient view instead of the data dictionary.

The policy function creates a WHERE clause relevant to the current user by using information from the set of session environment variables called application context.

Application Context

Application context helps you apply fine-grained access control because you can link your function-based security policies with applications.

Oracle provides a built-in application context namespace, USERENV, which provides access to predefined attributes. These attributes are session primitives--information that the database automatically captures regarding a user's session. For example, the IP address from which a user connected, the username, and a proxy username (in cases where a user connection is proxied through a middle tier), are all available as predefined attributes through the USERENV application context.

Each application has its own application-specific context, which users cannot arbitrarily change (for example, through SQL*Plus). Context attributes are accessible to the functions implementing your security policies.

For example, context attributes you could use from a human resources application could include "position," "organizational unit," and "country." Attributes available from an order-entry control system might include "customer number" and "sales region".

Application contexts thus permit flexible, parameter-based access control using context attributes relevant to an application and to policies you might want to create for controlling its use.

You can:

• Base predicates on context values
• Use context values within predicates, as bind variables
• Set user attributes
• Access user attributes

To define an application context:

1. Create a PL/SQL package with functions that validate and set the context for your application. You may want to use an event trigger on login to set the initial context for logged-in users.
2. Use CREATE CONTEXT to specify a unique context name and associate it with the PL/SQL package you created.
3. Then do either of the following:
   • Reference the application context within the policy function implementing fine-grained access control.
   • Create an event trigger on login to set the initial context for a user. For example, you could query a user's employee number and set this as an "employee number" context value.
4. Reference the application context. For example, to limit customers to seeing their own records only, use fine-grained access control to dynamically modify the user's query from SELECT * FROM Orders_tab to the following:

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

   See Also: Regarding how applications configure, administer, and use application context, see Chapter 13, "Using Virtual Private Database to Implement Application Security Policies" Chapter 14, "Implementing Application Context and Fine-Grained Access Control" PL/SQL User's Guide and Reference PL/SQL Packages and Types Reference Oracle Database Application Developer's Guide - Fundamentals

The next subsection, Dynamic Contexts, describes run-time efficiencies you can establish by identifying how dynamic each of your policies is, using these categories: static, shared, context-sensitive, or dynamic.

Dynamic Contexts

When you create a policy, you can establish run-time efficiencies by specifying whether the policy is static, shared, context-sensitive, or dynamic:

Table 6-1  Policy Types and Run-Time Efficiencies  

Policy Type	Predicate and Policy Function	Description and Operational Explanation
Static	Same predicate string for anyone accessing the object	Executed once and cached in SGA. Policies for statements accessing the same object do not reexecute the policy function, but use the cached predicate instead.
Shared-static	Same as static, except the policy can be shared across multiple objects	Ideal for data partitions in hosting environments because almost all objects share the same function and the policy is static. Also executed once and cached in SGA, but the server first looks for a cached predicate generated by the same policy function of the same policy type.
Context-sensitive and shared context-sensitive	Policy function executed when statement parsed, but value returned is not cached	The policy function is not re-evaluated at statement execution time unless the server detects context changes since the last use of the cursor. (For session pooling where multiple clients share a database session, the middle tier must reset context during client switches.) When a context-sensitive policy is labeled "shared," the server first looks for a cached predicate generated by the same policy function of the same policy type within the same database session. If the predicate is found in the session memory, the policy function is not re-executed and the cached value is valid until session private application context changes occur.
Dynamic	Policy function always re-executed on each statement parsing or execution	Server assumes the predicate may be affected by any system or session environment at any time. Dynamic is the system default. If no policy type is specified when DBMS_RLS.ADD_POLICY is called, dynamic is assumed.

See also: The section titled How to Add a Policy to a Table, View, or Synonym in Chapter 14, "Implementing Application Context and Fine-Grained Access Control".

Security Followup: Auditing as well as Prevention

Even after designing and implementing protective measures using privileges, views, and policies, you want to know when these measures are threatened or breached. Auditing can notify you of suspicious or questionable activities. You can then investigate, strengthen your defenses, and deal with inappropriate actions, consequences, and security offenders.

Use auditing to complement your access controls from several perspectives:

• Audit the data you considered important enough to protect with database security mechanisms like access controls.
• Audit as a way of verifying that your access control mechanisms are implemented properly and working as you intended.
• Design audit policies that you expect will never actually "fire" because your other security mechanisms (authentication, authorization, access controls) should be protecting that data. Then if such an audit policy does fire, you are alerted that you have a security breach. It may mean, for example, that your security protections are not operating as you expected them to in protecting the data.

Chapter 8, "Database Auditing: Security Considerations" introduces Oracle's auditing facilities, and Chapter 11, "Configuring and Administering Auditing" describes them in detail.