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

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_reposuser_grants_to_ro_user.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_reposuser_grants_to_ro_user.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_reposuser_grants_to_ro_user.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      This script grants the necessary privs on the sysman user objects to the read-only
Rem      sysman_ro user.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    spramani    01/27/25 - synonyms ne cutover
Rem    akrasto     09/11/24 - logging during errors 
Rem    spramani    01/05/22 - 25 extra synonyms
Rem    spramani    01/22/20 - stop DBMS_OUT
Rem    spramani    12/11/19 - log all EMCT* objects grants
Rem    spramani    11/11/19 - fix bug 30065378
Rem    spramani    07/26/16 - optimize recompile
Rem    kamlesku    04/29/16 - Fix for Bug: 23109757 
Rem    kalchand    11/16/15 - Fix for Bug: 22020758 - ORA-20000 Failed to give
Rem                           grant/create synonym for EM_CNTR_QTABLE
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    rnadidar    12/17/14 - Revove grant to sysman_ro
Rem    mexi        01/14/14 - Grant execute on ad4j_pkg for bug 17258423
Rem    skurra      06/10/13 - Grant execute on chargeback type to sysman ro
Rem    akschand    12/12/12 - Bug 15923156
Rem    debasmoh    07/19/11 - bug# 12536085
Rem    skurra      07/11/11 - CaT addon object grants
Rem    vkhizder    06/30/11 - addl grant
Rem    vkhizder    05/03/11 - adding more synonyms for RO user, 
Rem                           adding direct grants for mgmt$ and cm$ views
Rem    vkhizder    03/31/11 - obj name change
Rem    sbadrina    01/05/11 - grant repos user privs to ro user
Rem    sbadrina    01/05/11 - Created
Rem

SET SERVEROUTPUT ON SIZE 500000

SET VERIFY OFF

DEFINE EM_REPOS_USER = "&1"
DEFINE EM_RO_USER = "&2"
DEFINE EM_ECHO_SQL   = "&3"

-- optimize recompile

-- @&EM_SQL_ROOT/core/latest/admin/admin_recompile_invalid.sql &EM_REPOS_USER

DECLARE
  err number;
  l_owner VARCHAR2(256);
  l_synonym_count NUMBER;
  l_sql varchar2(32767) := '"';

