6 Access Control 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 the objects they access. This provides protection to objects regardless of the entity who seeks, by whatever means, to access or alter them.

You can provide object protection 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 (VPD). Such policies call functions that you design to specify dynamic predicates that establish the restrictions. You can also group established policies by applying a policy group to a particular application.

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

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

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 a certain set of users to see only specific columns, then 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

Description of Figure 6-1 follows
Description of "Figure 6-1 An Example of a View"

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 Chapter 5 under the section titled "View Privileges". 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.

See Also:

In some circumstances, a finer level of access control than provided by views is needed for tables or rows and the possible actions on them, sometimes associated with particular applications. When such controls are needed, Oracle 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. 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.

See Also:

Using application context with fine-grained access control is called Virtual Private Database, or VPD. See these references:

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

  • Limit access at the row level by 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-level 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:

Using application context with fine-grained access control is called VPD. See the following references for more information:

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

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 specify. Rewritten queries are fully optimized and shareable.

Here is an example of such a 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 the EMP_SEC function (under the HR schema). This function returns a predicate (called P1) 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 following query:

SELECT * FROM hr.employees WHERE P1

Here, P1 (for example, WHERE SAL > 10000, or even a subquery) is the predicate returned from the 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.


Oracle Database does not implement fine-grained access control during MERGE statements. You must use equivalent INSERT and UPDATE statements instead of MERGE to avoid error messages and to ensure correct access control.

Application Context

Application context helps you apply fine-grained access control because you can link 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 which is information that the database automatically captures about a user session. For example, the IP address from which a user connects, the user name, and the proxy user name (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 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 could 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 the 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 that 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 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');

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 lists the policy types and run-time efficiencies.

Table 6-1 Policy Types and Run-Time Efficiencies

Policy Type Predicate and Policy Function Description and Operational Explanation


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.


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



shared context-sensitive

Policy function executed when statement parsed, but value returned is not cached

The policy function is not reevaluated 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, then the policy function is not rerun and the cached value is valid until session private application context changes occur.


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, then dynamic is assumed.

Security Followup: Auditing and 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 important data with database security mechanisms like access controls.

  • Audit as a way of verifying that your access control mechanisms are implemented properly and work 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. If such an audit policy still fires, then 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.

See Also:

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