Re: Executing stored procedure from diff user

From: Pete Finnigan <oracle_list_at_peterfinnigan.demon.co.uk>
Date: Fri, 25 Jul 2003 11:12:14 +0100
Message-Id: <26013.339585@fatcity.com>

Hi Saminathan,

Add the line:

authid current_user

here:

procedure show_space

( p_segname in varchar2,
  p_owner   in varchar2 default user,
  p_type    in varchar2 default 'TABLE',
  p_partition in varchar2 default NULL )

authid current_user
as

    l_free_blks                 number;

and recreate the procedure as system and then it should run as your other user. for example:

SQL> connect system/manager
Connected.
SQL> -- add the line authid current_user to show_space.sql SQL> @show_space

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 pete/pete
Connected.
SQL> create cluster phash (hash_key number) hashkeys 1000 size 8192;

Cluster created.

SQL> set serveroutput on size 1000000
SQL> exec show_space('PHASH',user,'CLUSTER');

Free Blocks.............................0
Total Blocks............................1456
Total Bytes.............................11927552
Unused Blocks...........................446
Unused Bytes............................3653632
Last Used Ext FileId....................1
Last Used Ext BlockId...................35049
Last Used Block.........................34

PL/SQL procedure successfully completed.

SQL> hope this helps

kind regards

pete

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

Received on Fri Jul 25 2003 - 05:12:14 CDT