CREATE OR REPLACE PROCEDURE CREATE_SYNONYMS_GRANTS(P_Owners varchar2) IS
cursor obj_crs is
select owner, object_name, object_type
from all_objects
where owner = upper(P_Owners)
and upper(object_type) in ('TABLE','VIEW','PROCEDURE','FUNCTION','SEQUENCE','SYNONYM','TYPE','PACKAGE');
obj_REC obj_CRS%ROWTYPE;
l_create_synonyms varchar2(500);
l_grants_objects varchar2(500);
BEGIN
FOR obj_REC IN obj_CRS LOOP
begin
--dbms_output.put_line(obj_REC.object_type);
l_create_synonyms := 'create synonym '||obj_REC.owner||'_USER.'||obj_REC.object_name||' for '||obj_REC.owner||'.'||obj_REC.object_name;
dbms_output.put_line(l_create_synonyms||';');
EXECUTE IMMEDIATE l_create_synonyms;
Exception
when others then
dbms_output.put_line(SQLERRM);
end;
begin
CASE obj_REC.object_type
WHEN 'TABLE' THEN l_grants_objects := 'grant select, update, delete, insert on '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
WHEN 'VIEW' THEN l_grants_objects := 'grant select, update, delete, insert on '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
WHEN 'PROCEDURE' THEN l_grants_objects := 'GRANT EXECUTE ON '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
WHEN 'FUNCTION' THEN l_grants_objects := 'GRANT EXECUTE ON '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
WHEN 'PACKAGE' THEN l_grants_objects := 'GRANT EXECUTE ON '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
WHEN 'SEQUENCE' THEN l_grants_objects := 'GRANT SELECT, ALTER ON '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
WHEN 'TYPE' THEN l_grants_objects := 'GRANT EXECUTE ON '||obj_REC.owner||'.'||obj_REC.object_name||' to '|| obj_REC.owner||'_user';
END CASE;
dbms_output.put_line(l_grants_objects||';');
EXECUTE IMMEDIATE l_grants_objects;
Exception
when others then
dbms_output.put_line(SQLERRM);
end;
end loop;
End;
/
Ex: -
exec CREATE_SYNONYMS_GRANTS ('OBJECTS_OWNER_NAME');
