How to Create MySQL Users and Grant Privileges (With Examples)

Managing MySQL users and permissions is a core database administration task. Instead of using the root user everywhere (which is unsafe), you should create separate users and grant them only the permissions they need.

In this guide, you’ll learn how to:

• Create MySQL users (local and remote)
• Grant and restrict database privileges
• Test user permissions
• Change user passwords
• Revoke privileges
• Delete MySQL users safely

This tutorial works for MySQL 5.7+, MySQL 8+, and MariaDB.

Prerequisites Before Creating MySQL Users and Granting Privileges

Before you start creating MySQL users and assigning privileges, make sure the following basic requirements are met. These prerequisites ensure that all commands in this guide work smoothly and securely.

1. MySQL Server Must Be Installed

You must have MySQL Server installed on your system. To check whether MySQL is installed or not , run the following command:

mysql --version

If MySQL is not installed, install it using the appropriate command:

On Ubuntu / Debian

sudo apt update
sudo apt install mysql-server

On CentOS / RHEL

sudo yum install mysql-server

On Windows

Download and install the XAMPP installer from their official website.

2. MySQL Service Must Be Running

Even if MySQL is installed, the service must be running. To check/start/enable the MySQL services, you can use the following command:

sudo systemctl status mysql
sudo systemctl start mysql
sudo systemctl enable mysql

3. Root or Admin Access to MySQL

You must have access to the MySQL root user or another user with CREATE USER and GRANT privileges.

Verify Root Login

mysql -u root -p

If you can log in successfully, you are ready to proceed.

4. Required MySQL Privileges

The user performing these steps must have the following permissions – CREATE USER, GRANT OPTION and CREATE DATABASE.

To verify the current user privileges, the command is:

SHOW GRANTS;

If these privileges are missing, you won’t be able to create users or assign permissions.

5. Understanding Local vs Remote Access (Important)

Before creating users, you should clearly understand where the user will connect from.

Local User (`localhost`)

• Connects only from the same server
• Used for:
  • PHP / Node / Python apps on the same server
  • Cron jobs
  • Local admin access

Remote User (`%` or IP-based)

• Connects from another machine
• Used for:
  • Remote applications
  • Reporting servers
  • BI tools

6. Network and Firewall Configuration (For Remote Users)

If you are creating a remote MySQL user, networking must be configured properly. The default MySQL port is 3306

Allow MySQL Port in Firewall (Linux Example)

sudo ufw allow 3306/tcp

Or for firewalld:

sudo firewall-cmd --add-port=3306/tcp --permanent
sudo firewall-cmd --reload

7. Strong Password Policy (Recommended)

Avoid weak passwords like `123456` or `password`.

Example of Strong Password

• Minimum 10-12 characters
• Uppercase, lowercase, numbers and symbols

If MySQL password validation is enabled, weak passwords will fail.

Step 1 – Log in to MySQL

First, log in to the MySQL server using the root account.

mysql -u root -p

Here,

• `-u root` → Login as the root user
• `-p` → Prompts for the MySQL root password

Once logged in, you will see the MySQL prompt:

MariaDB [(none)]>

Step 2 – Create a MySQL User

Now that you’re logged into the MySQL, let’s create a local user and remote user with a password.

a) Create a User for Local Access

This user can connect only from the same server where MySQL is installed.

CREATE USER 'howsnip'@'localhost' IDENTIFIED BY 'pass@123';

Here,

• `’howsnip’` → MySQL username
• `’localhost’` → User can log in only from the local machine
• `IDENTIFIED BY ‘pass@123’` → Sets the password

b) Create a User for Remote Access

This user can connect from other systems.

CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'pass@123';

Here, % means “allow connections from any host.”

In case if you want to create a remote user for a specific IP, then the command is:

CREATE USER 'remoteuser'@'192.168.1.11' IDENTIFIED BY 'pass@123';

Step 3 – Grant Privileges to a MySQL User

Creating a user is just the first step and 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.

Before assigning permissions, let’s create a new database.

CREATE DATABASE testdatabase;

This ensures the database exists before we start assigning permissions.

The general format of GRANT command looks like this:

GRANT privileges ON database.table TO 'username'@'host';

Here,

• `privileges` → SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES, etc.
• `database.table` → `testdatabase.*` means all tables
• `username@host` → Which user receives permissions

a) Grant Privileges to the Local User

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

Give full control to the local user.

GRANT ALL PRIVILEGES ON testdatabase.* TO 'howsnip'@'localhost';
FLUSH PRIVILEGES;

Here

• `ALL PRIVILEGES` → Full access (read, write, modify, delete)
• `testdatabase.*` → All tables in the database
• `FLUSH PRIVILEGES` → Reloads permission tables

b) 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:

Give read-only access.

GRANT SELECT ON testdatabase.* TO 'remoteuser'@'%';
FLUSH PRIVILEGES;

Here,

• `SELECT` → User can only read data
• No INSERT, UPDATE, or DELETE allowed

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

SHOW GRANTS FOR 'howsnip'@'localhost';

As you can see in above screenshot, the howsnip@localhost now has ALL privileges on the testdatabase.

SHOW GRANTS FOR 'remoteuser'@'%';

Here, remoteuser@% has only SELECT privileges on the testdatabase.

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 local user and remote user.

Login as Local User

mysql -u howsnip -p

Switch to the database:

USE testdatabase;

Create a Table

CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50)
);

If the table is created successfully, it means howsnip user has full privileges on testdatabase.

Login as Remote User

mysql -u remoteuser -p

And run the following commands:

USE testdatabase;
SELECT * FROM users;

This should work, even if the table is empty.

But if you try to insert data:

INSERT INTO users (username) VALUES ('chetansoni');

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

ERROR 1142 (42000): INSERT command denied to user 'remoteuser'@'localhost'

Here,

• `remoteuser` has SELECT only
• INSERT is not allowed

By testing the users this way, you have confirmed that the permissions are correctly applied: the howsnip has full control over the testdatabase, while the remoteuser is restricted to read-only access.

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

1. 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. 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.

ALTER USER 'howsnip'@'localhost' IDENTIFIED BY 'newpass@123';
FLUSH PRIVILEGES;

Here,

• Updates the password instantly
• Existing permissions remain unchanged

2. 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.

REVOKE Syntax

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

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

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

REVOKE INSERT, UPDATE, DELETE ON testdatabase.* FROM 'howsnip'@'localhost';
FLUSH PRIVILEGES;

Here,

• Removes write access
• SELECT access remains

To test this, log in as howsnip user and try to insert some data.

mysql -u howsnip -p
USE testdatabase;
INSERT INTO users (username) VALUES ('chetansoni');

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

ERROR 1142 (42000): INSERT command denied to user 'howsnip'@'localhost'

3. Deleting a MySQL User Account

If an user 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.

DROP USER 'howsnip'@'localhost';
DROP USER 'remoteuser'@'%';

Here,

• User is permanently removed
• All permissions are deleted automatically

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 howsnip -p). You should get an Access denied error, confirming the account has been completely removed from the system.

Conclusion

In this guide, you learned how to create local and remote MySQL users, grant and restrict database privileges, verify assigned permissions, change user passwords, revoke access when required, and securely delete user accounts.

Following this structured approach helps improve database security, maintain proper access control, and ensure overall system stability.