#

| Chapter 8 |

# 8. Deploying Fine-Grained Access Control

Contents:
FGAC Components
CREATE CONTEXT: Creating Contexts
SET_CONTEXT: Setting Context and Attribute Values
SYS_CONTEXT and LIST_CONTEXT: Obtaining Context Information
A Complete FGAC Example

Fine-grained access control (FGAC) is a new feature in Oracle8i that allows you to implement security policies with functions and then use those security policies to implement row-level security on tables and views. The database server automatically enforces these security policies, no matter how the data is accessed -- through SQL*Plus or the Internet, as an ad hoc query, or as an update processed through an Oracle Forms application.

What, you might ask, is a security policy ? Consider the following very simple scenario (I'll expand upon this scenario in the full example at the end of this chapter). Suppose that I have tables of hospital patients and their doctors defined as follows:

>     CREATE TABLE patient (
>        patient_id NUMBER,
>        name VARCHAR2(100),
>        dob DATE,
>        doctor_id INTEGER
>        );

>     CREATE TABLE doctor (
>        doctor_id NUMBER,
>        name VARCHAR2(100)
>        );

Now suppose that I want to let a doctor see only her own patients when she issues a query against the table. More than that, I don't want to let a doctor modify patient records unless those records belong to that doctor's patients.

You could achieve much of what is needed through the creation of a set of views, and many organizations have been doing just that for years. The view-based approach can become quite complex, especially if you want to make it foolproof. Wouldn't it be so much more elegant if you could just let any doctor connect to her schema in Oracle, issue the following query:

>     SELECT * FROM patient;

and then make certain that the doctor sees information only about her patients? With this approach, you embed all the rules needed to enforce the appropriate privacy and security rules into the database itself as a security policy so that it is transparent to users of the data structures. Oracle uses that policy to modify the WHERE clause of any SQL statement executed against the table,thereby restricting access to data. This process is illustrated in Figure 8.1.

#### Figure 8.1: Automatic application of a security policy (WHERE clause predicate)

With Oracle8i 's fine-grained access control, you can apply different policies to SELECT, INSERT, UPDATE, and DELETE operations and use security policies only where you need them (for example, on salary information). You can also design and enforce more than one policy for a table, and can even construct layers of policies (one policy building on top of an existing policy) to handle complex situations.

## 8.1 FGAC Components

To take advantage of FGAC, you have to use programs and functionality from a wide variety of sources within Oracle, including the following:

CREATE CONTEXT DDL statement

  Allows you to define a system or application context by name, and associate that context with a PL/SQL package. A context is a named set of attribute/value pairs that are global to your session.

DBMS_SESSION.SET_CONTEXT procedure

  Allows you to set the value for a specific attribute in a particular context.

SYS_CONTEXT function

  Returns the value of a specific attribute of a context. These attributes can be system values, such as the schema name, or they can be application-specific elements that you define.

DBMS_SESSION.LIST_CONTEXT procedure

  Returns the value of all attributes and values defined across all contexts in the current session.

DBMS_RLS package

  A variety of programs you can use to define security policies and to associate those policies with specific PL/SQL functions that will generate WHERE clause predicates for use in fine-grained access queries. See Chapter 7, New and Enhanced Built-in Packages in Oracle8i.

> TIP: The default database installation does not grant the EXECUTE privilege on the DBMS_RLS package to PUBLIC. Access is granted only to EXECUTE_CATALOG_ROLE, so schemas calling the package must have that role assigned to them.

Oracle discusses each of these topics in a different area of its documentation, making it difficult to pull them all together into a sensible, easy-to-deploy feature. This chapter takes a different approach. I will explain each area of functionality and the standalone steps needed to use them, but then immediately move to an extended example that will show you exactly how to implement FGAC in your own environment.

7.7 New DBMS_UTILITY Features |  | 8.2 CREATE CONTEXT: Creating Contexts

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.