8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 21c » Here

Multitenant : Common Mandatory Profiles in the CDB Root in Oracle Database 21c

This article demonstrates using a common mandatory profile to enforce password complexity in all PDBs without affecting existing user-level profiles. This functionality is new to Oracle Database 21c.

Related article.

Setup

Create a DBA common user in the root container, and a DBA user in the PDB.

-- CDB
conn sys/SysPassword1@//localhost:1521/cdb1 as sysdba

create user c##my_dba_user identified by DbaPassword1 container=all;
grant create session, dba, pdb_dba to c##my_dba_user container=all;

-- PDB
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

create user my_dba_user identified by DbaPassword1;
grant create session, dba, pdb_dba to my_dba_user;

We will use these users to test the password verification later.

User Profiles

We connect to the PDB and create a password verification function, which will accept any password so long as it is longer than 8 characters.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

create or replace function user_pwd_verify_function (
  username     varchar2,
  password     varchar2,
  old_password varchar2)
return boolean is
begin
  if not ora_complexity_check(password,
                              chars     => 8)
  then
    return(false);
  end if;
  return(true);
end;
/

We create a normal profile that references this password verification function, and assign it to the DBA user in the PDB.

create profile user_profile 
  limit password_verify_function user_pwd_verify_function;

alter user my_dba_user profile user_profile;

We connect to the DBA user in the PDB and test it. Notice it fails when we set the password to a value shorter than 8 characters.

conn my_dba_user/DbaPassword1@//localhost:1521/pdb1

alter user my_dba_user identified by short;

*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20000: password length less than 8 characters


alter user my_dba_user identified by DbaPassword1;

User altered.

SQL>

Mandatory Profiles

In the documentation you will see these referred to as "Common Mandatory Profiles", "Generic Mandatory Profiles" and "Mandatory Profiles", depending on the context.

We connect to a privileged user in the root container and create a password verification function that matches our needs.

conn sys/SysPassword1@//localhost:1521/cdb1 as sysdba

create or replace function c##cdb_pwd_verify_function (
  username     varchar2,
  password     varchar2,
  old_password varchar2)
return boolean is
begin
  if not ora_complexity_check(password,
                              chars     => 16,
                              uppercase => 1,
                              lowercase => 1,
                              digit     => 1,
                              special   => 1)
  then
    return(false);
  end if;
  return(true);
end;
/

We create a mandatory profile using a similar syntax to a regular profile, with the addition of the MANDATORY keyword. For a mandatory profile we can only set the PASSWORD_VERIFY_FUNCTION and PASSWORD_GRACE_TIME password limits, but we can set other non-password limits. In this example we set the PASSWORD_VERIFY_FUNCTION, PASSWORD_GRACE_TIME and IDLE_TIME limits.

create mandatory profile c##cdb_manadatory_profile 
  limit 
    password_verify_function cdb_pwd_verify_function
    password_grace_time 7
    idle_time 300
  container = all;

We can alter the limits using the ALTER PROFILE statement. We will set everything but the PASSWORD_VERIFY_FUNCTION limit back to the default values.

alter profile c##cdb_manadatory_profile 
  limit 
    password_grace_time default
    idle_time default
  container = all;

We can drop the profile using the DROP PROFILE command. The examples below require this profile, so don't drop it yet.

drop profile c##cdb_manadatory_profile;

Common Mandatory Profiles

With the mandatory profile created, we set it as the MADATORY_USER_PROFILE initialization parameter value in the root container, which makes it the default action for all users in the root container and all PDBs. This is now a common mandatory profile.

alter system set mandatory_user_profile=c##cdb_manadatory_profile;

show parameter mandatory_user_profile

NAME                   TYPE   VALUE
---------------------- ------ -------------------------
mandatory_user_profile string C##CDB_MANADATORY_PROFILE
SQL>

This mandatory profile will run before any user-specific profiles, so the most restrictive limit will take effect.

We test it at the CDB and PDB level and see the same password verification check is affecting both levels.

-- CDB
conn c##my_dba_user/DbaPassword1@//localhost:1521/cdb1

show parameter mandatory_user_profile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##CDB_MANADATORY_PROFILE
SQL>


alter user c##my_dba_user identified by DbaPassword1 container=all;

*
ERROR at line 1:
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 16 characters


-- PDB
conn my_dba_user/DbaPassword1@//localhost:1521/pdb1

show parameter mandatory_user_profile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##CDB_MANADATORY_PROFILE
SQL>


alter user my_dba_user identified by DbaPassword1;
*
ERROR at line 1:
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 16 characters

Notice at the PDB level the password length limit is now 16 characters, not 8 characters. The mandatory profile has run before the user profile, so the more restrictive limit has taken effect.

To remove the common mandatory profile we need to reset the MANDATORY_USER_PROFILE initialization parameter. Unfortunately this doesn't take effect without a restart of the database.

conn sys/SysPassword1@//localhost:1521/cdb1 as sysdba

alter system reset mandatory_user_profile;

show parameter mandatory_user_profile

NAME                   TYPE   VALUE
---------------------- ------ -------------------------
mandatory_user_profile string C##CDB_MANADATORY_PROFILE
SQL>

conn / as sysdba
shutdown immediate;
startup;

show parameter mandatory_user_profile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string
SQL>

PDB Mandatory Profiles

We can set a mandatory profile at the PDB-level, so it is PDB-specific. We connect to the PDB (not the CDB) and set the MADATORY_USER_PROFILE initialization parameter as before.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

alter system set mandatory_user_profile=c##cdb_manadatory_profile;

show parameter mandatory_user_profile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##CDB_MANADATORY_PROFILE
SQL>

Assuming you reset the common mandatory profile before, we should see this setting has no impact at the CDB level, just the PDB level.

-- CDB
conn c##my_dba_user/DbaPassword1@//localhost:1521/cdb1

show parameter mandatory_user_profile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string
SQL>


alter user c##my_dba_user identified by DbaPassword1 container=all;

User altered.

SQL>


-- PDB
conn my_dba_user/DbaPassword1@//localhost:1521/pdb1

show parameter mandatory_user_profile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##CDB_MANADATORY_PROFILE
SQL>


alter user my_dba_user identified by DbaPassword1;
*
ERROR at line 1:
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 16 characters

For more information see:

Hope this helps. Regards Tim...

Back to the Top.