# [26ai] Mandatory Profile

*If you prefer to read in Spanish* [***Spanish version***](https://dbaenlasombra.com/26ai-mandatory-profile)*.*

Today, we’re going to talk about **Mandatory Profi**[**le**.](https://dbaenlasombra.com/26ai-oracle-temporary-tablespace-groups)

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
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
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
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
```

Looking forward to seeing you in the next article :)