How to create a user with socket authentication in MySQL/MariaDB

Introduction

In MySQL and MariaDB, you can create users who connect to the database using socket authentication instead of the traditional username and password combination. This method is safe and convenient, as it uses the operating system user credentials. Here's how you can set it up.

What is socket authentication?

Socket authentication allows a user to connect to the database without a password, instead using their Unix or Linux user credentials. This is useful for secure environments. This simplifies login processes by eliminating the need to enter passwords for frequent users.

How does it work?

Socket authentication works by matching the operating system user to a MySQL/MariaDB user. When the operating system user logs in, MySQL/MariaDB verifies their identity via the system socket, allowing passwordless login.

Creating a user with socket authentication in MySQL

Let's see the steps necessary to create a user with socket authentication in MySQL/MariaDB.

Step 1: Create a Unix/Linux user

First, create a new Unix/Linux user if you don't already have one. Open your terminal and use the following command:

sudo adduser myuser

This will create a new user named "myuser" in your system.

Step 2: Log in to MySQL/MariaDB

Next, log in to your MySQL/MariaDB server as root user:

sudo mysql -u root -p

Step 3: Create a user with socket authentication

Once logged in, create a new MySQL/MariaDB user that uses auth_socket authentication:

CREATE USER 'myuser'@'localhost' IDENTIFIED WITH auth_socket;

This command creates a user named "myuser" who can connect from "localhost" using socket authentication.

Step 4: Grant privileges

Grant the necessary privileges to the new user. For example, to give all privileges on a specific database:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

Step 5: Apply your changes

Apply changes by clearing privileges:

FLUSH PRIVILEGES;

Step 6: Test your connection

Now, exit MySQL/MariaDB and try logging in as a new user using socket authentication:

mysql -u myuser

If everything is set up correctly, you should be able to connect without being asked for a password.

Conclusion

Creating a user with socket authentication in MySQL/MariaDB makes logging in easier and more secure. Use operating system user details rather than requiring a separate password for the database. By following these steps, you can quickly set up socket authentication. This way, local users can access the database securely and conveniently.