Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_profiles.sql /main/4 2023/01/10 06:11:54 spramani Exp $
Rem
Rem admin_profiles.sql
Rem
Rem Copyright (c) 2008, 2022, Oracle and/or its affiliates.
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    12/15/22 - remove DB user var
Rem    spramani    05/10/22 - PDBA deploy
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);
begin
    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 sys_context( 'userenv', 'session_user' ) into uname from dual;
        select sys_context('USERENV','CLOUD_SERVICE') into onprem from dual;
        select count(*) into lcm_admin_exists from dba_role_privs where granted_role='OEM_DBA' and grantee=uname; 
        if (lcm_admin_exists = 0 )
        then 
            l_dbtype := NVL(onprem,'ONP');
            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;
   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 '||
           -- 'PASSWORD_VERIFY_FUNCTION '||'DB_USER'||'.' ||'MGMT_INTERNAL_PASS_VERIFY ' ; --FOR ATP-s 
           'PASSWORD_VERIFY_FUNCTION MGMT_INTERNAL_PASS_VERIFY ' ;
   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 '||
   --        'PASSWORD_VERIFY_FUNCTION '||'DB_USER'||'.' ||'MGMT_INTERNAL_PASS_VERIFY ' ; -- FOR ATP-s 
           'PASSWORD_VERIFY_FUNCTION MGMT_INTERNAL_PASS_VERIFY ' ;
   end if;
   
   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 '||
          --'PASSWORD_VERIFY_FUNCTION '||'DB_USER'||'.' || 'MGMT_PASS_VERIFY ' ;  -- FOR ATP-s 
          'PASSWORD_VERIFY_FUNCTION MGMT_PASS_VERIFY ' ;
   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 '||
          --'PASSWORD_VERIFY_FUNCTION '||'DB_USER'||'.'|| 'MGMT_PASS_VERIFY ' ;  -- FOR ATP-s 
          'PASSWORD_VERIFY_FUNCTION MGMT_PASS_VERIFY ' ;
          
          
   end if;
   
end;
/

