How to Create a MySQL User and Grant Privileges (Step by Step Guide)

Key Takeaways

• Never use the root account for applications or daily tasks. Instead, create specific users with limited privileges to reduce your risk.
• The entire process revolves around two main commands: CREATE USER to set up a new account and GRANT to give that user permissions on a specific database or table.
• Regularly use commands like REVOKE to remove permissions and DROP USER to delete accounts that are no longer needed.

Managing users is one of the most important things that you can do to build and maintain security and structure in your MySQL databases. Instead of allowing every application or developer access through the root account, you should always configure users with necessary controls and if it’s applicable, limit their permission levels to only what they need.

By providing limited user access, you reduce risk and protect sensitive information, while still maintaining control over who can do what in the database.

In this tutorial, we’ll detail how to create a user in MySQL from start to finish, and we’ll even show you how to create a user with a password and grant them the appropriate privileges.

In addition, we will detail some additional user management tasks, like changing passwords, revoking access, or deleting accounts.

Prerequisites

Before we start creating a MySQL user, let’s make sure the basics are covered. At a minimum, you’ll need:

• A working MySQL server (or MariaDB) – MySQL should already be installed and running. If you’re using XAMPP, WAMP, or MAMP, note that these packages typically include MariaDB, which works the same way for the commands we’ll cover. If you installed MySQL directly, that works just as well.
• An account with administrative privileges – Usually the root account, since you’ll need full rights to create new users and assign permissions.
• Access to a command line or terminal – Almost everything in this tutorial will be done through commands. On Windows, that could be Command Prompt (CMD), PowerShell, or the XAMPP Shell. On Linux or macOS, you’ll use the terminal.

That’s all you really need to follow along.

For this tutorial, I’ll be working on a Windows machine using XAMPP with access to MySQL/MariaDB through the Command Prompt (CMD). If you’re on Linux or macOS, don’t worry, the commands we’ll cover are exactly the same, you’ll just be running them from your terminal instead.

Step 1: Log in to MySQL

The first thing you need to do is access MySQL with an account that has the right permissions. Most of the time, this will be the root user, since it has full privileges and can create or manage other accounts.

To do this:

• On Windows (with XAMPP), you can click the Shell button inside the XAMPP Control Panel.

• On Linux or macOS, just open the terminal.

Once you’re in, type the following command and press Enter:

mysql -u root -p

Here’s what each part means:

• mysql → tells the system you want to use the MySQL client.
• -u root → specifies the username (root in this case).
• -p → tells MySQL to prompt you for a password.

After you run the command, you’ll be asked to enter the root password. If you’re using XAMPP on Windows, the default root account usually has no password set (you can just press Enter).

If successful, you’ll see something like this:

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 8

Server version: 10.4.28-MariaDB mariadb.org binary distribution

MariaDB [(none)]>

This prompt (MariaDB [(none)]>) means you’re now inside the MySQL/MariaDB shell and ready to start running commands.

On Linux or macOS

Open your terminal and run:

mysql -u root -p

Enter your root password when prompted. If everything is set up correctly, you’ll see a similar mysql> or MariaDB> prompt.

Step 2: Create a New User in MySQL

Now that you’re logged into the MySQL shell, let’s create a new user with a password, let’s do two: one that works locally (on the same server) and one that can connect remotely. This way, you’ll understand both use cases.

Syntax of CREATE USER

This is the command for mysql create user with password:

CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;

• username → the name of the new MySQL user.
• host → tells MySQL where this user can connect from.
• password → the secure password for the account.

1. Create a User for Local Access

Run this command inside the MySQL shell:

CREATE USER 'localuser'@'localhost' IDENTIFIED BY 'LocalPass123!';

This creates a user named localuser who can only connect from the same server where MySQL is installed.

2. Create a User for Remote Access

Now, let’s create another user that can connect from outside the server:

CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'RemotePass123!';

Here, % means “allow connections from any host.” If you want to lock it down to a specific IP for better security, replace % with that IP, for example:

CREATE USER 'remoteuser'@'192.168.1.50' IDENTIFIED BY 'RemotePass123!';

The two Query OK messages you see in the screenshot above means both users were successfully created in your MariaDB instance.

Importance of Using a Strong Password

When creating MySQL users, always use a strong and unique password. Weak credentials are one of the most common ways databases get compromised. A good password should:

• Contain uppercase and lowercase letters.
• Include numbers and special characters.
• Be at least 12–16 characters long.
• Not be reused from another account.

At this point, both localuser and remoteuser exist in MySQL, but they don’t have permission to do anything yet. In the next step, we’ll grant them the privileges they need.

Step 3: Grant Privileges to a MySQL User

