Tutorial

MySQL User Management: Create Users, Grant Privileges, Secure

May 22, 2026

Tutorial May 22, 2026

Managing servers the hard way? Panelica gives you isolated hosting, built-in Docker and AI-assisted management.

Why MySQL User Management Matters

Every MySQL installation begins with a root user that has unlimited power over every database, table, and row. Running your application with the root account is like giving every employee the master key to every room in the building. A single SQL injection vulnerability, a leaked configuration file, or a compromised application server can expose your entire database infrastructure.

Proper MySQL user management is the foundation of database security. By creating dedicated users with specific privileges, you limit the blast radius of any security incident. An attacker who compromises your WordPress database user should never be able to drop your billing database or read your customer credentials table.

This guide covers everything you need to manage MySQL users effectively: creating users with proper authentication, granting and revoking privileges at every level, enforcing password policies, restricting host access, and auditing who has access to what. Whether you are managing a single-application server or a multi-tenant hosting environment, these practices will keep your databases secure.

Prerequisites: You need MySQL 8.0+ installed and root (or SUPER privilege) access to the MySQL server. All commands are executed in the MySQL CLI or through a management interface.

Creating MySQL Users

Basic CREATE USER Syntax

The CREATE USER statement creates a new MySQL account. At minimum, you specify a username and the host from which the user can connect:

mysql> CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'Str0ng_P@ssw0rd!2026';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE USER 'readonly'@'192.168.1.%' IDENTIFIED BY 'R3ad0nly_Acc3ss!';
Query OK, 0 rows affected (0.01 sec)

The user identity in MySQL is always a combination of 'username'@'host'. The same username with different hosts creates completely separate accounts with independent passwords and privileges. 'appuser'@'localhost' and 'appuser'@'192.168.1.50' are two different users.

Host Restriction Patterns

The host specification controls where connections are accepted from. This is your first line of defense:

Host Pattern	Meaning	Security Level
`'user'@'localhost'`	Local socket connections only	Most Secure
`'user'@'127.0.0.1'`	Local TCP connections only	Secure
`'user'@'192.168.1.50'`	Single specific IP	Secure
`'user'@'192.168.1.%'`	Entire subnet	Moderate
`'user'@'%.example.com'`	Any host in domain	Moderate
`'user'@'%'`	Any host anywhere	Dangerous

Warning: Never use '%' as the host for production database users. If your application runs on the same server as MySQL, always use 'localhost'. For remote connections, specify the exact IP address or the narrowest possible subnet.

Authentication Plugins

MySQL 8.0 introduced caching_sha2_password as the default authentication plugin, replacing the older mysql_native_password. Understanding the difference matters because some client libraries and legacy applications do not support the newer plugin.

caching_sha2_password

Default in MySQL 8.0+

Uses SHA-256 hashing with server-side caching. More secure but requires SSL/TLS on first connection or RSA key exchange. Some older PHP versions and MySQL clients may not support it.

mysql_native_password

Legacy Compatible

Uses SHA-1 based challenge-response. Universally supported by all client libraries. Less secure than SHA-256 but still adequate when combined with SSL and strong passwords.

To specify the authentication plugin when creating a user:

-- Modern (default in MySQL 8.0+)
CREATE USER 'modern_app'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'SecureP@ss123!';

-- Legacy compatibility
CREATE USER 'legacy_app'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'LegacyP@ss123!';

Understanding Privilege Levels

MySQL privileges operate at multiple levels, from global (entire server) down to individual columns. Understanding this hierarchy is essential for implementing the principle of least privilege.

Global
*.*

→

Database
db.*

→

Table
db.table

→

Column
db.table(col)

→

Routine
PROCEDURE/FUNCTION

Common Privileges Reference