BEGIN

  begin
    for rec in (select object_name,owner from dba_objects where owner='SYSMAN_TYPES' and  object_type like 'TYPE%') 
    loop
    BEGIN
      l_sql := 'grant execute on '||rec.owner||'.'||rec.object_name||' to  SYSMAN with grant option';
      --execute immediate 'grant execute on '||rec.owner||'.'||rec.object_name||' to  SYSMAN_RO ';
      execute immediate l_sql;
    EXCEPTION
      WHEN OTHERS THEN
        insert into sysman.MGMT_EMDEP_LOGS(log_level,component_name,log_message_id,log_message,logging_time) values  ( 1 ,'admin_reposuser_grants_to_ro_user','EXTRA_SYNONYMS_ID#2022',l_sql,sysdate);
    END;
    end loop;
  end;




    -- loop through mgmt$ 
    -- SYSMAN_RO user requires access to all views and tables in SYSMAN schema. 
    --    Ignore grants to temporary IOT tables (using generate=N clause)


    l_owner := '&EM_REPOS_USER' ;
    DBMS_OUTPUT.PUT_LINE('Granting select on ' || l_owner);

    -- Grant select on SYSMAN tables to SYSMAN_RO 
    --    Nested tables are excluded as grants cannot refer to nested table column's storage table 
    --                 AND nested = 'NO'
    --    IOT Overflow tables are excluded to avoiud
    --       ORA-25191: cannot reference overflow table of an index-organized table
    FOR crec in (SELECT table_name, iot_type
                   FROM dba_tables
                  WHERE owner='&EM_REPOS_USER'
                    AND nested = 'NO'
                    AND ( (iot_type IS NULL) OR (iot_type != 'IOT_OVERFLOW') )
                  ORDER BY table_name)
    LOOP
      BEGIN
        -- Note: cannot grant thru the role since derived assocs are using execute immediate 
        -- to exec proc to exec query and role-based grants are not enough; direct grants are
        -- required
      --  DBMS_OUTPUT.PUT_LINE('Granting select on type = TABLE ' || ' name = ' || crec.table_name);
	
        --Retry Logic for Bug 22020758 - ORA-20000 Failed to give grant/create synonym for EM_CNTR_QTABLE 
        BEGIN
          l_sql := 'GRANT SELECT ON &EM_REPOS_USER' || '.' || crec.table_name || ' TO &&EM_RO_USER';
          EXECUTE IMMEDIATE l_sql;

          EXCEPTION
            WHEN OTHERS THEN
              insert into sysman.MGMT_EMDEP_LOGS(log_level,component_name,log_message_id,log_message,logging_time) values  ( 1 ,'admin_reposuser_grants_to_ro_user','EXTRA_SYNONYMS_ID#2022',l_sql,sysdate);
              IF (SQLCODE = -04063 OR SQLCODE = -40362) THEN
                BEGIN
                  em_ebr_util.config_sql_retry(l_sql, 'Failed to give grant for ' || crec.table_name);		
                END;
              ELSE
                RAISE_APPLICATION_ERROR(-20000, 'Failed to give grant for ' || crec.table_name || ' Error : ' || SQLERRM);
              END IF;
        END;

        SELECT COUNT(*) INTO l_synonym_count FROM DBA_SYNONYMS WHERE TABLE_NAME='l_table_name' AND OWNER='&&EM_RO_USER';
        IF (l_synonym_count = 0) THEN 
          BEGIN
            l_sql := 'CREATE OR REPLACE SYNONYM ' || '&&EM_RO_USER' || '.' || crec.table_name || ' FOR '|| '&EM_REPOS_USER' || '.' || crec.table_name;
            EXECUTE IMMEDIATE l_sql;

            EXCEPTION
              WHEN OTHERS THEN
                insert into sysman.MGMT_EMDEP_LOGS(log_level,component_name,log_message_id,log_message,logging_time) values  ( 1 ,'admin_reposuser_grants_to_ro_user','EXTRA_SYNONYMS_ID#2022',l_sql,sysdate);

                IF (SQLCODE = -04063 OR SQLCODE = -40362) THEN
                  BEGIN
                    em_ebr_util.config_sql_retry(l_sql, 'Failed to create synonym for ' || crec.table_name);		
                  END;
                ELSE
                    RAISE_APPLICATION_ERROR(-20000, 'Failed to create synonym for ' || crec.table_name || ' Error : ' || SQLERRM);
                END IF;
          END;
        END IF;
      END;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Done Granting select on SYSMAN Tables ');

    -- Grant select on all views
    FOR crec in (SELECT object_name, object_type FROM dba_objects
                  WHERE status = 'VALID'
                    AND object_type IN ('VIEW')
                    AND owner='&EM_REPOS_USER')
    LOOP
      BEGIN
        -- Note: cannot grant thru the role since derived assocs are using execute immediate 
        -- to exec proc to exec query and role-based grants are not enough; direct grants are
        -- required
        -- DBMS_OUTPUT.PUT_LINE('Granting select on type =' || crec.object_type || ' name = ' || crec.object_name);

        l_sql :=  'GRANT SELECT ON &EM_REPOS_USER' || '.' || crec.object_name || ' TO &&EM_RO_USER';
        EXECUTE IMMEDIATE l_sql;

        l_sql := 'CREATE OR REPLACE SYNONYM ' || '&&EM_RO_USER' || '.' || crec.object_name || ' FOR '|| '&EM_REPOS_USER' || '.' || crec.object_name;
        EXECUTE IMMEDIATE l_sql;

        EXCEPTION
        WHEN OTHERS THEN
          insert into sysman.MGMT_EMDEP_LOGS(log_level,component_name,log_message_id,log_message,logging_time) values  ( 1 ,'admin_reposuser_grants_to_ro_user','EXTRA_SYNONYMS_ID#2022',l_sql,sysdate);
          RAISE_APPLICATION_ERROR(-20000,
                        'Failed to give grant/create synonym for type = ' || crec.object_type || 
                        ' name = ' || crec.object_name || ' Error : ' || SQLERRM);
      END;
    END LOOP;

   -- loop through cat plugin GTTs
   -- Grant insert, update, delete access to temporary tables in SYSMAN schema
    FOR crec in (SELECT object_name FROM dba_objects
                 WHERE object_name like 'EMCT%' 
                   AND object_type='TABLE'
                   AND TEMPORARY='Y' 
                   AND owner='&EM_REPOS_USER')
    LOOP
      BEGIN
        l_sql := 'GRANT SELECT, INSERT, UPDATE, DELETE ON &EM_REPOS_USER' || '.' || 
                             crec.object_name || 
                             ' TO &&EM_RO_USER';
        EXECUTE IMMEDIATE l_sql;

        l_sql := 'CREATE OR REPLACE SYNONYM ' || 
                             '&&EM_RO_USER' || '.' || crec.object_name || 
                             ' FOR '|| 
                             '&EM_REPOS_USER' || '.' || crec.object_name;
        EXECUTE IMMEDIATE l_sql;
        
        EXCEPTION
          WHEN OTHERS THEN
          insert into sysman.MGMT_EMDEP_LOGS(log_level,component_name,log_message_id,log_message,logging_time) values  ( 1 ,'admin_reposuser_grants_to_ro_user','EXTRA_SYNONYMS_ID#2022',l_sql,sysdate);
                  RAISE_APPLICATION_ERROR(-20000,
                  'Failed to give grant/create synonym for ' ||
                    crec.object_name);
	    END;
    END LOOP;

