How to Create Users and Grant Privileges in MySQL

Posted by Kyle Hankinson September 5, 2025

---

Managing user access is one of the first things you need to do when setting up a MySQL database. This guide covers creating users, granting privileges, and the key differences in MySQL 8.0+.

Creating a User

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'strong_password_here';

The @'localhost' part specifies which host the user can connect from:

Host	Meaning
'localhost'	Only local connections
'%'	Any host (remote connections)
'192.168.1.%'	Any host on that subnet
'db.example.com'	A specific hostname

To allow remote connections:

CREATE USER 'appuser'@'%' IDENTIFIED BY 'strong_password_here';

Granting Privileges

Specific Permissions on One Database

GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'%';

All Permissions on One Database

GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'%';

Read-Only Access

GRANT SELECT ON myapp.* TO 'readonly_user'@'%';

Specific Table Only

GRANT SELECT, INSERT ON myapp.orders TO 'appuser'@'%';

Apply Changes

After granting privileges, apply them:

FLUSH PRIVILEGES;

Viewing Existing Grants

SHOW GRANTS FOR 'appuser'@'%';

To see all users:

SELECT user, host FROM mysql.user ORDER BY user;

Changing a Password

MySQL 8.0+

ALTER USER 'appuser'@'%' IDENTIFIED BY 'new_password_here';

MySQL 5.7

SET PASSWORD FOR 'appuser'@'%' = PASSWORD('new_password_here');

Revoking Privileges

REVOKE INSERT, UPDATE, DELETE ON myapp.* FROM 'appuser'@'%';

Dropping a User

DROP USER 'appuser'@'%';

MySQL 8.0 Changes

MySQL 8.0 introduced some important changes to user management:

• No implicit user creation: In MySQL 5.7, GRANT would create a user if it did not exist. In 8.0, you must CREATE USER first.
• Default authentication plugin: MySQL 8.0 defaults to caching_sha2_password instead of mysql_native_password. Some older clients may need the legacy plugin:

CREATE USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

• Password expiration: MySQL 8.0 can enforce password expiration policies:

ALTER USER 'appuser'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

Best Practices

• Never use root for application connections. Create a dedicated user with only the permissions your app needs.
• Use specific privileges (SELECT, INSERT, UPDATE, DELETE) rather than ALL PRIVILEGES for application users.
• Restrict the host to the narrowest scope possible. If your app server is at 10.0.1.5, use 'appuser'@'10.0.1.5' instead of 'appuser'@'%'.
• Use strong passwords. MySQL 8.0's VALIDATE_PASSWORD component can enforce password complexity.

Using SQLPro Studio

You can manage users and run all of these queries in SQLPro Studio. Connect as a user with admin privileges and execute the CREATE USER and GRANT statements in the query editor. SQLPro Studio supports MySQL 5.5 through 8.x and MariaDB.

---