To fix this problem, compile your package with AUTHID CURRENT USER
directive in the Package Signature.
Basically when user B executes package of user A, for the course of
the package's run user B becomes user A and the ownership context is
that of user A's. When user B runs show_user(tablename), effectively
ORacle is trying to resolve tablename in user A's namespace....which
is doomed for failure. Worse if A, B and C both have tables with the
same name, you will be reporting incorrect results (always A's
tables).
HTH
--- Saminathan Seerangan <[EMAIL PROTECTED]> wrote:
>
> Hi List,
>
> Could you please help me out to resolve this issue?
> Basically i have created one stored procedure(show_space) in
> SYSTEM schema, then granted execute rights to PUBLIC and created
> public synonym.
> When i conenct as diff user(MUT) I am not able to execute the
> procedure. Any help would be really appreciated.
>
> TIA
>
> SQL> connect system/[EMAIL PROTECTED]
> Connected.
> SQL> @D:\share\oracle\Asktom\show_space8i.sql
> 51 /
>
> Procedure created.
>
> SQL> grant execute on show_space to public;
> Grant succeeded.
>
> SQL> create public synonym show_space for show_space;
> Synonym created.
>
> SQL> connect mut/[EMAIL PROTECTED]
> Connected.
>
> SQL> execute show_space('RW_RFX_HEADER');
> BEGIN show_space('RW_RFX_HEADER'); END;
>
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
> ORA-06512: at "SYS.DBMS_SPACE", line 55
> ORA-06512: at "SYSTEM.SHOW_SPACE", line 22
> ORA-06512: at line 1
>
>
> SQL> desc show_space
> PROCEDURE show_space
> Argument Name Type In/Out
> Default?
> ------------------------------ ----------------------- ------
> --------
> P_SEGNAME VARCHAR2 IN
> P_OWNER VARCHAR2 IN
> DEFAULT
> P_TYPE VARCHAR2 IN
> DEFAULT
>
>
> SQL> desc dbms_space
> PROCEDURE FREE_BLOCKS
> Argument Name Type In/Out
> Default?
> ------------------------------ ----------------------- ------
> --------
> SEGMENT_OWNER VARCHAR2 IN
> SEGMENT_NAME VARCHAR2 IN
> SEGMENT_TYPE VARCHAR2 IN
> FREELIST_GROUP_ID NUMBER IN
> FREE_BLKS NUMBER OUT
> SCAN_LIMIT NUMBER IN
> DEFAULT
> PARTITION_NAME VARCHAR2 IN
> DEFAULT
> PROCEDURE UNUSED_SPACE
> Argument Name Type In/Out
> Default?
> ------------------------------ ----------------------- ------
> --------
> SEGMENT_OWNER VARCHAR2 IN
> SEGMENT_NAME VARCHAR2 IN
> SEGMENT_TYPE VARCHAR2 IN
> TOTAL_BLOCKS NUMBER OUT
> TOTAL_BYTES NUMBER OUT
> UNUSED_BLOCKS NUMBER OUT
> UNUSED_BYTES NUMBER OUT
> LAST_USED_EXTENT_FILE_ID NUMBER OUT
> LAST_USED_EXTENT_BLOCK_ID NUMBER OUT
> LAST_USED_BLOCK NUMBER OUT
> PARTITION_NAME VARCHAR2 IN
> DEFAULT
> ________________________________
> Free multi-lingual web-based and POP3 email service with a
> generous 15MB of storage, a choice of themes for your mailbox,
> message filtering, plus spam and virus protection
> Sign up now: http://www.gawab.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Saminathan Seerangan
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting
> services
>
---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like
> subscribing).
>
=====
Sundeep Maini
Consultant
Currently on Assignement at Caterpillar Peoria
[EMAIL PROTECTED]
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: sundeep maini
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