-- grant prev for correspoding EVs to SYSMAN_RO

    FOR crec in (select view_name from dba_editioning_views a where  a.owner='&EM_REPOS_USER' and exists (
    SELECT 1 FROM dba_objects b
                  WHERE object_name like 'EMCT%'
                  AND object_type='TABLE'
                  AND TEMPORARY='Y' AND b.owner='&EM_REPOS_USER' AND a.table_name=b.object_name))
    LOOP
      BEGIN
        l_sql := 'GRANT SELECT, INSERT, UPDATE, DELETE ON &EM_REPOS_USER' || '.' ||
                             crec.view_name ||
                             ' TO &&EM_RO_USER';

        EXECUTE IMMEDIATE l_sql;
        l_sql:= 'CREATE OR REPLACE SYNONYM ' ||
                             '&&EM_RO_USER' || '.' || crec.view_name ||
                             ' FOR '||
                             '&EM_REPOS_USER' || '.' || crec.view_name;
        EXECUTE IMMEDIATE l_sql;
        -- insert into sysman.MGMT_EMDEP_LOGS(log_level,component_name,log_message_id,log_message,logging_time) values  ( 1 ,'admin_reposuser_grants_to_ro_user','EMCT_OBJ_GRANT_TO_RO_ID','GRANT ALL FOR EV:'|| crec.view_name || 'TO SYSMAN_RO',sysdate);

      EXCEPTION
        WHEN OTHERS THEN
          insert into sysman.MGMT_EMDEP_LOGS(log_level,component_name,log_message_id,log_message,logging_time) values  ( 1 ,'admin_reposuser_grants_to_ro_user','EXTRA_SYNONYMS_ID#2022',l_sql,sysdate);
          RAISE_APPLICATION_ERROR(-20000,
          'Failed to give grant/create synonym for EV' ||
            crec.view_name);
      END;
    END LOOP;

   -- loop through da_ packages 
   -- Grant execute on invoker rights procedures 
    FOR pkgname in (SELECT object_name FROM dba_procedures
                     WHERE object_name like 'DA\_%' ESCAPE '\'
                       AND procedure_name is null 
                       AND authid='CURRENT_USER' 
                       AND owner='&EM_REPOS_USER')
    LOOP
      BEGIN
        l_sql := 'GRANT EXECUTE ON &EM_REPOS_USER' || '.' || pkgname.object_name || ' TO &&EM_RO_USER';
        EXECUTE IMMEDIATE l_sql;
        l_sql := 'CREATE OR REPLACE SYNONYM ' || 
                             '&&EM_RO_USER' || '.' || pkgname.object_name || 
                             ' FOR '|| 
                             '&EM_REPOS_USER' || '.' || pkgname.object_name;

        EXECUTE IMMEDIATE l_sql;
        EXCEPTION
          WHEN OTHERS THEN
          insert into sysman.MGMT_EMDEP_LOGS(log_level,component_name,log_message_id,log_message,logging_time) values  ( 1 ,'admin_reposuser_grants_to_ro_user','EXTRA_SYNONYMS_ID#2022',l_sql,sysdate);
            RAISE_APPLICATION_ERROR(-20000,
            'Failed to give grant/create synonym for ' ||
              pkgname.object_name);

	    END;
    END LOOP;

    -- Grant execute on invoker rights procedures
    -- add the package name to the in clause
    FOR pkgname in (SELECT object_name FROM dba_procedures
                     WHERE object_name in ('AD4J_PKG')
                       AND procedure_name is null 
                       AND authid='CURRENT_USER' 
                       AND owner='&EM_REPOS_USER')
    LOOP
      BEGIN
        l_sql := 'GRANT EXECUTE ON &EM_REPOS_USER' || '.' || pkgname.object_name || 
                             ' TO &&EM_RO_USER'; 
        EXECUTE IMMEDIATE l_sql;
        l_sql := 'CREATE OR REPLACE SYNONYM ' || 
                             '&&EM_RO_USER' || '.' || pkgname.object_name || 
                             ' FOR '|| 
                             '&EM_REPOS_USER' || '.' || pkgname.object_name;

        EXECUTE IMMEDIATE l_sql;
        EXCEPTION
          WHEN OTHERS THEN
            insert into sysman.MGMT_EMDEP_LOGS(log_level,component_name,log_message_id,log_message,logging_time) values  ( 1 ,'admin_reposuser_grants_to_ro_user','EXTRA_SYNONYMS_ID#2022',l_sql,sysdate);
            RAISE_APPLICATION_ERROR(-20000,
            'Failed to give grant/create synonym for ' ||
              pkgname.object_name);

        END;
    END LOOP;
