8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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...