Re: rights from a procedure

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 27 Oct 1999 21:44:42 +0800
Message-ID: <381701CA.170D@yahoo.com>

Ken Leach wrote:
>
> I am trying to run this procedure
>
> Procedure JUNK as
> BEGIN
> EXECUTE IMMEDIATE 'ALTER TABLE rsnet.pos MODIFY PARTITION
> REVLON_D_CVS_199701 DEALLOCATE UNUSED KEEP 0';
> END;
>
> this procedure is in the LOADER schema and owned by it..
> I am trying to alter a table in the RSNET schema..
>
> I CAN run this from the command line, just not in a procedure.
>
> I am in 8.1.5
>
> RSNET & LOADER have the DBA role granted..
> also
> LOADER has the following rights granted explicitly..
>
> ALTER ANY TABLE
> CREATE ANY INDEX
> DROP ANY TABLE
> ALTER ANY TRIGGER
> UNLIMITED TABLESPACE
> EXECUTE ANY TYPE
> GLOBAL QUERY REWRITE
>
> WHY do I get a ORA-01031: insufficient privileges error when In try to
> run JUNK from LOADER?
>
> -Ken

roles are not enabled during procedure execution. in sqlplus try

SQL> set role none;

and then see if you can execute the procedure. You need to have the execution granted directly not via a role.

HTH
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Wed Oct 27 1999 - 08:44:42 CDT