Choosing a password scheme for the database

In the Security Guide there is a section to assist you with the decisions about what rules you might want to have in place when users choose passwords, namely attributes like the minimum length of a password, the types of characters it must (and must not) contain, re-use of old passwords etc etc. The documentation refers to a number of pre-supplied routines that are now available in 12c to assist administrators.  This is just a quick blog post to let you know that there is no “smoke and mirrors” going on here in terms of these functions. We’re implementing them in the same way that you might choose to build them yourself. In fact, you can readily take a look at exactly what the routines do because they are just simple PL/SQL code:

SQL> select text
  2   from dba_source
  3  where name in (
  8  order by name, line;

function ora12c_strong_verify_function
(username varchar2,
 password varchar2,
 old_password varchar2)
return boolean IS
   differ integer;
   if not ora_complexity_check(password, chars => 9, upper => 2, lower => 2,
                           digit => 2, special => 2) then
   end if;

   -- Check if the password differs from the previous password by at least
   -- 4 characters
   if old_password is not null then
      differ := ora_string_distance(old_password, password);
      if differ < 4 then
         raise_application_error(-20032, 'Password should differ from previous '
                                 || 'password by at least 4 characters');
      end if;
   end if;


FUNCTION ora12c_verify_function
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
   differ integer;
   db_name varchar2(40);
   i integer;
   reverse_user dbms_id;
   canon_username dbms_id := username;
   -- Bug 22369990: Dbms_Utility may not be available at this point, so switch
   -- to dynamic SQL to execute canonicalize procedure.
   IF (substr(username,1,1) = '"') THEN
     execute immediate 'begin dbms_utility.canonicalize(:p1,  :p2, 128); end;'
                        using IN username, OUT canon_username;
   END IF;
   IF NOT ora_complexity_check(password, chars => 8, letter => 1, digit => 1,
                               special => 1) THEN
   END IF;

   -- Check if the password contains the username
   IF regexp_instr(password, canon_username, 1, 1, 0, 'i') > 0 THEN
     raise_application_error(-20002, 'Password contains the username');
   END IF;

   -- Check if the password contains the username reversed
   FOR i in REVERSE 1..length(canon_username) LOOP
     reverse_user := reverse_user || substr(canon_username, i, 1);
   IF regexp_instr(password, reverse_user, 1, 1, 0, 'i') > 0 THEN
     raise_application_error(-20003, 'Password contains the username ' ||
   END IF;

   -- Check if the password contains the server name
   select name into db_name from sys.v$database;
   IF regexp_instr(password, db_name, 1, 1, 0, 'i') > 0 THEN
      raise_application_error(-20004, 'Password contains the server name');
   END IF;

   -- Check if the password contains 'oracle'
   IF regexp_instr(password, 'oracle', 1, 1, 0, 'i') > 0 THEN
        raise_application_error(-20006, 'Password too simple');
   END IF;

   -- Check if the password differs from the previous password by at least
   -- 3 characters
   IF old_password IS NOT NULL THEN
     differ := ora_string_distance(old_password, password);
     IF differ < 3 THEN
        raise_application_error(-20010, 'Password should differ from the '
                                || 'old password by at least 3 characters');
     END IF;
   END IF ;


function ora_complexity_check
(password varchar2,
 chars integer := null,
 letter integer := null,
 upper integer := null,
 lower integer := null,
 digit integer := null,
 special integer := null)
return boolean is
   digit_array varchar2(10) := '0123456789';
   alpha_array varchar2(26) := 'abcdefghijklmnopqrstuvwxyz';
   cnt_letter integer := 0;
   cnt_upper integer := 0;
   cnt_lower integer := 0;
   cnt_digit integer := 0;
   cnt_special integer := 0;
   delimiter boolean := false;
   len integer := nvl (length(password), 0);
   i integer ;
   ch char(1);
   -- Check that the password length does not exceed 2 * (max DB pwd len)
   -- The maximum length of any DB User password is 128 bytes.
   -- This limit improves the performance of the Edit Distance calculation
   -- between old and new passwords.
   if len > 256 then
      raise_application_error(-20020, 'Password length more than 256 characters');
   end if;

   -- Classify each character in the password.
   for i in 1..len loop
      ch := substr(password, i, 1);
      if ch = '"' then
         delimiter := true;
      elsif instr(digit_array, ch) > 0 then
         cnt_digit := cnt_digit + 1;
      elsif instr(alpha_array, nls_lower(ch)) > 0 then
         cnt_letter := cnt_letter + 1;
         if ch = nls_lower(ch) then
            cnt_lower := cnt_lower + 1;
            cnt_upper := cnt_upper + 1;
         end if;
         cnt_special := cnt_special + 1;
      end if;
   end loop;

   if delimiter = true then
      raise_application_error(-20012, 'password must NOT contain a '
                               || 'double-quotation mark which is '
                               || 'reserved as a password delimiter');
   end if;
   if chars is not null and len < chars then
      raise_application_error(-20001, 'Password length less than ' ||
   end if;

   if letter is not null and cnt_letter < letter then
      raise_application_error(-20022, 'Password must contain at least ' ||
                                      letter || ' letter(s)');
   end if;
   if upper is not null and cnt_upper < upper then
      raise_application_error(-20023, 'Password must contain at least ' ||
                                      upper || ' uppercase character(s)');
   end if;
   if lower is not null and cnt_lower < lower then
      raise_application_error(-20024, 'Password must contain at least ' ||
                                      lower || ' lowercase character(s)');
   end if;
   if digit is not null and cnt_digit < digit then
      raise_application_error(-20025, 'Password must contain at least ' ||
                                      digit || ' digit(s)');
   end if;
   if special is not null and cnt_special < special then
      raise_application_error(-20026, 'Password must contain at least ' ||
                                      special || ' special character(s)');
   end if;


function ora_string_distance
(s varchar2,
 t varchar2)
return integer is
   s_len    integer := nvl (length(s), 0);
   t_len    integer := nvl (length(t), 0);
   type arr_type is table of number index by binary_integer;
   d_col    arr_type ;
   dist     integer := 0;
   if s_len = 0 then
      dist := t_len;
   elsif t_len = 0 then
      dist := s_len;
   -- Bug 18237713 : If source or target length exceeds max DB password length
   -- that is 128 bytes, then raise exception.
   elsif t_len > 128 or s_len > 128 then
     raise_application_error(-20027,'Password length more than 128 bytes');
   elsif s = t then
      for j in 1 .. (t_len+1) * (s_len+1) - 1 loop
          d_col(j) := 0 ;
      end loop;
      for i in 0 .. s_len loop
          d_col(i) := i;
      end loop;
      for j IN 1 .. t_len loop
          d_col(j * (s_len + 1)) := j;
      end loop;

      for i in 1.. s_len loop
        for j IN 1 .. t_len loop
          if substr(s, i, 1) = substr(t, j, 1)
             d_col(j * (s_len + 1) + i) := d_col((j-1) * (s_len+1) + i-1) ;
             d_col(j * (s_len + 1) + i) := LEAST (
                       d_col( j * (s_len+1) + (i-1)) + 1,      -- Deletion
                       d_col((j-1) * (s_len+1) + i) + 1,       -- Insertion
                       d_col((j-1) * (s_len+1) + i-1) + 1 ) ;  -- Substitution
          end if ;
        end loop;
      end loop;
      dist :=  d_col(t_len * (s_len+1) + s_len);
   end if;

   return (dist);

The good thing about this is that:

  • the routines are transparent, which is how all good security models should be presented. Because then they are open to scrutiny and not reliant on any kind of obfuscation to be secure, and 
  • if the routines do not exactly meet your requirements, then you now have a well established starting point from which to build your own custom routines.

Of course, if you are just after a string distance function, you might be better off using the pre-supplied UTL_MATCH package.

Quick tip–database link passwords

If you are relying on database links in your application, think carefully about how you want to manage the accounts that you connect with, in particular, when it comes to password expiry.

With a standard connect request to the database, if your password is going to expire soon, you will get some feedback on this:

SQL> conn demo/demo@np12
ORA-28002: the password will expire within 6 days


But when using those same credentials via a database link, you will not get any warning, so when that password expires…you might be dead in the water.

SQL> create database link demolink connect to demo identified by demo using 'np12';

Database link created.

SQL> select * from tab@demolink;

------- ----------