Re: rights from a procedure

A copy of this was sent to Ken Leach <ktl_at_rs-net.com> (if that email address didn't require changing) On Wed, 27 Oct 1999 15:55:00 -0400, you 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
>

see http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html for that answer. Now, you could (since you are using Oracle8i, release 8.1) use

create or replace procedure Junk
authid current_user
as
....

see "invokers rights" in the manual for more info on this new feature. It'll let the procedure run with the rights of the INVOKER (roles and all) not the owner of the procedure.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Oct 27 1999 - 16:03:45 CDT