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
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(orsudo mariadb) from an SSH session. - The name of the database the user should reach. The examples use
shopdb.
Open the client as an admin:
sudo mysqlHow 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:
CREATE USER 'shop'@'localhost' IDENTIFIED BY 'StrongPassword123!ReplaceMe';To avoid an error when the user may already exist, use CREATE USER IF NOT EXISTS.
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:
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:
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.
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:
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
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:
SELECT user, host FROM mysql.user ORDER BY user;Change a password, revoke or drop
Change a password:
ALTER USER 'shop'@'localhost' IDENTIFIED BY 'NewStrongPassword!ReplaceMe';Take back one right:
REVOKE DROP ON shopdb.* FROM 'shop'@'localhost';Take back everything but keep the account:
REVOKE ALL PRIVILEGES ON shopdb.* FROM 'shop'@'localhost';Remove the account completely:
DROP USER 'shop'@'localhost';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:
mysql -u shop -p shopdbEnter the password when asked. Then confirm the limits:
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 atlocalhost. - `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 USERto change it, orCREATE 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 RunRacks AI for a quick answer, or open a ticket and our team will take a look at your server.
Related guides
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
Databases
How to create a database and user for a web app in MySQL or MariaDB
Set up a utf8mb4 database and a dedicated user with a strong password for WordPress, Laravel or any web app, then test the login before you configure the app.
- Beginner
- Linux VPS
- 4 min read