Create a MySQL user and assign him a database

This tutorial explains how to create a new MySQL user and a new database, and grant this user the permissions needed to assign the database to him.

You need to know this if you want to have a dedicated MySQL user associated with an application, with permissions on a single database only. In production, the MySQL "root" user must not be directly associated with an application, for security reasons.

We will use a database named db_tutobox and a user named user_tutobox as practical examples.

Need help installing MySQL on Debian? Follow this tutorial :

• Comment installer MySQL sur Debian ?

1. Creating a new MySQL database

To begin with, you need to create a new MySQL database, which will then be associated with a new application or website (WordPress, GLPI, Nextcloud, Matomo, etc.). The " CREATE DATABASE "will be used.

But first, you need to connect to the MySQL instance:

mysql -u root -p

Enter the password and a MySQL prompt will appear on the screen. The following command creates a new database:

CREATE DATABASE db_tutobox;

Here's the result:

To check the presence of the database, you can list those present on your instance with this command:

SHOW DATABASES;

The " db_tutobox "is clearly visible, alongside other databases such as " mysql "and " information_schema" .

2. Creating a MySQL user and assigning rights

Once the database has been created, we need to create a new user and assign permissions directly to the new database.

The command below creates the user " user_tutobox "and associates the password " YourPassword" . It will have full rights to all database tables. db_tutobox" . This user can only connect locally (hence the localhost). If your database and web server are not on the same machine, additional configuration is required.

GRANT ALL PRIVILEGES ON db_tutobox.* TO user_tutobox@localhost IDENTIFIED BY "YourPassword";

After granting these permissions, it is necessary to reload the privileges to ensure that they take effect immediately:

FLUSH PRIVILEGES;

Now your database is ready, and this user has all the permissions he needs to manage it: create tables, add/delete/modify records, etc.

3. View users and their permissions

To manage MySQL users efficiently, it's often useful to list existing users and view their permissions (local/remote connection).

To list all MySQL database users, run this command:

SELECT User, Host FROM mysql.user;

This command returns a list of all registered users, together with the hosts from which they are authorized to connect.

To view the permissions associated with a specific user, you can use the following command:

SHOW GRANTS FOR 'user_tutobox'@'localhost';

Here's an example:

4. Conclusion

By reading this article in the IT Tutorials box, you've learned how to create a MySQL user and assign permissions to a newly created database. This is a very common administrative action when deploying a new application that requires a MySQL database.

The steps described in this article apply to MySQL Server and MariaDB Server.