Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Which "USER" is used while executing procedure

UllhasNov 9 2009 — edited Nov 10 2009
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 8 2009
Added on Nov 9 2009
9 comments
8,375 views