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

Question

What is the difference between the two? In both cases? what happens to privileges granted for this function? Automatically canceled in both cases and should be re-provided upon re-creation? Please explain.

Answer 1

When an object is dropped, all objects associated with it are also dropped, including privileges. This does not apply to 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 that if you replace the function, the privileges remain intact, although I usually like to create public synonyms and grant them privileges.

The docs say :

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