Privilege	Level	Description
`ALL PRIVILEGES`	Global/DB/Table	All privileges at the specified level (except GRANT OPTION)
`SELECT`	Global/DB/Table/Column	Read data from tables
`INSERT`	Global/DB/Table/Column	Add new rows to tables
`UPDATE`	Global/DB/Table/Column	Modify existing rows
`DELETE`	Global/DB/Table	Remove rows from tables
`CREATE`	Global/DB/Table	Create databases, tables, indexes
`DROP`	Global/DB/Table	Drop databases, tables, views
`INDEX`	Global/DB/Table	Create and drop indexes
`ALTER`	Global/DB/Table	Alter table structure
`REFERENCES`	Global/DB/Table/Column	Create foreign key constraints
`EXECUTE`	Global/DB/Routine	Execute stored procedures and functions
`PROCESS`	Global	View active threads (SHOW PROCESSLIST)
`RELOAD`	Global	FLUSH operations
`SUPER`	Global	Administrative operations (deprecated in 8.0)

Granting Privileges

Database-Level Grants

The most common use case is granting a user full access to a specific database. This is the recommended approach for application database users:

-- Full access to a specific database
GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wp_user'@'localhost';

-- Read-only access to a database
GRANT SELECT ON analytics_db.* TO 'report_user'@'192.168.1.%';

-- Read + Write (no structural changes)
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce_db.* TO 'shop_app'@'localhost';

-- Apply the privilege changes
FLUSH PRIVILEGES;

Note on FLUSH PRIVILEGES: When you use GRANT and REVOKE statements, MySQL automatically reloads the privilege tables. FLUSH PRIVILEGES is only strictly necessary when you modify the grant tables directly (INSERT/UPDATE on mysql.user), but it is a good habit for safety.

Table-Level and Column-Level Grants

For tighter security, you can restrict access to specific tables or even specific columns within a table:

-- Table-level: read only from orders table
GRANT SELECT ON ecommerce_db.orders TO 'auditor'@'localhost';

-- Column-level: update only specific columns
GRANT SELECT, UPDATE (status, tracking_number)
ON ecommerce_db.orders TO 'shipping_app'@'localhost';

-- Column-level: read only non-sensitive columns
GRANT SELECT (id, name, email, created_at)
ON app_db.users TO 'marketing_team'@'192.168.1.%';

The GRANT OPTION Privilege

The WITH GRANT OPTION clause allows a user to grant their own privileges to other users. Use this sparingly and never for application accounts:

-- DBA can manage users for their database
GRANT ALL PRIVILEGES ON project_db.*
TO 'dba_user'@'localhost' WITH GRANT OPTION;

Security Warning: A user with GRANT OPTION can create new users with the same privileges they hold. If you grant ALL PRIVILEGES with GRANT OPTION on a database, that user can create additional accounts with full access. Reserve this for trusted administrators only.

Revoking Privileges

Removing privileges is just as important as granting them. When an application no longer needs write access, when an employee leaves, or when you discover overly broad permissions, use REVOKE:

-- Remove specific privileges
REVOKE INSERT, UPDATE, DELETE ON analytics_db.* FROM 'report_user'@'192.168.1.%';

-- Remove all privileges on a database
REVOKE ALL PRIVILEGES ON old_project_db.* FROM 'dev_user'@'localhost';

-- Remove GRANT OPTION specifically
REVOKE GRANT OPTION ON project_db.* FROM 'dba_user'@'localhost';

Auditing User Access with SHOW GRANTS

Regular auditing is critical. Use SHOW GRANTS to see exactly what privileges each user holds:

-- Check current user's privileges
SHOW GRANTS;

-- Check a specific user's privileges
SHOW GRANTS FOR 'wp_user'@'localhost';
+-----------------------------------------------------------+
| Grants for wp_user@localhost |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `wp_user`@`localhost` |
| GRANT ALL PRIVILEGES ON `wordpress_db`.* TO `wp_user`... |
+-----------------------------------------------------------+

Querying the mysql.user Table

For a broader overview of all users and their global privileges, query the system table directly:

-- List all users and their hosts
SELECT User, Host, plugin, account_locked, password_expired
FROM mysql.user ORDER BY User;

