simple_password_check_letters_same_case

Overview

Minimal required number of letters of the same letter case.This limit is applied separately to upper-case and lower-case letters

See also: System Variables for MariaDB Enterprise Server 23.08, in 23.07 ES, in 10.6 ES, in 10.5 ES, in 10.4 ES, in 10.3 ES, in 10.2 ES, in 10.6 CS, in 10.5 CS, in 10.4 CS, in 10.3 CS, and in 10.2 CS

See also: System Variables for MariaDB Enterprise Server 23.08, in 23.07 ES, in 10.6 ES, in 10.5 ES, and in 10.4 ES

USAGE

The simple_password_check_letters_same_case system variable can have its default value configured via SkySQL Configuration Manager.

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

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

[mariadb]
plugin_load_add=simple_password_check
simple_password_check_letters_same_case=3

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

SET GLOBAL simple_password_check_letters_same_case=3;

When the simple_password_check_letters_same_case 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_letters_same_case 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 letter characters in either upper case or lower case 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 letter characters in either upper case or lower case than the configured value, then the password will be rejected, and the user account password will not be changed.

  • With the default locale, an upper case letter character is any character in the range A-Z and a lower case letter character is any character in the range a-z.

SYNONYMS

SCHEMA

PARAMETERS

Command-line

--simple_password_check_letters_same_case=#

Configuration file

Supported

Dynamic

Yes

Scope

Global

Data Type

INT UNSIGNED

Minimum Value

0

Maximum Value

1000

Product Default Value

1

SKYSQL

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

Multi-Node Analytics

10.6

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

Single Node Analytics

10.6

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

Replicated Transactions

10.4

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

10.5

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

10.6

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

Single Node Transactions

10.4

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

10.5

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

10.6

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

GCP

Multi-Node Analytics

10.6

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

Single Node Analytics

10.6

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

Replicated Transactions

10.4

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

10.5

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

10.6

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

Single Node Transactions

10.4

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

10.5

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

10.6

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

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

ColumnStore Data Warehouse

10.6

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

Enterprise Server With Replica(s)

10.4

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

10.5

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

10.6

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

Enterprise Server Single Node

10.4

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

10.5

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

10.6

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

GCP

ColumnStore Data Warehouse

10.6

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

Enterprise Server With Replica(s)

10.4

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

10.5

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

10.6

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

Enterprise Server Single Node

10.4

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

10.5

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

10.6

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

PRIVILEGES

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

EXAMPLES

Dependence on Simple Password Check Plugin

The simple_password_check_letters_same_case 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_letters_same_case=3;

ERROR 1193 (HY000): Unknown system variable 'simple_password_check_letters_same_case'

Invalid Password

If a password has fewer "letter" characters in either upper case or lower case than the value of the simple_password_check_letters_same_case system variable, then an error with the ER_NOT_VALID_PASSWORD error code will be raised:

SHOW GLOBAL VARIABLES LIKE 'simple_password_check_letters_same_case';

+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| simple_password_check_letters_same_case | 3     |
+-----------------------------------------+-------+

SET PASSWORD = PASSWORD('$secure1PW');

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

SET PASSWORD = PASSWORD('$secure1PWD');

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_letters_same_case system variable can be dynamically reset to the default value by setting it to DEFAULT:

SET GLOBAL simple_password_check_letters_same_case=3;

SHOW GLOBAL VARIABLES LIKE 'simple_password_check_letters_same_case';

+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| simple_password_check_letters_same_case | 3     |
+-----------------------------------------+-------+

SET GLOBAL simple_password_check_letters_same_case=DEFAULT;

SHOW GLOBAL VARIABLES LIKE 'simple_password_check_letters_same_case';

+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| simple_password_check_letters_same_case | 1     |
+-----------------------------------------+-------+

Invalid Value for Variable

If a user tries to set the simple_password_check_letters_same_case 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_letters_same_case 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_letters_same_case=-1;

SHOW WARNINGS;

+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect simple_password_check_letters_sa value: '-1' |
+---------+------+------------------------------------------------------------------+

SHOW GLOBAL VARIABLES LIKE 'simple_password_check_letters_same_case';

+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| simple_password_check_letters_same_case | 0     |
+-----------------------------------------+-------+

SET GLOBAL simple_password_check_letters_same_case=1001;

SHOW WARNINGS;

+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect simple_password_check_letters_sa value: '1001'        |
| Warning | 1292 | Adjusted the value of simple_password_check_minimal_length from 8 to 2002 |
+---------+------+---------------------------------------------------------------------------+

SHOW GLOBAL VARIABLES LIKE 'simple_password_check_letters_same_case';

+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| simple_password_check_letters_same_case | 1000  |
+-----------------------------------------+-------+

If the sql_mode system variable does contain STRICT_ALL_TABLES and a user tries to set the simple_password_check_letters_same_case 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_letters_same_case=-1;

ERROR 1231 (42000): Variable 'simple_password_check_letters_same_case' can't be set to the value of '-1'

Invalid Type for Variable

If a user tries to set the simple_password_check_letters_same_case 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_letters_same_case='';

ERROR 1232 (42000): Incorrect argument type to variable 'simple_password_check_letters_same_case'

SET GLOBAL simple_password_check_letters_same_case='infinity';

ERROR 1232 (42000): Incorrect argument type to variable 'simple_password_check_letters_same_case'

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

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.

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES