Password complexity

OceanBase Database has a password complexity policy for Oracle mode, which is compatible with Oracle database's password policy. OceanBase Database allows you to set password complexity in Oracle mode, in order to verify the identity of logon users, prevent malicious password attacks, thereby improving database security.

Prerequisites

In Oracle mode, you can specify the PASSWORD_VERIFY_FUNCTION parameter in the profile to check whether the password complexity meets the requirements. To verify the password complexity, you must create a PL function that meets the following requirements:

FUNCTION verify_function (username     IN VARCHAR2,
           password     IN VARCHAR2,
           old_password IN VARCHAR2)
RETURN BOOLEAN;

When creating a user or modifying a password, the verification function will be called, and the new password will be judged whether it meets complexity requirements based on the execution result of the function.

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 adding the PL password complexity verification function to the CREATE PROFILE and ALTER PROFILE statements as parameters.

1. Log on to an Oracle tenant of a cluster as the sys user.

   obclient -usys@oracle -h10.xxx.xxx.1 - P2881 -P*******
   

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

   The rules for setting password complexity are as follows:

   • The password must be 8 to 256 characters long.

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

   • The password cannot be the username, the reverse of the username, or the server name.

   • If you change the password, the new password must have at least 3 characters that are different from the old password.

   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's 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 setting has taken effect

1. Log on to an Oracle tenant of a cluster as the sys user.

   obclient -usys@oracle -h10.xxx.xxx.1 - P2881 -P*******
   

2. Create a user named sectest1 with a password that meets the requirements, and create another user named sectest1 with a password that does not meet the requirements.

   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 successfully setting the password complexity, if you need to view the specific complexity rules next time, you can obtain them by querying the content of the function.

Perform the following steps to view the complexity rules:

1. Log on to an Oracle tenant of a cluster as the root user.

   obclient -usys@oracle -h10.xxx.xxx.1 - P2881 -P*******
   

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 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 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.