oracle-databasestored-proceduresprivilegesora-01031

ORACLE - Create Procedure granted but can't create procedure


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.


Solution

  • 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.