3. Invoker Rights: Your Schema or Mine?

Contents:
A Look at the Definer Rights Model
The Invoker Rights Model
One Program, Multiple Schemas
Combining the Definer and Invoker Rights Models

Invoker rights refers to a new model for resolving references to database elements (tables, views, objects, programs) in a PL/SQL program unit.

Back in the "old days" of Oracle7 and Oracle 8.0 (those days, of course, in which most of us still spend our time), whenever you executed a stored program, it executed under the authority of the owner of that program. This was not a big deal if your entire application -- code, data, and users -- worked out of the same Oracle account, a scenario that probably covers about 0.5% of all Oracle shops. It proved to be a real pain in the neck for the other 99.5%, though, because code was usually stored in one schema and then shared through GRANT EXECUTE statements with other users (directly or through roles).

For one thing, that centralized, stored code would not automatically apply the privileges of a user (also known as an invoker ) to the code's objects. The user might not have had DELETE privileges on a table, but the stored code did, so delete away! Now, in some circumstances, that is just how you wanted it to work. In other situations, particularly when you were executing programs relying on the DBMS_SQL (dynamic SQL) package, awesome complications could ensue.

In Oracle 8.1, PL/SQL has now been enhanced so that at the time of compilation, you can decide whether a program (or all programs in a package) should run under the authority of the definer (the only choice in Oracle 8.0 and earlier) or of the invoker of that program.

3.1 A Look at the Definer Rights Model

Before exploring the new invoker rights feature of PL/SQL, let's review the definer rights model. You need to understand the nuances of both models, because most PL/SQL applications will very likely rely on a combination of those models.

Before a PL/SQL program can be executed from within a database instance, it must be compiled and stored in the database itself. Thus, program units are always stored within a specific schema or Oracle account. A program can reference other PL/SQL programs, database tables, and so on. These are called external references , since these database elements are not defined within the current program unit.

With the definer rights model, you should keep the following rules in mind:

Any external reference in a program unit is resolved at compile time using the directly granted privileges of the schema in which the program unit is compiled.
Database roles are ignored completely at compile time.
Whenever you run a program compiled with the definer rights model (the default), it executes under the authority of the schema that owns the program.
Although direct grants are needed to compile a program, you can rely on grants of EXECUTE authority to give other schemas the ability to run your program.

Figure 3.1 shows how you can use the definer rights model to control access to underlying data objects. All the order entry data is stored in the OEData schema. All the order entry code is defined in the OECode schema. OECode has been granted the direct privileges necessary to compile the Order_Mgt package, which allows you to both place and cancel orders.

Figure 3.1: Controlling access to data with the definer rights model

To make sure that the orders table is updated properly, no direct access (either via roles or privileges) is allowed to that table through any schema other than OECode. Suppose, for example, that the Sam_Sales schema needs to run through all the outstanding orders and close out old ones. Sam will not be able to issue a DELETE statement from the Close_Old_Orders procedure. Instead, Sam will have to call Order_Mgt.cancel to get the job done.

3.1.1 The Advantages of Definer Rights

There are certainly situations that cry out for (and are handled well by) the definer rights model. With definer rights:

You are better able to control access to underlying data structures. You can guarantee that the only way the contents of a table can be changed is by going through a specific programmatic interface (usually a package).
Application performance improves, because the PL/SQL engine does not have to perform checks at runtime to determine if you have the appropriate privileges or -- just as important -- which object you should actually be manipulating (my accounts table may be quite different from yours!).
You don't have to worry about manipulating the wrong table. With definer rights, your code will work with the same data structure you would be accessing directly in SQL in your SQL*Plus (or other execution) environment. It is simply more intuitive.

But there are problems with the definer rights model as well. These are explored in the next sections.

3.1.2 Where'd My Table Go?

Let's see what all those definer rights rules can mean to a PL/SQL developer on a day-to-day basis. In many database instances, developers write code against tables and views that are owned by other schemas, with public synonyms created for them to hide the schema. Privileges are then granted via database roles.

This very common setup can result in some frustrating experiences. Suppose that my organization relies on roles to grant access to objects. I am working with a table called accounts, and can execute this query without any problem in SQL*Plus:

SQL> SELECT account#, name FROM accounts;

Yet, when I try to use that same table (same query, even) inside a procedure, I get an error:

