77 lines
3.1 KiB
SQL
77 lines
3.1 KiB
SQL
-- -----------------------------------------------------------------------------------
|
|
-- File Name : https://oracle-base.com/dba/miscellaneous/switch_schema.sql
|
|
-- Author : Tim Hall
|
|
-- Description : Allows developers to switch synonyms between schemas where a single 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 privileges to perform the actions (SYS)
|
|
-- : Amend the list of schemas in the "reset_grants" FOR LOOP as necessary.
|
|
-- : Call SWITCH_SCHEMA.RESET_GRANTS once to grant privileges to the developer role.
|
|
-- : Assign the developer role to all developers.
|
|
-- : Tell developers to use EXEC SWITCH_SCHEMA.RESET_SCHEMA_SYNONYMS ('SCHEMA-NAME'); to switch
|
|
-- : there synonyms between schemas.
|
|
-- Call Syntax : EXEC SWITCH_SCHEMA.RESET_SCHEMA_SYNONYMS ('SCHEMA-NAME');
|
|
-- Last Modified: 02/06/2003
|
|
-- -----------------------------------------------------------------------------------
|
|
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') THEN
|
|
EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || cur_obj.owner || '."' || cur_obj.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;
|