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.