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.
Add a comment
|
2 Answers
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>
1 Comment
Rob
Might be wrong but is the audit history maintained also?
I believe if you replace a function, the privileges stay intact, although I usually like to create public synonyms and grant privileges to those.
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.
Comments
