3.4 Combining the Definer and Invoker Rights Models

Invoker rights programs allow central code to reflect back to the calling schema. Definer rights programs allow remote schemas to access local data (i.e., data in the same schema as the program). Many applications require a combination of these flavors.

Suppose, for example, that the national Stolen Lives Project also maintains a table of "perpetrators," law enforcement officers who have killed one or more people in the United States. Due to the sensitivity of the information, the SLP has decided to maintain a single headquarters table that cannot be accessed directly by the city/town schemas. Yet both the location-specific stolen_life table and the systemwide perpetrators table need to be accessed by the check_city_statistics procedure.

What's a code architect to do? One thought might be to create a public synonym for the perpetrators table and make sure that no city schema has its own perpetrators table. When the city schema runs the central code under invoker rights, the reference to perpetrators would, in fact, be to that central source of data.

That works fine for the check_city_statistics procedure, but what about the rest of the application? With this approach, any city schema can directly access the perpetrators table, a violation of security. So the synonyms solution is no solution at all.

With Oracle 8.1, however, you don't need to do anything more than introduce a layer of code around the shared data structure. You need to do at least that, however, so that you can change the model used for resolving external references.

If the perpetrators table is accessed directly by the check_city_statistics procedure, the reference can only be resolved by the city schema's having direct access (via a synonym) to the table, which is a no-no. The check_city_statistics procedure cannot, therefore, query the perpetrators table directly. Instead, as shown in Figure 3.5 , it will call another procedure, compiled under the definer rights model, which, in turn, works with the perpetrators table.

Figure 3.5: Combination of definer and invoker rights model

The authid4.sql file provides an implementation that reflects this blended approach. It creates a separate procedure, show_perps, to access the perpetrators table:

/* Filename on companion disk: authid4.sql */
CREATE OR REPLACE PROCEDURE show_perps (loc IN VARCHAR2)
  AUTHID DEFINER
AS
BEGIN
   FOR rec IN (SELECT * FROM perpetrator WHERE location = loc)
   LOOP
      pl (loc || ' perpetrator is ' || rec.rank || ' ' || rec.name);
   END LOOP;
END;
/

After granting PUBLIC access to this procedure (but not to the table), I modify show_descriptions to include this information:

CREATE OR REPLACE PROCEDURE show_descriptions
  AUTHID CURRENT_USER
AS
BEGIN
  HQ.show_perps (USER);
  
  pl ('');
  
  FOR lifestolen IN (SELECT * FROM stolen_life)
  LOOP
     show_victim (lifestolen);
  END LOOP;
END;
/

The Chicago group can then access a full array of information:

SQL> exec show_descriptions
CHICAGO perpetrator is Sergeant Tim "BigBoy" Cop
CHICAGO perpetrator is Commander John Burge

Bernard Solomon

After years of police harassment, Solomon was told by police that they 
would kill him. A few days later he was arrested. He was found hung in 
his cell at the 2259 S. Damen police station. Although police claim he 
hung himself with his shirt, when his body was examined by family 
members, he was found still wearing his shirt on one arm.

even though Chicago cannot directly access this table:

SQL> connect chicago/chicago
Connected.
SQL> select * from hq.perpetrators;
select * from hq.perpetrators
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

The authid5.sql file on the disk offers a somewhat simpler example of this same basic technique.

By offering both the definer and invoker rights models in Oracle8 i , Oracle demonstrates its continuing commitment to the PL/SQL language. As we move to the more distributed model of the Internet (and not just distributed data -- distributed everything ), PL/SQL needs to become more flexible in the way that it lets us build and execute our code.

The invoker rights model gives all of us another tool to use as we construct our applications. By coming up with a simple syntax for applying this model, Oracle makes it easier for us to learn and implement this new approach.