CREATE OR REPLACE v/s Dropping a function and re-creating it

Question

What is the difference between the two? In both the cases? what happens to the privileges granted on this function? Are automatically revoked in both the cases and have to be provided again while re-creating? Kindly explain.

Answer 1

When an object is dropped everything associated with it is dropped too, including privileges. This is not true of CREATE OR REPLACE.

SQL> create procedure p1 is
  2  begin
  3      null;
  4  end;
  5  /

Procedure created.

SQL> grant execute on p1 to xyz;

Grant succeeded.

SQL> select * from user_tab_privs_made
  2  /

GRANTEE                        TABLE_NAME                     GRANTOR                        PRIVILEGE                     GRA HIE
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
XYZ                            P1                             APC                            EXECUTE                       NO  NO

SQL> create or replace procedure p1 is
  2    n pls_integer;
  3  begin
  4   n := 1;
  5  end;
  6  /

Procedure created.

SQL> select * from user_tab_privs_made
  2  /

GRANTEE                        TABLE_NAME                     GRANTOR                        PRIVILEGE                     GRA HIE
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
XYZ                            P1                             APC                            EXECUTE                       NO  NO

SQL> drop procedure p1;

Procedure dropped.

SQL> create or replace procedure p1 (p in out pls_integer) is
  2  begin
  3    p := p+1;
  4  end;
  5  /

Procedure created.

SQL> select * from user_tab_privs_made
  2  /

no rows selected

SQL> 

Answer 2

I believe if you replace a function, the privileges stay intact, although I usually like to create public synonyms and grant privileges to those.

The docs say:

REPLACE is an optional keyword used in object definitions (DDL) to override the older objet definition with a new one. It retains the access privileges of the object during the definition modification process. If the object is dropped and recreated, however, its privileges are lost.