Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_transfer_privs_to_lcm_admin.sql /main/7 2025/02/04 13:29:13 akrasto Exp $
Rem
Rem admin_transfer_privs_to_lcm_admin.sql
Rem
Rem Copyright (c) 2022, 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_to_lcm_admin.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_transfer_privs_to_lcm_admin.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_to_lcm_admin.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_to_lcm_admin.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    akrasto     10/15/24 - privs fix
Rem    spramani    10/04/24 - update version
Rem    spramani    10/04/24 - add privileges for roles for EM schemas
Rem    spramani    07/22/24 - transfer role grants to lcm-admin
Rem    spramani    01/27/23 - stop dbms_output
Rem    spramani    12/15/22 - stop creating sys procs in CDB container
Rem    spramani    11/29/22 - all-case LCM ADMIN
Rem    spramani    10/11/22 - Created
Rem
define LCM_ADMIN_USER = "&1"
declare 
  l_sql varchar2(1024);
  uname VARCHAR2(255);
  l_admin_user varchar2(30);
  l_ev number;
  onprem VARCHAR2(255) := 'ONP';
  lcm_admin_exists number := 0;
  lcm_admin VARCHAR2(255);
  l_dbtype VARCHAR2(255);
  iscdb VARCHAR2(32) := 'NO'; 
  iscdb_con VARCHAR2(255); 
  l_valid_user VARCHAR2(128);
procedure exec_immediate(p_sql in varchar2)
is
BEGIN
           -- dbms_output.put_line(l_sql);
            EXECUTE IMMEDIATE p_sql;
           -- dbms_output.put_line('done running: ' || p_sql);
EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line ('error: ' || SQLERRM ||' in executing :' || p_sql);       
END;
    
BEGIN
     BEGIN 
         -- TODO 
         --DBMS_OUTPUT.PUT_LINE('LCM_ADMIN_USER: ' || '&LCM_ADMIN_USER');
         if (( LENGTH(SYS.DBMS_ASSERT.enquote_literal('&LCM_ADMIN_USER')) > 130) OR  (LENGTH(SYS.DBMS_ASSERT.enquote_literal('&LCM_ADMIN_USER')) <2))    -- enquote adds two more chars  
         then 
                raise_application_error(-20137, 'Invalid EM LCM Admin user name');
         else
                BEGIN
                     select SYS.DBMS_ASSERT.SIMPLE_SQL_NAME('&LCM_ADMIN_USER') into l_valid_user from dual;
                EXCEPTION when others 
                THEN 
                     raise_application_error(-20138, 'Invalid EM LCM Admin user name provided'); 
                END; 
         end if;
     END;
     BEGIN 
       select CDB into iscdb from V$DATABASE;
--       iscdb := 'YES';
       if iscdb = 'YES' then 
           select sys_context('USERENV','CON_NAME') into iscdb_con from dual;
  --          iscdb_con := 'CDB$ROOT';
           if iscdb_con = 'CDB$ROOT' then 
               raise_application_error(-20132, 'you cannot run this script in CDB containter, run it inside a PDB');
           end if;
       end if;
     END;
     BEGIN
        select sys_context( 'userenv', 'session_user' ) into uname from dual;
        select sys_context('USERENV','CLOUD_SERVICE') into onprem from dual;
        l_dbtype := NVL(onprem,'ONP');
        if l_dbtype = 'ONP'
        then 
                if (uname != 'SYS' )
                then  
                    raise_application_error(-20134, 'No privilege to run this script');
                end if;
        else
               if (uname != 'ADMIN' )
               then  
                   raise_application_error(-20135, 'No privilege to run this script');
               end if;
        end if;
     END;
     l_admin_user := upper('&LCM_ADMIN_USER');
     
      select count(*) into lcm_admin_exists from dba_role_privs where granted_role='OEM_DBA' and grantee=l_admin_user; 
      if (lcm_admin_exists = 0 )
      then  
           raise_application_error(-20139, 'Invalid LCM ADMIN user');
      end if;
     l_ev := 0;
     l_sql := NULL;
     begin
         for obj_rec in ( select object_name, object_type, owner from dba_objects where owner in ( select username from dba_users where username like '%SYSMAN%' or username='MGMT_VIEW') and object_type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','SEQUENCE', 'TABLE', 'TYPE', 'TYPE BODY') and object_name not in (select table_name from dba_tables where iot_type='IOT_OVERFLOW') and object_name not in (select table_name from dba_nested_tables where owner like '%SYSMAN%' or owner='MGMT_VIEW'))
         loop
             begin
                 if ( obj_rec.object_type = 'SEQUENCE' ) then 
                     l_sql := 'grant SELECT on '||obj_rec.owner ||'.'||obj_rec.object_name || ' to ' || l_admin_user ;
                 elsif (( obj_rec.object_type = 'PACKAGE' ) or (obj_rec.object_type = 'PACKAGE BODY') or (obj_rec.object_type = 'FUNCTION') or (obj_rec.object_type = 'PROCEDURE') or (obj_rec.object_type = 'TYPE') or (obj_rec.object_type = 'TYPE BODY')) then 
                      l_sql := 'grant EXECUTE on '||obj_rec.owner ||'.'||obj_rec.object_name || ' to ' || l_admin_user ; 
                 elsif ( obj_rec.object_type = 'TABLE' ) then  
                      l_sql := 'grant SELECT,INSERT,UPDATE,DELETE on '||obj_rec.owner ||'.'||obj_rec.object_name || ' to ' || l_admin_user ;
                 else
                     dbms_output.put_line ('NULL');
                 end if;
                 -- dbms_output.put_line(l_sql);
                 if ( l_sql IS NOT NULL ) then 
                     exec_immediate(l_sql);
                 end if;
             end;
         end loop;
     end;
     begin
         for obj_rec in ( select object_name, object_type, owner from dba_objects where owner in (select username from dba_users where username like '%SYSMAN%' or username='MGMT_VIEW' ) and object_type in ('VIEW') and object_name not in (select view_name from dba_editioning_views where owner in (select username from dba_users where username like '%SYSMAN%' or username='MGMT_VIEW')))
         loop
             begin
                 l_sql := 'grant SELECT on '||obj_rec.owner ||'.'||obj_rec.object_name || ' to ' || l_admin_user ;
                 -- dbms_output.put_line(l_sql);
                 exec_immediate(l_sql);
             end;
         end loop;
     end;
     begin
         for obj_rec in ( select view_name, owner from dba_editioning_views where owner in (select username from dba_users where username like '%SYSMAN%' or username='MGMT_VIEW' )) 
         loop
             begin
                 l_sql := 'grant SELECT,INSERT,UPDATE,DELETE on '||obj_rec.owner ||'.'||obj_rec.view_name || ' to ' || l_admin_user ;
                 --dbms_output.put_line(l_sql);
                 exec_immediate(l_sql);
             end;
         end loop;
     end;
     begin 
         for obj_rec in ( select unique granted_role from dba_role_privs where grantee in ('SYSMAN', 'SYSMAN_RO', 'MGMT_VIEW')) 
         loop 
             l_sql := 'grant ' || obj_rec.granted_role || ' to ' || l_admin_user || ' with ADMIN option'; 
             --dbms_output.put_line(l_sql);
             exec_immediate(l_sql);
         end loop;
     end;
END;
/

