By Mouhamadou Diaw

With Oracle 21c, it is now possible to enforce a password policy (length, number of digits…) for all pluggable databases or for specific pluggable databases via profiles. This is done by creating a mandatory profile in the root CDB and this profile will be attached to corresponding PDBs.
The mandatory profile is a generic profile that can only have a single parameter, the PASSWORD_VERIFY_FUNCTION.
The password complexity verification function of the mandatory profile is checked before the password complexity function that is associated with the user account profile.
For example, the password length defined in the mandatory profile will take precedence on any other password length defined in any other profile associated to the user.
When defined the limit of the mandatory profile will be enforced in addition to the limits of the actual profile of the user.
A mandatory profile cannot be assigned to a user but should attached to a PDB

In this demonstration we will consider a instance DB21 with 3 PDB
-PDB1
-PDB2
-PDB3

We will create 2 mandatory profiles:
c##mand_profile_pdb1_pdb2 which will be assigned to PDB1 and PDB2
c##mand_profile_pdb3 which will be assigned to PDB3

1
2
3
4
5
6
7
8
9
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO
SQL>

We will create two verification functions in the root container that we will associate to our mandatory profiles. The first function will check for a password length to 6

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> CREATE OR REPLACE FUNCTION func_pdb1_2_verify_function
 ( username     varchar2,
   password     varchar2,
   old_password varchar2)
 return boolean IS
BEGIN
   if not ora_complexity_check(password, chars => 6) then
      return(false);
   end if;
   return(true);
END;
Function created.
SQL>

The second function will check for a password length to 10

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> CREATE OR REPLACE FUNCTION func_pdb3_verify_function
 ( username     varchar2,
   password     varchar2,
   old_password varchar2)
 return boolean IS
BEGIN
   if not ora_complexity_check(password, chars => 10) then
      return(false);
      end if;
   return(true);
END;
/
Function created.
SQL>

Now let’s create the two mandatory profiles in the root container

1
2
3
4
5
6
SQL>
CREATE MANDATORY PROFILE c##mand_profile_pdb1_pdb2
LIMIT PASSWORD_VERIFY_FUNCTION func_pdb1_2_verify_function
CONTAINER = ALL;
Profile created.

Remember that we want to associate the mandatoty profile c##mand_profile_pdb1_pdb2 to PDB1 and PDB2. So we can first attach this profile to all PDBs

1
2
3
4
5
SQL> CREATE MANDATORY PROFILE c##mand_profile_pdb3
LIMIT PASSWORD_VERIFY_FUNCTION func_pdb3_verify_function
CONTAINER = ALL
Profile created.

Remember that we want to associate the mandatoty profile c##mand_profile_pdb1_pdb2 to PDB1 and PDB2. So we can first attach this profile to all PDBs

1
2
3
4
5
6
7
8
9
10
11
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> alter system set mandatory_user_profile=c##mand_profile_pdb1_pdb2;
System altered.
SQL>

To associate the profile c##mand_profile_pdb3 to PDB3, we can edit the spfile of PDB3

1
2
3
4
5
6
7
8
9
10
SQL> show con_name;
CON_NAME
------------------------------
PDB3
SQL>  alter system set mandatory_user_profile=c##mand_profile_pdb3;
System altered.
SQL>

We can then verify the different values of the parameter MANDATORY_USER_PROFILE in the different PDBs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SQL> show con_name;
CON_NAME
------------------------------
PDB3
SQL>  alter system set mandatory_user_profile=c##mand_profile_pdb3;
System altered.
SQL> show parameter mandatory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##MAND_PROFILE_PDB3
SQL> alter session set container=PDB1;
Session altered.
SQL> show parameter mandatory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##MAND_PROFILE_PDB1_PDB2
SQL>  alter session set container=PDB2;
Session altered.
SQL> show parameter mandatory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##MAND_PROFILE_PDB1_PDB2
SQL>

To test we will try to create a user in PDB3 for example with with a password length < 10

1
2
3
4
5
6
7
8
9
SQL> create user toto identified by "DGDTr##5";
create user toto identified by "DGDTr##5"
*
ERROR at line 1:
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 10 characters
SQL>