-- Find users with global privileges (dangerous)
SELECT User, Host, Super_priv, Grant_priv, File_priv
FROM mysql.user WHERE Super_priv = 'Y' OR Grant_priv = 'Y';

-- Find users who can connect from anywhere
SELECT User, Host FROM mysql.user WHERE Host = '%';

Password Policies with validate_password

MySQL includes the validate_password component (MySQL 8.0+) that enforces password strength requirements. This prevents users from setting weak passwords:

Install the component

INSTALL COMPONENT 'file://component_validate_password';
SHOW VARIABLES LIKE 'validate_password%';

Configure the policy level

Policy	Length	Requirements
LOW	8+	Length only
MEDIUM (default)	8+	Numeric + Mixed case + Special character
STRONG	8+	MEDIUM requirements + Dictionary check

Set your preferred policy

-- Set to MEDIUM (recommended)
SET GLOBAL validate_password.policy = MEDIUM;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

Password Expiration and History

MySQL 8.0 supports password expiration to force periodic password changes, and password history to prevent reuse:

-- Expire password every 90 days
ALTER USER 'admin_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Prevent reuse of last 5 passwords
ALTER USER 'admin_user'@'localhost' PASSWORD HISTORY 5;

-- Require password change on next login
ALTER USER 'temp_user'@'localhost' PASSWORD EXPIRE;

-- Set global defaults
SET GLOBAL default_password_lifetime = 180;
SET GLOBAL password_history = 3;

Account Locking and Resource Limits

Beyond password policies, MySQL provides account locking and resource limits to further control user access:

-- Lock an account (prevent all logins)
ALTER USER 'former_employee'@'localhost' ACCOUNT LOCK;

-- Unlock when needed
ALTER USER 'former_employee'@'localhost' ACCOUNT UNLOCK;

-- Set resource limits per hour
ALTER USER 'api_user'@'localhost'
WITH MAX_QUERIES_PER_HOUR 10000
MAX_UPDATES_PER_HOUR 5000
MAX_CONNECTIONS_PER_HOUR 500
MAX_USER_CONNECTIONS 20;

Failed Login Tracking (MySQL 8.0.19+)

MySQL 8.0.19 introduced automatic account locking after failed login attempts, similar to fail2ban but at the database level:

-- Lock account after 3 failed attempts, unlock after 1 day
ALTER USER 'secure_user'@'localhost'
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1;

-- Lock indefinitely until admin unlocks
ALTER USER 'critical_user'@'localhost'
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME UNBOUNDED;

Dropping Users Safely

When an account is no longer needed, remove it completely. Simply revoking all privileges leaves an empty account that could be re-granted privileges in the future:

-- Safely drop a user (no error if missing)
DROP USER IF EXISTS 'old_app'@'localhost';

-- Check for active connections first
SELECT * FROM information_schema.PROCESSLIST WHERE USER = 'old_app';

-- Kill active connections if needed
-- Then drop
DROP USER 'old_app'@'localhost';

Before dropping: Always check for active connections and scheduled events owned by the user. Dropping a user while their queries are running will terminate those queries. Check information_schema.PROCESSLIST and information_schema.EVENTS first.

Privilege Best Practices

Follow these principles to maintain a secure MySQL installation:

Principle of Least Privilege

Grant only the minimum privileges required
Use database-level grants, not global
Prefer SELECT,INSERT,UPDATE,DELETE over ALL
Never grant SUPER or FILE to application users

Separation of Duties

Separate read and write accounts
Different accounts for different applications
Admin accounts separate from app accounts
Backup user needs only SELECT and LOCK TABLES

Host Restrictions

Use localhost for local applications
Specify exact IPs for remote access
Never use % in production
Use SSL for remote connections

Regular Auditing

Review SHOW GRANTS quarterly
Remove unused accounts promptly
Check for wildcard host accounts
Verify password expiration settings

Real-World User Templates

Here are ready-to-use templates for common scenarios:

Web Application User

