Mandatory Profile in Oracle Database

As you know, profiles can be used to impose restrictions on each user in two areas: resource consumption and password complexity. Profiles are set at the user level, and each user can have only one profile.

In Oracle 21c, a new type of profile called Mandatory Profile was introduced. It can be created at the root container level and can be assigned to one or more PDBs. In this case, the policies defined in the profile will apply to all users in the assigned PDB(s).

A Mandatory Profile is specifically intended to enforce password-related restrictions, such as ensuring that a user’s password has at least n characters. These restrictions can be implemented by creating a FUNCTION and assigning it to the PASSWORD_VERIFY_FUNCTION parameter in the profile.

Example: In this example, by creating a function func_pass_limit and using it in a Mandatory Profile, we aim to enforce a restriction where users’ passwords in PDB5 and PDB8 must have at least 5 characters:

Creating the function:

SQL> CREATE OR REPLACE FUNCTION func_pass_limit(username     VARCHAR2,password     VARCHAR2,old_password VARCHAR2)

  RETURN BOOLEAN IS

BEGIN

  IF ora_complexity_check(password, chars => 5) THEN

    return(true);

  END IF;

  RETURN(false);

END;

/

Function created.

Creating a Mandatory Profile:

SQL> CREATE MANDATORY PROFILE c##EJBARI_PROF LIMIT PASSWORD_VERIFY_FUNCTION func_pass_limit CONTAINER = ALL;

Profile created

Mandatory Profiles are assigned to a PDB using the mandatory_user_profile parameter, which can be set at the PDB level.
Next, we assign the created Mandatory Profile to PDB5 and PDB8:

SQL> show pdbs

    CON_ID CON_NAME            OPEN MODE  RESTRICTED

———- ————-               ———- ———-

         2 PDB$SEED                   READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB5                           READ WRITE NO

         6 PDB8                           READ WRITE NO

SQL> alter session set container=PDB5;

Session altered.

SQL> show parameter mandatory_user_profile;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

mandatory_user_profile               string

SQL> alter system set mandatory_user_profile=c##EJBARI_PROF;

System altered.

SQL> alter session set container=PDB8;

Session altered.

SQL> show parameter mandatory_user_profile;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

mandatory_user_profile               string

SQL> alter system set mandatory_user_profile=c##EJBARI_PROF;

System altered.

After setting this parameter, we try to change the password for user vahid in PDB5 and PDB8:

SQL> alter session set container=PDB5;

Session altered.

SQL> create user vahid identified by a;

ORA-28219: password verification failed for mandatory profile

ORA-20000: password length less than 5 characters

SQL> create user vahid identified by a1234;

User created.

SQL> create user vahid identified by a;

User created.

If local profiles have a policy for minimum password length, the chosen password must still satisfy the Mandatory Profile requirements.

For example, continuing the previous scenario, if in PDB5 the local profile has a minimum password length of 3, the Mandatory Profile will still prevent user creation:

SQL> CREATE OR REPLACE FUNCTION local_func(username     VARCHAR2,password     VARCHAR2,old_password VARCHAR2)

  RETURN BOOLEAN IS

BEGIN

  IF ora_complexity_check(password, chars => 3) THEN

    return(true);

  END IF;

  RETURN(false);

END;

/

Function created.

SQL> create profile local_prf limit PASSWORD_VERIFY_FUNCTION local_func;

Profile created

SQL> alter user vahid profile local_prf;

User altered

SQL> alter user vahid identified by abc;

ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 5 characters

However, if the minimum password length in the local profile is set higher than in the Mandatory Profile, the local profile takes precedence:

SQL>CREATE OR REPLACE FUNCTION local_func(username     VARCHAR2,password     VARCHAR2,old_password VARCHAR2)

  RETURN BOOLEAN IS

BEGIN

  IF ora_complexity_check(password, chars => 7) THEN

    return(true);

  END IF;

  RETURN(false);

END;

/

Function created.

SQL> alter user vahid identified by abcde;

ORA-28003: password verification for the specified password failed
ORA-20000: password length less than 7 characters

SQL> alter user vahid identified by abcdefg;

User altered

In Mandatory Profiles, PASSWORD_VERIFY_FUNCTION is the only parameter that can be used; other parameters either cannot be set or do not enforce restrictions on users:

SQL> alter profile c##mand limit PASSWORD_LIFE_TIME 1;

ORA-02387: invalid password parameter for mandatory profile

To identify Mandatory Profiles, you can query the dba_profiles view:

SQL> select distinct profile from dba_profiles where mandatory=’YES’;

PROFILE

—————

C##EJBARI_PROF