Oracle PL/SQL Programming: Guide to Oracle8i Features

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:

• Doctors can see only those patients who are assigned to their clinic.

• Regulators can see only those patients who reside in the same state.

• Patients can see only information about themselves .

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:

• I created a trigger on the LOGON system event to automatically set various context attributes. In the early stages of testing, my package was failingand as a result, I could not connect to any of my test schemas! I would simply get this error:

  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.

  What's a fella to do? What I have to do is drop the trigger so that I can recreate the package and solve my problem. So I did it this way:

  CONNECT INTERNAL/oracle DROP TRIGGER set_id_on_logon;

  and then I could get on with my test.

• If there is any kind of error in your function, you will see this error when you try to execute a query that includes the predicate:

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

  This is, obviously, a very generic error message. How do you figure out what went wrong and then fix it? The best thing to do at this point is execute the function outside the query to verify its contents. You will also want to display the various system context values. I created the nhc_pkg.show_context procedure for just this purpose.

• Your predicate function must take two string arguments for schema and object names , even if you do not use them. Otherwise, you get this error:

  ORA-28112: failed to execute policy function