Securing MariaDB: Enabling Password and Unix Socket Authentication (for educational purposes only)

• MariaDB: An open-source relational database management system (RDBMS) similar to MySQL.
• root user: The most privileged user account in MariaDB, granting full access to all databases and operations.
• Authentication: The process of verifying a user's identity before allowing them to access the database.
• Password authentication: A common method where a user provides a secret password to authenticate.
• Unix socket authentication: A method specific to Unix-like operating systems (Linux, macOS) that leverages the system's user identification for authentication.

Why Use Both?

• Convenience: Unix socket authentication allows the system's root user to connect to MariaDB on the local machine (localhost) without needing a password. This is useful for quick administrative tasks when logged in as root.
• Security: Password authentication provides an extra layer of security, especially when connecting remotely or if the system root account is compromised. Someone who gains access to your system wouldn't automatically have access to MariaDB.

Steps (Not recommended for production due to security concerns):

Check Existing Configuration (Optional):

• Run the following query to see the current authentication plugins for the root user:

  SELECT user, host, password, plugin FROM mysql.user;
  

Look for the root@localhost entry and the plugin column. If it shows mysql_native_password, a password is likely required. If it's auth_socket, unix socket authentication is in effect.

Enable Password Authentication (if not already set):

• default_authentication_plugin=mysql_native_password
  

• Set a password for the root user:

  mysql -u root
  
  # Enter when prompted for password (if it's blank)
  USE mysql;
  UPDATE user SET password = PASSWORD('your_strong_password') WHERE user = 'root' AND host = 'localhost';
  FLUSH PRIVILEGES;
  

Considerations:

• Security: Enabling both methods introduces a risk. If an attacker gains access to your system, they could potentially use unix socket authentication to access MariaDB. It's generally recommended to disable unix socket authentication for the root user in production environments and rely solely on a strong password.
• Alternatives: Consider creating a separate, less privileged user account for administrative tasks instead of using the root user directly. Grant the necessary privileges to this user.

mysql -u root  # Assuming you don't have a password set yet

# If prompted for password, enter it here.

USE mysql;
SELECT user, host, password, plugin FROM mysql.user;

This code snippet connects to MariaDB using the mysql command (assuming you don't have a password set yet) and then checks the plugin column for the root@localhost entry in the mysql.user table. This will tell you if password or unix socket authentication is currently in use.

a. Stopping MariaDB (if running):

sudo systemctl stop mariadb  # Replace with your service management command if different

b. Editing configuration file:

Note: Edit the configuration file with caution. Incorrect changes could prevent MariaDB from starting.

3. Add or modify the following line under the [mysqld] section:

   default_authentication_plugin=mysql_native_password
   

c. Starting MariaDB:

sudo systemctl start mariadb  # Replace with your service management command if different

mysql -u root  # Assuming you don't have a password set yet

# Enter when prompted for password (if it's blank)

USE mysql;
UPDATE user SET password = PASSWORD('your_strong_password') WHERE user = 'root' AND host = 'localhost';
FLUSH PRIVILEGES;

This code snippet stops MariaDB (if running), modifies the configuration file to enable password authentication by default, restarts MariaDB, and then sets a strong password for the root user using the UPDATE statement.

Important Security Considerations:

• Do not use these steps in a production environment. Enabling both password and unix socket authentication for the root user introduces a security risk.
• If an attacker gains access to your system, they could potentially use unix socket authentication to access MariaDB, even if you have a strong password set.
• It's generally recommended to:
  • Consider creating a separate, less privileged user account for administrative tasks and grant it the necessary privileges instead of using the root user directly.
  • Disable unix socket authentication for the root user in production environments.

• Set a Strong Password: Use a complex password for the root user that's at least 12 characters long and includes a combination of uppercase and lowercase letters, numbers, and symbols. This significantly increases the difficulty of brute-force attacks.
• Create a Separate Administrative User: Instead of using the root user directly for administrative tasks, create a new user with the necessary privileges. This user should have a strong password as well. Grant this user the specific privileges required for administrative actions (e.g., GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'strong_password' WITH GRANT OPTION;). This approach minimizes the damage if an attacker gains access to the administrative account.

Password Authentication with Restricted Access:

• Maintain Password Authentication: Keep password authentication enabled for the root user. This provides a robust login method.
• Restrict Root User Access (Optional): Consider restricting the root user's access to only the local machine (localhost) by adding 'localhost' after the username in the grant statement (e.g., GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'strong_password' WITH GRANT OPTION;). This prevents remote connections using the root user, further enhancing security.

Advanced Authentication Methods (MariaDB 10.4+):

• MariaDB 10.4 and later offer more secure authentication plugins in addition to the traditional password-based method. These include plugins like caching_sha2_password and sha256_password which utilize stronger hashing algorithms. If your MariaDB version supports them, consider using these plugins for enhanced security.

Remember:

• Regularly update MariaDB to benefit from the latest security fixes and features.
• Implement a robust security strategy that goes beyond database access control. This might include firewall rules, system user account management, and regular security audits.
• Choose the method that best suits your security requirements and the specific version of MariaDB you're using.