Default Profiles

Oracle User Profiles control Password expiration and complexity, and resource limits.

Oracle ships with some default Profiles:

• DEFAULT

• MONITORING_PROFILE

In 12.2+ we also get...

• ORA_STIG_PROFILE

In 21c+ we also get...

• ORA_CIS_PROFILE

Check

SELECT profile,

       resource_name,

       resource_type,

       limit

  FROM dba_profiles

 ORDER BY profile,

          resource_type,

          resource_name;

Create

Custom Profile

CREATE PROFILE custom_profile LIMIT 

FAILED_LOGIN_ATTEMPTS 5

PASSWORD_LOCK_TIME UNLIMITED

PASSWORD_GRACE_TIME 7

PASSWORD_LIFE_TIME 53

PASSWORD_REUSE_MAX 3 

PASSWORD_REUSE_TIME 365

PASSWORD_VERIFY_FUNCTION CUSTOM_VERIFY_FUNCTION;

Example Profile with non-expiring password

CREATE PROFILE noexpire LIMIT 

   FAILED_LOGIN_ATTEMPTS 5

   PASSWORD_LOCK_TIME UNLIMITED

   PASSWORD_GRACE_TIME 7

   PASSWORD_LIFE_TIME UNLIMITED

   PASSWORD_REUSE_MAX UNLIMITED

   PASSWORD_REUSE_TIME UNLIMITED

   PASSWORD_VERIFY_FUNCTION CUSTOM_VERIFY_FUNCTION;

CUSTOM_VERIFY_FUNCTION

User Profiles rely on a "verify" Function to enforce password complexity. If you want to change the password complexity rules from the Oracle default you can update the default Function or create your own (recommended).

CREATE OR REPLACE FUNCTION CUSTOM_VERIFY_FUNCTION

  (username     VARCHAR2,

   password     VARCHAR2,

   old_password VARCHAR2)

  RETURN BOOLEAN IS

     n          BOOLEAN;

     m          INTEGER;

     differ     INTEGER;

     isdigit    BOOLEAN;

     ischar     BOOLEAN;

     ispunct    BOOLEAN;

     digitarray VARCHAR2(20);

     punctarray VARCHAR2(25);

     chararray  VARCHAR2(52);

  BEGIN

     digitarray:= '0123456789';

     chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

     punctarray:='!"#$%&()``*+,-/:;<=>?_';

     -- Check if the password is same as the username

     IF NLS_LOWER(password) = NLS_LOWER(username)

     THEN

        RAISE_APPLICATION_ERROR(-20001, 'Password same as or similar to user');

     END IF;

     -- Check for the minimum length of the password

     IF LENGTH(password) < 8

     THEN

        RAISE_APPLICATION_ERROR(-20002, 'Password length less than 8');

     END IF;

     -- Check if the password is too simple. A dictionary of words may be

     -- maintained and a check may be made so as not to allow the words

     -- that are too simple for the password.

     IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd')

     THEN

        RAISE_APPLICATION_ERROR(-20002, 'Password too simple');

     END IF;

     -- Check if the password contains at least one letter, one digit and one

     -- punctuation mark.

     -- 1. Check for the digit

     isdigit:=FALSE;

     m := LENGTH(password);

     FOR i IN 1..10

     LOOP

        FOR j IN 1..m

        LOOP

           IF SUBSTR(password,j,1) = SUBSTR(digitarray,i,1)

           THEN

              isdigit:=TRUE;

              GOTO findchar;

           END IF;

        END LOOP;

     END LOOP;

     IF isdigit = FALSE

     THEN

        RAISE_APPLICATION_ERROR(-20003, 'Password should contain at least one digit, one character and one punctuation');

     END IF;

     -- 2. Check for the character

     <<findchar>>

     ischar:=FALSE;

     FOR i IN 1..LENGTH(chararray)

     LOOP

        FOR j IN 1..m

        LOOP

           IF SUBSTR(password,j,1) = SUBSTR(chararray,i,1)

           THEN

              ischar:=TRUE;

              GOTO findpunct;

           END IF;

        END LOOP;

     END LOOP;

     IF ischar = FALSE

     THEN

        RAISE_APPLICATION_ERROR(-20003, 'Password should contain at least one digit, one character and one punctuation');

     END IF;

     -- 3. Check for the punctuation

     <<findpunct>>

     ispunct:=FALSE;

     FOR i IN 1..length(punctarray)

     LOOP

        FOR j IN 1..m

        LOOP

           IF SUBSTR(password,j,1) = SUBSTR(punctarray,i,1)

           THEN

              ispunct:=TRUE;

              GOTO endsearch;

           END IF;

        END LOOP;

     END LOOP;

     IF ispunct = FALSE

     THEN

        RAISE_APPLICATION_ERROR(-20003, 'Password should contain at least one digit, one character and one punctuation');

     END IF;

     <<endsearch>>

     -- Check if the password differs from the previous password by at least

     -- 3 letters

     IF old_password IS NOT NULL

     THEN

        differ := LENGTH(old_password) - LENGTH(password);

        IF ABS(differ) < 3

        THEN

           IF LENGTH(password) < LENGTH(old_password)

           THEN

              m := LENGTH(password);

           ELSE

              m := LENGTH(old_password);

           END IF;

           differ := ABS(differ);

           FOR i IN 1..m

           LOOP

              IF SUBSTR(password,i,1) != SUBSTR(old_password,i,1)

              THEN

                 differ := differ + 1;

              END IF;

           END LOOP;

           IF differ < 3

           THEN

              RAISE_APPLICATION_ERROR(-20004, 'Password should differ by at least 3 characters');

           END IF;

        END IF;

     END IF;

     -- Everything is fine; return TRUE ;

     RETURN(TRUE);

  END;

/

Drop

DROP PROFILE custom_profile;

Bibliography