Creating a user is just the first step — by default, new users have no permissions. To actually let them do something (like create databases, read/write tables, or manage users), you’ll need to grant them privileges.

First, let’s create the database we’ll be working with. Run the following command in the MySQL shell:

CREATE DATABASE testdb;

This ensures the database exists before we start assigning permissions.

Syntax of GRANT

The general format looks like this:

GRANT privileges ON database.table TO ‘username’@’host’;

• privileges → what actions the user can perform (SELECT, INSERT, ALL PRIVILEGES, etc.).
• database.table → where those privileges apply (mydb.* means all tables in mydb).
• ‘username’@’host’ → the MySQL user you created earlier.

After granting, always run:

FLUSH PRIVILEGES;

This forces MySQL to reload the updated permissions.

1. Grant Privileges to the Local User

Let’s allow localuser to fully manage a database named testdb. Run the commands below one by one inside the same shell (MariaDB [(none)]> prompt):

GRANT ALL PRIVILEGES ON testdb.* TO 'localuser'@'localhost';

FLUSH PRIVILEGES;

This means localuser can create, update, and delete tables/data inside testdb, but nothing else outside of it.

2. Grant Privileges to the Remote User

For remoteuser, let’s say you want them to have read-only access to the same database. That way, they can query data but not change it. Run these commands as well one by one:

GRANT SELECT ON testdb.* TO 'remoteuser'@'%';

FLUSH PRIVILEGES;

This ensures remoteuser can query data remotely but not modify it to avoid the risk of accidentally deleting or overwriting anything.

3. Checking Granted Privileges

At any point, you can check what permissions a user has by running the commands below:

SHOW GRANTS FOR 'localuser'@'localhost';

As you can see in the screenshot, the localuser@localhost now has ALL privileges on the testdb database.

SHOW GRANTS FOR 'remoteuser'@'%';

remoteuser@% has only SELECT privileges on the testdb database.

With this, your two users now have different roles:

• localuser → full control over testdb.
• remoteuser → read-only access to testdb.

Step 4: Test the New MySQL Users

Creating users and granting privileges is only half the job — you also want to verify that the permissions are working as expected. Here’s how you can test both localuser and remoteuser.

1. Log in as localuser

From your XAMPP shell (exit your current MariaDB session first by typing exit;), then log in with:

mysql -u localuser -p

When prompted, enter the password you set earlier (LocalPass123!).

As you can see, now I’m logged in as localuser.

Once inside, switch to the testdb database:

USE testdb;

Try creating a simple table:

CREATE TABLE sample_table (

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(50)

);

If the table is created successfully, it means localuser has full privileges on testdb.

As you can see from the screenshot, the Query OK message confirms that the table has been successfully created.

2. Log in as remoteuser

Still in the XAMPP shell, log out from the current session (exit;) and log in with:

mysql -u remoteuser -p

Enter the password you set (RemotePass123!).

Switch to the same database:

USE testdb;

Now try running:

SELECT * FROM sample_table;

This should work, even if the table is empty.

But if you try to insert data:

INSERT INTO sample_table (name) VALUES ('Test Name');

You’ll see a permission denied error, which confirms that remoteuser only has SELECT privileges.

By testing like this, you’ve confirmed that your permissions are applied correctly:

• localuser can fully manage testdb.
• remoteuser is limited to read-only access.

Additional User Management Tasks

Creating users and granting privileges are a big part of database security, but you’ll also need to manage those users over time. As roles change or projects end, you may need to update a password, remove certain permissions, or even delete an account entirely.

Here’s how you handle those tasks.

Changing a User’s Password

If a user needs to update their password, you can do it easily from the MySQL shell. This is a common task for security or when a password has been compromised.

Syntax:

ALTER USER 'username'@'host' IDENTIFIED BY 'NewSecurePassword!';

Example:

To change the password for localuser to NewPassword!456, you would run:

ALTER USER 'localuser'@'localhost' IDENTIFIED BY 'NewPassword!456';

But first, to change a password or manage users, you must be logged in as an account with administrative privileges, such as the root user.

So login as root user (mysql -u root -p) and then run the command shared earlier.

After you run the command, log out of your current session (exit;) and try logging back in with the old password (mysql -u localuser -p). It should fail.

Then, try logging in with the new password. If you can get in, you’ll know the change was successful!

The new password worked flawlessly!

Revoking Privileges

You may want to take away a user’s permissions without deleting the account. The REVOKE command is the opposite of GRANT and lets you remove specific privileges.

Syntax:

REVOKE privileges ON database.table FROM 'username'@'host';

Example:

To revoke localuser’s ability to create or delete data, but still let them read it, you can remove INSERT, UPDATE, and DELETE privileges by running the command below. But first login as a root user.

