DEVELOPER: PL/SQL Practices

On Invokers and Outs

By Steven Feuerstein
 

Best practices for invoker rights and functions

I have made lots of use of the AUTHID CURRENT_USER (invoker rights) clause lately. I frequently write utilities for other developers on my team, and I define them in a central schema named SHARED_CODE. When I define a program with invoker rights, all developers can call that program and it will automatically perform the desired operations on that developer's own schema objects. But recently I ran into a problem with invoker rights. On our next project, we are going to be working a lot with operating system files via UTL_FILE. In particular, we will be writing text out to files, and that text is going to be formatted in various different ways. So I decided I would write a "write to file" engine that would do all the UTL_FILE operations but let the users specify the formatting by calling their own functions. That way they would have maximum flexibility to do that formatting. But it doesn't seem to work. I define my "write to file" program with invoker rights, but it refuses to call the invoker's formatting routine. What am I doing wrong?

You aren't doing anything wrong. Instead, you ran straight into a restriction on how invoker rights works. Fortunately, I can help you work around that restriction and get to the end point you desire.

Generally, what you need to be able to do is write a generic program that issues a callback to a subprogram whose name you do not know when you write your program. Thus, your program needs to figure out at runtime which program to call.

One way to implement runtime callbacks is with dynamic polymorphism and Oracle object types. That is a rather advanced topic that will need to be addressed another day. In this answer, I show how you can use runtime callbacks and invoker rights to achieve the desired result.

Let's start with some background on invoker rights, which applies to all runtime SQL statements (both static and dynamic) executed within a PL/SQL unit. With invoker rights, both name resolution and computation of relevant privileges occur at runtime (and for the latter, roles are recognized and used to compute those privileges). Invoker rights affects the following statements: 

• SELECT, INSERT, UPDATE, and DELETE data manipulation statements

• The LOCK TABLE transaction control statement

• OPEN and OPEN-FOR cursor control statements

• All dynamic SQL statements (EXECUTE IMMEDIATE, OPEN FOR, and DBMS_SQL)

Suppose I include the AUTHID CURRENT_USER clause in the header of my program. When I compile that program, the current schema will still need directly granted privileges on all objects the program references.

Then I grant authority to other schemas to execute my program. When a user connected to one of those schemas calls my program, references to database objects in any of the above statements will be resolved according to the privileges (direct or granted through roles) of the currently connected schema.

One obvious conclusion that must be drawn from this is that the AUTHID CURRENT_USER clause will not be applied to static invocations of other subprograms you make within the invoker rights programs.

A static invocation of a program is one that is resolved at compile time. For example, in the following block of code, proc1 calls proc2 statically. 

PROCEDURE proc1
IS
BEGIN
   proc2 ();
END proc1;

 

When I compile proc1 in schema ABC, Oracle Database resolves the reference to proc2 according to the privileges of that schema. Now suppose that your database finds a schema-level procedure named proc2 defined in ABC. I grant EXECUTE authority on ABC.proc1 to another schema, such as DEF, which has its own procedure called proc2. When a user connected to DEF executes ABC.proc1, that user will always run ABC.proc2, not DEF.proc2.

So does that mean that it's impossible to create a working "write to file" program with invoker rights? Not at all. You can , in fact, instruct Oracle to dynamically change which program it calls at runtime, but to do this, you need to take advantage of dynamic PL/SQL.

Let's look at two simple demonstrations of the impact of invoker rights, and then I will demonstrate the dynamic PL/SQL "workaround."

First, let's consider AUTHID CURRENT USER with data objects. In Listing 1, I create an invoker rights procedure, show_my_data, in the HR schema to show the number of rows in the my_data table (10). I grant the EXECUTE privilege to SCOTT to run that program. SCOTT also has a my_data table, with just 1 row. When SCOTT runs HR.show_my_data, the program displays 1 and not 10.

Code Listing 1: Create and run show_my_data 

SQL> CONNECT hr/hr
Connected.

SQL> CREATE TABLE my_data (VALUE NUMBER)
  2    /

Table created.

SQL> BEGIN
  2       FOR indx IN 1 .. 10
  3       LOOP
  4           INSERT INTO my_data
  5                   VALUES (indx);
  6       END LOOP;
  7  
  8       COMMIT;
  9    END;
 10    /

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE PROCEDURE show_my_data
  2    AUTHID CURRENT_USER
  3    IS
  4       num   PLS_INTEGER;
  5    BEGIN
  6       SELECT COUNT (*)
  7         INTO num
  8         FROM my_data;
  9  
 10       DBMS_OUTPUT.put_line ('Count of my_data = ' || num);
 11   END show_my_data;
 12   /

