Re: Permissions problem when attempting to execute a procedure (Solved!)

This should work

Randy Baker wrote:
>
> Bingo! After adding the permission directly to the user, the procedure
> executed without any problems.
>
> One thing that I have noticed is that DDL statements executed via DBMS_SQL
> seem to be implicitly committed, which means I apparently can't use this
> approach to apply a series of schema changes inside a transaction from a
> PL/SQL script. Is there a better way?
>
> Thanks for the informative reply,
>
> --
> Randy Baker (remove Z from address in email replies)
>
> Andrew Barnett wrote in message
> <01bd5392$58ee8840$8c0564a1_at_azmelw1358.mel.az.bp.com>...
> >your user has the privilege to create triggers through the resource role.
> >privileges granted through roles don't work in stored procs and views.
> >therefore, you need to get a dba to grant "create trigger" directly to your
> >user.
> >--
> >Andrew - Wizzard
> >
> >barnetaj_at_bp.com
> >
> >Randy Baker <rsbakerZ_at_msn.com> wrote in article
> ><6es6gq$39g_at_ecuador.earthlink.net>...
> >> I've been writing scripts to distribute to our clients to make any schema
> >> changes required between versions of our applications.
> >>
> >> One of the most frequent things I have to do is create a simple trigger
> >> which inserts a sequence value as the primary key of a table.
> >>
> >> Rather than include the trigger text in each script which has to create a
> >> trigger, I created a procedure to do the job for me. The procedure
 generates
> >> the code for the trigger and passes it to DBMS_SQL.Parse() to perform the
> >> update. However, if I try to
> >> execute the procedure, I get an error about insufficient priviledges:
> >>
> >> ORA-01031: insufficient privileges
> >> ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> >> ORA-06512: at "SYS.DBMS_SQL", line 32
> >> ORA-06512: at "CRIBSAMPLE.WXDB_CREATESEQUENCETRIGGER", line 30
> >> ORA-06512: at line 2
> >>
> >> In this case, I am logged in as CRIBSAMPLE, and CRIBSAMPLE owns its
 default
> >> table space.
> >>
> >> Note that if I execute the trigger creation text generated by the
 procedure
> >> directly from the worksheet, the trigger is created without any problems.
> >> The procedure was also created properly via the same worksheet while
 logged
> >> in as the same user.
> >>
> >> I'm somewhat baffled. Summarizing...
> >>
> >> 1. I can create a trigger
> >> 2. I can create a procedure which generate the same text as used to
 create
> >> the trigger in step (1).
> >> 3. I *can't* call the procedure I created.
> >>
> >> --
> >> Randy Baker (remove Z from address in email replies)
> >>
> >>
> >>
  Received on Fri Mar 20 1998 - 00:00:00 CST