Troubleshooting MariaDB User Creation: Encountering Error 1396

CREATE USER new_user@localhost;

This code attempts to create a new user named "new_user" who can access the database from the local machine ("localhost"). However, this code by itself might cause Error 1396 because it doesn't specify a password for the user.

Here's a corrected version that should work without the error:

CREATE USER new_user@localhost IDENTIFIED BY 'strong_password';

This version defines a password named "strong_password" for the user "new_user".

1. Using Authentication Plugins:

MariaDB supports various authentication plugins beyond the standard password-based method. These plugins offer different ways to verify a user's identity. Here's an example using the ed25519 plugin for public key authentication:

CREATE USER secure@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;

This code creates a user "secure" who can connect from any host (%) and uses either a public key stored with the ed25519 plugin or a Unix socket connection for authentication. You'll need to configure public key authentication for this method to work.

1. Using Management Tools:

Many MariaDB management tools offer a graphical interface to create users. These tools can simplify the process, especially for users unfamiliar with SQL commands. These tools typically connect to the MariaDB server and execute commands behind the scenes.

Choosing the Right Method:

• The standard CREATE USER with a password is the most straightforward method and suitable for most cases.
• Authentication plugins offer advanced security options but require additional configuration.
• Management tools provide a user-friendly interface but might not offer the same level of control as direct SQL commands.