-- Privs for mgmt_deriv_changes_temp, mgmt_deriv_new_assocs_temp, mgmt_assoc_inst_origins
EXECUTE IMMEDIATE 'GRANT INSERT ON &EM_REPOS_USER' || '.' || 'MGMT_DERIV_CHANGES_TEMP' || 
                      ' TO &&EM_RO_USER';
EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM ' || '&&EM_RO_USER' || '.' || 'MGMT_DERIV_CHANGES_TEMP' || 
                      ' FOR '|| '&EM_REPOS_USER' || '.' || 'MGMT_DERIV_CHANGES_TEMP';

EXECUTE IMMEDIATE 'GRANT INSERT ON &EM_REPOS_USER' || '.' || 'MGMT_DERIV_NEW_ASSOCS_TEMP' || 
                      ' TO &&EM_RO_USER';
EXECUTE IMMEDIATE 'GRANT DELETE ON &EM_REPOS_USER' || '.' || 'MGMT_DERIV_NEW_ASSOCS_TEMP' || 
                      ' TO &&EM_RO_USER';
EXECUTE IMMEDIATE 'GRANT SELECT ON &EM_REPOS_USER' || '.' || 'MGMT_DERIV_NEW_ASSOCS_TEMP' || 
                      ' TO &&EM_RO_USER';
EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM ' || '&&EM_RO_USER' || '.' || 'MGMT_DERIV_NEW_ASSOCS_TEMP' || 
                      ' FOR '|| '&EM_REPOS_USER' || '.' || 'MGMT_DERIV_NEW_ASSOCS_TEMP';

-- execute on gc$deriv_assoc_result_set and gc$deriv_assoc_row
EXECUTE IMMEDIATE 'GRANT EXECUTE ON &EM_REPOS_USER' || '.' || 'GC$DERIV_ASSOC_RESULT_SET' || 
                      ' TO &&EM_RO_USER';
EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM ' || '&&EM_RO_USER' || '.' || 'GC$DERIV_ASSOC_RESULT_SET' || 
                      ' FOR '|| '&EM_REPOS_USER' || '.' || 'GC$DERIV_ASSOC_RESULT_SET';

EXECUTE IMMEDIATE 'GRANT EXECUTE ON &EM_REPOS_USER' || '.' || 'GC$DERIV_ASSOC_ROW' || 
                      ' TO &&EM_RO_USER';
EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM ' || '&&EM_RO_USER' || '.' || 'GC$DERIV_ASSOC_ROW' || 
                      ' FOR '|| '&EM_REPOS_USER' || '.' || 'GC$DERIV_ASSOC_ROW'; 


