Fine-grained access control (FGAC) is a new feature in Oracle8i that allows you to implement security policies with functions and then associate those security policies with tables or views (this feature is also known as row-level security). The database server then automatically enforces those 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 presented in Part 2). 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 doctors modify patient records unless they are that doctor's patients.

You could achieve much of what's 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, and then make certain that the doctor sees information only about her patients?

SELECT * FROM patient;
 

With this approach, you embed all of the rules needed to enforce the appropriate privacy/security rules into the database itself as a "security policy" so that it's 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 1.

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 you can even construct layers of policies (one policy building on top of an existing policy) to handle complex situations.

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

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 treats each of these topics in different areas of its documentation, making it difficult to pull it all together into a sensible, easy-to-deploy feature. This article takes a different approach. I'll explain each of the areas of functionality and the "standalone" steps needed to use them, but then in Part 2, I'll move to an extended example that will show you exactly how to implement FGAC in your own environment.

Application contexts facilitate the implementation of FGAC. They allow you to implement security policies with functions and then associate those security policies with applications. Each application can have its own application-specific context. Users aren't allowed to arbitrarily change their context (for example, through SQL*Plus).

A context is a named set of attribute-value pairs associated with a PL/SQL package. A context is attached to and global within a session. Your application can use a context to set values that are then accessed from within your code; specifically, code that's used to generate WHERE clause predicates for FGAC.

Suppose you're building a human resources application. You might create a context called "HRINFO" and define the following attributes for that context:

position
 organizational_unit
 country
 

You can then set values for each of these attributes from within your PL/SQL programs.

Oracle provides a DDL (data definition language) statement to create a context, which is a named set of application-defined attributes used to validate and secure an application. The format of this statement is as follows:

CREATE [OR REPLACE] CONTEXT namespace 
    USING [schema.]plsql_package;
 

You might deduce from this statement that a context has two attributes. Parameters are summarized in Table 1.

To create a context namespace, you must have the CREATE ANY CONTEXT system privilege. Here's the format for this grant:

GRANT CREATE ANY CONTEXT TO schema_name;
 

To make it easier for you to construct contexts and the code to support them, Oracle doesn't verify the existence of the schema or the validity of the package at the time you create the context.

By the way, you don't have to use contexts only with the FGAC feature. These contexts can be used simply to give you a more general and flexible way of setting and obtaining attributes for a session. I'll explore that capability in the "Obtaining context information" section.

The DBMS_SESSION built-in package has been enhanced with the SET_CONTEXT procedure so that you can set the value for an attribute within a context. Here's the header for that procedure:

PROCEDURE DBMS_SESSION.SET_CONTEXT (
    namespace VARCHAR2, 
    attribute VARCHAR2, 
    value VARCHAR2);
 

The parameters are listed in Table 2.

This procedure can only be called inside the package specified for the namespace context in the CREATE CONTEXT statement. This relationship is shown in the following steps:

/* earth.pkg */
 
 CREATE CONTEXT pollution_indicators USING earth_pkg;
 
 CREATE OR REPLACE PACKAGE earth_pkg
 IS
    PROCEDURE set_contexts;
 END;
 /
 CREATE OR REPLACE PACKAGE BODY earth_pkg
 IS
    c_context CONSTANT VARCHAR2(30) :=
       'pollution_indicators';
       
    PROCEDURE set_contexts IS 
    BEGIN   
       DBMS_SESSION.SET_CONTEXT (
          c_context, 'acidrain', 'corrosive');
       DBMS_SESSION.SET_CONTEXT (
          c_context, 'smog', 'dense');
    END;
 END;
 /
 

If you try to execute DBMS_SESSION.SET_CONTEXT "out of context," you'll get an error, as shown here:

SQL> BEGIN   
   2     DBMS_SESSION.SET_CONTEXT (
   3        'pollution_indicators', 'smog', 'dense');
   4  END;
   5  /
 BEGIN
 *
 ERROR at line 1:
 ORA-01031: insufficient privileges
 

You can obtain the value of a context's attribute in one of two ways:

The header for the SYS_CONTEXT function is shown here:

FUNCTION SYS_CONTEXT (
    namespace VARCHAR2, 
    attribute VARCHAR2) 
 RETURN VARCHAR2;
 

It returns the value associated with attribute as defined in the specified context namespace.

In addition to your own application context information, you can retrieve information about your current connection by calling SYS_CONTEXT as follows, where "attribute" can be any of the values listed in Table 3:

SYS_CONTEXT ('USERENV', attribute)
 

Use the following script to examine each of these values:

/* showucntxt.sql */
 DECLARE
    PROCEDURE showenv (str IN VARCHAR2) IS
    BEGIN
       DBMS_OUTPUT.PUT_LINE (
          str || '=' || SYS_CONTEXT ('USERENV', str));
    END;
 BEGIN
    showenv ('NLS_CURRENCY');
    showenv ('NLS_CALENDAR');
    showenv ('NLS_DATE_FORMAT');
    showenv ('NLS_DATE_LANGUAGE');
    showenv ('NLS_SORT');
    showenv ('SESSION_USER');
    showenv ('CURRENT_USER');
    showenv ('CURRENT_SCHEMA');
    showenv ('CURRENT_SCHEMAID');
    showenv ('SESSION_USERID');
    showenv ('CURRENT_USERID');
    showenv ('IP_ADDRESS');
 END;
 /
 

The DBMS_SESSION built-in package provides a procedure that retrieves the list of defined attributes and values for all contexts in your session. Here's the header of that procedure:

PROCEDURE DBMS_SESSION.LIST_CONTEXT (
    list OUT DBMS_SESSION.AppCtxTabTyp, 
    lsize OUT number);
 

The LIST_CONTEXT attributes are specified in Table 4: lsize is the number of elements in list, and list is an index-by table of records.

Each record has the following format, where namespace and attribute have the meanings described for SYS_CONTEXT:

TYPE DBMS_SESSSION.AppCtxRecTyp IS RECORD ( 
    namespace VARCHAR2(30), 
    attribute VARCHAR2(30), 
    value VARCHAR2(4000));
 

Here's a program that utilizes this procedure to retrieve and display all defined context attributes:

/* showcntxt.sp */
 CREATE OR REPLACE PROCEDURE show_context_info 
 IS
    context_info DBMS_SESSION.AppCtxTabTyp;
    info_count PLS_INTEGER;
    indx PLS_INTEGER;
 BEGIN
    DBMS_SESSION.LIST_CONTEXT (
       context_info,
       info_count);
    indx := context_info.FIRST; 
 LOOP
       EXIT WHEN indx IS NULL;
       DBMS_OUTPUT.PUT_LINE (
          context_info(indx).namespace || '.' ||
          context_info(indx).attribute || ' = ' ||
          context_info(indx).value);
       indx := context_info.NEXT (indx);
    END LOOP; 
 END;
 /
 

Here's a script and output that demonstrates the use of this procedure (building upon contexts and packages defined by running the earth.pkg and prison.pkg scripts first, which are avialable in the accompanying Download file):

/* showcntxt.tst */
 BEGIN 
    /* Set context information.*/
    earth_pkg.set_contexts; 
 prison_pkg.set_contexts; 
 show_context_info;
 END;
 /
 
 INCARCERATION_FACTORS.CLASS = poor
 POLLUTION_INDICATORS.SMOG = dense
 INCARCERATION_FACTORS.EDUCATION = minimal
 POLLUTION_INDICATORS.ACIDRAIN = corrosive
 

Table 5 shows the data dictionary views Oracle provides that you can query to obtain information about policies defined in or accessible to your schema.

The columns for the *_POLICIES views are described in Table 6. These values are set through calls to the DBMS_RLS programs ADD_POLICY and ENABLE_POLICY. The columns for the *_CONTEXT views are described in Table 7.

You can, of course, write queries and stored programs to access this information. Here's a procedure that you can use to drop one or all of your policies:

/* droppol.sp */
 CREATE OR REPLACE PROCEDURE drop_policies (
    objname IN VARCHAR2,
    polname IN VARCHAR2 := '%',
    objschema IN VARCHAR2 := NULL)
    AUTHID CURRENT_USER
 IS
 BEGIN
    FOR rec IN (
       SELECT object_owner, 
              object_name, 
              policy_name
         FROM ALL_POLICIES
        WHERE object_owner LIKE NVL (objschema, USER)
          AND object_name LIKE objname
          AND policy_name LIKE polname)
    LOOP
       DBMS_RLS.DROP_POLICY (
          rec.object_owner, rec.object_name, 
          rec.policy_name);
    END LOOP;
 END;
 /
 

Notice that I use AUTHID CURRENT_USER to make sure that the procedure will only drop policies for the tables/views for which the CURRENT_USER has the right access privileges, regardless of who owns the procedure itself. The WHERE clause will further limit the policies to those created for the objects owned by the CURRENT_USER.

You can also use the DDL statement DROP CONTEXT to drop a context/policy directly within an SQL execution environment (or via dynamic SQL).

 To illustrate the steps you'd follow to take advantage of fine-grained access control, I'm going to share with you one of my dearest dreams: The year is 2010, and a national healthcare system has been established.

Of course, we need an excellent database to back up this system; here are four of the many tables in that database (see fgac.sql in the accompanying Download file for all of the DDL statements and subsequent commands in this example):

CREATE TABLE patient  (      CREATE TABLE clinic (  
    patient_id NUMBER,           clinic_id INTEGER,  
    schema_name VARCHAR2(30),    name VARCHAR2(100),  
    last_name VARCHAR2(100),     state CHAR(2)  
    first_name VARCHAR2(100),    ); 
 dob DATE,
    home_clinic_id INTEGER,
    state CHAR(2)
    ); 
 
 CREATE TABLE doctor (        CREATE TABLE regulator (
    doctor_id NUMBER,            regulator_id NUMBER,
    schema_name VARCHAR2(30),    schema_name VARCHAR2(30),
    last_name VARCHAR2(100),     last_name VARCHAR2(100),
    first_name VARCHAR2(100),    first_name VARCHAR2(100),
    home_clinic_id INTEGER       state CHAR(2)
    ); );
 

Of course, we also insist on privacy. So here are the following rules that I'm going to enforce with FGAC:

Sure, I can create views to build in some or all of these types of security rules. But instead I'll use FGAC to accomplish the same objective at a more fundamental and comprehensive level. For example, with FGAC in place, any doctor can issue the following query and only see her patients at the clinic:

SELECT * FROM patient;
 

Regulators (whose job it is to make sure that patients receive top-notch care) can see all of (and only) their clients with the same query:

SELECT * FROM patient;
 

And if a patient issues an unqualified query against the patient table, she'll see only her row. "Same" query, different results, processed transparently with FGAC.

Here are the steps I'll take to get this job done:

Once all of these pieces are in place, I can test my newly secured environment. All of the preceding steps are contained in the fgac.sql script in the accompanying Download file. In the sections that follow, I'll focus on the context-specific elements (as opposed to the CREATE TABLE statements and so on).

I decided to create one package that would contain all of the programs I need to set and manage my context attributes, and generate the security predicates. Listing 1 shows the National Health Care package specification.

CREATE OR REPLACE PACKAGE nhc_pkg 
 IS
    c_context CONSTANT VARCHAR2(30) := 'patient_restriction';
    c_person_type_attr CONSTANT VARCHAR2(30) := 'person_type';
    c_person_id_attr CONSTANT VARCHAR2(30) := 'person_id';
    c_patient CONSTANT CHAR(7) := 'PATIENT';
    c_doctor CONSTANT CHAR(6) := 'DOCTOR';
    c_regulator CONSTANT CHAR(9) := 'REGULATOR';
    
    PROCEDURE show_context;
 
    PROCEDURE set_context;
 
    FUNCTION person_predicate (
       schema_in VARCHAR2, 
       name_in VARCHAR2)
    RETURN VARCHAR2; 
 END nhc_pkg;
 /
 

