In MySQL and MariaDB, you can create users that connect to the database using socket authentication instead of traditional username and password combination. This method is secure and convenient, as it uses the operating system’s 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, using their Unix or Linux user credentials instead. This is useful for secure environments. This simplify the login processes by eliminating requirement of entering password for frequent users.

How it Works?

Socket authentication works by matching the operating system’s user with a MySQL/MariaDB user. When the OS user logs in, MySQL/MariaDB verifies their identity through the system socket, allowing access without a password.

Creating a User with Socket Authentication in MySQL

Let’s go through the steps 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 the root user:

sudo mysql -u root -p

Step 3: Create User with Socket Authentication

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

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

This command creates a user named “myuser” that 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 Changes

Apply the changes by flushing the privileges:

FLUSH PRIVILEGES;

Step 6: Verify the Connection

Now, log out of MySQL/MariaDB and try to log in as the new user using socket authentication:

mysql -u myuser

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

Conclusion

Creating a user with socket authentication in MySQL/MariaDB makes logging in easier and more secure. It uses the operating system’s user details instead of needing a separate password for the database. By following these steps, you can set up socket authentication quickly. This way, local users can access the database safely and conveniently.