-- execute on gc$cba_curr_rollup_target_obj and gc$cba_curr_rollup_targets

EXECUTE IMMEDIATE 'GRANT EXECUTE ON &EM_REPOS_USER' || '.' || 'GC$CBA_CURR_ROLLUP_TARGET_OBJ' ||
                      ' TO &&EM_RO_USER';
EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM ' || '&&EM_RO_USER' || '.' || 'GC$CBA_CURR_ROLLUP_TARGET_OBJ' ||
                      ' FOR '|| '&EM_REPOS_USER' || '.' || 'GC$CBA_CURR_ROLLUP_TARGET_OBJ';

EXECUTE IMMEDIATE 'GRANT EXECUTE ON &EM_REPOS_USER' || '.' || 'GC$CBA_CURR_ROLLUP_TARGETS' ||
                      ' TO &&EM_RO_USER';
EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM ' || '&&EM_RO_USER' || '.' || 'GC$CBA_CURR_ROLLUP_TARGETS' ||
                      ' FOR '|| '&EM_REPOS_USER' || '.' || 'GC$CBA_CURR_ROLLUP_TARGETS'; 


COMMIT;
END;
/

GRANT CREATE PROCEDURE TO &EM_RO_USER;

DECLARE
  l_repos_owner  VARCHAR2(256);
  l_view_user  VARCHAR2(256);
  l_synonym_exists_and_ne NUMBER;
  l_synonym_exists_and_e NUMBER;
  l_error_message VARCHAR2(4000);
BEGIN
  -- Get the owner of repository schema
  l_repos_owner := '&EM_REPOS_USER';
  -- Get view user name
  l_view_user := '&EM_RO_USER';

  EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM '|| l_view_user ||'.MGMT_TARGET_GUID_ARRAY FOR '|| l_repos_owner ||'.MGMT_TARGET_GUID_ARRAY';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM '|| l_view_user ||'.MGMT_METRIC_VALUE_ARRAY FOR '|| l_repos_owner ||'.MGMT_METRIC_VALUE_ARRAY';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM '|| l_view_user ||'.MGMT_METRIC_VALUE_OBJ FOR '|| l_repos_owner ||'.MGMT_METRIC_VALUE_OBJ';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM '|| l_view_user ||'.MGMT_METRIC_RESULTS FOR '|| l_repos_owner ||'.MGMT_METRIC_RESULTS';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM '|| l_view_user ||'.MGMT_METRIC_RESULT FOR '|| l_repos_owner ||'.MGMT_METRIC_RESULT'; 
  
  BEGIN
    SELECT COUNT(*) INTO l_synonym_exists_and_ne
    FROM DBA_SYNONYMS 
    WHERE SYNONYM_NAME = 'MGMT_MEDIUM_STRING_ARRAY'
    AND OWNER = 'SYSMAN_RO' 
    AND EXISTS (
        SELECT OBJECT_NAME 
        FROM DBA_OBJECTS 
        WHERE OBJECT_TYPE = 'SYNONYM' 
        AND OBJECT_NAME = 'MGMT_MEDIUM_STRING_ARRAY' 
        AND EDITION_NAME IS NULL
    );

    SELECT COUNT(*) INTO l_synonym_exists_and_e
    FROM DBA_SYNONYMS 
    WHERE SYNONYM_NAME = 'MGMT_MEDIUM_STRING_ARRAY'
    AND OWNER = 'SYSMAN_RO' 
    AND EXISTS (
        SELECT OBJECT_NAME 
        FROM DBA_OBJECTS 
        WHERE OBJECT_TYPE = 'SYNONYM' 
        AND OBJECT_NAME = 'MGMT_MEDIUM_STRING_ARRAY' 
        AND EDITION_NAME IS NOT NULL
    );

    IF l_synonym_exists_and_ne > 0 THEN
        MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'admin_reposuser_grants_to_ro_user', 'EXTRA_SYNONYMS_ID#2022', 'MGMT_MEDIUM_STRING_ARRAY synonym already non editionable so no action taken.');                    
    ELSIF (l_synonym_exists_and_e > 0) THEN
        MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'admin_reposuser_grants_to_ro_user', 'EXTRA_SYNONYMS_ID#2022', 'MGMT_MEDIUM_STRING_ARRAY synonym already exists and is editionable, it will be made non editionable in upgrade path.');                    
    ELSE    
        EXECUTE IMMEDIATE 'CREATE NONEDITIONABLE SYNONYM SYSMAN_RO.MGMT_MEDIUM_STRING_ARRAY FOR SYSMAN_TYPES.MGMT_MEDIUM_STRING_ARRAY';
    END IF;    

  EXCEPTION
    WHEN OTHERS THEN
        l_error_message := 'Encountered error when dropping and creating synonym MGMT_MEDIUM_STRING_ARRAY ' || SQLERRM;
        MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'admin_reposuser_grants_to_ro_user', 'EXTRA_SYNONYMS_ID#2022', l_error_message);  
        RAISE;                
  END;

  
  EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM '|| l_view_user ||'.MGMT_NAMEVALUE_ARRAY FOR '|| l_repos_owner ||'.MGMT_NAMEVALUE_ARRAY';
  EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM '|| l_view_user ||'.MGMT_NAMEVALUE_OBJ FOR '|| l_repos_owner ||'.MGMT_NAMEVALUE_OBJ';  

