Re: calling dbms_redefinition via a stored procedure.

djspira_at_hotmail.com wrote:
> Hi,
>
> I'm trying to create a stored procedure function which is a wrapper to
> the dbms_redefintion function.
>
> I have allocated EXECUTE ON DBMS_REDEFINITION and DBA rights
>
> if I create a script that grants these rights, and logs on as the user
> I grant the rights to, and then call the
> dbms_redefintion.start_redef_table this works ok.
>
> If I create a stored procedure eg ESP_ConvertTable which takes three
> parameters
> p_OldTable, p_NewTable, and p_ColumnDefintion which I pass to the
> dbms_redefinition functions, and then call exactly the same script as
> previously but making a call to the stored procedure instead I get an
> insufficient privilges error.
>
> I have read/seen things about invoker rights - does calling a stored
> procedure from a stored procedure pass through the privileges of the
> stored procedures caller/owner?
>
> Any advice would be greatly appreciated. Having to use the
> dbms_redefintion as we're moving to unicode converting clobs to nclobs
> in Oracle 9iR2 which means a insert into... doesn't work (it does in
> 10g)...
>
> Kind Regards
>
> Rich

Rights for PL/SQL MUST be granted explicitly ... not through roles.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)