-- Connect to APPS Database Schema
CREATE USER APPS_RO IDENTIFIED BY APPS_RO;
GRANT CONNECT, RESOURCE TO APPS_RO;
------------------ Synonyms List ------------------------
/* Generate script for synonyms for new schema
*/
SELECT 'CREATE OR REPLACE SYNONYM APPS_RO.'||DS.synonym_name|| ' FOR '|| DS.table_owner||'.'||DS.table_name||';' script
FROM dba_synonyms DS
WHERE owner = 'APPS';
------------------ Tables List ------------------------
/* Generate script for tables synonym for new schema
*/
SELECT 'CREATE OR REPLACE SYNONYM APPS_RO.'||DS.TABLE_NAME|| ' FOR '|| DS.OWNER||'.'||DS.table_name||';' script
FROM dba_tables DS
WHERE owner = 'APPS'
;
------------------ View List ------------------------
/* Generate script for view synonym for new schema
*/
SELECT 'CREATE OR REPLACE SYNONYM APPS_RO.'||DS.VIEW_NAME|| ' FOR '|| DS.OWNER||'.'||DS.VIEW_name||';' script
FROM dba_views DS
WHERE owner = 'APPS';
------------------------------------ Grant SELECT(table,views) / EXECUTION(pkg,procudure,function) to Synonyms----------------------------------------
-- Connect to APPS Schema to assign rights
DECLARE
LV_SCRIPT VARCHAR(500);
LV_SCRIPT_PRO VARCHAR(500);
LV_COUNTER NUMBER := 0;
--IS_PKG_PRO_FUN_OBJ exception;
-- pragma EXCEPTION_INIT(IS_PKG_PRO_FUN_OBJ, '-980');
BEGIN
FOR CUR IN (
SELECT 'GRANT SELECT ON '||DS.synonym_name||' TO APPS_RO' SCRIPT,
'GRANT EXECUTE ON '||DS.synonym_name||' TO APPS_RO' SCRIPT_PRO ,
DS.*
FROM dba_synonyms DS
WHERE owner = 'APPS_RO'
-- and DS.synonym_name IN ('ACK','AP_INVOICE_DISTRIBUTIONS_ALL')
) LOOP
LV_SCRIPT := CUR.SCRIPT;
LV_SCRIPT_PRO := CUR.SCRIPT_PRO;
BEGIN
EXECUTE IMMEDIATE(LV_SCRIPT);
EXCEPTION WHEN OTHERS THEN
BEGIN
EXECUTE IMMEDIATE(LV_SCRIPT_PRO);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(LV_SCRIPT);
dbms_output.put_line(LV_SCRIPT_PRO);
dbms_output.put_line('**Internal Exception ******');
END;
END;
END LOOP;
dbms_output.put_line('PROCESS COMPLETED');
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('------------------Main Exception------------------------');
dbms_output.put_line(LV_SCRIPT_PRO);
dbms_output.put_line(LV_SCRIPT);
dbms_output.put_line(SQLERRM||'~'||SQLCODE);
END;
------------------------------------------
-- Connect to APPS and execute for ('FUNCTION','PACKAGE','PROCEDURE')
BEGIN
FOR CUR IN ( SELECT 'CREATE OR REPLACE SYNONYM APPS_RO.'||DP.object_name|| ' FOR APPS.'|| DP.object_name CREATE_SYNONYME
,'GRANT EXECUTE ON '||DP.object_name||' TO APPS_RO' GRANT_OBJECT
FROM DBA_PROCEDURES DP
WHERE 1 = 1
AND DP.OWNER = 'APPS'
AND DP.object_type IN ('FUNCTION','PACKAGE','PROCEDURE')
--AND DP.object_name IN('AP_INVOICES_PKG','AP_CHECKS_PKG')
GROUP BY DP.object_name
)
LOOP
EXECUTE IMMEDIATE (CUR.CREATE_SYNONYME);
EXECUTE IMMEDIATE (CUR.GRANT_OBJECT);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Process Completed !') ;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
END;
CREATE USER APPS_RO IDENTIFIED BY APPS_RO;
GRANT CONNECT, RESOURCE TO APPS_RO;
------------------ Synonyms List ------------------------
/* Generate script for synonyms for new schema
*/
SELECT 'CREATE OR REPLACE SYNONYM APPS_RO.'||DS.synonym_name|| ' FOR '|| DS.table_owner||'.'||DS.table_name||';' script
FROM dba_synonyms DS
WHERE owner = 'APPS';
------------------ Tables List ------------------------
/* Generate script for tables synonym for new schema
*/
SELECT 'CREATE OR REPLACE SYNONYM APPS_RO.'||DS.TABLE_NAME|| ' FOR '|| DS.OWNER||'.'||DS.table_name||';' script
FROM dba_tables DS
WHERE owner = 'APPS'
;
------------------ View List ------------------------
/* Generate script for view synonym for new schema
*/
SELECT 'CREATE OR REPLACE SYNONYM APPS_RO.'||DS.VIEW_NAME|| ' FOR '|| DS.OWNER||'.'||DS.VIEW_name||';' script
FROM dba_views DS
WHERE owner = 'APPS';
------------------------------------ Grant SELECT(table,views) / EXECUTION(pkg,procudure,function) to Synonyms----------------------------------------
-- Connect to APPS Schema to assign rights
DECLARE
LV_SCRIPT VARCHAR(500);
LV_SCRIPT_PRO VARCHAR(500);
LV_COUNTER NUMBER := 0;
--IS_PKG_PRO_FUN_OBJ exception;
-- pragma EXCEPTION_INIT(IS_PKG_PRO_FUN_OBJ, '-980');
BEGIN
FOR CUR IN (
SELECT 'GRANT SELECT ON '||DS.synonym_name||' TO APPS_RO' SCRIPT,
'GRANT EXECUTE ON '||DS.synonym_name||' TO APPS_RO' SCRIPT_PRO ,
DS.*
FROM dba_synonyms DS
WHERE owner = 'APPS_RO'
-- and DS.synonym_name IN ('ACK','AP_INVOICE_DISTRIBUTIONS_ALL')
) LOOP
LV_SCRIPT := CUR.SCRIPT;
LV_SCRIPT_PRO := CUR.SCRIPT_PRO;
BEGIN
EXECUTE IMMEDIATE(LV_SCRIPT);
EXCEPTION WHEN OTHERS THEN
BEGIN
EXECUTE IMMEDIATE(LV_SCRIPT_PRO);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(LV_SCRIPT);
dbms_output.put_line(LV_SCRIPT_PRO);
dbms_output.put_line('**Internal Exception ******');
END;
END;
END LOOP;
dbms_output.put_line('PROCESS COMPLETED');
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('------------------Main Exception------------------------');
dbms_output.put_line(LV_SCRIPT_PRO);
dbms_output.put_line(LV_SCRIPT);
dbms_output.put_line(SQLERRM||'~'||SQLCODE);
END;
------------------------------------------
-- Connect to APPS and execute for ('FUNCTION','PACKAGE','PROCEDURE')
BEGIN
FOR CUR IN ( SELECT 'CREATE OR REPLACE SYNONYM APPS_RO.'||DP.object_name|| ' FOR APPS.'|| DP.object_name CREATE_SYNONYME
,'GRANT EXECUTE ON '||DP.object_name||' TO APPS_RO' GRANT_OBJECT
FROM DBA_PROCEDURES DP
WHERE 1 = 1
AND DP.OWNER = 'APPS'
AND DP.object_type IN ('FUNCTION','PACKAGE','PROCEDURE')
--AND DP.object_name IN('AP_INVOICES_PKG','AP_CHECKS_PKG')
GROUP BY DP.object_name
)
LOOP
EXECUTE IMMEDIATE (CUR.CREATE_SYNONYME);
EXECUTE IMMEDIATE (CUR.GRANT_OBJECT);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Process Completed !') ;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
END;
