I love to learn from others!

I have noticed that over the years as I spend more and more of my time holding forth (teaching classes, writing, doing presentations and seminars), my listening skills have degraded. Just ask my wife.

Fortunately, however, I still listen well enough (or selectively) to be able to learn from others.

I tell people attending my courses that if they think I say something wrong, or they have another way of doing things, to speak right up. I tell students that I learn something new in every course I give, and while that isn't really entirely true, it is true enough.

Well, I learned a very, very cool technique at OPP2007, the two day conference on PL/SQL organized by ODTUG (and I was the technical co-chair). My teacher in this case was Alex Nuijten of AMIS (whose Oracle team also won the Innovation and Community Award at OPP2007!).

OPP2007 was, by the way, a really fine event and if you write PL/SQL code you should be sure to attend the next one.

So, what did I learn? A very clever way to apply AUTHID CURRENT_USER to PL/SQL subprograms.

What the heck does that mean, you might ask. Well, AUTHID CURRENT_USER is a clause you can add to the header of your program (in the ZBI, as Bryn Llewellyn PL/SQL Product Manager, described it at OPP2007: the Zone Before the Is). It tells Oracle that when you run the program, any references to data objects (tables, views, etc.) should be resolved according to the privileges of the currently connected schema (the "invoker"), and not the owner of the program (the "definer"). That's why this feature is called "invoker rights."

But invoker rights only applies to data objects. If in your application, program A calls program B, and at the time of compilatoin B is resolved to be "APPDEV.B", then when you run A, it will call APPDEV.B, even if A is defined as AUTHID CURRENT_USER and you have your own program B in your schema.

Sorry, I know that was a rather dense explanation, but it is now 10:30 PM, I am sitting in the Admiral's Club business center at the San Fran airport, and I am tired. So that is all the explanation you get. The code below, however, should straighten things out for you.

So that's the problem: invoker rights doesn't affect the call stack of your programs.

Or does it?

The very cool technique that Alex shared during his excellent talk, Design Patterns in PL/SQL, is that you can use dynamic PL/SQL block execution to achieve the desired effect.

You can see this quite easily by running the following sequence of statements. Note that it assumes you have both the HR and SCOTT schemas present.

CONNECT hr/hr

CREATE OR REPLACE PROCEDURE show_user
AUTHID CURRENT_USER
IS
BEGIN
DBMS_OUTPUT.put_line ('HR!');
END show_user;
/

CREATE OR REPLACE PROCEDURE show_user_dynamic
AUTHID CURRENT_USER
IS
BEGIN
EXECUTE IMMEDIATE 'BEGIN show_user; END;';
END show_user_dynamic;
/

GRANT EXECUTE ON show_user_dynamic TO scott
/
GRANT EXECUTE ON show_user TO scott
/
CONNECT scott/tiger
SET serveroutput on

CREATE OR REPLACE PROCEDURE show_user
AUTHID CURRENT_USER
IS
BEGIN
DBMS_OUTPUT.put_line ('SCOTT!');
END show_user;
/

BEGIN
show_user;
hr.show_user;
hr.show_user_dynamic;
END;
/

After running this code you should see three lines of output:

SCOTT!
HR!
SCOTT!

And what it reveals is that when I call a program inside a dynamic PL/SQL block, then the invoker rights mechanism is applied to the "SQL" statement, and the reference to my program is resolved at runtime according to the privileges of the currently connected schema.

Brilliant!