MySQL - Create a read-only user
What's in the box?
How do you create a read-only user on a MySQL (or MariaDB) database? This tutorial will answer that question!
When creating a user on a database server, it is essential to assign permissions to one or more databases. In SQL, this is done with the " GRANT "This is used to add authorizations to a user on a database.
1. Create a MySQL user
The first step is to create a user on our MySQL server. It's only in a second step that we give this user rights.
Start by connecting to your MySQL instance as "root" to gain full rights. The command to use is :
mysql -u root -p
Then create the user with the "CREATE USER" command. In this example, the user is called "user_ro" and has the password "Tutobox2023":
CREATE USER 'user_ro'@'localhost' IDENTIFIED BY 'Tutobox2023';
Now that the user has been created, all that remains is to grant him rights.
2. Assign read rights to a MySQL user
To tune the read-only rights to the user "user_ro" on the "db_tutobox" database, use the following SQL command template based on the GRANT statement:
GRANT SELECT ON db_tutobox.* TO 'user_ro'@'localhost';
Specifying "db_tutobox.*" applies this right to all tables in the target database. We give the right to use the "SELECT" command since it can only be used to read data. In other words, it will not be able to use other commands such as UPDATE and DELETE.
Finally, reload privileges using the following command:
FLUSH PRIVILEGES;All that remains is to exit the MySQL command line:
exit;
You can now connect to the database using the credentials of the user you've just created, who will only have read-only rights to the database. If necessary, you can run a "mysql -u user_ro -p" test!
3. Conclusion
Thanks to this new item in the tutorial box, you will be able to create a read-only user on your MySQL server. You are free to adapt the various values in this example (username, password and database).
Resources :
