Re: ora -1031 - Why???

Sybrand Bakker wrote:
>
> This is Roles. Roles are not enabled during execution of a procedure.
> As an anonymous pl/sql block is a one-time operation (from the perspective
> of the database) roles *are* enabled during execution of anonymous blocks.
> In Oracle 8i you have the option of defining the procedure with invokers
> rights instead of with definers rights (Oracle 7 and 8.0 behavior).
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> Kenneth C Stahl <BlueSax_at_Unforgettable.com> schreef in berichtnieuws
> 3925896A.82409182_at_Unforgettable.com...
> > I have a pl/sql procedure which uses dbms_sql to rebuild some indexes.
> > If I run it from within sqlplus it runs fine. But, if I change the first
> > line from 'declare' to 'create or replace rebuildindexes as', it will
> > compile correctly but when it runs it generates an ORA-1031 error. The
> > user that I am running this has 'execute any procedure'. I've looked at
> > the messages manual and it really doesn't help. Does anyone know what it
> > going on?

I sort of understood that the "roles" thing, but I don't understand what role I am missing.

Here is the situation: The stored procedure was created with a specific account/password. I'll use abc/xyz for the sake of this message. This means that abc owns the procedure. The tables and indexes in this case are also owned by abc. Even the dbms_jobs.submit is performed while logged in as abc. Since abc has complete rights over all of the objects it owns then what privilege that would normally be assigned to a role is missing. In my tests I have assigned "create session" and "create indextype" to abc and still cannot run the job via dbms_job even though I can run the same stored procedure from an interactive session while logged in as abc. The only two roles assigned to abc are "connect" and "resource". If I look at the "connect" role I see the system privileges of "ALTER SESSION", "CREATE CLUSTER", "CREATE DATABASE LINK", "CREATE SEQUENCE", "CREATE SESSION", "CREATE SYNONYM", "CREATE TABLE" AND "CREATE VIEW". Out of all of them it is only the "create session" that would seem to have any possible relevance to my stored procedure. Under the "resource" role is "CREATE CLUSTER", "CREATE INDEXTYPE", CREATE OPERATOR", "CREATE PROCEDURE", "CREATE SEQUENCE", "CREATE TABLE", "CREATE TRIGGER" and "CREATE TYPE". Out of all of those the only one that might be relevant is "CREATE INDEXTYPE". So, what am I missing? Received on Mon May 22 2000 - 00:00:00 CDT