simple_password_check_other_characters

Minimal required number of other (not letters or digits) characters

See also: System Variables in 10.5 ES, in 10.5 CS, in 10.4 ES, in 10.4 CS, in 10.3 ES, in 10.3 CS, in 10.2 ES, and in 10.2 CS

USAGE

The simple_password_check_other_characters system variable can only be set if the simple_password_check plugin is installed.

The simple_password_check_other_characters system variable can be set in a configuration file:

[mariadb]
plugin_load_add=simple_password_check
simple_password_check_other_characters=3

The simple_password_check_other_characters system variable can also be set dynamically at runtime by executing SET GLOBAL:

SET GLOBAL simple_password_check_other_characters=3;

When the simple_password_check_other_characters system variable is set dynamically at runtime, its value will be reset the next time the server restarts. To make the value persist on restart, set it in a configuration file too.

DETAILS

The simple_password_check_other_characters system variable is one of the system variables that controls how the simple_password_check plugin validates new passwords:

  • If a user tries to create a user account with the CREATE USER or GRANT statements and the specified password has fewer other characters than the configured value, then the password will be rejected, and the user account will not be created.

  • If a user tries to set a user account password with the SET PASSWORD, ALTER USER, or GRANT statements and the password has fewer other characters than the configured value, then the password will be rejected, and the user account password will not be changed.

  • An other character is any valid character that is not considered a digit or a letter. For definitions of digit and letter characters, see the details of the simple_password_check_digits and simple_password_check_letters_same_case system variables.

PARAMETERS

Command-line

--simple_password_check_other_characters=#

Configuration file

Supported

Dynamic

Yes

Scope

Global

Data Type

INT UNSIGNED

Minimum Value

0

Maximum Value

1000

Default Value

1

SKYSQL

Cloud

SkySQL Topology

ES Ver

Default

AWS

HA (Primary/Replica)

10.4

1 (choices: 0, 1, 2, 5, 10)

AWS

HA (Primary/Replica)

10.5

1 (choices: 0, 1, 2, 5, 10)

AWS

Transactional Standalone

10.4

1 (choices: 0, 1, 2, 5, 10)

AWS

Transactional Standalone

10.5

1 (choices: 0, 1, 2, 5, 10)

AWS

Distributed Transactions

10.5

Not present (choices: 0, 1, 2, 5, 10)

GCP

ColumnStore

10.5

1 (choices: 0, 1, 2, 5, 10)

GCP

ColumnStore Multi

10.5

1 (choices: 0, 1, 2, 5, 10)

GCP

Galera

10.4

1 (choices: 0, 1, 2, 5, 10)

GCP

Galera

10.5

1 (choices: 0, 1, 2, 5, 10)

GCP

HA (Primary/Replica)

10.4

1 (choices: 0, 1, 2, 5, 10)

GCP

HA (Primary/Replica)

10.5

1 (choices: 0, 1, 2, 5, 10)

GCP

HTAP

10.5

1 (choices: 0, 1, 2, 5, 10)

GCP

Transactional Standalone

10.4

1 (choices: 0, 1, 2, 5, 10)

GCP

Transactional Standalone

10.5

1 (choices: 0, 1, 2, 5, 10)

GCP

Distributed Transactions

10.5

Not present (choices: 0, 1, 2, 5, 10)

See MariaDB SkySQL documentation to learn more about the MariaDB SkySQL database as a service.

PRIVILEGES

The simple_password_check_other_characters system variable requires the SUPER privilege to dynamically set its value at runtime.

CHANGE HISTORY

Release Series

History

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

EXAMPLES

Dependence on Simple Password Check Plugin

The simple_password_check_other_characters system variable can only be set if the simple_password_check plugin is installed.

To check if this plugin is installed, execute the following:

SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_LIBRARY, LOAD_OPTION
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME='simple_password_check';
+-----------------------+---------------+--------------------------+-------------+
| PLUGIN_NAME           | PLUGIN_STATUS | PLUGIN_LIBRARY           | LOAD_OPTION |
+-----------------------+---------------+--------------------------+-------------+
| simple_password_check | ACTIVE        | simple_password_check.so | ON          |
+-----------------------+---------------+--------------------------+-------------+

If the plugin is not installed, then referencing the system variable will raise an error with the ER_UNKNOWN_SYSTEM_VARIABLE error code:

