Apr 13

Written by: StevenFeuersteinTW
Tuesday, April 13, 2010 5:54 AM 

"Invoker rights, what's that?" you may be asking.

That wouldn't surprise me, greatly, though one might consider it a bit odd because the invoker rights feature of PL/SQL was added in Oracle8i - many years ago!

So I will first offer a brief overview of invoker rights, why you'd use it, how you use it. Then I will introduce one of the major challenges with invoker rights. Finally, I offer a utility that can help you overcome that challenge.

When you create a PL/SQL program unit, you can include an optional AUTHID clause. There are two forms of this clause:

AUTHID DEFINER
AUTHID CURRENT_USER

AUTHID DEFINER is the default. So these two procedures specify "definer rights"

PROCEDURE show_emp_count AUTHID DEFINER
PROCEDURE show_emp_count

And this version specifies "invoker rights":

PROCEDURE show_emp_count AUTHID CURRENT_USER

So that's the syntax. What does it do for you?

In many application environments, the code is owned by one schema (let's call it CODE_SCHEMA), and then privileges are granted to execute that code to other schemas (USER_SCHEMA).

When a program unit is created in CODE_SCHEMA with definer rights, then when a user connected to USER_SCHEMA runs that program, it runs under the privileges of CODE_SCHEMA.

When a program unit is created in CODE_SCHEMA with invoker rights, then when a user connected to USER_SCHEMA runs that program, it runs under the privileges of USER_SCHEMA. And all such privileges must be directly granted (no role-based privileges).

In other words, at the time an invoker rights program is executed, Oracle resolves all references to SQL-related database objects (tables and views, for the most part) according to the current user's privileges, and not the privileges of the owner of the program. And role-based privileges are used by Oracle with invoker rights programs.

I will step through a simple demonstration below. You are, however, probably wondering what invoker rights is good for. Invoker rights comes in very handy whenever you have a program that needs to work differently (with different tables) depending on who is running the code.

Consider Quest Code Tester for Oracle. This automated testing tool for PL/SQL is built around a test repository: 30+ tables and associated code. Usually, this repository is installed in a central schema, and then access to the repository is allowed through synonyms.

As a use of Quest Code Tester, I connect to my own development schema, and then call Code Tester programs to define and run my tests.

If Quest Code Tester relied on definer rights, then the test repository schema would need to have the authority to execute code in all the development schemas, and probably have directly granted privileges on underlying tables as well. That is a very risky test repository and most DBAs will not create such a powerful schema.

Instead, many of the packages in Quest Code Tester are defined as invoker rights. So when the developer asks Code Tester to run a test of the program in her schema, no special privileges are needed in the test repository schema. And Code Tester is not able to do anything to application data that the developer's code does not specifically allow.
In essence, with invoker rights, Oracle "reflects" back into the invoking schema to resolve references as you see below:

 
Now let's walk through a simple demonstration. I will first create a table in SCOTT with two rows:

CONNECT scott/tiger

CREATE TABLE authid_demo (n NUMBER)
/

BEGIN
   INSERT INTO authid_demo
   VALUES (1);

   INSERT INTO authid_demo
   VALUES (2);

   COMMIT;
END;
/

I will create the "same" table in HR, but with no rows:

CONNECT hr/hr

CREATE TABLE authid_demo (n NUMBER)
/

Then in SCOTT, I create three procedures, each of which do mostly the same thing: show the number of rows in the authid_demo table.

CREATE OR REPLACE PROCEDURE proc1 AUTHID CURRENT_USER
IS
   num   PLS_INTEGER;
BEGIN
   SELECT COUNT (*) INTO num FROM authid_demo;
   DBMS_OUTPUT.put_line ('proc 1 invoker authid_demo count = ' || num);
END;
/

CREATE OR REPLACE PROCEDURE proc2 AUTHID DEFINER
IS
   num   PLS_INTEGER;
BEGIN
   SELECT COUNT (*) INTO num FROM authid_demo;
   DBMS_OUTPUT.put_line ('proc 2 definer authid_demo count = ' || num);
   proc1;
END;
/

CREATE OR REPLACE PROCEDURE proc3 AUTHID CURRENT_USER
IS
   num   PLS_INTEGER;
BEGIN
   SELECT COUNT (*) INTO num FROM authid_demo;
   DBMS_OUTPUT.put_line ('proc 3 invoker authid_demo count = ' || num);
   proc1;
   proc2;
END;
/

Note that we have these two sequence of calls:

PROC3 -> PROC1
PROC3 -> PROC2 -> PROC1

and that PROC3 and PROC1 are invoker rights, while PROC2 is definer rights.

I then allow HR to run all this code and access the underlying table in SCOTT:

GRANT EXECUTE ON proc1 TO hr
/

GRANT EXECUTE ON proc2 TO hr
/

GRANT EXECUTE ON proc3 TO hr
/

GRANT SELECT ON scott.authid_demo TO hr
/

Finally, I run PROC3 - from HR:

BEGIN
   scott.proc3;
END;
/

Here's the output I see:

Line 1: proc 3 invoker authid_demo count = 0
Line 2: proc 1 invoker authid_demo count = 0
Line 3: proc 2 definer authid_demo count = 2
Line 4: proc 1 invoker authid_demo count = 2

Lines 1 and 2 demonstrate invoker rights at work: even though HR called SCOTT's PROC3 and PROC1 programs, the count of rows in authid_demo is retuned as 0, not 2.

Line 3 demonstrates definer rights: it shows the number of rows in SCOTT's authid_demo table, even though the program was called by HR.

But line 4 - now that is puzzling, isn't it? Notice that I got an answer of 2, not 0, for the count in the authid_demo table. Yet I called the same program (PROC1) that two lines earlier showed 2.

So: same code executes, but returns different answer. Very troubling, wouldn't you say?

Here's the problem: if at any point along the execution call stack (in this case, PROC3 -> PROC2 -> PROC1), a definer rights program is executed, then from that point on down the stack the "current user" is resolved by Oracle to be the owner of that definer rights program and not the currently connected user.

When PROC3 called PROC1, that was invoker rights calling invoker rights, and so the current user was HR and the procedure showed 0 rows.

When PROC3 called PROC2, Oracle reset "current user" to SCOTT. Then when PROC2 called PROC1, Oracle resolved the reference to the authid_demo table using SCOTT, and theprocedure therefore showed 2 rows.

This change in expected behavior can cause many problems in your application, ranging from raising "table or view does not exist" and "insufficient privileges" errors to returning or changing the wrong rows of data.

Assuming this is not the behavior you want, you need to make sure that all the program units executed in your call stack are all definer rights or all invoker rights, or at least that once you call a definer rights program, you only call other definer rights programs from that point on.

Fine advice (at least I think so), but how do you apply it? Call stacks in real applications can be very deep, easily a dozen or more entries. Plus, how can you even get hold of the call stack to analyze it?

First, you can call the DBMS_UTILITY.FORMAT_CALL_STACK at any point in your code, and Oracle will return a string that contains the call stack. Here's an example of the formatted call stack Oracle returns, taken from the Quest Code Tester log:

 

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
3B6C8C64         1  anonymous block
3FA473E8      1012  package body QCTO183.QU_RUNTIME
3FA473E8      1042  package body QCTO183.QU_RUNTIME
3FA32EF8      3120  package body QCTO183.QU_ALL_OBJECTS
3FA32EF8      3138  package body QCTO183.QU_ALL_OBJECTS
3FA12F2C      2613  package body QCTO183.QU_GENERATE
3FA12F2C      3269  package body QCTO183.QU_GENERATE
3FA12F2C      3341  package body QCTO183.QU_GENERATE
3FA12F2C      3601  package body QCTO183.QU_GENERATE
3FA12F2C      6192  package body QCTO183.QU_GENERATE
3FA12F2C      8835  package body QCTO183.QU_GENERATE
3FA12F2C      9138  package body QCTO183.QU_GENERATE
3FA12F2C      9196  package body QCTO183.QU_GENERATE
3FA12F2C      9487  package body QCTO183.QU_GENERATE
4DA12F23      9737  package body QCTO183.QU_HARNESS
3BC12F2C      9800  package body QCTO183.QU_TEST
3BC12F2C     10130  package body QCTO183.QU_TEST
3B6F83F8         4  anonymous block

 

So to follow my own advice, I would now have to open each of these package specifications and check the AUTHID setting. That's rather tedious.

Perhaps there is another way. Consider the ALL_PROCEDURES data dictionary view:

 
Notice the AUTHID column. You can look up the AUTHID setting for your program unit from this view. So it seems as though you could write a program to parse the call stack and then look up the AUTHID setting for each program unit in the stack, and make sure there are no problems with  a switch from invoker rights to definer rights, and back, in that stack.

Yes, you could do that. But likely you want, because you have a real job and a real life and very little time to write such programs.

Fortunately, I don't have a "real job" (well, believe you me, they keep me very busy here at Quest, but they also more or less let me do what I want. J ) and I sure don't have a real life. My office is at home, so the boundaries between work (PL/SQL) and non-work crumbled years ago.

Well, enough about me and my life. The bottom line is that I have created a package, called authid_analysis, available in authid_analysis.pkg file of my demo.zip, that will perform precisely this analysis on your behalf.

Here's the header of the package:

CREATE OR REPLACE PACKAGE authid_analysis
IS
   FUNCTION authid_setting (program_owner_in IN VARCHAR2
                          , program_name_in  IN VARCHAR2
                           )
      RETURN VARCHAR2;

   FUNCTION is_current_user (program_owner_in IN VARCHAR2
                           , program_name_in  IN VARCHAR2
                            )
      RETURN BOOLEAN;

   FUNCTION is_definer (program_owner_in IN VARCHAR2
                      , program_name_in  IN VARCHAR2
                       )
      RETURN BOOLEAN;

   PROCEDURE analyze_callstack (callstack_in IN VARCHAR2 DEFAULT NULL);
END authid_analysis;

You can retrieve the AUTHID setting of a program from ALL_PROCEDURES and find out if a program is invoker rights (is_current_user) or definer rights (is_definer).

Finally, call the analyze_callstack procedure to identify possible problems, as described above. If you do not pass the call stack, analyze_callstack will call the FORMAT_CALL_STACK function for you.

You can easily integrate this package into your error logging and tracing routine, making it easy to identify possible program areas in your code. I plan to add this to Quest Error Manager as soon as I have a moment.

The authid_analysis.tst scritp will allow you run a quick test of this utility. Here's what I see:

CREATE OR REPLACE PROCEDURE proc1
   AUTHID CURRENT_USER
IS
BEGIN
   authid_analysis.analyze_callstack;
END;
/

CREATE OR REPLACE PROCEDURE proc2
IS
BEGIN
   proc1;
END;
/

CREATE OR REPLACE PROCEDURE proc3
   AUTHID CURRENT_USER
IS
BEGIN
   proc2;
END;
/

BEGIN
   proc3;
END;
/
> DEFINER      package body HR.AUTHID_ANALYSIS called by...
> CURRENT_USER procedure HR.PROC1 called by...
> DEFINER      procedure HR.PROC2 called by...
> CURRENT_USER procedure HR.PROC3 called by...
>              anonymous block

As you can see, I have a switch from invoker rights to definer rights in the call to PROC2 and that will likely cause problems.

I hope you find this utility useful.