My Oracle Support Banner

Cannot Create or Replace a Stored Object in Another Schema (Doc ID 1005208.6)

Last updated on OCTOBER 26, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.5 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Unknown Platform

Symptoms

Even though a user (say user A) that has the CREATE ANY PROCEDURE privilege cannot 'create or replace' a stored object in someone else's schema (say user B)
 
Possible Errors:
~~~~~~~~~~~~~~~~
ORA-1031 insufficient privileges
 
Scenario:
~~~~~~~~~
SQL> create user a identified by a ;
User created.
SQL>  grant connect,resource, create any procedure to a;
Grant succeeded.
SQL> create user b identified by b;
User created.
SQL>  grant connect,resource to b;
Grant succeeded.
SQL>connect a/a
Connected.
SQL> create procedure b.testproc as
  2  begin
  3    null;
  4  end;
  5  /
Procedure created.
SQL>  create or replace procedure b.testproc as
  2  begin
  3    null;
  4  end;
  5  /
 create or replace procedure b.testproc as
*
ERROR at line 1:
ORA-01031: insufficient privileges

Changes

 NA

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.