Re: calling dbms_redefinition via a stored procedure.

Hi Daniel,

Thanks for the reply

does this mean when I create the stored proc I use the syntax AUTHID CURRENT_USER AS reading through the following website -
http://www.lc.leidenuniv.nl/awcourse/oracle/appdev.920/a96624/08_subs.htm#18575

Who Is the Current User During Subprogram Execution?

In a sequence of calls, if an invoker-rights subprogram is the first subprogram called, the current user is the session user. That remains true until a definer-rights subprogram is called, in which case the owner of that subprogram becomes the current user. If the definer-rights subprogram calls any invoker-rights subprograms, they execute with the privileges of the owner. When the definer-rights subprogram exits, control reverts to the previous current user.

I have a user 'RICH' who has created tables tableA tableA_temp.

RICH has created the stored procedure ESP_ConvertTable which contains calls to DBMS_REDEFINITION (which is owned by SYS?) If I add the syntax AUTHID CURRENT_USER AS will this solve my problem.or when I created the procedure do I still need to grant a privilege to execute the ESP_ConvertTable even though RICH created it?

I'm guessing that the script that I wrote with a PL/SQL block that works is an anonymous block and that this behaves differently.

Am I heading on the right lines?

Kind Regards

Rich Received on Sun Apr 09 2006 - 18:35:14 CDT