Script to create schema and grant proper security from schema owner

Assume the schema is ACCDB and the new schema is ACCDBP1.

This script will create the new schema, setup synonym and grant security to it.

CREATE USER accdbp1 IDENTIFIED BY passwordxyz
 DEFAULT TABLESPACE ACC_DATA
 TEMPORARY TABLESPACE TEMP
 QUOTA UNLIMITED ON ACC_DATA;

 GRANT "CONNECT" TO accdbp1;

ALTER USER accdbp1 DEFAULT ROLE "CONNECT";

 SELECT 'DROP SYNONYM accdbp1.'
 || object_name
 || ';'
 || CHR (13)
 || CHR (10)
 || 'Create synonym accdbp1.'
 || object_name
 || ' for accdb.'
 || object_name
 || ';'
 || CHR (13)
 || CHR (10)
 || CASE
 WHEN object_type IN ('TABLE', 'VIEW')
 THEN
 'grant select,update,insert,delete on accdb.' || object_name || ' to accdbp1;'
 WHEN object_type IN ('SEQUENCE')
 THEN
 'grant select on accdb.' || object_name || ' to accdbp1;'
 WHEN object_type IN ('PROCEDURE', 'PACKAGE', 'FUNCTION')
 THEN
 'grant execute on accdb.' || object_name || ' to accdbp1;'
 END
 syn_sql
 FROM all_objects
 WHERE object_type IN ('TABLE',
 'VIEW',
 'SEQUENCE',
 'PROCEDURE',
 'PACKAGE',
 'FUNCTION')
 AND object_name NOT LIKE 'SEB%'
 AND object_name NOT LIKE '%\_H' ESCAPE '\'
 AND object_name NOT LIKE '%\_AUDIT' ESCAPE '\'
 AND ( (object_type = 'TABLE' AND data_object_id IS NOT NULL) OR object_type <> 'TABLE')
 AND owner = 'ACCDB'
ORDER BY object_type, object_name;