SQL> CREATE OR REPLACE PROCEDURE show_accounts   2  IS   3  BEGIN   4     FOR rec IN (SELECT account#, name FROM accounts)   5     LOOP   6        DBMS_OUTPUT.PUT_LINE (rec.name);   7     END LOOP;   8  END;   9  /  Warning: Procedure created with compilation errors.  SQL> sho err Errors for PROCEDURE SHOW_ACCOUNTS:  LINE/COL ERROR -------- ------------------------------------------------------ 4/16     PL/SQL: SQL Statement ignored 4/43     PLS-00201: identifier 'ACCOUNTS' must be declared

This doesn't make any sense . . . or does it? The problem is that accounts is actually owned by another schema; I was unknowingly relying on a synonym and roles to get at the data. So if you are ever faced with this seemingly contradictory situation, don't bang your head against the wall in frustration. Instead, obtain the directly granted privileges you require to get the job done.

3.1.3 How Do I Maintain All That Code?

Suppose that my database instance is set up with a separate schema for each of the regional offices in my company. I build a large body of code that each office uses to analyze and maintain its data. Everybody has the same sets of tables, but the data is different.

Now, I would like to install this code so that I spend the absolute minimum amount of time and effort setting up and maintaining the application. The way to do that would be to install the code in one schema and share that code among all the regional office schemas.

With the definer rights model, unfortunately, this goal and architecture would be impossible to achieve. If I install the code in a central schema and grant EXECUTE authority to all regional schemas, then all those offices will be working with whatever set of tables is accessible to the central schema (perhaps one particular regional office or, more likely, a dummy set of tables). That's no good. I must instead install this body of code in each separate regional schema, as shown in Figure 3.2 .

Figure 3.2: Repetitive installations of code needed with definer rights

The result is a maintenance and enhancement nightmare. Perhaps invoker rights will give us new options for a better solution.

3.1.4 Dynamic SQL and Definer Rights

Another common source of confusion with definer rights occurs when using dynamic SQL. Suppose I create a generic "run DDL" program using DBMS_SQL as follows:

/* Filename on companion disk: runddl.sp */ CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2) IS    cur INTEGER:= DBMS_SQL.OPEN_CURSOR;    fdbk INTEGER; BEGIN    DBMS_SQL.PARSE (cur, ddl_in, DBMS_SQL.NATIVE);    fdbk := DBMS_SQL.EXECUTE (cur);    DBMS_SQL.CLOSE_CURSOR (cur); EXCEPTION    WHEN OTHERS    THEN          DBMS_OUTPUT.PUT_LINE (          'RunDDL Failure on ' || ddl_in);       DBMS_OUTPUT.PUT_LINE (SQLERRM);       DBMS_SQL.CLOSE_CURSOR (cur); END; /

Now, dynamic SQL can be tricky stuff (notice the exception handler that closes the cursor instead of leaving it hanging open -- we often forget housekeeping like this). So I decide to share this neat utility (after testing it in my schema with outstanding results) with everyone else in my development organization. I compile it into the COMMON schema, where all reusable code is managed, grant EXECUTE to public, and create a public synonym. Then I send out an email announcing its availability.

A few weeks later, I start getting calls from my coworkers. "Steven, I asked it to create a table and it ran without any errors, but I don't have the table." "Steven, I asked it to drop my table, and runddl said that there is no such table. But I can do a DESCRIBE on it." "Steven. . . ." Well, you get the idea. I begin to have serious doubts about sharing my code with other people. Sheesh, if they can't use something as simple as runddl without screwing things up . . . but I decide to withhold judgment and do some research.

I log into the COMMON schema and find that, sure enough, all of the objects people were trying to create or drop or alter were sitting here in COMMON. And then it dawns on me: unless a user of runddl specifies his own schema when he asks to create a table, the results will be most unexpected.

In other words, this call to runddl:

SQL> exec runddl ('create table newone (rightnow DATE)');

would create the newone table in the COMMON schema. And this call to runddl:

SQL> exec runddl ('create table scott.newone (rightnow DATE)');

might solve the problem, but would fail with the following error:

ORA-01031: insufficient privileges

unless I grant CREATE ANY TABLE to the COMMON schema. Yikes . . . my attempt to share a useful piece of code got very complicated very fast! It sure would be nice to let people run the runddl procedure under their own authority and not that of COMMON, without having to install multiple copies of the code.