SET GLOBAL simple_password_check_other_characters=3;
ERROR 1193 (HY000): Unknown system variable 'simple_password_check_other_characters'

Invalid Password

If a password has fewer special characters than the value of the simple_password_check_other_characters system variable, then an error with the ER_NOT_VALID_PASSWORD error code will be raised:

SHOW GLOBAL VARIABLES LIKE 'simple_password_check_other_characters';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| simple_password_check_other_characters | 3     |
+----------------------------------------+-------+
SET PASSWORD = PASSWORD('securePW1$#');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
SET PASSWORD = PASSWORD('securePW1$#%');

Automatic Adjustment of Minimal Length

The value of the simple_password_check_minimal_length system variable must be greater than or equal to the following equation: simple_password_check_digits + 2 * simple_password_check_letters_same_case + simple_password_check_other_characters

If one of these system variables is set to some value that causes simple_password_check_minimal_length to be less than the result of this equation, then its value will be automatically adjusted to the result of the equation. A warning with the ER_TRUNCATED_WRONG_VALUE error code will also be raised:

SET GLOBAL simple_password_check_minimal_length=8;

SET GLOBAL simple_password_check_digits=3;

SET GLOBAL simple_password_check_letters_same_case=2;

SET GLOBAL simple_password_check_other_characters=3;

SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------+
| Level   | Code | Message                                                                 |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1292 | Adjusted the value of simple_password_check_minimal_length from 8 to 10 |
+---------+------+-------------------------------------------------------------------------+

Dynamically Resetting Global Value to Default

The global value of the simple_password_check_other_characters system variable can be dynamically reset to the default value by setting it to DEFAULT:

SET GLOBAL simple_password_check_other_characters=3;

SHOW GLOBAL VARIABLES LIKE 'simple_password_check_other_characters';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| simple_password_check_other_characters | 3     |
+----------------------------------------+-------+
SET GLOBAL simple_password_check_other_characters=DEFAULT;

SHOW GLOBAL VARIABLES LIKE 'simple_password_check_other_characters';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| simple_password_check_other_characters | 1     |
+----------------------------------------+-------+

Invalid Value for Variable

If a user tries to set the simple_password_check_other_characters system variable to an invalid value, then the specific behavior depends on the value of the sql_mode system variable.

If the sql_mode system variable does not contain STRICT_ALL_TABLES and a user tries to set the simple_password_check_other_characters system variable to an invalid value, then the operation will appear to succeed, but a warning will be raised with the ER_TRUNCATED_WRONG_VALUE error code. The warning will point out that the value was truncated, which means that the value was automatically set to the closest invalid value:

SET GLOBAL simple_password_check_other_characters=-1;

SHOW WARNINGS;
+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect simple_password_check_other_char value: '-1' |
+---------+------+------------------------------------------------------------------+
SHOW GLOBAL VARIABLES LIKE 'simple_password_check_other_characters';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| simple_password_check_other_characters | 0     |
+----------------------------------------+-------+
SET GLOBAL simple_password_check_other_characters=1001;

SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect simple_password_check_other_char value: '1001'        |
| Warning | 1292 | Adjusted the value of simple_password_check_minimal_length from 8 to 1003 |
+---------+------+---------------------------------------------------------------------------+
SHOW GLOBAL VARIABLES LIKE 'simple_password_check_other_characters';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| simple_password_check_other_characters | 1000  |
+----------------------------------------+-------+

If the sql_mode system variable does contain STRICT_ALL_TABLES and a user tries to set the simple_password_check_other_characters system variable to an invalid value, then the operation will fail with the ER_WRONG_VALUE_FOR_VAR error code:

SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
SET SESSION sql_mode=CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES');

SET GLOBAL simple_password_check_other_characters=-1;
ERROR 1231 (42000): Variable 'simple_password_check_other_characters' can't be set to the value of '-1'

Invalid Type for Variable

If a user tries to set the simple_password_check_other_characters system variable to an invalid argument, then the operation will fail with the ER_WRONG_TYPE_FOR_VAR error code:

SET GLOBAL simple_password_check_other_characters='';
ERROR 1232 (42000): Incorrect argument type to variable 'simple_password_check_other_characters'
SET GLOBAL simple_password_check_other_characters='infinity';
ERROR 1232 (42000): Incorrect argument type to variable 'simple_password_check_other_characters'

EXTERNAL REFERENCES