-- Standard web app (CRUD operations + stored procedures)
CREATE USER 'myapp'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'V3ryStr0ng_P@ss!'
PASSWORD EXPIRE INTERVAL 365 DAY
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1;

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON myapp_production.* TO 'myapp'@'localhost';

Backup User

-- Backup user (read + lock for consistent dumps)
CREATE USER 'backup'@'localhost'
IDENTIFIED BY 'B@ckup_S3cure_2026!';

GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD,
REPLICATION CLIENT, EVENT, TRIGGER
ON *.* TO 'backup'@'localhost';

Read-Only Reporting User

-- Reporting user with limited connections
CREATE USER 'reporter'@'192.168.1.%'
IDENTIFIED BY 'R3port_@ccess!'
WITH MAX_USER_CONNECTIONS 5
MAX_QUERIES_PER_HOUR 50000;

GRANT SELECT ON analytics_db.* TO 'reporter'@'192.168.1.%';
GRANT SELECT ON sales_db.orders TO 'reporter'@'192.168.1.%';
GRANT SELECT ON sales_db.products TO 'reporter'@'192.168.1.%';

Security Audit Checklist

Run through this checklist periodically to ensure your MySQL user configuration is secure:

Remove anonymous accounts: SELECT User, Host FROM mysql.user WHERE User = ''; — if any results, drop them immediately. Anonymous accounts are a major security risk.

Check for wildcard hosts: SELECT User, Host FROM mysql.user WHERE Host = '%'; — ensure no production application user uses the % wildcard host.

Verify root remote access is disabled: SELECT Host FROM mysql.user WHERE User = 'root' AND Host != 'localhost'; — root should only be accessible from localhost.

Find users with global privileges: SELECT User, Host FROM mysql.user WHERE Super_priv='Y' OR Grant_priv='Y' OR File_priv='Y'; — minimize these accounts.

Check for expired passwords: SELECT User, Host, password_expired FROM mysql.user WHERE password_expired = 'Y'; — follow up on accounts with expired passwords.

Verify validate_password is active: SHOW VARIABLES LIKE 'validate_password%'; — ensure the password validation component is installed and configured.

Renaming Users and Changing Passwords

MySQL allows you to rename users and change their passwords without dropping and recreating them:

-- Rename a user
RENAME USER 'old_name'@'localhost' TO 'new_name'@'localhost';

-- Change password
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'N3w_Str0ng_P@ss!';

-- Change your own password
SET PASSWORD = 'My_N3w_P@ssw0rd!';

MySQL User Management with Panelica

While understanding CLI user management is essential knowledge, manually creating users and managing privileges for dozens of databases and applications becomes tedious and error-prone. This is where a server management panel streamlines the process.

Panelica provides MySQL user management through the panel GUI — create database users, assign per-database privileges, and enforce secure passwords without touching the command line. The panel automatically applies the principle of least privilege: each user gets access only to their assigned databases, with host restrictions set to localhost by default. Password strength requirements are enforced through the interface, and all user operations are logged in the audit trail for compliance.

5 sec

Create user + grant privileges via panel

100%

Per-user database isolation enforced

Summary

MySQL user management is not a set-and-forget task. It requires ongoing attention: creating users with the narrowest possible privileges, enforcing strong passwords, restricting connections by host, and regularly auditing who has access to what. The principle of least privilege should guide every GRANT statement you write. Every application should have its own dedicated user with access only to its own database. Global privileges should be reserved for administrative accounts that are protected with strong passwords and locked down to localhost access.

Remember these critical rules: never run applications as root, never use the % wildcard host in production, always enable the validate_password component, and audit your user accounts at least quarterly. A compromised database user with minimal privileges is an inconvenience. A compromised root account with wildcard access is a catastrophe.

Security-first hosting panel

Run your servers on a modern panel.

Panelica is a modern, security-first hosting panel — isolated services, built-in Docker and AI-assisted management, with one-click migration from any panel.

Start Free — No Card Required See how Panelica compares

Zero-downtime migration Fully isolated services Cancel anytime