You've Got to Fight for Your Invoker's Rights

This post is about a PL/SQL feature that doesn't get enough respect, "invoker's rights".

First off, what's its real name? Depending on the source, you'll see the feature name spelled "invoker's rights", "invokers' rights", or "invoker rights". That makes a difference -- you'll get different results in Google depending on what combination of singular, plural, and possessive you use. And to be strictly correct, shouldn't you hyphenate the adjective form, that is, refer to things like "invoker's-rights subprograms"? I'm not even going to go there. Although I personally call the whole feature "invoker's rights" to agree with the PL/SQL manual, I'll try to make it through the rest of the post without using that phrase at all.

After all that, the syntax for the feature is AUTHID CURRENT_USER. Although there is an opposite AUTHID DEFINER clause, since that's the default, you would probably only ever use the CURRENT_USER form of the clause. It might get more love (and be easier to search for) if we called them "CURRENT_USER subprograms" or some such.

The mechanics of this feature are relatively easy to see. You can find the details in the PL/SQL manual, or get a tutorial that points out some of the nuances, or this Steven Feuerstein article with some best practices.

But still, how does that play out in the real world?

Well, you may have a PL/SQL application that goes through several versions, with each version in a different schema on the same database server -- MYAPPV1, MYAPPV2, MYAPPV3, etc. Or maybe there are slightly different incarnations of the app for different business groups. When you make a fix or improvement to one procedure or function, if that change is applicable for the older or alternate versions, you need to recompile the procedure or function in each schema. If program units that needed periodic upgrades were put into a central schema and declared with AUTHID CURRENT_USER, making the change in one place would propagate the improvements to all versions of the application. You could hardcode the central schema name in all calls to the CURRENT_USER subprograms, or create synonyms and pretend they're in the same schema as the rest of the code.

The trick then would be to identify which procedures and functions are the best candidates for this treatment. Logically, they should be small simple subprograms that have relatively few dependencies, so they won't break if your application gains or loses tables, columns, or other subprograms as it evolves. They should also be subprograms that you could predict would be important to fix or upgrade in the future -- ones that could give a big speedup when you learn some tuning technique or use some feature in the latest database release; ones that implement security checks that you'll make more stringent as security practices evolve; ones that display common UI elements that you can make more usable and accessible over time.

Of course, this type of foresight is easier said than done. Sure, just take all your slowest, buggiest subprograms with the worst output, and separate them out. But you might be able to retrofit such changes at a reasonable point. I'd suggest evaluating whether you could make use of AUTHID CURRENT_USER around the time of the 3rd instance or version of the application on the same server.