Procedure created.

SQL> GRANT EXECUTE ON show_my_data TO scott
  2    /

Grant succeeded.

SQL> CONNECT scott/tiger
Connected.

SQL> CREATE TABLE my_data (VALUE NUMBER)
  2    /

Table created.

SQL> BEGIN
  2       INSERT INTO my_data
  3            VALUES (1);
  4  
  5       COMMIT;
  6   END;
  7   /

PL/SQL procedure successfully completed.

SQL> CALL hr.show_my_data();
Count of my_data = 1

 

As you can see, even though I ran HR's show_my_data procedure, that program showed me the count of SCOTT's my_data table.

But now let's try to create a "write to file" program. First, I set up a database directory owned by HR and create the "default" formatting function (uppercase the text): 

CONNECT hr/hr

CREATE DIRECTORY temp AS 'c:\temp'
/
GRANT WRITE ON DIRECTORY temp 
TO scott;
/

CREATE OR REPLACE FUNCTION 
format_line (line_in IN VARCHAR2)
   RETURN VARCHAR2
IS
BEGIN
   RETURN UPPER (line_in);
END format_line;
/

 

Then I build a very simple program, shown in Listing 2, that simulates "write to file" functionality, and I make sure that SCOTT can call it.

Code Listing 2: Create write_to_file 

CREATE OR REPLACE PROCEDURE write_to_file (
   dir_in IN VARCHAR2
 , file_name_in IN VARCHAR2
 , lines_in IN DBMS_SQL.varchar2s
)
AUTHID CURRENT_USER
IS
    l_file   UTL_FILE.file_type;
BEGIN
    l_file :=
        UTL_FILE.fopen (LOCATION            => dir_in
                                , filename           => file_name_in
                                , open_mode       => 'W'
                                , max_linesize     => 32767
                                 );

    FOR indx IN 1 .. lines_in.COUNT
    LOOP
        UTL_FILE.put_line (l_file, format_line (lines_in (indx)));
    END LOOP;

    UTL_FILE.fclose (l_file);
END write_to_file;
/
GRANT EXECUTE ON write_to_file TO scott
/

 

I then connect as SCOTT, create a formatting function that lowercases the text, and run write_to_file as shown in Listing 3.

Code Listing 3: Create format_line (and call write_to_file) 

CREATE OR REPLACE FUNCTION format_line (line_in IN VARCHAR2)
    RETURN VARCHAR2
IS
BEGIN
    RETURN LOWER (line_in);
END format_line;
/

DECLARE
    l_lines   DBMS_SQL.varchar2s;
BEGIN
    l_lines (1) := 'steven feuerstein';
    l_lines (2) := 'is obsessed with PL/SQL.';
    hr.write_to_file ('TEMP', 'myfile.txt', l_lines);
END;
/

 

Unfortunately, I find the following text in the myfile.txt output file: 

STEVEN FEUERSTEIN
IS OBSESSED WITH PL/SQL.

 

So it seems very certain that you cannot take advantage of invoker rights with program calls, correct? Well, maybe there is a way: using dynamic SQL.

I rewrite the original write_to_file program in HR as shown in Listing 4.

Code Listing 4: Create updated write_to_file 

CREATE OR REPLACE PROCEDURE write_to_file (
   dir_in IN VARCHAR2
 , file_name_in IN VARCHAR2
 , lines_in IN DBMS_SQL.varchar2s
)
AUTHID CURRENT_USER
IS
    l_file       UTL_FILE.file_type;
    l_newline   VARCHAR2 (32767);
BEGIN
    l_file :=
        UTL_FILE.fopen (LOCATION            => dir_in
                                , filename           => file_name_in
                                , open_mode       => 'W'
                                , max_linesize     => 32767
                                 );

   FOR indx IN 1 .. lines_in.COUNT
   LOOP
       EXECUTE IMMEDIATE 
          'BEGIN :new_line := format_line (:old_line); END;'
         USING OUT l_newline, IN lines_in (indx);

       UTL_FILE.put_line (l_file, l_newline);
   END LOOP;

   UTL_FILE.fclose (l_file);
END write_to_file;
/

 

