3.3 One Program, Multiple Schemas

You have the syntax down and we've covered some rules. Let's try out the new AUTHID clause in a more elaborate example.

A couple of years ago, a group of people started the Stolen Lives Project. The objective of this project is to document the lives "stolen" from families and communities when people are killed by law enforcement officers. I will use this project as the basis for my example in this section.

TIP: Neither the Stolen Lives Project nor I believe that all law enforcement officers commit acts of brutality. I also recognize that some killings by officers are justifiable, and that many, if not the vast majority of, officers are committed to improving the lives and guaranteeing the safety of all the citizens in their jurisdiction.

You can get lots more information about the Stolen Lives Project (hereafter referred to as SLP) at the following URL:

http://www.unstoppable.com/22/english/stolenlivesPROJECT

Suppose then that there is a national headquarters for the SLP, and that Headquarters maintains an Oracle database with a separate schema for each city and town in which information is being collected by local law enforcement brutality activists. Each schema has its own stolen_life table, but all schemas perform the same analyses on this information.

The DBA/developer for SLP, Salinda, has upgraded recently to 8.1 and sees an immediate opportunity for the AUTHID feature. She would like to maintain all the code in one place, but when activists in a given city run that code for their locale, they see their data and analyses. Figure 3.4 shows the architecture Salinda wants to implement.

Figure 3.4: One program analyzes data in different tables

TIP: I do not show all the statements needed to set up the various schemas in Oracle; you will find them, however, in the authid.ins file on the companion disk.

Salinda connects to the HQ schema and creates the following objects. First, a stolen_life table that contains just one row, indicating that you shouldn't look here for data:

/* Filename on companion disk: authid.hql */
CREATE TABLE stolen_life (
   dod DATE,
   ethnicity VARCHAR2(100),
   victim VARCHAR2(100),
   age NUMBER,
   description VARCHAR2(2000)
   );

INSERT INTO stolen_life (dod, ethnicity, victim, age, description) VALUES (
   SYSDATE, 'N/A', 'HQ Table', 0,
   'All information is stored in city tables.');

As you will see, Salinda needs to have this table in the schema so that she can compile the stored program units that will be shared throughout all the local schemas.

Next, she creates two programs, only the second of which specifies invoker rights. First, a program to display information about a life stolen:

/* Create a display program, run as DEFINER. */
CREATE OR REPLACE PROCEDURE show_victim (
   stolen_life IN stolen_life%ROWTYPE
   )
AS
BEGIN
   DBMS_OUTPUT.PUT_LINE (stolen_life.victim);
   DBMS_OUTPUT.PUT_LINE ('');
   DBMS_OUTPUT.PUT_LINE (stolen_life.description);
   DBMS_OUTPUT.PUT_LINE ('');
END;
/

And then the analysis program (which, in this case, simply displays the victims for a location):

CREATE OR REPLACE PROCEDURE show_descriptions
  AUTHID CURRENT_USER
AS
BEGIN
  FOR lifestolen IN (SELECT * FROM stolen_life)
  LOOP
     show_victim (lifestolen);
  END LOOP;
END;
/

Both of these programs compile by resolving the reference to stolen_life against the all-but-empty HQ table. Since these programs are to be used by all, Salinda then performs the necessary grants:

GRANT EXECUTE ON show_descriptions TO PUBLIC;
DROP PUBLIC SYNONYM show_descriptions;
CREATE PUBLIC SYNONYM show_descriptions FOR show_descriptions;

All of the above statements can be found, by the way, in the authid.hq1 file.

Once the centralized objects are in place, Salinda can now get everything defined in the local (city/town) schemas. Remember that the objective in this architecture is to store all of the code in one schema; the only step Salinda should have to take in her local schemas, therefore, is to create the stolen_life table specific to that locale. Here are the steps for Chicago. For these steps see authid.chi -- and note that all data shown is taken from the web site and reflects real-world tragedy:

/* Filename on companion disk: authid.chi */

DROP TABLE stolen_life;

CREATE TABLE stolen_life (
   dod DATE,
   ethnicity VARCHAR2(100),
   victim VARCHAR2(100),
   age NUMBER,
   description VARCHAR2(2000),
   moreinfoat VARCHAR(200) DEFAULT
      'http://www.unstoppable.com/22/english/stolenlivesPROJECT'
   );

Now that the table is created, Salinda populates it with just a tiny fragment of all the broken lives you will find on the web site. I will show just a single entry in the text:

INSERT INTO stolen_life (dod, ethnicity, victim, age, description) VALUES (
   '23-OCT-96', 'Puerto Rican', 'Angel Castro, Jr.', 15,
   'After being beaten, abused with racial epithets and told by police 
that he would be killed if he did not move, Angel Castro's family moved. 
Angel returned to the neighborhood for a friend's birthday party. After 
leaving the party, a police car rammed him as he rode his bike. As Angel 
tried to get on his knees, the police shot and killed him');

Salinda also performs the same steps for New York City; see the authid.ny file for all the details. Now let's see how well it all works.

I connect to the NY schema and show the stolen lives:

SQL> CONNECT newyork/newyork
SQL> set serveroutput on size 1000000 format wrapped
SQL> exec show_descriptions
Amadou Diallo

Shot 19 times by four police officers outside his Bronx apartment. Diallo 
was a devout Muslim working 12 hour days selling CDs and tapes to earn 
money to finish his bachelor's degree. He was unarmed.
...

And when I connect to CHICAGO, I see different information:

SQL> CONNECT chicago/chicago
SQL> set serveroutput on size 1000000 format wrapped
SQL> exec show_descriptions
Angel Castro, Jr.

After being beaten, abused with racial epithets and told by police that he
would be killed if he did not move, Angel Castro's family moved. Angel
returned to the neighborhood for a friend's birthday party. After leaving the
party, a police car rammed him as he rode his bike. As Angel tried to get on
his knees, the police shot and killed him.
...

Perfectly abominable behavior, but perfect implementation of shared code and nonshared data!

Let's just verify that Salinda did need the AUTHID clause to get things to work right. Suppose she goes back to the HQ account and recompiles the show_descriptions procedure, this time with the default rights model:

CREATE OR REPLACE PROCEDURE show_descriptions
  AUTHID DEFINER -- The default
AS
BEGIN
  FOR lifestolen IN (SELECT * FROM stolen_life)
  LOOP
     show_victim (lifestolen);
  END LOOP;
END;
/

Then when activists connect to the CHICAGO and NY schemas to see their data, this is all they see:

SQL> exec show_descriptions
HQ Table

All information is stored in city tables.

Clearly, without the help of the invoker rights model, Salinda could not achieve her design objectives. With AUTHID CURRENT_USER, though, it will be easier for the Stolen Lives Project to inform the American public about this issue.