MySQL Create User and Grant Privileges: Full Guide

Running your entire MySQL instance under the root account is the database equivalent of leaving your server’s front door unlocked. Here’s how to create users with the right privileges and nothing more.

Prerequisites: MySQL Access and Setup

You need an existing MySQL installation and the ability to log in as root or a user with GRANT OPTION privileges:

mysql -u root -p

Enter your root password when prompted. From here you can create users, assign privileges, and manage the entire access control layer.

How to Create a MySQL User

CREATE USER Statement Syntax

The full syntax for creating a MySQL user:

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

The host portion controls where connections can originate. localhost restricts to local connections only. % allows connections from any host. You can also specify an IP address or subnet.

Creating a User with a Password

Create a local user named appuser:

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';

On MySQL 8.0+, the default authentication plugin is caching_sha2_password. Older clients may need mysql_native_password instead:

CREATE USER 'appuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';

Creating a User for Remote Access

Allow connections from any host by using % as the host value:

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

For tighter control, restrict to a specific IP:

CREATE USER 'remoteuser'@'192.168.1.100' IDENTIFIED BY 'RemotePassword456!';

How to Grant Privileges in MySQL

Granting All Privileges on a Database

Give a user full access to a specific database:

GRANT ALL PRIVILEGES ON mydatabase.* TO 'appuser'@'localhost';

The *.* syntax grants privileges across all databases, which you should only use for administrative accounts.

Granting Specific Privileges

Least-privilege is the right approach. A read-only reporting user needs SELECT only:

GRANT SELECT ON mydatabase.* TO 'readonly_user'@'localhost';

An application that only reads and writes records:

GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'appuser'@'localhost';

Available privileges include: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, and more.

Applying Privilege Changes with FLUSH PRIVILEGES

After granting privileges, flush the grant tables to ensure the changes take immediate effect:

FLUSH PRIVILEGES;

MySQL 8.0 applies GRANT and REVOKE automatically, but running FLUSH PRIVILEGES is still good practice when directly modifying system tables.

How to View Granted Privileges

Check what privileges a user currently has:

SHOW GRANTS FOR 'appuser'@'localhost';

For the currently logged-in user:

SHOW GRANTS FOR CURRENT_USER();

How to Revoke and Drop MySQL Users

Revoking Specific Privileges

Remove write access while keeping read access:

REVOKE INSERT, UPDATE, DELETE ON mydatabase.* FROM 'appuser'@'localhost';

Then flush:

FLUSH PRIVILEGES;

Deleting a MySQL User Account

Remove a user entirely:

DROP USER 'appuser'@'localhost';

This removes the user account and all associated privileges. Verify the user is gone:

SELECT User, Host FROM mysql.user;

MySQL User Security Best Practices

• Never use the root account for application connections. Create dedicated users per application.
• Grant only the privileges the application actually needs. A CMS needs SELECT, INSERT, UPDATE, DELETE. It doesn’t need DROP or ALTER.
• Use strong, unique passwords. Store them in environment variables or a secrets manager, not in code.
• Restrict host access. Use localhost or a specific IP rather than % wherever possible.
• Audit your user list periodically: SELECT User, Host FROM mysql.user;

FAQ: MySQL Create User