Create a MySQL User and Grant Privileges

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:

Bash

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.

Tip: Keep app passwords in the app's config file or a secrets manager, not in shell history. If you type SQL with a password into the shell with mysql -e, clear it afterwards with history -d.

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.

Warning: Never use GRANT ALL ON *.* or WITH GRANT OPTION for an app account. That gives it control over every database and lets it create other users.

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:

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';

Warning: Dropping a user that a live app still uses takes that app offline at once. Check the app's config first. DROP USER does not delete the database or its data.

Check it works

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

Bash

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

Set up a new app from scratch: Create a database and user for a web app.
Browse the server from a desktop tool: Connect with MySQL Workbench or DBeaver.

Was this guide helpful?

Still stuck?

Ask MdmHost AI for a quick answer, or open a ticket and our team will take a look. We are here 24/7.

Ask MdmHost AI Open a ticket