CREATE_SYNONYMS_GRANTS.SQL

1.create a 2nd user ending with '_user' where synonyms needs to be created of the original user.

eg:-

create user ids_user identified by ids_user;

2.Copy the below contents of procedure to a file and run the file as a superuser(sys,system)

CREATE OR REPLACE PROCEDURE CREATE_SYNONYMS_GRANTS(P_Owners varchar2) IScursor obj_crs isselect owner, object_name, object_typefrom all_objectswhere 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 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;

3.Execute the procedure as a super user(sys,system) replacing the value with original username

eg:-

exec CREATE_SYNONYMS_GRANTS('ORIGINAL_USER_NAME');

exec CREATE_SYNONYMS_GRANTS('IDS');