What is the proper use of AUTHID CURRENT_USER

Question:
I encountered a bottleneck recently when coding Oracle PL/SQL programs around a data dictionary view. This is the kind of code I want to run:
CREATE OR REPLACE PROCEDURE view_schema_tables (v_schema VARCHAR2)
IS
CURSOR c1
IS
SELECT table_name
FROM all_tables
WHERE owner = v_schema;

rec_schema c1%ROWTYPE;
v_count NUMBER := 0;
BEGIN
FOR rec_schema IN c1
LOOP
DBMS_OUTPUT.put_line (rec_schema.table_name);
v_count := v_count + 1;
END LOOP;

DBMS_OUTPUT.put_line ('Count: ' || v_count);
END;
I granted EXECUTE authority on this program to certain users who have the authority to view other schemas' complete table list.
The problem here is that I can logon to SQL*Plus and execute this SQL statement directly:
SQL> SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='HR';
to get the expected results. But if I run the procedure, the results are different. Shouldn't I get the same results, regardless of where I run the query?
Answer:
Samuel, assuming you are running Oracle8i or later, the solution is close at hand: simply add the AUTHID CURRENT_USER clause to your program header. In other words, define your program as follows:
CREATE OR REPLACE PROCEDURE view_schema_tables (v_schema VARCHAR2)
AUTHID CURRENT_USER
IS
CURSOR c1
IS
SELECT table_name
FROM all_tables
WHERE owner = v_schema;

rec_schema c1%ROWTYPE;
v_count NUMBER := 0;
BEGIN
FOR rec_schema IN c1
LOOP
DBMS_OUTPUT.put_line (rec_schema.table_name);
v_count := v_count + 1;
END LOOP;

DBMS_OUTPUT.put_line ('Count: ' || v_count);
END;
By adding AUTHID CURRENT_USER, you are telling the PL/SQL runtime engine to use the authority (privileges) of the current user (caller of the program), rather than those of the owner or definer of the program.
Note that with AUTHID CURRENT_USER (known as "invoker rights" execution mode), the runtime engine will also use roles active in that session to resolve references to any tables or views in your SQL statements.