Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_sys_procs.sql /main/6 2025/08/29 10:20:11 spramani Exp $
Rem
Rem admin_sys_procs.sql
Rem
Rem Copyright (c) 2008, 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_sys_procs.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_sys_procs.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_sys_procs.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/19/25 - atp-s cutove-cloud pvf
Rem    sactyagi    13/08/24 - bugId 36440329 grant privilege MGMT_INTERNAL_PASS_VALIDATE
Rem    spramani    12/15/22 - stop creating sys procs in CDB container
Rem    tsubrama    07/12/21 - fix for bug 31697124 - password validations for
Rem                           SYSMAN
Rem    spramani    05/20/09 - fixing # 8503890
Rem    chyu        02/20/09 - putting the workaround for the default users
Rem    jashukla    05/29/08 - Created
Rem
-- -------------------------
-- IMPORTANT NOTE:
-- -------------------------
-- Installer has set of validations when creating SYSMAN user with EM Repository in fresh DB.
-- Same set of validations need to be done when SYSMAN user's password is changed after EM Installation.
-- This function is following the same validations (by fixing bug# 31697124) provided by Installer, so if any modification 
-- done in the validation needs to be taken care in the Installer code as well.
-- refer emcore/install/validateXmls/noseed_validate.xml
-- -------------------------
declare 
l_sql VARCHAR2(32700);
uname VARCHAR2(255);
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);

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(-20122, '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; 
        l_dbtype := NVL(onprem,'ONP');
        if (lcm_admin_exists = 0 )
        then 
            if l_dbtype = 'ONP'
            then 
                if (uname != 'SYS' )
                then  
                    raise_application_error(-20124, 'No privilege to run this script');
                end if;
            else
               if (uname != 'ADMIN' )
               then  
                   raise_application_error(-20125, 'No privilege to run this script');
               end if;
            end if;
        end if;
    END;
    BEGIN
    l_sql:= q'[CREATE OR REPLACE FUNCTION MGMT_INTERNAL_PASS_VALIDATE (
    password varchar2)
RETURN integer IS 
    n integer;

  one_char_of_pwd varchar2(1);
  first_char_not_in varchar2(30) := '0123456789_$ #';
  should_contain_char varchar2(30) := '0123456789';
  found_contain_char number := 0;
  should_be_in_chars varchar2(150) := '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_ #$';
  validation_err_msg varchar(256);
  status_code number := 0;
BEGIN 
    -- Check for the minimum length of the password

    if length(trim(password)) = 0 then
       status_code := 20100;
    end if;

    if status_code = 0 and length(password) > 30 then
       status_code := 20101;
    end if;

    if status_code = 0  and length(password) < 8 then
       status_code := 20102;
    end if;

    for indx in 1..length(password)
    loop
       one_char_of_pwd := substr(password,indx, 1);

       -- first char should not be any char in <first_char_not_in> 
       if( indx=1 and instr(first_char_not_in, one_char_of_pwd) > 0 ) then
          status_code := 20103;
          exit;
       end if;

       -- check if it is having one of chars in <should_contain_char> as MUST char
       if( found_contain_char=0 and instr(should_contain_char, one_char_of_pwd)>0 ) then
           found_contain_char := 1;
       end if;

       -- check if it contains char which is not in <should_be_any_chars_in>
       if( instr(should_be_in_chars, one_char_of_pwd) <= 0 ) then
           status_code := 20104;
           exit;
       end if;
    end loop;

     if(status_code = 0) then
        if(found_contain_char=0) then
           status_code := 20105;
        end if;
     end if;
   
    RETURN(status_code);
    END;]';
        IF l_dbtype = 'ONP'
        THEN 
            -- this is needed only for for on-prem
            execute immediate l_sql;
            -- This function is used to validate password
        END IF;
    END;
    BEGIN
    l_sql:= q'[CREATE OR REPLACE FUNCTION MGMT_INTERNAL_PASS_VERIFY (
    username varchar2,
    password varchar2,
    old_password varchar2)
RETURN boolean IS 
    n boolean;
    m integer;
    differ integer;

  validation_err_msg varchar(256);
BEGIN 
    m := MGMT_INTERNAL_PASS_VALIDATE(password);
    -- Check for the minimum length of the password
