How to Create a New User and Set Permissions in MySQL

MySQL remains one of the most widely deployed relational database management systems in the world, powering everything from small personal projects to enterprise-scale applications. Whether you're a developer, a systems administrator, or a business owner managing your own infrastructure, understanding how to create MySQL users and configure their permissions is a non-negotiable skill for maintaining a secure, well-organized database environment.

Poor user management is one of the leading causes of database breaches. Granting excessive privileges, reusing credentials, or leaving default accounts active can expose your entire data layer to unnecessary risk. This guide walks you through the complete process — from logging into MySQL to verifying permissions — with clear commands, practical examples, and security best practices at every step.

Why MySQL User Management Matters

Before diving into commands, it's worth understanding *why* this matters. MySQL operates on a principle of least privilege: every user should have access only to the specific databases, tables, and operations they genuinely need. This minimizes the blast radius of a compromised account and makes auditing far easier.

If you're running MySQL on a managed VPS Hosting plan or a Dedicated Server, you have full root-level access to your MySQL instance, giving you complete control over user creation and permission management. Many control panel environments — such as those available with VPS with cPanel — also provide a graphical interface for MySQL management, which can complement the command-line approach described here.

Prerequisites

Before you begin, make sure you have:

• MySQL 5.7+ or MySQL 8.0+ installed and running on your server
• Root access or access to a MySQL superuser account with GRANT OPTION privileges
• Terminal access (Linux/macOS) or Command Prompt / PowerShell (Windows)

Step 1: Log In to MySQL

To manage users and permissions, you must authenticate as a user with sufficient administrative privileges. In most cases, this is the root account.

Open Your Terminal or Command Prompt

• Linux / macOS: Open your terminal application
• Windows: Open Command Prompt or PowerShell

Connect to the MySQL Server

Run the following command:

mysql -u root -p

You will be prompted to enter the root password. Upon successful authentication, you'll see the MySQL prompt:

mysql>

> Security tip: If you're connecting to a remote MySQL server, specify the host with the -h flag:

> “`bash

> mysql -u root -p -h your_server_ip

> “`

Step 2: Create a New MySQL User

Once you're inside the MySQL shell, you can create a new user with the CREATE USER statement.

Syntax

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

Parameter breakdown:

Example: Create a User Accessible from Any Host

CREATE USER 'newuser'@'%' IDENTIFIED BY 'StrongP@ssw0rd!';

Example: Create a User Restricted to Localhost

CREATE USER 'localuser'@'localhost' IDENTIFIED BY 'AnotherStr0ng#Pass';

> Best practice: Wherever possible, restrict users to localhost or a specific IP address rather than using %. Wildcard host access increases your attack surface unnecessarily.

MySQL 8.0 Note on Authentication Plugins

MySQL 8.0 introduced caching_sha2_password as the default authentication plugin, which may cause compatibility issues with older clients or applications. If needed, you can explicitly set the authentication method:

CREATE USER 'newuser'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongP@ssw0rd!';

Step 3: Grant Permissions to the User

Creating a user does not automatically grant them access to any database. You must explicitly assign privileges using the GRANT statement.

Syntax

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

Parameter breakdown:

Common MySQL Privileges

Example: Grant Full Access to a Specific Database

GRANT ALL PRIVILEGES ON exampledb.* TO 'newuser'@'%';

Example: Grant Read-Only Access

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

Example: Grant Specific Privileges on a Single Table

GRANT SELECT, INSERT, UPDATE ON exampledb.orders TO 'app_user'@'localhost';

Example: Grant Global Privileges (Use Sparingly)

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

> Warning: Granting ALL PRIVILEGES ON *.* gives a user superuser-equivalent access. Reserve this only for trusted administrative accounts.

Step 4: Flush Privileges

After making changes to user permissions, it is good practice to reload the privilege tables so MySQL immediately recognizes your changes:

FLUSH PRIVILEGES;

