Practice: Enforcing a Minimum Password Length on All PDBs

Overview

This practice shows how to enforce CDB-wide, the minimum password length for database user accounts without restricting access to database user profiles.

Before starting any new practice, refer to the Practices Environment recommendations.

Step 1 : Create a mandatory profile in the CDB root

  • Connect to the CDB root in CDB21.

    
    $ sqlplus sys@CDB21 AS SYSDBA
    
    Enter password:
    Connected to:
    
    SQL>
  • Create the mandatory root profile. The mandatory root profile acts as an always-on user profile. Mandatory profile limits are enforced in addition to the existing limits from the profile which the user is assigned to. This creates a union effect in the sense that the password complexity verification script of the mandatory profile will be executed before the password complexity script from the profile of the user account (if any).

    
    SQL> COL resource_name FORMAT A30
    SQL> COL limit FORMAT A30
    SQL> CREATE MANDATORY PROFILE c##prof_min_pass_len 
                      LIMIT PASSWORD_VERIFY_FUNCTION ora12c_stig_verify_function 
                      CONTAINER=ALL;
    
    Profile created.
    
    SQL> SELECT resource_name, limit, mandatory FROM cdb_profiles 
            WHERE profile='C##PROF_MIN_PASS_LEN' AND resource_type='PASSWORD';
    
    RESOURCE_NAME                  LIMIT                          MAN
    ------------------------------ ------------------------------ ---
    PASSWORD_VERIFY_FUNCTION       FROM ROOT                      YES
    FAILED_LOGIN_ATTEMPTS                                         YES
    PASSWORD_LIFE_TIME                                            YES
    PASSWORD_REUSE_TIME                                           YES
    PASSWORD_REUSE_MAX                                            YES
    PASSWORD_LOCK_TIME                                            YES
    PASSWORD_GRACE_TIME                                           YES
    INACTIVE_ACCOUNT_TIME                                         YES
    PASSWORD_ROLLOVER_TIME                                        YES
    PASSWORD_VERIFY_FUNCTION       ORA12C_STIG_VERIFY_FUNCTION    YES
    FAILED_LOGIN_ATTEMPTS                                         YES
    PASSWORD_LIFE_TIME                                            YES
    PASSWORD_REUSE_TIME                                           YES
    PASSWORD_REUSE_MAX                                            YES
    PASSWORD_LOCK_TIME                                            YES
    PASSWORD_GRACE_TIME                                           YES
    INACTIVE_ACCOUNT_TIME                                         YES
    PASSWORD_ROLLOVER_TIME                                        YES
    
    18 rows selected.
    
    SQL> 

Step 2 : Set the MANDATORY_USER_PROFILE initialization parameter

  • Set the initialization parameter to the profile name.

    
    SQL> ALTER SYSTEM SET mandatory_user_profile=C##PROF_MIN_PASS_LEN;
    
    System altered.
    
    SQL> SHOW PARAMETER mandatory_user_profile
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    mandatory_user_profile               string      C##PROF_MIN_PASS_LEN
    SQL>

    The password verify function of the mandatory profile is envisioned to be always enforced from CDB$ROOT, which means that the password resource limit is always fetched and executed from CDB$ROOT and enforced on the PDBs in the entire CDB depending on the MANDATORY_USER_PROFILE initialization parameter.


Step 3 : Replace the password verification function to enforce the minimum password length.

  • Replace the password verification function.

    
    SQL> CREATE OR REPLACE FUNCTION ora12c_stig_verify_function 
                  ( username VARCHAR2, password VARCHAR2, old_password VARCHAR2) 
                  RETURN BOOLEAN 
                  IS
                  BEGIN   
                   -- mandatory verify function will always be evaluated regardless of the  
                   -- password verify function that is associated to a particular profile/user
                   -- requires the minimum password length to be 10 characters 
                   IF NOT ora_complexity_check(password, chars => 10) THEN return(false);   
                   END IF;
                   RETURN(true);
                   END;
                   /
    
    Function created.
    
    SQL>