The show_context procedure comes in handy when I want to verify the context information in a session. Here's the body of this program:

PROCEDURE show_context
 IS
 BEGIN
    DBMS_OUTPUT.PUT_LINE ('Type: ' || 
       SYS_CONTEXT (c_context, c_person_type_attr));
    DBMS_OUTPUT.PUT_LINE ('  ID: ' || 
       SYS_CONTEXT (c_context, c_person_id_attr));
    DBMS_OUTPUT.PUT_LINE ('Predicate: ' ||
       person_predicate (USER, 'PATIENT'));
 END;
 

Here's the output from this procedure when run, for example, from the schema of Sandra Wallace, a doctor (see fgac.sql INSERT statements to verify this data):

Type: DOCTOR
   ID: 1060
 Predicate:
 home_clinic_id IN
    (SELECT home_clinic_id FROM doctor
      WHERE doctor_id = SYS_CONTEXT (
        'patient_restriction', 'person_id'))
 

The nhc_pkg.set_context procedure sets the context based on the type of person the current schema represents: patient, doctor, or regulator (you can only be one of these in my simplified system). I set up two explicit cursors:

PROCEDURE set_context
 IS
    CURSOR doc_cur IS
       SELECT doctor_id FROM doctor
        WHERE schema_name = USER;
 
    CURSOR reg_cur IS
       SELECT regulator_id FROM regulator
        WHERE schema_name = USER;
        
    l_person_type VARCHAR2(10) := c_patient;
    l_person_id INTEGER;
 

along with a local module to set the context of both of my attributes:

   PROCEDURE set_both (
       persType IN VARCHAR2, persID IN VARCHAR2)
    IS BEGIN
       DBMS_SESSION.SET_CONTEXT (
          c_context, c_person_type_attr, persType);
       DBMS_SESSION.SET_CONTEXT (
          c_context, c_person_id_attr, persID);
    END;
 

The executable section then sets the attributes for a doctor, regulator, or patient, depending on the schema name:

BEGIN
    OPEN doc_cur; FETCH doc_cur INTO l_person_id;
    IF doc_cur%FOUND
    THEN
       l_person_type := c_doctor;
    ELSE
       OPEN reg_cur; FETCH reg_cur INTO l_person_id;
       IF reg_cur%FOUND
       THEN
          l_person_type := c_regulator;
       END IF;
       CLOSE reg_cur;
    END IF;
    set_both (l_person_type, l_person_id);
    CLOSE doc_cur;
 END;
 

The main purpose of the National Health Care package is to generate the predicate that will be attached to any query against the patient table. This action is performed by the person_predicate function:

FUNCTION person_predicate (
    schema_in VARCHAR2, 
    name_in VARCHAR2)
    RETURN VARCHAR2 
 

As you'll see in this function's implementation, the schema_in and name_in parameters aren't used at all. I still must include these arguments in the parameter list if it's to be callable by the FGAC mechanism. Now, in the declaration section, I obtain the value for the person type attribute (doctor, regulator, or patient):

IS
     l_context VARCHAR2(100) := 
         SYS_CONTEXT (c_context, c_person_type_attr);
     retval VARCHAR2(2000);
 

This value is set by a call to nhc_pkg.set_context that's made whenever a person connects to the database instance (explained in the next section). Once I have this value, I can create the appropriate predicate. For a doctor, I use the following:

BEGIN
    IF l_context = 'DOCTOR' 
    THEN
       retval := 
          'home_clinic_id IN 
             (SELECT home_clinic_id FROM doctor 
               WHERE doctor_id = SYS_CONTEXT (''' ||
                  c_context || ''', ''' || 
                  c_person_id_attr || '''))';
 

In other words, the doctor can only see patients whose clinic ID matches that of the doctor. Notice I call SYS_CONTEXT directly within the predicate (at runtime, not during the execution of this function) to obtain the doctor's ID number. I construct a very similar predicate for a regulator:

   ELSIF l_context = 'REGULATOR'
    THEN
       retval := 
          'state IN 
             (SELECT state FROM regulator 
               WHERE regulator_id = SYS_CONTEXT (''' ||
                  c_context || ''', ''' || 
                  c_person_id_attr || '''))';
 