--    IF length(password) < 8 AND username <> 'SYSMAN' 

    if m = 20100 then
       validation_err_msg := 'You have not provided a password. Enter a valid password.';
    end if;

    if validation_err_msg is null and m = 20101 then
       validation_err_msg := 'Password cannot be more than 30 characters.';
    end if;

    if validation_err_msg is null and m = 20102 then
       validation_err_msg := 'Password must contain at least 8 characters, begin with a letter, and include at least one numeric value.';
    end if;
   
    IF validation_err_msg IS NOT NULL 
    THEN
        raise_application_error(-20002, validation_err_msg);
    END IF;

    for indx in 1..length(password)
    loop
       if( m = 20103 ) then
          validation_err_msg := 'Passwords must begin only with a letter.';
          exit;
       end if;

       if( m = 20104 ) then
           validation_err_msg := 'Invalid Password. The SYSMAN password should meet the following criteria: (1) Contain at least 8 characters. (2) Start with a letter. (3) Contain at least one numeric value. (4) May contain any of these special characters: $#_';
           exit;
       end if;
    end loop;

     if(validation_err_msg is null) then
        if(m = 20105) then
           validation_err_msg := 'Password must contain at least 8 characters, begin with a letter, and include at least one numeric value.';
        end if;
     end if;

     IF validation_err_msg IS NOT NULL
     THEN
        raise_application_error(-20002, validation_err_msg);
     END IF;

    -- Everything is fine; return TRUE ;   
    RETURN(TRUE);
    END;]';
        IF l_dbtype = 'ONP'
        THEN 
            -- this is needed only for for on-prem
            execute immediate l_sql;
            -- This function is used to verify password
        END IF;
    END;
    BEGIN
    l_sql :=q'[CREATE OR REPLACE FUNCTION MGMT_PASS_VERIFY (
    username varchar2,
    password varchar2,
    old_password varchar2)
RETURN boolean IS 
    n boolean;
    m integer;
    differ integer;
    isdigit boolean;
    ischar  boolean;
    ispunct boolean;
    digitarray varchar2(20);
    punctarray varchar2(25);
    chararray varchar2(52);

BEGIN 
    digitarray:= '0123456789';
    chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    punctarray:='#!$%&()``*+,-/:;<=>?_'||chr(34); -- RCU cannot parse double-quote correctly, so use chr, 
                                                  -- also shufle pos of exclamation as it is special when used after single quote 

    -- Check if the password is same as the username
    IF NLS_LOWER(password) = NLS_LOWER(username) THEN
        raise_application_error(-20001, 'Password same as or similar to user');
    END IF;

    -- Check for the minimum length of the password
    IF length(password) < 8 THEN
        raise_application_error(-20002, 'Password length less than 8');
    END IF;

    -- Check if the password contains at least one letter, one digit and one
    -- punctuation mark.
    -- 1. Check for the digit
    isdigit:=FALSE;
    m := length(password);
    FOR i IN 1..10 LOOP 
        FOR j IN 1..m LOOP 
            IF substr(password,j,1) = substr(digitarray,i,1) THEN
                isdigit:=TRUE;
                GOTO findchar;
            END IF;
        END LOOP;
    END LOOP;
    IF isdigit = FALSE THEN
        raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
    END IF;
    -- 2. Check for the character
    <<findchar>>
    ischar:=FALSE;
    FOR i IN 1..length(chararray) LOOP 
        FOR j IN 1..m LOOP 
            IF substr(password,j,1) = substr(chararray,i,1) THEN
                ischar:=TRUE;
                GOTO findpunct;
            END IF;
        END LOOP;
    END LOOP;
    IF ischar = FALSE THEN
        raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
    END IF;
    -- 3. Check for the punctuation
    <<findpunct>>
    ispunct:=FALSE;
    FOR i IN 1..length(punctarray) LOOP 
        FOR j IN 1..m LOOP 
            IF substr(password,j,1) = substr(punctarray,i,1) THEN
                ispunct:=TRUE;
                GOTO endsearch;
            END IF;
        END LOOP;
    END LOOP;
    IF ispunct = FALSE THEN
       raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
    END IF;

    <<endsearch>>
    -- Everything is fine; return TRUE ;   
    RETURN(TRUE);
    END;]';
        IF l_dbtype = 'ONP'
        THEN 
            -- this is needed only for for on-prem
            execute immediate l_sql;
        END IF;

    END;
EXCEPTION 
    WHEN OTHERS THEN 
        RAISE;
END;
/
