Allows developers to switch synonyms between schemas wher

-- Description : Allows developers to switch synonyms between schemas where a s

-- -----------------------------------------------------------------------------
------
-- Author : aws-senior.com
-- Description : Allows developers to switch synonyms between schemas where a s
ingle instance
-- : contains multiple discrete schemas.
-- Requirements : Must be loaded into privileged user such as SYS.
-- Usage : Create the package in a user that has the appropriate privileg
es to perform the actions (SYS)
-- : Amend the list of schemas in the "reset_grants" FOR LOOP as ne
cessary.
-- : Call SWITCH_SCHEMA.RESET_GRANTS once to grant privileges to th
e developer role.
-- : Assign the developer role to all developers.
-- : Tell developers to use EXEC SWITCH_SCHEMA.RESET_SCHEMA_SYNONYM
S ('SCHEMA-NAME'); to switch
-- : there synonyms between schemas.
-- Call Syntax : EXEC SWITCH_SCHEMA.RESET_SCHEMA_SYNONYMS ('SCHEMA-NAME');
-- -----------------------------------------------------------------------------
------
CREATE OR REPLACE PACKAGE switch_schema AS

PROCEDURE reset_grants;
PROCEDURE reset_schema_synonyms (p_schema IN VARCHAR2);

END;
/

SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY switch_schema AS

PROCEDURE reset_grants IS
BEGIN
FOR cur_obj IN (SELECT owner, object_name, object_type
FROM all_objects
WHERE owner IN ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4')
AND object_type IN ('TABLE','VIEW','SEQUENCE', 'PACKAGE', '
PROCEDURE', 'FUNCTION', 'TYPE'))
LOOP
CASE
WHEN cur_obj.object_type IN ('TABLE','VIEW') THEN
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || cur_obj.
owner || '."' || cur_obj.object_name || '" TO developer';
WHEN cur_obj.object_type IN ('SEQUENCE') THEN
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || cur_obj.owner || '."' || cur_obj
.object_name || '" TO developer';
WHEN cur_obj.object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'TYPE') T
HEN
EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || cur_obj.owner || '."' || cur_ob
j.object_name || '" TO developer';
END CASE;
END LOOP;
END;

PROCEDURE reset_schema_synonyms (p_schema IN VARCHAR2) IS
v_user VARCHAR2(30) := USER;
BEGIN
-- Drop all existing synonyms
FOR cur_obj IN (SELECT synonym_name
FROM all_synonyms
WHERE owner = v_user)
LOOP
EXECUTE IMMEDIATE 'DROP SYNONYM ' || v_user || '."' || cur_obj.synonym_name
|| '"';
END LOOP;

-- Create new synonyms
FOR cur_obj IN (SELECT object_name, object_type
FROM all_objects
WHERE owner = p_schema
AND object_type IN ('TABLE','VIEW','SEQUENCE'))
LOOP
EXECUTE IMMEDIATE 'CREATE SYNONYM ' || v_user || '."' || cur_obj.object_name
|| '" FOR ' || p_schema || '."' || cur_obj.object_name || '"';
END LOOP;
END;

END;
/

SHOW ERRORS

CREATE PUBLIC SYNONYM switch_schema FOR switch_schema;
GRANT EXECUTE ON switch_schema TO PUBLIC;

CREATE ROLE developer;


--------------------------------------------------------------------------------------
-- File Name : https://github.com/Zaalouni/OracleDba
-- Author : Zaalouni
-- website : www.aws-senior.com
-- Github : https://github.com/Zaalouni/OracleDba
--------------------------------------------------------------------------------------

aws-senior.com

www.aws-senior.com
Visite out website www.aws-senior.com
http://www.aws-senior.com
www.aws-senior.com -- Description : Allows developers to switch synonyms between schemas where a s
-- Description : Allows developers to switch synonyms between schemas where a s
-- Description : Allows developers to switch synonyms between schemas where a s