How to create a MySQL user and grant privileges

Create MySQL and MariaDB user accounts, understand the host part, grant only the rights each app needs, and review, revoke or drop access safely.

  • Beginner
  • Linux VPS
  • 5 min read

By RunRacks Team · Published

Every app that talks to MySQL should log in with its own account, limited to the data it needs. That way, a bug or a breach in one app cannot read or wipe everything else on the server. This guide covers creating users, granting rights, checking them and taking them away. The commands work on both MySQL and MariaDB.

Before you start

  • MySQL or MariaDB installed on your Linux VPS. See install MySQL 8.4 on Ubuntu and Debian if you have not done this yet.
  • Admin access to the database server. On most fresh installs, that means running sudo mysql (or sudo mariadb) from an SSH session.
  • The name of the database the user should reach. The examples use shopdb.

Open the client as an admin:

Terminal
sudo mysql

How MySQL accounts work

A MySQL account is a user name plus a host, written 'user'@'host'. The host says where that user may connect from:

  • 'shop'@'localhost': only from the server itself, through the local socket. The right choice when the app runs on the same VPS.
  • 'shop'@'127.0.0.1': only from the server itself, over TCP. Some apps and Docker setups connect this way.
  • 'shop'@'198.51.100.20': only from that one remote IP address.
  • 'shop'@'%': from any host. Avoid this unless a firewall already limits who can reach port 3306.

'shop'@'localhost' and 'shop'@'%' are two separate accounts, each with its own password and rights. That surprises many people when a login works from one place but not another.

Create a user

Choose your own long, random password. You can generate one in the shell with openssl rand -base64 24. The password below is an example only:

SQL
CREATE USER 'shop'@'localhost' IDENTIFIED BY 'StrongPassword123!ReplaceMe';

To avoid an error when the user may already exist, use CREATE USER IF NOT EXISTS.

Grant rights on one database

Give the user what a typical web app needs on its own database, and nothing else:

SQL
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP
  ON shopdb.* TO 'shop'@'localhost';

shopdb.* means every table in shopdb. The user has no rights on any other database.

Many apps also run their own schema upgrades, which is why CREATE, ALTER, INDEX and DROP appear above. If you run migrations yourself, you can leave those out and keep the app to data changes only.

For the simplest case, where the app owns its database fully, this is also acceptable:

SQL
GRANT ALL PRIVILEGES ON shopdb.* TO 'shop'@'localhost';

ALL PRIVILEGES on one database is still limited to that database. It does not make the user an admin.

Read-only and reporting users

For a reporting tool or a backup script, grant less:

SQL
CREATE USER 'report'@'localhost' IDENTIFIED BY 'AnotherStrongPassword!ReplaceMe';
GRANT SELECT ON shopdb.* TO 'report'@'localhost';

A backup account for mysqldump usually needs SELECT, SHOW VIEW, TRIGGER, LOCK TABLES and, if you dump events, EVENT.

You do not need FLUSH PRIVILEGES after CREATE USER or GRANT. It is only needed when someone edits the grant tables directly, which you should not do.

Check a user's rights

SQL
SHOW GRANTS FOR 'shop'@'localhost';

Expected output, in short:

Text
GRANT USAGE ON *.* TO `shop`@`localhost`
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `shopdb`.* TO `shop`@`localhost`

USAGE means "may log in, nothing more". To list every account and its host:

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

Change a password, revoke or drop

Change a password:

SQL
ALTER USER 'shop'@'localhost' IDENTIFIED BY 'NewStrongPassword!ReplaceMe';

Take back one right:

SQL
REVOKE DROP ON shopdb.* FROM 'shop'@'localhost';

Take back everything but keep the account:

SQL
REVOKE ALL PRIVILEGES ON shopdb.* FROM 'shop'@'localhost';

Remove the account completely:

SQL
DROP USER 'shop'@'localhost';

Check it works

Leave the admin session with exit, then log in as the new user:

Terminal
mysql -u shop -p shopdb

Enter the password when asked. Then confirm the limits:

SQL
SELECT CURRENT_USER();
SHOW DATABASES;

CURRENT_USER() should return shop@localhost, and SHOW DATABASES should list shopdb plus built-in schemas such as information_schema, and none of your other databases.

Troubleshooting

  • `ERROR 1045 Access denied for user 'shop'@'localhost'`: wrong password, or the account exists for a different host. Compare with SELECT user, host FROM mysql.user.
  • Works with `localhost` but not `127.0.0.1`: they are different accounts. Create 'shop'@'127.0.0.1' too, or point the app at localhost.
  • `ERROR 1044 Access denied ... to database`: the user has no grant on that database. Check the name in SHOW GRANTS; it is case-sensitive on Linux.
  • `ERROR 1396 Operation CREATE USER failed`: the account already exists. Use ALTER USER to change it, or CREATE USER IF NOT EXISTS.
  • `Your password does not satisfy the current policy`: the password validation component is on. Use a longer password with mixed character types.

Next steps

Was this guide helpful?

Still stuck?

Ask RunRacks AI for a quick answer, or open a ticket and our team will take a look at your server.

Databases

Reset a Forgotten MySQL Root Password on Linux

Recover access to MySQL or MariaDB on a Linux VPS by starting the server with skip-grant-tables, setting a new root password and restarting normally.

  • Intermediate
  • Linux VPS
  • 5 min read

Databases

MySQL Point-in-Time Recovery Using Binary Logs

Recover a MySQL or MariaDB database to an exact moment after a bad write or dropped table by restoring a full backup and replaying binary logs up to the incident.

  • Advanced
  • Linux VPS
  • 7 min read