How to Create a New User and Grant Permissions in MySQL

Managing users and permissions is a vital aspect of MySQL database administration. Whether you’re setting up a fresh database or maintaining an existing one, understanding how to create new user in MySQL and grant the right permissions ensures both security and functionality. This guide will walk you through the entire process in a simple, human-friendly way, covering all MySQL versions.

What is MySQL User Management?

MySQL user management is the process of creating, and editing user accounts, as well as deleting them which communicate directly with the MySQL server. Additionally, it involves giving permissions that define what actions users can take on databases.

Imagine MySQL users as employees of an office. Every user has a job and only requires access to a particular system or files based on their job. The proper management of users makes sure that no one gets access to more than they require.

Why is User Management Important?

• Security: Limit access to sensitive data.
• Efficiency: Give users only the tools they need.
• Accountability: Track who made changes to the database.
• Scalability: Easily add or remove users as your team grows.

Prerequisites

Before diving in, make sure you have:

• MySQL installed (any version from MySQL 5.x to MySQL 8.x)
• Administrative privileges to access the MySQL server
• Basic knowledge of MySQL commands

How to Create a New User in MySQL

Let’s break it down step by step.

Step 1: Log in to MySQL

First, access your MySQL server using the root user or an admin user:

mysql -u root -p

Enter your root password when prompted.

Step 2: Create a New User in MySQL

The command to create a user varies slightly across different MySQL versions.

5.x MySQL and 5.7

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

MySQL 8.x

MySQL 8.0 uses a new authentication plugin by default:

CREATE USER 'username'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

👉 Replace username with the desired username and password with a secure password.

Step 3: Grant Permissions

Now let’s give the user access to certain databases.

Grant All Permissions on a Database

If you want to give full access to a specific database:

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

Grant Specific Permissions

Only want the user to read and write data?

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'localhost';

Global Permissions (All Databases)

For full access across all databases:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

Step 4: Apply Changes

Whenever you grant or revoke permissions, always run this command:

FLUSH PRIVILEGES;

This refreshes the permission settings without restarting the server.

Step 5: Check Permissions

Want to double-check what permissions a user has?

SHOW GRANTS FOR 'username'@'localhost';

How to Revoke Permissions

Mistakes happen, and sometimes you’ll need to revoke permissions:

REVOKE INSERT, DELETE ON database_name.* FROM 'username'@'localhost';

Don’t forget to run:

FLUSH PRIVILEGES;

How to Delete a MySQL User

If you no longer need a user, simply delete them:

DROP USER 'username'@'localhost';

✅ Always clean up unused users to avoid security risks.

Role-Based Permissions (MySQL 8.x)

MySQL 8.x introduces Roles to simplify permission management. Instead of assigning permissions to each user, you can assign them to a role and then assign the role to users.

Creating a Role

CREATE ROLE developer;
GRANT SELECT, INSERT, UPDATE ON database_name.* TO developer;

Assigning Role to User

GRANT developer TO 'username'@'localhost';
SET DEFAULT ROLE developer FOR 'username'@'localhost';

Common Errors & Troubleshooting

1. Access Denied (Error 1045)

This usually happens when the username, password, or host is incorrect. Double-check your credentials.

2. Permissions Not Applying

Always remember to run:

FLUSH PRIVILEGES;

3. Cannot Drop User

Make sure the user is not currently logged in before trying to delete them.

Backup and Restore User Privileges

You can export user permissions using the mysqldump command:

mysqldump --user=root --password --all-databases --routines --triggers --events > backup.sql

To restore the backup:

mysql --user=root --password < backup.sql

Security Tips

• Enable SSL connections.
• Set password expiration policies.
• Restrict access to users based on IP addresses.
• Use multi-factor authentication if possible.

Permission Best Practices

Environment	Permissions
Development	Full access
Testing	Limited data manipulation
Production	Read-only or minimal write

FAQs

1. What is the difference between GRANT and REVOKE?

GRANT gives permissions, while REVOKE removes them.

2. How can I reset a MySQL user password?

Use this command:

ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

3. Can I create a user without a password?

Technically yes, but it’s a terrible idea for security reasons. Always use strong passwords.

4. What is WITH GRANT OPTION?

It allows the user to grant permissions to other users — basically making them a mini-admin.

5. How can I see all users in MySQL?

SELECT user, host FROM mysql.user;

Conclusion

MySQL user management isn’t just about creating users – it’s about securing your database while allowing individuals who are qualified to access the information they require. Following this tutorial, you can create users, grant permissions, and ensure that your MySQL database is protected and organized.

If you’re using MySQL 5.x or the most recent MySQL 8.x These steps will assist you in maintaining the stability of your system.