Disadvantages of definer rights

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

20.6.1.2.1 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 databases, 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 (and the 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 errErrors for PROCEDURE SHOW_ACCOUNTS: LINE/COL ERROR-------- ------------------------------------------------------4/16 PL/SQL: SQL Statement ignored4/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, ask the owner of the object or the DBA to grant you the privileges you require to get the job done.

20.6.1.2.2 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. Each schema has its own tables with the same structure but different data. (Yes, I know that this is rarely a good design, but please suspend your disbelief temporarily.)

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 is 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 are 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 20-12.