home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  

Oracle PL/SQL Programming Guide to Oracle 8i Features

Oracle PL/SQL Programming Guide to Oracle 8i FeaturesSearch this book
Previous: 7.7 New DBMS_UTILITY Features Chapter 8 Next: 8.2 CREATE CONTEXT: Creating Contexts

8. Deploying Fine-Grained Access Control

Fine-grained access control (FGAC) is a new feature in Oracle8 i 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

   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)

Figure 8.1

With Oracle8 i '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:


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.


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.


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.

Previous: 7.7 New DBMS_UTILITY Features Oracle PL/SQL Programming Guide to Oracle 8i Features Next: 8.2 CREATE CONTEXT: Creating Contexts
7.7 New DBMS_UTILITY Features Book Index 8.2 CREATE CONTEXT: Creating Contexts

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference