Creating a read-only database user for MySQL/MariaDB

[isscbta]

This guide will show you how to create a new database user with read-only access to a specific database. We will use a generic database name, example_db, and create a new user named example_new_user with the password examplePassword. This user will only have SELECT privileges, meaning they can read data but cannot modify it.

Step 1: Log in to the MySQL/MariaDB Console

Open your terminal and log in as the root user, then run:

Code: Select all

mysql

Step 2: Create the New Database User

Create the new user example_new_user with the password examplePassword. You can replace examplePassword with a stronger, randomly generated password if desired:

Code: Select all

 CREATE USER 'example_new_user'@'localhost' IDENTIFIED BY 'examplePassword'; 

Step 3: Grant Read-Only Privileges on the Database

Grant the user SELECT privileges on the entire example_db database:

Code: Select all

 GRANT SELECT ON example_db.* TO 'example_new_user'@'localhost'; 

Step 4: Apply the Changes

To ensure all privilege changes take effect, flush the privileges:

Code: Select all

 FLUSH PRIVILEGES; 

Step 5: Exit the MySQL/MariaDB Console

Exit the MySQL/MariaDB console:

Code: Select all

 EXIT; 

Testing the New User

To test that the new user has the correct read-only access, try connecting to the database using the new credentials:

Code: Select all

 mysql -u example_new_user -p -D example_db 

Enter the password (examplePassword) when prompted. Once logged in, try executing a simple SELECT query to ensure you can read data. The user should not be able to perform any data modifications.

Conclusion

By following these steps, you have successfully created a new read-only database user for the example_db database. This setup is ideal for allowing web applications or external services to access your database securely without the risk of data modification.