C C++ CMake CSS Cypress date-fns Django Django REST Framework ESLint Git GNU Make HTML htmx HTTP JavaScript MariaDB NumPy pandas PHP PostgreSQL Python PyTorch Qt SQLite Tailwind CSS

Enforcing Password Complexity in MariaDB with password_reuse_check Plugin


Purpose

  • Used during password changes to disallow reuse of recent passwords.
  • Stores a history of old user passwords.

Structure

  • password_retrieval_value: The purpose of this column is not officially documented by MariaDB, but it's likely used internally by the plugin for managing password history retrieval.
  • password_hash: This column most likely stores a hashed version of the old password. Due to security best practices, passwords are never stored in plain text within databases. Instead, they are hashed using a one-way cryptographic function.
  • user: This column likely stores the username associated with the password history.

SQL Statements related to the table

  • You can't directly insert, update, or delete data from this table as it's managed by the plugin.
  • The password_reuse_check plugin likely creates and manages this table internally. There are no standard SQL statements provided by MariaDB to directly interact with this table.
  • The number of passwords stored in the history is configurable through the plugin options.
  • This table is only available in MariaDB versions 10.7.0 and later.

Creating a User

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'StrongPassword123';

This code creates a new user named new_user who can connect from the local machine (localhost) and sets their password to StrongPassword123.

Changing a User's Password

ALTER USER 'old_user'@'localhost' IDENTIFIED BY 'NewStrongPassword';

This code modifies the password for the user old_user on localhost to NewStrongPassword. When the user attempts to change their password, the password_reuse_check plugin will likely check the mysql.password_reuse_check_history table to ensure they aren't reusing a recent password.

Configuring the password_reuse_check Plugin (example)

SET GLOBAL password_reuse_history_length = 5;

This code sets the number of passwords stored in the mysql.password_reuse_check_history table to 5. This means a user will not be able to reuse any of their last 5 passwords.


  1. Enforce password complexity through user account creation

MariaDB offers built-in options to enforce password complexity rules during user creation. You can use the CREATE USER statement with the REQUIRE clause to specify complexity requirements such as minimum password length, character classes, and special character inclusion.

  1. Implement custom stored procedures

You can develop stored procedures in MariaDB to manage password history and enforce reuse prevention. This approach offers more granular control compared to the plugin, but it requires writing and maintaining your own logic.

  1. Use third-party password management tools

Several third-party password management tools integrate with MariaDB and offer features like password policy enforcement and password rotation. These tools can be a good option for managing passwords across multiple databases and applications.

  1. Consider alternative database systems

Some other database management systems might have built-in features for password complexity and reuse prevention. Explore the documentation of your target system to see if it offers similar functionalities.