How to Assign Specific Database to Specific User in MySQL

What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that uses SQL (Structured Query Language). It’s widely used for managing databases in various applications, from small websites to large enterprises.

How to Assign Specific Database to Specific User?

1. Login to MySQL with Root User:

To begin, log in to MySQL with the root user credentials:

mysql -u root -p

2. Create New User:

Create a new user with specific privileges and authentication method:

CREATE USER 'user_name'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'user_password';

3. Create New Database:

Create a new database for the user:

CREATE DATABASE database_name;

4. Verify Created New Database and New User:

Ensure the new database and user are created successfully:

show databases;
use mysql;
select user from user;

5. Assign Database to Specific User:

Grant all privileges on the created database to the specific user:

GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'localhost';

6. Exit from MySQL:

Exit from the MySQL command line:

exit

7. Restart Apache:

Restart the Apache service for changes to take effect:

service apache2 restart

FAQs:

How can I log in to MySQL with the root user?

To log in as root, use the command mysql -u root -p and enter the root password when prompted.

What is the syntax for creating a new user in MySQL?

The syntax is:

CREATE USER 'user_name'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'user_password';

How do I verify if a new database and user are created successfully?

You can verify by running the following commands in MySQL:

show databases;
use mysql;
select user from user;

What is the purpose of assigning a database to a specific user?

Assigning a database to a specific user ensures secure access control and data management.

How do I grant privileges on a database to a specific user?

Use the following command in MySQL:

GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'localhost';

Conclusion:

Assigning specific databases to specific users in MySQL is essential for maintaining data security and access control. By following the outlined steps, you can efficiently manage user privileges and ensure secure database operations.