How to Create New user in MSQL/MariaDB Server

Introduction

MySQL is a popular open-source database management system used to store and retrieve data. It is used in many applications and websites, from small enterprises to large organizations. One of the most important aspects of MySQL is its user account system, which allows users to access the database securely and manage their own data. In this article, we will show how to a new create user in MySQL/MariaDB.

Prerequisites

Create New user in MSQL/MariaDB Server via command line

1. Login to MySQL/MariaDB database using root:

# mysql -u root -p

Example with output :

# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1234567
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2. Create a New User in MySQL User testuser :

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'mypassword';

Example with output :

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'mypassword';
Query OK, 0 rows affected (0.12 sec)

3. Grant privileges of user testuser :

mysql> GRANT ALL PRIVILEGES ON database_name. * TO 'username'@'localhost';

Example with output :

mysql> GRANT ALL PRIVILEGES ON testdb.* TO testuser@localhost;

Query OK, 0 rows affected (0.23 sec)

4. Show granted previleges :

SHOW GRANTS FOR testuser@localhost;

Example with output :

mysql> SHOW GRANTS FOR testuser@localhost;
+--------------------------------------------------------------+
| Grants for testuser@localhost |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testuser`@`localhost` |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `testuser`@`localhost` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

5. Show all users created :

Example with output :

mysql> SELECT user,host FROM mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| testuser | localhost |
+------------------+-----------+
6 rows in set (0.01 sec)

Conclusion

Creating a new user in MySQL is an essential step to ensure the security and integrity of your data. With the right set up, you can create a user with the appropriate privileges to access and manage your database. This article explained the steps on how to create a new user in MySQL and assign it the necessary privileges. This way, business can make sure that only authorized users have access to your sensitive data.