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?
Answer:
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 comments:
Post a Comment