ORACLE - Create Procedure granted but can't create procedure

Question

There's a user in the database to whom CREATE PROCEDURE privelege is granted. But when that user tries to create a simple procedure the following error is thrown: ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges.

here's the DDL for the procedure:

 CREATE OR REPLACE PROCEDURE TOTALBASE.ROUNDUP 
          (CUR OUT SYS_REFCURSOR  ) 
 AS 
 BEGIN
  OPEN CUR FOR
   SELECT * FROM TOTALBASE.ABONENT; 
 END ROUNDUP;

What else should I consider to do to make this work? I'm suspecting that even if the privelege is granted anyone who's not in the administrators or ORA_DBA group can't create a procedure. but I'm not sure.

Are you in the totalbase schema when trying to create this procedure? It appears as though you're trying to add an object to totalbase from a different user... — Ben
No, I'm in a different schema. But I've granted that user CREATE PROCEDURE privelege. Isn't that enough for that? — Mikayil Abdullayev
Does that user also have SELECT permissions on the abonent table? — eaolson

Mark J. Bobak Mark J. Bobak · Accepted Answer · 2012-05-05T12:11:44

To create a procedure in a schema other than your own, you'll need CREATE ANY PROCEDURE privilege.

As a general rule, this privilege should not be granted lightly, as it could easily be used to circumvent database security.

Hope that helps.

ORACLE - Create Procedure granted but can't create procedure

2 Answers