REVOKE INSERT, UPDATE, DELETE ON testdb.* FROM 'localuser'@'localhost';

Afterward, be sure to run FLUSH PRIVILEGES; to apply the changes.

To test this, log in as localuser again (mysql -u localuser -p) and try to insert some data. To do this select the database with this command:

USE testdb;

You will see the message “Database changed” which confirms you are now working within the testdb database.

The insert the data with this command:

INSERT INTO sample_table (name) VALUES ('Test Data');

The command should fail, and you’ll see a permission denied error, which tells you the REVOKE command worked as intended.

 

Deleting a User Account

If an account is no longer needed, it’s a security best practice to remove it completely. The DROP USER command deletes the user account and all of its associated privileges.

Syntax:

DROP USER 'username'@'host';

Example:

To delete the remoteuser account you created earlier, simply run the command, but logged in as root user.

DROP USER 'remoteuser'@'%';

You can also drop multiple users at once by separating them with commas. Just be careful, as this action is irreversible.

To test this, try to log in with the deleted user (mysql -u remoteuser -p). You should get an Access denied error, confirming the account has been completely removed from the system.

And that is it…you now know how to:

• Establish a secure connection with administrative privileges.
• Create dedicated user accounts for both local and remote access.
• Grant precise permissions to each user, following the principle of least privilege.
• Maintain user accounts by changing passwords, revoking access, and securely deleting accounts when they’re no longer needed.

Managing MySQL Users on Cloudways with Database Manager

When working on a local MySQL setup, you typically create users and grant them privileges through the command line. On Cloudways, you can achieve the same using the Database Manager built into our platform.

Accessing the Database Manager

• Log in to the Cloudways Platform.
• Navigate to Applications > Your App > Application Management.
• Under Access Details, click Launch Database Manager.

Common Errors and Fixes

Even if you follow the steps correctly, working with MySQL users doesn’t always go smoothly. You might run into permission issues, connection problems, or errors that look confusing at first glance. Below are some of the most common errors you’ll likely encounter when creating or managing MySQL users — along with practical fixes to resolve them quickly.

1. Access Denied for User

This error usually appears as:

ERROR 1045 (28000): Access denied for user ‘username’@’localhost’

Cause: Wrong credentials, host mismatch, or insufficient privileges.

Fix: Double-check the username and password.

Run: SHOW GRANTS FOR ‘username’@’localhost’;  to confirm privileges.

If needed, reset the password with:

ALTER USER 'username'@'localhost' IDENTIFIED BY 'NewPass123!';

2. User Cannot Connect Remotely

Error looks like:

ERROR 1130 (HY000): Host ‘IP_ADDRESS’ is not allowed to connect to this MySQL server

Cause: MySQL by default may only allow local connections.

Fix: Grant access for all hosts with %:

CREATE USER 'username'@'%' IDENTIFIED BY 'UserPass123!';

GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'%';

FLUSH PRIVILEGES;

Ensure the server’s firewall and mysqld.cnf bind-address are configured to allow remote connections.

3. Error 1396: Operation CREATE USER Failed

ERROR 1396 (HY000): Operation CREATE USER failed for ‘username’@’localhost’

Cause: The user already exists in MySQL.

Fix: First, drop the old user if not needed:

DROP USER 'username'@'localhost';

Or, if the user exists but you just want to change the password/privileges, use ALTER USER instead of CREATE USER:

ALTER USER 'username'@'localhost' IDENTIFIED BY 'NewPass123!';

GRANT SELECT, INSERT ON dbname.* TO 'username'@'localhost';

Make Your MySQL User Work for You

Go live on PHP hosting built for MySQL apps. With fast setup, secure access, and daily backups, you’ll have everything you need to keep your projects running smoothly.

TRY NOW

Best Practices for MySQL User Management

Managing MySQL users isn’t just about creating accounts — it’s also about keeping your database secure and efficient. Following best practices helps prevent unauthorized access and reduces the risk of data leaks or accidental damage.

Here are a few key guidelines to keep in mind:

• Use strong and unique passwords – Always assign complex, unique passwords to each MySQL user. This prevents brute-force attacks and keeps accounts safe.
• Avoid using root for applications – The root account has full control over the database. Instead, create separate users for your apps with limited privileges.
• Grant only necessary privileges – Follow the principle of least privilege. For example, if a user only needs to read data, don’t give them write or admin rights.
• Restrict remote access when possible – Limit users to localhost unless remote connections are absolutely necessary. If you must enable remote access, whitelist only specific IPs.

By applying these practices, you’ll create a more secure MySQL environment while keeping user management simple and controlled.