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