Step 4 : Test

  • Create a new user named JOHN in PDB21.

    
    SQL> CONNECT system@PDB21
    Enter password:
    Connected.
    SQL> CREATE USER john IDENTIFIED BY pass;
    CREATE USER john IDENTIFIED BY pass
    *
    ERROR at line 1:
    ORA-28219: password verification failed for mandatory profile
    ORA-20000: password length less than 10 characters
    
    SQL> CREATE USER john IDENTIFIED BY password123;
    CREATE USER john IDENTIFIED BY password123
    *
    ERROR at line 1:
    ORA-28003: password verification for the specified password failed
    ORA-20000: password must contain 2 or more uppercase characters
    
    SQL> CREATE USER john IDENTIFIED BY PAssword123;
     CREATE USER john IDENTIFIED BY PAssword123
    *
    ERROR at line 1:
    ORA-28003: password verification for the specified password failed
    ORA-20000: password must contain 2 or more special characters
    
    SQL> CREATE USER john IDENTIFIED BY PAssword123##;
    
    User created.
    
    SQL> DROP USER john CASCADE;
    
    User dropped.
    
    SQL>

Step 5 : Reset the configuration

  • Drop the mandatory profile in the root.

    
    SQL> CONNECT / AS SYSDBA
    Connected.
    SQL> DROP PROFILE c##prof_min_pass_len;
    DROP PROFILE c##prof_min_pass_len
    *
    ERROR at line 1:
    ORA-02381: cannot drop C##PROF_MIN_PASS_LEN profile
    
    SQL> !oerr ora 2381
    02381, 00000, "cannot drop %s profile"
    //  *Cause:  An attempt was made to drop PUBLIC_DEFAULT or a mandatory profile,
    //           which is not allowed due to following restrictions:
    //             * PUBLIC_DEFAULT profile can be dropped only when the database
    //               is in migration mode.
    //             * A mandatory profile can be dropped only if it is not set as a
    //               mandatory profile in root container (CDB$ROOT) of a multitenant
    //               container database (CDB) or in a Pluggable Database (PDB).
    //  *Action: If you are trying to drop the PUBLIC_DEFAULT profile, try dropping
    //           it during migration mode. If you are trying to drop a mandatory
    //           profile, check the MANDATORY_USER_PROFILE system parameter setting
    //           in the root container (CDB$ROOT) or in a Pluggable Database (PDB)
    //           and retry the operation after resetting the MANDATORY_USER_PROFILE
    //           system parameter by executing ALTER SYSTEM RESET DDL statement.
    
    SQL>
  • Reset the MANDATORY_USER_PROFILE initialization parameter first.

    
    SQL> ALTER SYSTEM RESET mandatory_user_profile;
    
    System altered.
    
    SQL> SHOW PARAMETER mandatory_user_profile
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    mandatory_user_profile               string      C##PROF_MIN_PASS_LEN
    SQL> DROP PROFILE c##prof_min_pass_len;
    DROP PROFILE c##prof_min_pass_len
    *
    ERROR at line 1:
    ORA-02381: cannot drop C##PROF_MIN_PASS_LEN profile
    
    SQL>
  • Restart the instance.

    
    SQL> SHUTDOWN IMMEDIATE
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> STARTUP
    ORACLE instance started.
    
    Total System Global Area 1426060208 bytes
    Fixed Size                  9687984 bytes
    Variable Size             419430400 bytes
    Database Buffers          989855744 bytes
    Redo Buffers                7086080 bytes
    Database mounted.
    Database opened.
    SQL> ALTER PLUGGABLE DATABASE pdb21 OPEN;
    
    Pluggable database altered.
    
    SQL> SHOW PARAMETER mandatory_user_profile
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    mandatory_user_profile               string
    SQL> DROP PROFILE c##prof_min_pass_len;
    
    Profile dropped.
    
    SQL> EXIT
    $