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:
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).
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'))
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;
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;
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.
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:
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'))
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
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:
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.