Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_transfer_privs_for_ne_cutover_per_edition.sql rbali_oneoff_updated/1 2025/03/14 06:41:15 rbali Exp $
Rem
Rem admin_transfer_privs_for_ne_cutover_per_edition.sql
Rem
Rem Copyright (c) 2025, Oracle and/or its affiliates.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_transfer_privs_for_ne_cutover_per_edition.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_transfer_privs_for_ne_cutover_per_edition.sql

Rem EMRU_FILE_POSTINSTALL:<!!! enter the view based location of the executable file. If there is NONE leave as-is !!!>
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem
Rem    NAME
Rem      admin_transfer_privs_for_ne_cutover_per_edition.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    BEGIN SQL_FILE_METADATA
Rem    SQL_SOURCE_FILE: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_transfer_privs_for_ne_cutover_per_edition.sql
Rem    SQL_SHIPPED_FILE:
Rem    SQL_PHASE:
Rem    SQL_STARTUP_MODE: NORMAL
Rem    SQL_IGNORABLE_ERRORS: NONE
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    spramani    02/20/25 - per edition grants to lcm admin user
Rem    spramani    02/20/25 - Created
Rem

 
DEFINE TARGET_EDITION = "&1"
DEFINE DEFAULT_EDITION = "&2"
DEFINE LCM_ADMIN_USER ="&3"
GRANT USE ON EDITION &TARGET_EDITION to &LCM_ADMIN_USER;
ALTER SESSION SET EDITION = &TARGET_EDITION;

DECLARE
    l_sql VARCHAR2(2000);
    l_flag_state VARCHAR2(256);
    l_org_flag_state VARCHAR2(256);
    l_mgmt_admin_msg_synonym_exists NUMBER;
    l_synonym_exists NUMBER;
    l_final_ne_synonym_check NUMBER;
    l_start_time TIMESTAMP := SYSTIMESTAMP;
    l_timeout_seconds NUMBER := 3600; 
    l_loop_counter NUMBER := 0; 
    l_already_granted NUMBER := 0;
    l_property_value VARCHAR2(256);
    l_admin_user VARCHAR(1024);
BEGIN
    
    BEGIN
            FOR rec IN ( SELECT s.owner, s.table_owner, s.table_name, s.synonym_name, o.edition_name
                                FROM dba_synonyms s
                                JOIN dba_objects_ae o
                                ON s.synonym_name = o.object_name
                                AND s.owner = o.owner
                                WHERE s.owner IN ('SYSMAN', 'SYSMAN_RO', 'MGMT_VIEW', 'SYSMAN_ZDTUSER')
                                AND o.object_type = 'SYNONYM'
                                AND s.table_owner = 'SYSMAN_TYPES'
                                AND o.edition_name = '&&TARGET_EDITION')
            LOOP
                BEGIN                                                
                    l_already_granted := 0;
                    SELECT count(*) into l_already_granted from dba_tab_privs where table_name=UPPER(rec.synonym_name) and grantee='&&LCM_ADMIN_USER'; 
                    IF l_already_granted > 0 THEN  
                        DBMS_OUTPUT.PUT_LINE(rec.owner || '.' || rec.synonym_name || ' synonym in edition ' || rec.edition_name || 'already granted to' || '&&LCM_ADMIN_USER' ||', so no action taken.');

                    ELSE
                        l_admin_user := UPPER('&LCM_ADMIN_USER');
                            l_sql := 'grant EXECUTE on '||rec.owner ||'.'||rec.synonym_name || ' to ' || l_admin_user;
                            BEGIN
                                EXECUTE IMMEDIATE l_sql;
                                DBMS_OUTPUT.PUT_LINE( 'granted, running: '|| l_sql || ' in edition ' || rec.edition_name);                    
                            EXCEPTION
                                WHEN OTHERS THEN    
                                    sysman.em_ebr_util.config_sql_retry(l_sql, 'Failed to grant EXECUTE to synonym: ' || rec.synonym_name || 'to ' || l_admin_user || ' in edition ' || rec.edition_name);		
                                    DBMS_OUTPUT.PUT_LINE( 'granting synonym using em_ebr_util.config_sql_retry procedure: '|| l_sql || ' in edition ' || rec.edition_name);                    
                            END;
                    END IF; 
                EXCEPTION
                    WHEN OTHERS THEN
                        DBMS_OUTPUT.PUT_LINE( 'Exception while dropping and creating synonym, while running:' || l_sql);                    
                        RAISE_APPLICATION_ERROR(-20005, 'Exception while dropping and creating synonym, while running:' || l_sql);
                END;
            END LOOP; 
        END;
END;
/ 

ALTER SESSION SET EDITION = &DEFAULT_EDITION;