EXCEPTION WHEN OTHERS THEN 
  RAISE;
END;
/

--
-- Create synonym for CONFIG_UTIL package,function in this
-- package is used in the source sql for  DB_REDO_LOG_COUNT
-- and  DB_CONTROL_FILE_COUNT metric of oracle_database target type
-- 
DECLARE
  l_repos_owner  VARCHAR2(256);
  l_view_user  VARCHAR2(256);
BEGIN
  -- Get the owner of repository schema
  l_repos_owner := '&EM_REPOS_USER';
  -- Get view user name
  l_view_user := '&EM_RO_USER';

  EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM '|| l_view_user ||'.CONFIG_UTIL FOR '|| l_repos_owner ||'.CONFIG_UTIL';

EXCEPTION WHEN OTHERS THEN
  -- Ignore Error
  NULL;
END;
/

GRANT EXECUTE ON "&EM_REPOS_USER".MGMT_TARGET_GUID_ARRAY TO &EM_RO_USER;
GRANT EXECUTE ON "&EM_REPOS_USER".MGMT_METRIC_VALUE_ARRAY TO &EM_RO_USER;
GRANT EXECUTE ON "&EM_REPOS_USER".MGMT_METRIC_VALUE_OBJ TO &EM_RO_USER;
GRANT EXECUTE ON "&EM_REPOS_USER".MGMT_METRIC_RESULTS TO &EM_RO_USER;
GRANT EXECUTE ON "&EM_REPOS_USER".MGMT_METRIC_RESULT TO &EM_RO_USER;

GRANT EXECUTE ON SYSMAN_TYPES.MGMT_MEDIUM_STRING_ARRAY TO &EM_RO_USER;
GRANT EXECUTE ON "&EM_REPOS_USER".MGMT_NAMEVALUE_ARRAY TO &EM_RO_USER;
GRANT EXECUTE ON "&EM_REPOS_USER".MGMT_NAMEVALUE_OBJ TO &EM_RO_USER;



DECLARE
  l_sql varchar2(32767) := '"';
  l_err__code NUMBER;
  l_err__msg VARCHAR2(32000);
  begin
    for rec in (select object_name,owner from dba_objects where object_type like 'TYPE%' and owner='SYSMAN_TYPES')
    loop
    BEGIN
      l_sql := 'create or replace noneditionable synonym sysman.'||rec.object_name||' for '||rec.owner||'.'||rec.object_name;
      EXECUTE IMMEDIATE l_sql;
    EXCEPTION
      WHEN OTHERS THEN
        l_err__code := SQLCODE;
        l_err__msg := SQLERRM;
        insert into sysman.MGMT_EMDEP_LOGS(log_level,component_name,log_message_id,log_message,logging_time) values  ( 1 ,'admin_reposuser_grants_to_ro_user','EXTRA_SYNONYMS_ID#2022','err: ' || l_err__code || ', err msg: '||l_err__msg || ' while executing: ' ||   l_sql,sysdate);
    END;
    end loop;
end;
/




set verify &EM_ECHO_SQL


