Oracle PL/SQL Programming, 4th Edition Available!

On August 23, 2005, O'Reilly Media started shipping the 4th Edition of Oracle PL/SQL Programming to the bookstores. Regarding this book, I have good news and bad news. First, the good news: it is the best edition ever. We fixed another batch of the seemingly endless minor typos and other such mistakes. We added coverage of all new PL/SQL features up through and including Oracle Database 10g Release 2. We also put in entirely new chapters on security, globalization/localization, and PL/SQL IO (file, email, internet and screen). And now the bad news: the page count including index is 1,171. Edition 3's page count was "only" 989. Sigh. I really wanted to keep the page count under 1,000. But I do believe the added value of that new count will more than outweigh the additional weight. So order your copy now!

I am just back from a week in Scandinavia and the Netherlands, doing full-day seminars for Quest. Now I am home for a whole week before heading out to Oracle World. I will be presenting on Sunday and Monday, with expert panels on Monday and Tuesday. I will be doing book signings at the Quest booth and at the conference bookstore. So hopefully if you are coming, I will see you there!

Hey, don't forget!

>> OOW is great, but it covers all Oracle technologies and actually pays relatively little attention to PL/SQL. I invite you to join me on November 2-3, 2005 for a two-day PL/SQL-only conference: Oracle PL/SQL Programming 2005

>> Help influence the future of the PL/SQL language by visiting and contributing to the 21st Century PL/SQL Idea Collector.

>> Check out Qnxo, www.qnxo.com, which you can use to quickly generate a PL/SQL code library customized to your own database objects. We just released version 1.3, which includes Codebar, a very cool "coding toolbar" that you can use from your favorite editor to instantly access your Qnxo-generated code library. Qnxo and Codebar will totally transform the way you write PL/SQL applications! And we are working hard now on adding groundbreaking unit testing features: you will be able to specify the test cases through the Qnxo Unit Tester (Qut) interface and then generate utPLSQL-compatible unit test code.

Tip of the Month: Confirming Invoker Rights State

Oracle8i Database introduced the AUTHID clause for procedures, functions and packages. When set to AUTHID DEFINER (the default), then your program runs under "definer rights." This means that any references to data objects (such as tables and views) are resolved at compile time, based on the directly granted privileges of the definer or owner of the program. Roles are ignored. If, on the other hand, you set the clause to AUTHID CURRENT_USER, then any references to data objects are resolved at run time, based on the privileges of the currently-connected schema. And (the DBAs have got to love this) role-based privileges are now applied.

Invoker rights comes in very handy when your application architecture requires that you have multiple schemas with the same table structures, but you don't want to maintain multiple copies of your code base. It is also extremely important to use in all stored programs that contain dynamic SQL -- if you want to make sure that the dynamically contructed and executed SQL statement runs in the currently connected schema.

Suppose for example that I have created a utility that reads the contents of a file and then executes it as a DDL statement. In fact, I have created such a program and it is described (with link) in the Download section. The header of the program looks like this:

PROCEDURE exec_ddl_from_file (
 dir_in IN VARCHAR2
 , file_in IN VARCHAR2
) 
IS ...

That is, you pass in the location and name of the file, and I execute its contents for you. For example, you can use this program to compile programs from file. Now suppose I want all my developers to be able to use this handy utility. I could do the following:

GRANT EXECUTE ON exec_ddl_from_file TO PUBLIC
/
CREATE PUBLIC SYNONYM exec_ddl_from_file FOR exec_ddl_from_file
/

So everyone can have at it! Now suppose that SCOTT is the owner of exec_ddl_from_file. If the HR schema then runs this program to execute a file that contains a "CREATE TABLE MY_TABLE", HR will create this table in the SCOTT schema. Not exactly what was desired. All I have to do, though, is add AUTHID CURRENT_USER to header and then HR will create the table in her own schema, not that of SCOTT:

PROCEDURE exec_ddl_from_file (
 dir_in IN VARCHAR2
 , file_in IN VARCHAR2
) AUTHID CURRENT_USER
IS ...

So that's the idea behind the AUTHID clause and invoker rights. There is, unfortunately, another complication that lies at the heart of this month's tip: if definer rights program DEFPROG (with AUTHID DEFINER) calls an invoker rights program INVPROG (with AUTHID CURRENT_USER), then the invoker rights program will be run with "current user" set to the definer/owner of DEFPROG. That is, the currently-connected schema will be ignored. (Check out the invdefinv.sql script in the Download section for an example of this behavior).

There is, in other words, no way to guarantee that your invoker rights program will indeed be run under the invoker's privileges. It depends on the call stack and the AUTHID setting of previous programs in the stack. Too bad....

In the case of a program like exec_ddl_from_file, I really don't want it to even run if it is not running under the authority of the currently-connected schema. Fortunately, there is a way to to this. I can take advantage the SYS_CONTEXT built-in to obtain information about my "user environment" by specifying the "USERENV" context. I can then ask for the name of the session user and compare it to that of the current user. If they are the same, I can proceed. If not, then I terminate my program. Here is an example of applying this logic:

CREATE OR REPLACE PROCEDURE exec_ddl_from_file (
 dir_in IN VARCHAR2
 , file_in IN VARCHAR2
)
AUTHID CURRENT_USER
IS
 FUNCTION invoker_rights_mode RETURN BOOLEAN
 IS
 -- Original idea from Solomon Yakobson
 v_retval NUMBER;
 BEGIN
 RETURN SYS_CONTEXT ( 'USERENV', 'SESSION_USER' ) =
 SYS_CONTEXT ( 'USERENV', 'CURRENT_USER' );
 END;
 ...
BEGIN
 IF ( NOT invoker_rights_mode )
 THEN
 raise_application_error
 ( -20999
 , 'Exec DDL from file must run under invoker privileges!'
 );
 END IF;

Useful Code of the Month: Exec DDL from File

The exec_ddl_from_file program will execute the contents of your file as a DDL statement. This means that you may not use any bind variables, nor may you return information from the statement that is executed dynamically. But you can use this program to read in a file (using UTL_FILE) that contains, say, the CREATE OR REPLACE PACKAGE definition of a package, and create that package. Hopefully you will find such a utility handy, but I wrote it mostly to demonstrate how you can use DBMS_SQL.PARSE to execute arbitrarily large dynamic SQL strings. EXECUTE IMMEDIATE of Native Dynamic SQL fame is limited to 32K characters. Not so with DBMS_SQL. So here are the files in the download:

exec_ddl_from_file.sql - creates a procedure that executes the contents of a file. You will need to have UTL_FILE configured to get this program to run properly.

exec_ddl_from_file2.sql - creates a procedure that executes the contents of a file and includes the logic required to verify that you are indeed running under invoker rights. You will need to have UTL_FILE configured to get this program to run properly.

invdefinv.sql - a script that demonstrates how when a definer rights program calls an invoker rights program, the current user for that invoker rights program is set to the owner of the definer rights program. It also shows the usefulness of the DBMS_UTILITY.FORMAT_CALL_STACK, which reveals the PL/SQL call stack.

Click here to download the zip of these files.

Upcoming public trainings

As mentioned above, I will be at Oracle Open World the week of September 19 and the week of October 4 I will be over in the United Kingdom (trainings sponsored by Quest). Hope to see you at one of those!