> Note: In modern versions of MySQL (5.7.3+), FLUSH PRIVILEGES is automatically triggered after GRANT, REVOKE, and CREATE USER statements. However, running it explicitly after manual edits to the mysql system tables (via INSERT or UPDATE) remains essential.

Step 5: Verify User Permissions

Always confirm that the permissions you've set are correct before putting a user account into production.

Check Grants for a Specific User

SHOW GRANTS FOR 'newuser'@'%';

Sample output:

+--------------------------------------------------------------+
| Grants for newuser@%                                         |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `newuser`@`%`                          |
| GRANT ALL PRIVILEGES ON `exampledb`.* TO `newuser`@`%`       |
+--------------------------------------------------------------+

List All MySQL Users

SELECT user, host FROM mysql.user;

Check the Current User's Own Privileges

SHOW GRANTS;

Step 6: Revoking Permissions (When Needed)

Permissions change over time. When a user no longer needs certain access, revoke it promptly using the REVOKE statement:

Syntax

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

Example: Revoke All Privileges from a User

REVOKE ALL PRIVILEGES ON exampledb.* FROM 'newuser'@'%';
FLUSH PRIVILEGES;

Example: Revoke a Specific Privilege

REVOKE DELETE ON exampledb.orders FROM 'app_user'@'localhost';

Step 7: Dropping a User (When Necessary)

If a user account is no longer needed, remove it entirely to eliminate unnecessary access vectors:

DROP USER 'newuser'@'%';

This command removes the user account and all associated privileges in a single operation.

MySQL User Management on Hosted Environments

If you're managing MySQL on a shared or managed hosting environment, the process may differ slightly depending on your control panel. Platforms like cPanel, Plesk, and DirectAdmin provide graphical interfaces for creating MySQL users and assigning permissions without writing SQL directly.

For users who need full command-line control without the overhead of a dedicated server, VPS Hosting plans offer the ideal balance of performance, flexibility, and cost. If you're running database-intensive applications that require maximum throughput and isolated resources, consider upgrading to a Dedicated Server for guaranteed performance.

For smaller projects or development environments, Shared Web Hosting plans typically include phpMyAdmin access, which provides a browser-based interface for MySQL user and database management.

Additionally, if your application handles sensitive user data, pairing your database server with a valid SSL Certificate ensures that data transmitted between your application and database server remains encrypted and protected from interception.

Security Best Practices for MySQL User Management

Following the technical steps is only half the job. Here are critical security practices every administrator should implement:

1. Never use the root account for application connections. Create dedicated, least-privilege users for each application.
2. Use strong, unique passwords for every MySQL user. Consider a password manager or secrets vault.
3. Restrict host access to localhost or specific IP addresses wherever possible.
4. Audit user accounts regularly. Remove accounts that are no longer active or necessary.
5. Avoid wildcard grants (*.*) unless absolutely required for administrative tasks.
6. Enable MySQL's audit log plugin on production servers to track all user activity.
7. Rotate passwords periodically, especially after personnel changes or suspected compromises.
8. Use SSL/TLS for MySQL connections when connecting over a network, even within a private LAN.

Quick Reference: Essential MySQL User Management Commands

Conclusion

Proper MySQL user management is a foundational pillar of database security and operational hygiene. By creating dedicated user accounts, assigning only the permissions each account genuinely requires, and regularly auditing your user roster, you dramatically reduce the risk of unauthorized access, data leakage, and privilege escalation.

The commands covered in this guide — CREATE USER, GRANT, REVOKE, FLUSH PRIVILEGES, SHOW GRANTS, and DROP USER — form the core toolkit every database administrator needs. Whether you're managing a single development database or overseeing dozens of production instances, applying these practices consistently will keep your MySQL environment secure, maintainable, and compliant.

For a hosting environment that gives you the full control needed to implement these best practices, explore AlexHost's range of solutions — from Shared Web Hosting for straightforward projects to fully managed VPS Control Panels for teams that need power and flexibility without the complexity of bare-metal administration.