Note that I have moved my invocation of the format_line function so that it is inside an EXECUTE IMMEDIATE statement. That is, I call the function from within a dynamic PL/SQL block. And now I connect to SCOTT and call write_to_file: 

DECLARE
  l_lines   DBMS_SQL.varchar2s;
BEGIN
  l_lines (1) := 'steven feuerstein';
  l_lines (2) := 'is obsessed 
                    with PL/SQL.';
  hr.write_to_file ('TEMP', 
                  'myfile.txt', 
                  l_lines); 
END;
/

 

I then see the following contents in the myfile.txt output file: 

steven feuerstein
is obsessed with pl/sql.

 

Hey, it worked! How is this possible?

With the call shifted to the program inside the dynamically executed PL/SQL block, the call now executes from within a statement to which invoker rights can be applied: EXECUTE IMMEDIATE. That is, the block now executes under SCOTT's authority, so SCOTT's version of format_line is invoked.

So there you have it: invoker rights can apply to the execution call stack when you use dynamic PL/SQL.

Please note that dynamic execution of a PL/SQL subprogram is slower than a static invocation. For long-running programs, that overhead will be insignificant, but you might notice it for programs that finish very quickly.

I compared the performance of statically and dynamically running a program that called the USER function and deposited that value in a local variable. Running the program 100,000 times through a static invocation took 2.83 seconds. Running it dynamically took 6.66 seconds.

I picked up this technique from Alex Nuijten of AMIS (technology.amis.nl/blog), who recently attended the Oracle PL/SQL Programming 2007 conference in San Mateo, California (www.odtugopp07.com) and accepted the first Oracle Development Tools User Group PL/SQL Innovation and Community Award on behalf of the AMIS Oracle group.

One final note: an alternative way to achieve the same effect (runtime selection of the program that is to be executed) is with dynamic polymorphism. This technique is implemented by object types in PL/SQL—specifically, by setting up an object type hierarchy with the UNDER keyword. For more details on object types and dynamic polymorphism in PL/SQL, check out Chapter 12, "Using PL/SQL With Object Types" in the Oracle Database PL/SQL User's Guide and Reference.

No Way Out

My understanding is that a function should send back data only through its return clause. Why does PL/SQL allow us to define OUT parameters with functions? Is there any specific application of this feature?

I make the following recommendations for function structure: 

• Do not put OUT or IN OUT arguments into the parameter list of a function.

• Return all data in the RETURN clause of the function.

• Code only one RETURN statement in the executable section of the function, and make that RETURN the last line of that section.

• If you need to return multiple pieces of information, either return a composite datatype (record, object, collection, and so on) or change the function to a procedure and then use OUT or IN OUT arguments.

Why does Oracle allow us to put OUT arguments into a function's parameter list? Probably because it recognizes that the world is not black and white but made up of many shades of gray. Programmers are a widely varied lot, with many different code-writing styles.

I am very glad that Oracle did not decide to enforce this best practice of avoiding OUT parameters with functions (which is not, by the way, a universally accepted or adopted practice in the world of programming) in its implementation in PL/SQL.

My understanding of the world of C programming, for example, is that developers commonly write functions that return a status code indicating whether or not the function was successful. They then use OUT arguments in the parameter list to return the information from the function.

I don't like that approach, certainly not in PL/SQL, but I feel we need more language flexibility, not less. And we need to develop our own personal discipline to decide which features of PL/SQL to use and which to leave alone.

Certainly, there can be situations in which a function with an OUT parameter is useful. Consider the following block. The source_code.more_data function returns a Boolean value, which is used to terminate the loop, but also returns as an OUT argument the data structure that is then processed within the loop. 

BEGIN
  source_code.init (...);

  WHILE source_code.more_data 
            (output_structure) 
  LOOP
     process (output_structure);
  END LOOP;

  source_code.TERMINATE ();
END;

 

Now, I could certainly rewrite this code so that it relies on either a function with no OUT parameters or a procedure, but that would make the code more clumsy and difficult to read.

So, we definitely don't want Oracle making such a function with an OUT or IN OUT parameter illegal, but I do think that such a parameter list makes sense only for special-purpose situations.

 

---

Steven Feuerstein (steven.feuerstein@quest.com) is Quest Software's PL/SQL evangelist. He has published 10 books on Oracle's programming language, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media). Feuerstein's self-appointed mission in life these days is to improve the quality and quantity of PL/SQL code testing.