Re: sys.DBMS_DDL.CREATE_WRAPPED what other priviledges are needed?

Andy Hassall wrote:
> On Wed, 05 Jul 2006 19:34:24 +0100, Andy Hassall <andy_at_andyh.co.uk> wrote:
>
> >On 5 Jul 2006 08:29:30 -0700, sybrandb_at_yahoo.com wrote:
> >
> >>nkunkov_at_escholar.com wrote:
> >>> I'm trying to use sys.DBMS_DDL.CREATE_WRAPPED in my own stored
> >>> procedure. I logged in as SYS and gave myself priviledges to execute
> >>> this package. But Oracle 10g still gives me this error:
> >>>
> >>> ORA-01031: insufficient privileges
> >>> ORA-06512: at "SYS.DBMS_DDL", line 643
> >>>
> >>> Is there some other package that I should grant execute permissions to?
> >>> What else should I do to be able to execute the above?
> >>
> >>Evidently, one would need *direct* privilege (ie not through a role) to
> >>execute the actual commands submitted, as roles are ignored when
> >>executing stored procedures.
> >
> > No, that's for definer rights procedures. DBMS_DDL.CREATE_WRAPPED is invoker
> >rights, as listed in the documentation. Privileges from roles are not ignored
> >in invoker rights procedures.
>
> Replying to my own post, the OP's said he's using it inside his own stored
> procedure, which probably IS definer rights, so yes that's probably the
> problem, once you go via a definer rights procedure then the usual no-roles
> rules kick back in.
>
> --
> Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Thank you all for your input. I actually right away gave direct rights to my user to execute DBMS_DDL.CREATE_WRAPPED. I eventually managed to create my package using create_wrapped, but am still getting the error when I try to rerun the commad effectively replacing the package. I have "create or replace package...." that I send to create_wrapped, when the package doesn't exist yet it goes well. When it's already created and I rerun create_wrapped it gives me "insufficient priviledges" error. I assume my user is unable to drop the package for it to be recreated?
My apologies if the questions are silly I'm not a DBA nor an Oracle guru. Received on Wed Jul 05 2006 - 15:27:33 CDT