SQL & PL/SQL

Hi,

AFAK, when a procedure is executed, it is run as the procedure owner. But I am confused with the usage of "USER" in this context.

I did a small test. Procedure owner is user "LHOWNER". And other user I am using is CSOWNER.
Following commands are run as user lhowner:

kallo:DZHLHFC1> cat test1.sql
CREATE OR REPLACE PROCEDURE test1 IS
l_var NUMBER;
l_user VARCHAR2(30);
l_owner VARCHAR2(30);
BEGIN
        SELECT col1 INTO l_var FROM tt1;
        DBMS_OUTPUT.PUT_LINE('l_var = ' || l_var);
        SELECT owner INTO l_owner FROM user_tab_privs WHERE table_name = 'TEST1';
        DBMS_OUTPUT.PUT_LINE('l_owner = ' || l_owner);
        select user into l_user from user_users;
        DBMS_OUTPUT.PUT_LINE('l_user = ' || l_user);
END;
/

kallo:f632736> sqlplus lhowner/password

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 9 11:02:12 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table tt1 (col1 number);

Table created.
SQL> insert into tt1 values(10);

1 row created.

SQL> commit;

SQL> @test1.sql

Procedure created.

SQL> grant execute on test1 to csowner;

Grant succeeded.

Following is the output when procedure run as user CSOWNER:

SQL> SELECT owner FROM user_tab_privs WHERE table_name = 'TEST1';

OWNER
------------------------------
LHOWNER

SQL> set serverout on
SQL> exec test1;
l_var = 10
l_owner = LHOWNER
l_user = CSOWNER

PL/SQL procedure successfully completed.

I then revoke direct grant and grant CSOWNER though role so that above select statement does not return anything. Below if output when run as CSOWNER:

SQL> SELECT owner FROM user_tab_privs WHERE table_name = 'TEST1';

no rows selected

SQL> set serverout on
SQL> exec test1;
l_var = 10
l_owner = LHOWNER
l_user = CSOWNER

PL/SQL procedure successfully completed.

My doubt here is - Why USER_USERS return CSOWNER whereas USER_TAB_PRIVS gives output as if run from LHOWNER?
Could someone explain me this concept?

Also is there a way (other than through USER_TAB_PRIVS) where I can access procedure owner username within the procedure?

Thanks & Regards,
Ullhas