Set password complexity

The password complexity rules of OceanBase Database (Oracle mode) are compatible with those of Oracle Database. To protect OceanBase Database from malicious password attacks and improve database security, you can set a password complexity function as needed to authenticate user identity.

Set password complexity and enable password complexity verification

The Oracle mode of OceanBase Database provides the PASSWORD_VERIFY_FUNCTION parameter to verify password complexity.

The PASSWORD_VERIFY_FUNCTION parameter allows you to add PL password complexity verification functions to the CREATE PROFILE and ALTER PROFILE statements as parameters.

1. Log on to an Oracle tenant of OceanBase Database as the administrator.

2. Write the function corresponding to the PASSWORD_VERIFY_FUNCTION parameter and grant privileges.

   The following are example password complexity rules:

   • The password must be 8 to 256 characters in length.

   • The password must contain at least one uppercase letter, one lowercase letter, and one special character.

   • The password must not be the username, username in reverse order, or server name.

   • When you change your password, the new password must be different from the old password by at least three characters.

   Here is an example:

   delimiter//
   
   CREATE OR REPLACE FUNCTION complexity_check
   (password varchar2,
       chars integer := NULL,
       letter integer := NULL,
       upper_c integer := NULL,
       lower_c 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;
       flag boolean := FALSE;
       len INTEGER := NVL(length(password), 0);
       i integer ;
       ch CHAR(1);
   BEGIN
       -- 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');
   END IF;
   
       -- Classify each character in the password.
       FOR i in 1..len LOOP
           ch := substr(password, i, 1);
           IF ch = '"' THEN
               flag := TRUE;
           ELSIF instr(digit_array, ch) > 0 THEN
               cnt_digit := cnt_digit + 1;
           ELSIF instr(alpha_array, LOWER(ch)) > 0 THEN
               cnt_letter := cnt_letter + 1;
               IF ch = LOWER(ch) THEN
                   cnt_lower := cnt_lower + 1;
               ELSE
                   cnt_upper := cnt_upper + 1;
               END IF;
           ELSE
               cnt_special := cnt_special + 1;
           END IF;
       END LOOP;
       IF flag = 1 THEN
           raise_application_error(-20012, 'password must NOT contain a '
                                                              || 'double-quote character, 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 ' ||
                                                            chars);
       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_c IS NOT NULL AND cnt_upper < upper_c THEN
           raise_application_error(-20023, 'Password must contain at least ' ||
                                                               upper_c || ' uppercase character(s)');
       END IF;
       IF lower_c IS NOT NULL AND cnt_lower < lower_c THEN
           raise_application_error(-20024, 'Password must contain at least ' ||
                                                                lower_c || ' 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;
       RETURN(TRUE);
       END;
   //
   CREATE OR REPLACE FUNCTION 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;
   BEGIN
       IF s_len = 0 THEN
               dist := t_len;
       ELSIF t_len = 0 THEN
               dist := s_len;
       ELSE
           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)
       THEN
               d_col(j * (s_len + 1) + i) := d_col((j-1) * (s_len+1) + i-1) ;
       ELSE
               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);
   END;
   //
   CREATE OR REPLACE FUNCTION verify_function
   (username varchar2,
       password varchar2,
       old_password varchar2)   
       -- This example defines three function parameters, namely username, password, and old_password.
   RETURN boolean IS
       differ integer;
       pw_lower varchar2(256);
       db_name varchar2(40);
       i integer;
       simple_password varchar2(10);
       reverse_user varchar2(32);
   BEGIN
       IF NOT complexity_check(password, 8, 1, 1) THEN
           RETURN(FALSE);
       END IF;
   
       -- Check if the password contains the username.
       pw_lower := LOWER(password);
       IF instr(pw_lower, LOWER(username)) > 0 THEN
           raise_application_error(-20002, 'Password contains the username');
       END IF;
   
       -- Check if the password contains the username reversed.
       reverse_user := '';
       FOR i in REVERSE 1..length(username) LOOP
           reverse_user := reverse_user || substr(username, i, 1);
       END LOOP;
       IF instr(pw_lower, LOWER(reverse_user)) > 0 THEN
           raise_application_error(-20003, 'Password contains the username ' ||
                                                                                                   'reversed');
       END IF;
   
       -- Check if the password contains the server name.
       db_name := 'oceanbase';
       IF instr(pw_lower, LOWER(db_name)) > 0 THEN
           raise_application_error(-20004, 'Password contains the server name');
   
       END IF;
   
       -- Check if the password contains 'oracle'.
       IF instr(pw_lower, 'oracle') > 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 := 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 ;
       RETURN(TRUE);
   END;
   //
   delimiter;
   
   obclient> GRANT EXECUTE ON verify_function TO PUBLIC;
   obclient> CREATE PUBLIC SYNONYM verify_function FOR sys.verify_function;
   

   For information about how to create a function, see Create a function.

