ORA-01031 error executing stored procedure
Hi all. I have a simple stored procedure that I'd like to run, but I get an insufficient privileges error, even though the user running the procedure owns all the objects involved:
Here is the error:
SQL> exec UPDATE_ITEMS
BEGIN UPDATE_ITEMS; END;
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCP.UPDATE_ITEMS", line 5
ORA-06512: at line 1
and the source for the procedure: (again, this user owns both table and index)
CREATE OR REPLACE PROCEDURE UPDATE_ITEMS as
execute immediate 'DROP INDEX ITEMS_INDEX';
execute immediate 'DROP TABLE ITEMS';
execute immediate 'CREATE TABLE ITEMS as SELECT * FROM STG_ITEMS';
execute immediate 'CREATE INDEX ITEMS_INDEX ON ITEMS(CONTRACT_NUMBER, ITEM_NUMBER, ITEM_ID)';
Thanks in advance for any advice.
problem is solved.
ok. i had initially granted the 'create any table', 'create any index', etc role to this user through a role. after granting these privileges directly to the user, the problem is fixed. it isn't very important now, but if anyone could explain why this is the case, i would appreciate it. thanks.
First of all these are not roles, but are called SYSTEM Privilages.
Originally posted by sgm3u
i had initially granted the 'create any table', 'create any index', etc role
Roles are disabled for any Stored Program.
granted the 'create any table', 'create any index', etc role to this user through a role
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
sorry, typo. i meant that i granted those privileges to that role and granted that role to the user in question. i am guessing, however, that your final statement is the answer to my question...
Click Here to Expand Forum to Full Width