If the user is a patient, then the predicate is much simpler: She can only see information about herself, so I force a match on the schema_name column:

   ELSIF l_context = 'PATIENT'
    THEN
       retval := 'schema_name = ''' || USER || '''';
 

Finally, if the person type attribute isn't set to one of the preceding values, I've identified someone outside of the healthcare system entirely, so I refuse access to any patient information:

   ELSE 
       /* Refuse any access to information. */
       retval := 'person_id IS NULL';
    END IF;
 

Then I return the predicate:

   RETURN retval;
 END person_predicate; 
  

I still need to register the security policy (a.k.a., the predicate to be attached to the patient table). To do this, I call DBMS_RLS.ADD_POLICY as follows:

BEGIN
    DBMS_RLS.ADD_POLICY (
       'SCOTT',
       'patient',
       'patient_privacy',
       'SCOTT',
       'nhc_pkg.person_predicate',
       'SELECT,UPDATE,DELETE');
 END;
 /
 

This program call specifies that whenever a SELECT, UPDATE, or DELETE on the SCOTT.patient table is executed, the SCOTT.nhc_pkg.person_predicate function is to be called to generate a predicate that will be added to the WHERE clause of the statement.

I could define a different security policy for the different SQL statements, but in this case, the same predicate would be applied to each.

Now all of the pieces are in place. To get things rolling, however, I need to create a trigger that will execute whenever anyone logs in to the database:

CONNECT sys/sys
 
 /* Create a logon trigger that automatically sets
    the NHC privacy attributes. */
 CREATE OR REPLACE TRIGGER set_id_on_logon
 AFTER logon ON DATABASE
 BEGIN
    nhc_pkg.set_context;
 END;
 /
 

With this trigger, I guarantee that no one can have unrestricted access to the patient data. Let's give it a try.

I connect as Suni Maximo, a regulator:

CONNECT smaximo/smaximo
 

I'll show the context information before I try to get patient information:

SQL> exec nhc_pkg.show_context
 Type: REGULATOR
   ID: 542
 Predicate:
 state IN
    (SELECT state FROM regulator
      WHERE regulator_id = SYS_CONTEXT (
         'patient_restriction', 'person_id'))
 

Let's confirm the state in which Suni Maximo is supposed to regulate healthcare activity:

SQL> SELECT last_name, state FROM regulator;
 LAST_NAME            ST
 -------------------- --
 Halloway             IL
 Maximo               NY
 

And when I run a query against the patient table in this schema, you'll see that the predicate has been appended properly:

SQL> SELECT last_name, state FROM patient;
 LAST_NAME            ST
 -------------------- --
 Walsh                NY
 DeUrso               NY
 

Getting this code to work can be tricky; there are lots of interdependencies, and, of course, the very nature of the feature is that it automatically appends predicates to your SELECT statement. How do you watch that to see whether it's working correctly?

Here are descriptions of some of the errors I encountered and what I did to fix the code:

SQL> CONNECT csilva/csilva
 ERROR:
 ORA-04098: trigger 'SET_ID_ON_LOGON' is invalid 
            and failed re-validation
 
 Warning: You are no longer connected to ORACLE.
 

CONNECT INTERNAL/oracle
 DROP TRIGGER set_id_on_logon;
 

SELECT * FROM patient
               *
 ERROR at line 1:
 ORA-28113: policy predicate has error
 

ORA-28112: failed to execute policy function
 

Fine-grained access control, a.k.a. row-level security, is a great example of the way that the much more efficient Oracle8i engine can be leveraged to improve the quality and performance of our applications. When you've got rules, especially security-related rules, you want to bury those rules as deeply as possible within your application—preferably right alongside or connected to your data structures. FGAC gives you a way to make sure that a user can never bypass restrictions on data access. I'm sure that this feature will be enjoyed greatly by DBAs—and also increase the need for DBAs to become proficient at writing PL/SQL packages.