How to Create New user in MSQL/MariaDB Server

Creating new users in MySQL or MariaDB is a fundamental task for database and Linux administrators managing database security and access control. Proper user management ensures that only authorized individuals can interact with the database while maintaining compliance with security best practices. In this guide, we will walk you through the process of creating new users in MySQL and MariaDB, assigning appropriate privileges, and implementing security best practices. Whether you are setting up a new database environment or managing existing user accounts, this tutorial will help you streamline the process efficiently.

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

Managing user accounts in MySQL and MariaDB is essential for maintaining database security and access control. By following best practices when creating new users—such as setting strong passwords, granting minimal privileges, and using role-based access—you can enhance database security and ensure efficient user management. Regularly reviewing user permissions and revoking unnecessary access will further strengthen your database security posture. With these steps, Linux and database administrators can confidently manage user accounts while optimizing database performance and security.