How to create new user in MySQL and give it full access to one database?

Creating separate MySQL users with well-defined privileges is a security best practice, and MySQL makes it simple to do so. Below is a step-by-step guide for adding a new user and giving them full access to one specific database.

1. Log In as a Privileged User

First, open a terminal or command prompt and log into MySQL using an existing user with administrative privileges (e.g., root):

mysql -u root -p

Enter the password when prompted.

2. Create a New User

Use the CREATE USER statement to create the new user. Replace:

• new_user with your desired username.
• host with either localhost or % (for any host), depending on your needs.
• password123 with a strong password.

CREATE USER 'new_user'@'host' IDENTIFIED BY 'password123';

Example

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password123';

This creates a user who can only connect from localhost. If you want the user to be able to connect remotely, use % as the host:

CREATE USER 'new_user'@'%' IDENTIFIED BY 'password123';

3. Grant Full Privileges on One Database

Suppose you have a database named my_database. To give full access (including SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, etc.) on all tables in that database to your new user, run:

GRANT ALL PRIVILEGES ON my_database.* TO 'new_user'@'host';

Note:

• The my_database.* syntax means all tables in my_database.
• ALL PRIVILEGES grants the user full rights on those tables, but only within my_database.

4. Refresh the Privileges

In some older MySQL versions, you’ll need to refresh the privileges for changes to take effect:

FLUSH PRIVILEGES;

On most modern MySQL installations (8.0+), this step is usually not required as MySQL automatically updates privileges when CREATE USER or GRANT statements are executed. However, it doesn’t hurt to run the command if you’re unsure.

5. Test the New User

Disconnect from MySQL:

EXIT;

Then, try logging in with the new user credentials to confirm the privileges:

mysql -u new_user -p

Enter the password. Now you can run commands against my_database. For instance:

USE my_database;
SHOW TABLES;

6. Best Practices

1. Use Unique Logins: Give each application or developer a separate MySQL user to easily track and manage privileges.
2. Limit Host Access: Use a specific host (like localhost for local connections) rather than % whenever possible.
3. Follow Principle of Least Privilege: Grant only the minimum privileges a user needs. For example, if the user only needs read-only access, use SELECT instead of ALL PRIVILEGES.
4. Rotate Passwords: Regularly update the user’s password, especially in production environments.

7. Level Up Your Database Skills

If you want to deepen your knowledge—whether for professional development or interview prep—consider these courses by DesignGurus.io:

• Grokking Database Fundamentals for Tech Interviews
  Dive into indexing, normalization, transactions, and best practices for designing scalable databases.

• Grokking SQL for Tech Interviews
  Master real-world SQL querying, optimization techniques, and interview-focused problem-solving.

8. Mock Interviews: Refine Your Approach

If you’re preparing for job interviews (especially at tech giants), you can book Mock Interviews with ex-FAANG engineers. Get personalized feedback on both technical and behavioral aspects, ensuring you’re fully prepared.

In summary:

1. Create the user with a strong password.
2. Grant them ALL PRIVILEGES on the specific database.
3. Flush privileges (if needed).
4. Verify by logging in as the new user.

Following these steps ensures you maintain good security practices while giving the new user everything they need on that database.