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

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

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.

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

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.

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

• Dynamic Predicates

• Application Context

• Dynamic Contexts

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

SELECT * FROM hr.employees WHERE P1

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

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