[26ai] Mandatory Profile

If you prefer to read in Spanish Spanish version.

Today, we’re going to talk about Mandatory Profile.

Even though It’s not a feature in 26ai, it was available in 21c, we’re going to set it up in the latest release.

This type of Profile can be created at the CDB$Root and can be assigned to our PDBs. Another important point is that if we need to upgrade it, we must do it at the CDB$ROOT, not at the PDB level, and using common users, not local users.

Furthermore, the policies defined in the profile will be applied to all users of the PDB.

What parameters can be used? Unlike in the Profile, where we can assign a bunch of parameters for the setup, here we only have two parameters:

password_verify_function: To enforce password complexity using a function assigned to the Mandatory Profile. By default is null.
password_grace_time: to set a grace period for accounts that are not compliant with mandatory password complexity rules. By default is 0.

First of all, let’s define a new function in order to assing password_verify_function.

SQL> 
CREATE OR REPLACE FUNCTION VALIDATION_BUSINESS(username     varchar2,
                                               password     varchar2,
                                               old_password varchar2)
  return boolean IS
BEGIN
  if not ora_complexity_check(password, chars => 8, digit => 4) then
    return(false);
  end if;
  return(true);
END;
/
Function created.

As we can see, we have defined the VALIDATION_BUSINESS function within CDB$Root in order to validate that the user’s password has at least 8 characters “chars => 8” and 4 digits “digit=>4“.

After that, let’s create the Mandatory Profile using the following parameters:

PASSWORD_VERIFY_FUNCTION To assign the previous function .
PASSWORD_GRACE_TIME To set a 5‑day grace period for user accounts.

SQL> 
  CREATE MANDATORY PROFILE C##VALIDATION_BUSINESS 
   LIMIT 
     PASSWORD_VERIFY_FUNCTION VALIDATION_BUSINESS 
     PASSWORD_GRACE_TIME 5 
   CONTAINER = ALL; 

Profile created.

As the last step, let’s upgrade the following parameter: MANDATORY_USER_PROFILE.

Here we have several options. On one hand, if we want to apply it to all PDBs, we need to make the change within CDB$ROOT. On the other hand, if we want to apply it to only one PDB, we need to make the change within that specific PDB.

SQL> show parameter MANDATORY_USER_PROFILE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string

SQL> ALTER SYSTEM SET MANDATORY_USER_PROFILE=C##VALIDATION_BUSINESS;
System altered.

SQL> show parameter MANDATORY_USER_PROFILE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##VALIDATION_BUSINESS