Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/emregistry/latest/emlcm_admin_pkgbody.sql /main/6 2023/01/10 06:11:54 spramani Exp $
Rem
Rem emlcm_admin_pkgbody.sql
Rem
Rem Copyright (c) 2022, Oracle and/or its affiliates.
Rem
Rem    NAME
Rem      emlcm_admin_pkgbody.sql - has LCM ADMIN user related APIs 
Rem
Rem    DESCRIPTION
Rem      provides LCM ADMIN user related APIs 
Rem
Rem    NOTES
Rem      NONE
Rem
Rem    BEGIN SQL_FILE_METADATA
Rem    SQL_SOURCE_FILE: emcore/source/oracle/sysman/emdrep/sql/emregistry/latest/emlcm_admin_pkgbody.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    12/15/22 - cbd container check
Rem    spramani    11/16/22 - new return code
Rem    spramani    10/27/22 - fix day 0 lcm session user
Rem    spramani    10/16/22 - LCM tracking fixes
Rem    spramani    09/20/22 - review comments
Rem    spramani    09/08/22 - track LCM ADMINs
Rem    spramani    09/04/22 - EM LCM Admin utilities
Rem    spramani    09/04/22 - Created
Rem

DEFINE MY_REPOS_USER = "&1"
DEFINE MY_DBA_USER = "&2"

ALTER SESSION SET CURRENT_SCHEMA=&MY_REPOS_USER;

CREATE OR REPLACE PACKAGE BODY EM_LCM_ADMIN AS

  -- functions that returned constants
  FUNCTION phase_create
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_CREATE;
  END phase_create;

  FUNCTION phase_upgrade
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_UPGRADE;
  END phase_upgrade;

  FUNCTION phase_preupgrade
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_PREUPGRADE;
  END phase_preupgrade;

  FUNCTION phase_deferred
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_DEFERRED;
  END phase_deferred;

  FUNCTION phase_plugin
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_PLUGIN;
  END phase_plugin;

  FUNCTION phase_drop
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_DROP;
  END phase_drop;

  FUNCTION phase_revertpre
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_REVERTPRE;
  END phase_revertpre;

  FUNCTION phase_outofbox
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_OUTOFBOX;
  END phase_outofbox;


  FUNCTION phase_purgecomp
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_PURGECOMP;
  END phase_purgecomp;

  FUNCTION phase_purgeplugin
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_PURGEPLUGIN;
  END phase_purgeplugin;

  FUNCTION phase_ebrify
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_EBRIFY;
  END phase_ebrify;

  FUNCTION phase_ruapply
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_RUAPPLY;
  END phase_ruapply;


  FUNCTION phase_rurollback
    RETURN NUMBER IS
  BEGIN
    return G_PHASE_RUROLLBACK;
  END phase_rurollback;

FUNCTION IS_ONP
  RETURN VARCHAR2 
  IS 
    l_dbtype VARCHAR2(500) := NULL;
    l_onprem VARCHAR2(500);
  BEGIN
     begin 
         select sys_context('USERENV','CLOUD_SERVICE') into l_onprem from dual;
     exception 
         when others then 
         l_dbtype := NULL;
     end;
     l_dbtype := NVL(l_onprem,'ONP');
     return l_dbtype; 
END IS_ONP;

FUNCTION lcm_op_started_by(p_lcmadmin IN VARCHAR2, p_lcm_op IN NUMBER)
RETURN NUMBER
IS PRAGMA AUTONOMOUS_TRANSACTION;
      l_current NUMBER;
      l_dbtype VARCHAR2(500);
BEGIN
    SELECT count(*)
      INTO l_current
      FROM MGMT_LCM_ADMINS 
      WHERE completed IS NULL;

    IF l_current > 0
    THEN
      RETURN 0;
    END IF;

    SELECT MGMT_LCM_ADMIN_SEQ.NEXTVAL
      INTO l_current
      FROM DUAL;

    INSERT INTO MGMT_LCM_ADMINS 
        (id, lcm_admin, lcm_op)
      VALUES
        (l_current, 
         p_lcmadmin, 
         p_lcm_op);
    select IS_ONP into l_dbtype from dual;
    
    COMMIT;
    return l_current;
END lcm_op_started_by;

FUNCTION lcm_op_completed_by(p_lcmadmin IN VARCHAR2, p_lcm_op IN NUMBER)
RETURN NUMBER
IS PRAGMA AUTONOMOUS_TRANSACTION;
      l_current NUMBER;
BEGIN
   BEGIN
       SELECT id
         INTO l_current
         FROM MGMT_LCM_ADMINS 
         WHERE lcm_op = p_lcm_op and lcm_admin = p_lcmadmin 
           AND completed IS NULL;

          UPDATE MGMT_LCM_ADMINS 
              SET completed = sysdate
              WHERE id = l_current;
          COMMIT;
       return l_current;
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
         return 0;
     END;

END lcm_op_completed_by;

FUNCTION get_last_lcm_admin
    RETURN VARCHAR2 IS
      l_adminuser VARCHAR2(500);
  BEGIN
    l_adminuser := 'SYS';
    BEGIN
      SELECT lcm_admin INTO l_adminuser
        FROM (SELECT lcm_admin 
                FROM mgmt_lcm_admins
                WHERE id > 0
                ORDER BY id DESC
             )
        WHERE ROWNUM = 1;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        return l_adminuser;
    END;
    return l_adminuser;
  END get_last_lcm_admin;


FUNCTION is_lcm_adminuser(p_username IN VARCHAR2)
RETURN NUMBER
IS
l_count number;
l_lcmadmin VARCHAR2(500);
l_dbtype VARCHAR2(500);
l_overwrite VARCHAR2(500);
BEGIN
    BEGIN 
        select get_last_lcm_admin into l_lcmadmin from dual; 
        if NVL(l_lcmadmin,'NONE') = 'NONE' then 
            -- no previous LCM sessions 
            BEGIN 
                select count(*) into l_count from dba_role_privs where upper(grantee)=upper(p_username) and granted_role='OEM_DBA';
                if l_count = 1
                then 
                   return 1;
                else
                   BEGIN
                       select IS_ONP into l_dbtype from dual;
                       if (((l_dbtype = 'ONP') and (upper(p_username) = 'SYS')) OR ((l_dbtype != 'ONP') and (upper(p_username) = 'ADMIN')))  then 
                           return 1;
                       else 
                           return 0;
                       end if;
                   END;
                end if;
            END;
        else
           -- previous LCM sessions found 
           --TODO delete 
           -- DBMS_OUTPUT.PUT_LINE('multiple sessions found ');
           BEGIN 
               if upper(l_lcmadmin) = upper(p_username) then 
                   --TODO delete 
                   -- DBMS_OUTPUT.PUT_LINE('lcmadmin:  ' || l_lcmadmin ||' and username: ' || p_username);
                   return 1;
               else 
                    -- users are not the same 
                    -- SYS -> LCM_ADMIN 
                    -- LCM_ADMIN -> SYS 
                    -- SYS -> X 
                    -- LCM_ADMIN -> X 
                    select IS_ONP into l_dbtype from dual;
                    if l_dbtype = 'ONP'
                    then
                        select count(*) into l_count from dba_role_privs where granted_role='OEM_DBA' and upper(grantee) = upper(p_username) and upper(grantee) not in ('SYS');
                    else
                        select count(*) into l_count from dba_role_privs where granted_role='OEM_DBA' and upper(grantee) = upper(p_username) and upper(grantee) not in ('ADMIN');
                    end if;

                    if l_count = 1    -- LCM ADMIN user can always do LCM after previous LCM by SYS/LCM ADMIN  
                    then 
                        return 1;
                    else
                        BEGIN
                           -- SYS can't do LCM after previous LCM by LCM ADMIN
                           -- if internal param is set 
                           begin 
                               l_overwrite := 'FALSE';
                               select property_value into l_overwrite from MGMT_LCM_PROPERTIES where property_name = 'SYSADMIN_CAN_DO_LCM'; 
                           exception 
                               when others then 
                               l_overwrite := 'FALSE';
                               -- TODO delete 
                               -- DBMS_OUTPUT.PUT_LINE('overwrite : ' || l_overwrite);
                           end;

                           select IS_ONP into l_dbtype from dual;
                           if ((l_dbtype = 'ONP')  and (upper(p_username) ='SYS')) OR ((l_dbtype != 'ONP') and (upper(p_username) ='ADMIN')) then 
                               -- TODO delete 
                               -- DBMS_OUTPUT.PUT_LINE('SYS or ADMIN ');
                               if l_overwrite  = 'TRUE' then  
                                   return 1;
                               else 
                                   -- SYS/ ADMIN is tried after LCM_ADMIN 
                                   return 10;
                               end if;
                           else 
                               -- not a SYS or ADMIN 
                               return 0;
                           end if;
                        END;
                    end if;
               end if;                   
           END;
        end if;
    EXCEPTION 
            when others 
            then 
                return 0;
    END;
END is_lcm_adminuser;

FUNCTION get_session_lcm_adminuser
RETURN VARCHAR2 
IS
l_lcmadmin VARCHAR2(255);
l_dbtype VARCHAR2(64);
BEGIN
    BEGIN 
       select get_last_lcm_admin into l_lcmadmin from dual; 
       -- dbms_output.put_line('last lcm admin: ' || l_lcmadmin);
       if NVL(l_lcmadmin,'NONE')  = 'NONE' then 
            -- this means LCM MODE has not started 
            return 'SYS';
            -- raise_application_error(-20206, 'NO EM LCM Admin user found that started this session');
       else 
            return l_lcmadmin;
       end if;
    EXCEPTION 
        when others then 
            raise_application_error(-20206, 'NO EM LCM Admin user found that started this session, error: '||SQLCODE||' -ERROR- '||SQLERRM);
    END;
END get_session_lcm_adminuser;
FUNCTION is_cdb_con
RETURN NUMBER
IS 
iscdb VARCHAR2(32) := 'NO'; 
iscdb_con VARCHAR2(255); 
BEGIN
    BEGIN 
       select CDB into iscdb from V$DATABASE;
       if iscdb = 'YES' then 
           select sys_context('USERENV','CON_NAME') into iscdb_con from dual;
           if iscdb_con = 'CDB$ROOT' then 
                return 1;
           else 
               return 0;
           end if; 
       else 
           return 0;
       end if;
    END;
END is_cdb_con;

END EM_LCM_ADMIN;
/
--show errors;


ALTER SESSION SET CURRENT_SCHEMA=&MY_DBA_USER;