3. In the user profile, specify the PASSWORD_VERIFY_FUNCTION parameter to enable password complexity verification.

   The syntax is as follows:

   obclient> ALTER PROFILE profile_name LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
   

   You can set profile_name to your custom profile or the DEFAULT profile.

   For example, specify the PASSWORD_VERIFY_FUNCTION parameter by using the DEFAULT profile

   obclient> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
   Query OK, 0 rows affected
   

   For more information about the ALTER PROFILE statement, see ALTER PROFILE.

4. Execute the following statement to check whether password complexity verification is enabled.

   If the returned value is NULL, password complexity verification is not enabled. If the returned value is the function you specified, password complexity verification is enabled.

   obclient> SELECT LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION' AND PROFILE='profile_name';
   

   Here is an example:

   obclient> SELECT LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION' AND PROFILE='DEFAULT';
   +-----------------+
   | LIMIT           |
   +-----------------+
   | VERIFY_FUNCTION |
   +-----------------+
   1 row in set
   

Check whether the password complexity settings are valid

1. Log on to an Oracle tenant of OceanBase Database as the administrator.

2. Create a user named sectest1 with a valid password and an invalid password, respectively.

   obclient> CREATE USER sectest1 IDENTIFIED BY ******;
   ERROR-20023: Password must contain at least 1 uppercase character(s)
   
   obclient> CREATE USER sectest1 IDENTIFIED BY ******;
   ERROR-20002: Password contains the username
   
   obclient> CREATE USER sectest1 IDENTIFIED BY ******;
   Query OK, 0 rows affected
   

View password complexity rules

After the password complexity settings are configured, you can view the complexity rules in functions.

The procedure is as follows:

1. Log on to an Oracle tenant of OceanBase Database as the administrator.

2. Query the DBA_SOURCE view to find the corresponding function.

   The DBA_SOURCE view records all stored procedures and functions in the system.

   For more information about the fields in the DBA_SOURCE view, see DBA_SOURCE.

   Here is an example:

   obclient> SELECT OWNER,NAME,TYPE FROM DBA_SOURCE WHERE TYPE='FUNCTION';
   +-------+------------------+----------+
   | OWNER | NAME             | TYPE     |
   +-------+------------------+----------+
   | SYS   | COMPLEXITY_CHECK | FUNCTION |
   | SYS   | STRING_DISTANCE  | FUNCTION |
   | SYS   | VERIFY_FUNCTION  | FUNCTION |
   +-------+------------------+----------+
   3 rows in set
   

3. Query the USER_SOURCE view to obtain the content of the VERIFY_FUNCTION function owned by the current user.

   The USER_SOURCE view records all stored procedures and functions owned by the current user in the system.

   For more information about the fields in the USER_SOURCE view, see USER_SOURCE.

   Here is an example:

   obclient>SELECT TEXT FROM USER_SOURCE WHERE NAME='VERIFY_FUNCTION' AND
        TYPE='FUNCTION'\G
   *************************** 1. row ***************************
   TEXT: FUNCTION verify_function
   (username varchar2,
       password varchar2,
       old_password varchar2)
   RETURN boolean IS
       differ integer;
       pw_lower varchar2(256);
       db_name varchar2(40);
       i integer;
       simple_password varchar2(10);
       reverse_user varchar2(32);
   BEGIN
       IF NOT complexity_check(password, 8, 1, 1) THEN
           RETURN(FALSE);
       END IF;
   
   
       pw_lower := LOWER(password);
       IF instr(pw_lower, LOWER(username)) > 0 THEN
           raise_application_error(-20002, 'Password contains the username');
       END IF;
   
   
       reverse_user := '';
       FOR i in REVERSE 1..length(username) LOOP
           reverse_user := reverse_user || substr(username, i, 1);
       END LOOP;
       IF instr(pw_lower, LOWER(reverse_user)) > 0 THEN
           raise_application_error(-20003, 'Password contains the username ' ||
                                                                                                   'reversed');
       END IF;
   
   
       db_name := 'oceanbase';
       IF instr(pw_lower, LOWER(db_name)) > 0 THEN
           raise_application_error(-20004, 'Password contains the server name');
   
       END IF;
   
   
       IF instr(pw_lower, 'oracle') > 0 THEN
           raise_application_error(-20006, 'Password too simple');
       END IF;
   
   
       IF old_password IS NOT NULL THEN
           differ := 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 ;
       RETURN(TRUE);
   END
   1 row in set
   

For more information about function management, see Manage stored procedures and functions.