Oracle PL/SQL Programming Guide to Oracle 8i Features

Oracle PL/SQL Programming Guide to Oracle 8i FeaturesSearch this book
Previous: 8.4 SYS_CONTEXT and LIST_CONTEXT: Obtaining Context InformationChapter 8
Deploying Fine-Grained Access Control
Next: 9. Calling Java from PL/SQL
 

8.5 A Complete FGAC Example

To illustrate the steps you would follow to take advantage of fine-grained access control, I am going to share with you one of my dearest dreams. The year is 2010. A massive, popular uprising has forced the establishment of a national health care system. No more for-profit hospitals pulling billions of dollars out of the system; no more private insurance companies soaking up 30 cents on the dollar; all children are vaccinated; all pregnant women receive excellent prenatal care.

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 on the companion disk for all the DDL statements and subsequent commands in this example section):

/* Filename on companion disk: fgac.sql */
CREATE TABLE patient  (   
   patient_id NUMBER,   
   schema_name VARCHAR2(30),   
   last_name VARCHAR2(100),   
   first_name VARCHAR2(100),   
   dob DATE,
   home_clinic_id INTEGER,
   state CHAR(2)
   ); 

CREATE TABLE clinic (  
   clinic_id INTEGER,  
   name VARCHAR2(100),  
   state CHAR(2)  
  );  

CREATE TABLE doctor (  
   doctor_id NUMBER,  
   schema_name VARCHAR2(30),  
   last_name VARCHAR2(100), 
   first_name VARCHAR2(100), 
   home_clinic_id INTEGER 
   );  

CREATE TABLE regulator (
   regulator_id NUMBER,
   schema_name VARCHAR2(30),
   last_name VARCHAR2(100),
   first_name VARCHAR2(100),
   state CHAR(2)
   );   

We also insist on privacy. So here are the following rules that I am going to enforce with FGAC:

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

SELECT * FROM patient;

and only see her patients at the clinic. 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 will see only her row. "Same" query, different results, processed transparently with FGAC.

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

  1. Create all of the data structures and data in a central schema (SCOTT in the demonstration).

  2. Create separate schemas for each of the doctors, regulators, and patients.

  3. Create an application context for SCOTT that associates the named context with a package. This package will contain all the logic rules I need to enforce patient privacy.

  4. Create the package, which will be called nhc_pkg (National Health Care package). Make it publicly available (owned by SCOTT). The package allows me to define a predicate for the patient table, but also to set and verify the context information for any schema.

  5. Define an FGAC policy through DBMS_RLS that associates the patient table with the predicate-generating function.

  6. Create a database trigger on the system LOGON event so that every time a user connects to the database, her context will be set, guaranteeing privacy.

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

8.5.1 Creating the Security Package

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. Here is the National Health Care package specification:

/* Filename on companion disk: fgac.sql */
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 is 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 is the output from this procedure when run, for example, from the schema of Sandra Wallace, a doctor (see the 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'))

8.5.2 Setting the Context for the Schema

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

8.5.3 Defining the Predicate

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 will see later in this function's implementation, the schema_in and name_in parameters are not used at all. I still must include these parameters in the parameter list if it is 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 is 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 is not set to one of the values just discussed, I've identified someone outside of the health care system entirely, so I refuse access to any patient information:

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

and then return the predicate:

   RETURN retval;
END person_predicate; 

8.5.4 Defining the Policy

I still need to register the security policy (that is, 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.

8.5.5 Setting Up the LOGON Trigger

Now all 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 health care activity:

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

When I run a query against the patient table in this schema, we see that the predicate has been appended properly:

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

8.5.6 Debugging FGAC Code

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 if it is working correctly?

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


Previous: 8.4 SYS_CONTEXT and LIST_CONTEXT: Obtaining Context InformationOracle PL/SQL Programming Guide to Oracle 8i FeaturesNext: 9. Calling Java from PL/SQL
8.4 SYS_CONTEXT and LIST_CONTEXT: Obtaining Context InformationBook Index9. Calling Java from PL/SQL

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
This HTML Help has been published using the chm2web software.