password validation

We've got few requests to port the validate_password plugin from MySQL 5.6 to MariaDB. But we cannot get this code from MySQL (with all its limitations and gotchas), so this task is about adding a reasonably feature-compatible implementation.

How to set a password

Six different ways to set a password (xxx is the password, yyyyyy is the password hash, 'yyyyyy' = PASSWORD('xxx'):

1. GRANT ... IDENTIFIED BY 'xxx'
2. GRANT ... IDENTIFIED BY PASSWORD 'yyyyyy'
3. SET PASSWORD = PASSWORD('xxx')
4. SET PASSWORD = 'yyyyyy'
5. GRANT ... IDENTIFIED VIA mysql_native_password USING 'yyyyyy'
6. UPDATE mysql.user SET password='yyyyyy' WHERE ...

of all these cases MySQL seem to validate the password only in the case 1. We will also validate in the case 3.

In most cases it is impossible to validate the password, when given just the password hash, as in cases 2, 4, 5 (it is possible to try guess the password, so technically one write have a plugin that works like this, but we won't support this). Instead we'll have a "strict password validation" mode, kind of. In this mode statements like in 2, 4, 5 will be rejected with an error.

We won't do anything special for the case 6.

Per host, per user, etc.

One possible approach to support per-user policies is to add a new table with columns {user, host, password, password_data}. One global server policy is achieved by adding a default user (''@'%' as always).

Another approach would be to put two new columns (validation_plugin and validation_plugin_data) in the mysql.user table. In that case, for the global policy to work, we'll need the following convention: if validation_plugin is an empty string it means no validation, but if validation_plugin is NULL it means to continue searching in the user table, up until the default user.

The second approach is kind of strange and difficult to explain, I'd prefer the first one. I now think that this is what I should have done for pluggable authentication too.

Anyway, for this task we will not to do that and only support one global server policy. Later when (if) we add per user policies, the absence of the policy table (or columns) would mean to use (backward compatibly) the global policy.

Validation parameters

In MySQL validation plugin has command-line options to configure the validation. This doesn't work if different users can have different plugins — that's why the policy table (above) has the plugin_data column. Suggestion — a policy structure, much like per-engine table/field/index attributes. It is passed as an argument to the plugin. Command-line options are still possible, they can provide defauls values for policy options (of course, a badly written plugin can, technically, use only command-line options and ignore the policy structure, but our plugins won't do that).

Anyway, a policy structure is only needed when a policy table exists, so in this task we won't do it.

API

A validation plugin will provide only one method — validate_password(LEX_STRING username, LEX_STRING password, policy_struct). It will return a boolean, where, as usual in MariaDB, 1 means an error (validation failed) and 0 means a success (validation passed).

Multiple plugins, multiple checks

MySQL only uses one plugin for password validation, a plugin that has a name "validate_password". MariaDB, of course, will use any plugin of the correct type, independent of its name. The question is — shall we allow only one validation plugin to be loaded? Or one should be able to load many plugins and use them all?

We will allow many plugins — it makes perfect sense to have many password checks, for example, one plugin checks the minimal password length, while another one looks it up in a dictionary. Also, this work very well in the per-user model as above, one simply adds many rows for the same user and different plugins to the policy table.

If many password checks are enabled — all should pass for a password to be successfully validated.

Services

We might need to add a new service to allow plugins to use CHARSET_INFO and its methods safely.

New plugins

In this task we implement two password validation plugins — one similar to MySQL's, and one that uses cracklib or something similar (it should go in a separate package to avoid adding dependencies to the server).