Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_profiles.sql /main/6 2025/08/29 10:20:11 spramani Exp $
Rem
Rem admin_profiles.sql
Rem
Rem Copyright (c) 2008, 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_profiles.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_profiles.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_profiles.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    MODIFIED   (MM/DD/YY)
Rem    spramani    08/28/25 - fix multiple schema for em pvf
Rem    spramani    08/19/25 - atps - cloud pvf
Rem    spramani    05/08/25 - atps support
Rem    spramani    12/15/22 - remove DB user var
Rem    spramani    05/10/22 - PDBA deploy
Rem    spramani    01/15/22 - ATP-s suport
Rem    jashukla    05/29/08 - Created
Rem

DEFINE DB_USER = '&1'
declare
   pcount NUMBER;
   iscdb VARCHAR2(32) := 'NO'; 
   iscdb_con VARCHAR2(255); 
   uname VARCHAR2(255);
   onprem VARCHAR2(255) := 'ONP';
   lcm_admin_exists number := 0;
   lcm_admin VARCHAR2(255);
   l_dbtype VARCHAR2(255);
   l_postsql VARCHAR2(255);
   l_pvfowner VARCHAR2(255);
   l_pvfcount number := 0;
   l_sql VARCHAR2(1024);
   PROCEDURE remove_dup_pvf(p_pvf_func VARCHAR2)
   IS 
   BEGIN
      -- DBMS_OUTPUT.PUT_LINE('inside remove_dup_pvf');
      select count(*) into l_pvfcount from dba_objects where object_name=p_pvf_func and object_type='FUNCTION' and owner in ('SYSMAN', 'SYSMAN_RO', 'SYSMAN_ZDTUSER', 'SYSMAN_MDS', 'MGMT_VIEW') or owner like 'SYSMAN_OPSS%';
      if (l_pvfcount > 0 ) then 
          -- DBMS_OUTPUT.PUT_LINE(' found dup PVF');
          for crec in ( select owner from dba_objects where object_name=p_pvf_func and object_type='FUNCTION' and (owner in ('SYSMAN', 'SYSMAN_RO', 'SYSMAN_ZDTUSER', 'SYSMAN_MDS', 'MGMT_VIEW') or owner like 'SYSMAN_OPSS%'))
          loop
              l_sql := 'drop function ' || crec.owner||'.'||p_pvf_func;
              DBMS_OUTPUT.PUT_LINE('remove_dup_pvf: now running: ' || l_sql);
              execute immediate l_sql;
          end loop;
      end if;
   END remove_dup_pvf;
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');
    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(-20112, 'you cannot run this script in CDB containter, run it inside a PDB');
           end if;
       end if;
    END;
    BEGIN
        select count(*) into lcm_admin_exists from dba_role_privs where granted_role='OEM_DBA' and grantee=uname; 
        if (lcm_admin_exists = 0 )
        then 
            if l_dbtype = 'ONP'
            then 
                if (uname != 'SYS' )
                then  
                    raise_application_error(-20114, 'No privilege to run this script');
                end if;
            else
               if (uname != 'ADMIN' )
               then  
                   raise_application_error(-20115, 'No privilege to run this script');
               end if;
            end if;
        end if;
    END;
    BEGIN 
       remove_dup_pvf('MGMT_INTERNAL_PASS_VERIFY');
       if l_dbtype = 'ONP' then 
           -- in On-Prem, PVF owner is always SYS 
           l_postsql := 'PASSWORD_VERIFY_FUNCTION MGMT_INTERNAL_PASS_VERIFY';
       else 
           -- On autonomus DB, it's owned by ADMIN if EM's PVF is used, else it's owned by SYS if CLOUD PVF is used 
           select count(*) into l_pvfcount from dba_objects where object_name='MGMT_INTERNAL_PASS_VERIFY' and object_type='FUNCTION'; 
           if ( l_pvfcount > 0 ) then 
               l_postsql := 'PASSWORD_VERIFY_FUNCTION '||'&DB_USER'||'.' ||'MGMT_INTERNAL_PASS_VERIFY '; 
           else 
                l_postsql := 'PASSWORD_VERIFY_FUNCTION CLOUD_VERIFY_FUNCTION';
           end if;
       end if;
    END;
   select count(*) into pcount from dba_profiles where profile='MGMT_INTERNAL_USER_PROFILE';
   if (pcount = 0)
   then
      execute immediate 
           'create profile MGMT_INTERNAL_USER_PROFILE LIMIT '||
           'PASSWORD_LIFE_TIME UNLIMITED '||
           'PASSWORD_GRACE_TIME UNLIMITED '||
           'PASSWORD_REUSE_TIME UNLIMITED '||
           'PASSWORD_REUSE_MAX UNLIMITED '||
           'FAILED_LOGIN_ATTEMPTS UNLIMITED '||
           'PASSWORD_LOCK_TIME UNLIMITED '|| l_postsql;
   else
       execute immediate 
           'alter profile MGMT_INTERNAL_USER_PROFILE LIMIT '||
           'PASSWORD_LIFE_TIME UNLIMITED '||
           'PASSWORD_GRACE_TIME UNLIMITED '||
           'PASSWORD_REUSE_TIME UNLIMITED '||
           'PASSWORD_REUSE_MAX UNLIMITED '||
           'FAILED_LOGIN_ATTEMPTS UNLIMITED '||
           'PASSWORD_LOCK_TIME UNLIMITED '|| l_postsql;
   end if;
   
   BEGIN 
       remove_dup_pvf('MGMT_PASS_VERIFY');
       if l_dbtype = 'ONP' then 
           -- in On-Prem, PVF owner is always SYS 
           l_postsql := 'PASSWORD_VERIFY_FUNCTION MGMT_PASS_VERIFY';
       else 
           -- On autonomus DB, it's owned by ADMIN if EM's PVF is used, else it's owned by SYS if CLOUD PVF is used 
           select count(*) into l_pvfcount from dba_objects where object_name='MGMT_PASS_VERIFY' and object_type='FUNCTION'; 
           if ( l_pvfcount > 0 ) then 
               l_postsql := 'PASSWORD_VERIFY_FUNCTION '||'&DB_USER'||'.' ||'MGMT_PASS_VERIFY '; 
           else 
                l_postsql := 'PASSWORD_VERIFY_FUNCTION CLOUD_VERIFY_FUNCTION ';
           end if;
       end if;
   END;
   select count(*) into pcount from dba_profiles where profile='MGMT_ADMIN_USER_PROFILE';
   if (pcount = 0)
   then
      execute immediate 
          'create profile MGMT_ADMIN_USER_PROFILE LIMIT '||
          'PASSWORD_LIFE_TIME 180 '||
          'PASSWORD_GRACE_TIME 7 '||
          'PASSWORD_REUSE_TIME UNLIMITED '||
          'PASSWORD_REUSE_MAX UNLIMITED '||
          'FAILED_LOGIN_ATTEMPTS 10 '||
          'PASSWORD_LOCK_TIME 1 '|| l_postsql;
   else
      execute immediate 
          'alter profile MGMT_ADMIN_USER_PROFILE LIMIT '||
          'PASSWORD_LIFE_TIME 180 '||
          'PASSWORD_GRACE_TIME 7 '||
          'PASSWORD_REUSE_TIME UNLIMITED '||
          'PASSWORD_REUSE_MAX UNLIMITED '||
          'FAILED_LOGIN_ATTEMPTS 10 '||
          'PASSWORD_LOCK_TIME 1 '|| l_postsql;
          
          
   end if;
   
end;
/


