How to Manage MySQL and MariaDB Users and Privileges

Every application, developer and backup job that connects to MySQL or MariaDB should have its own account with only the privileges it needs. That limits the damage a leaked password or a buggy query can cause and makes it clear who did what. In this tutorial you will create users for an application, a remote client and a read-only analyst on Ubuntu 24.04, grant and revoke privileges, group privileges into roles, enforce password rules and review existing accounts. The commands use MySQL 8.0 syntax; differences for MariaDB are listed in their own section.

Prerequisites

To follow this guide you need:

• A server running Ubuntu 24.04 LTS, for example a CubePath VPS.
• MySQL 8.0 (sudo apt install mysql-server) or MariaDB 10.11 (sudo apt install mariadb-server) from the Ubuntu repositories.
• A non-root user with sudo privileges.

Step 1 - Connecting as the administrative user

On Ubuntu, the database root account authenticates with the Unix socket (auth_socket in MySQL, unix_socket in MariaDB): the system root user can log in without a password, and nobody can log in as database root over the network. Keep it that way and open the shell with sudo:

sudo mysql

Every MySQL account is identified by a user name and a host, written 'user'@'host'. 'app'@'localhost' and 'app'@'10.0.0.20' are two different accounts, with separate passwords and privileges. List the existing accounts:

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

+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+

The mysql.* accounts are internal and locked, and debian-sys-maint is used by the Ubuntu package scripts. Do not modify or delete them.

Step 2 - Creating an application user

Create a database and an account that the application on the same server will use. Replace your_strong_password with a long, random password, for example the output of openssl rand -base64 24:

CREATE DATABASE appdb;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'your_strong_password';

The user exists but cannot do anything yet. Grant it the privileges an application normally needs on its own database and nothing outside it:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP, REFERENCES ON appdb.* TO 'appuser'@'localhost';

• appdb.* limits every privilege to tables in appdb. Privileges on *.* apply to the whole server and should be reserved for administrators.
• CREATE, ALTER, INDEX, DROP and REFERENCES let the application's migrations manage its own schema. If you run migrations with a separate account, give the runtime account only SELECT, INSERT, UPDATE, DELETE.
• GRANT takes effect immediately. FLUSH PRIVILEGES is only needed if someone edits the grant tables directly with INSERT or UPDATE, which you should not do.

Check what the account can do:

SHOW GRANTS FOR 'appuser'@'localhost';

+-----------------------------------------------------------------------------------------------------------------+
| Grants for appuser@localhost                                                                                    |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `appuser`@`localhost`                                                                     |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `appdb`.* TO `appuser`@`localhost` |
+-----------------------------------------------------------------------------------------------------------------+

USAGE means "no privileges": it only indicates that the account exists. Exit the shell and log in as the new user to confirm it works and is confined to its database:

EXIT;

mysql -u appuser -p -e "SHOW DATABASES;"

+--------------------+
| Database           |
+--------------------+
| appdb              |
| information_schema |
| performance_schema |
+--------------------+

The mysql system database is not listed because appuser has no privileges on it.

Step 3 - Allowing access from another server

If the application runs on a different machine, create an account bound to that machine's IP address instead of using the wildcard host '%', which accepts connections from anywhere. This example allows 10.0.0.20:

sudo mysql

CREATE USER 'appuser'@'10.0.0.20' IDENTIFIED BY 'your_strong_password' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'10.0.0.20';
EXIT;

REQUIRE SSL rejects unencrypted connections for this account. MySQL 8.0 on Ubuntu creates self-signed TLS certificates at first start, so clients connect encrypted by default. To allow a whole private subnet, use a pattern such as 'appuser'@'10.0.0.%'.

MySQL on Ubuntu only listens on 127.0.0.1 by default. Change bind-address in the server configuration:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Set it to the server's private IP address (on MariaDB, the file is /etc/mysql/mariadb.conf.d/50-server.cnf):

bind-address = 10.0.0.10

Restart the service and open port 3306 only for the client's IP:

sudo systemctl restart mysql
sudo ufw allow from 10.0.0.20 to any port 3306 proto tcp

From the client machine (with mysql-client installed), test the connection:

mysql -h 10.0.0.10 -u appuser -p -e "SELECT CURRENT_USER();"

+-------------------+
| CURRENT_USER()    |
+-------------------+
| [email protected] |
+-------------------+

CURRENT_USER() shows which account the server actually matched, which is the first thing to check when privileges look wrong.

Step 4 - Granting read-only access and revoking privileges

Analysts, reporting tools and dashboards usually only need to read data. Create a read-only account:

sudo mysql

CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'your_strong_password';
GRANT SELECT ON appdb.* TO 'analyst'@'localhost';

You can also limit access to specific tables or even columns. For example, this lets a support tool read only two columns of customers:

GRANT SELECT (id, email) ON appdb.customers TO 'analyst'@'localhost';

To take a privilege away, use REVOKE with the same scope it was granted with:

REVOKE SELECT (id, email) ON appdb.customers FROM 'analyst'@'localhost';

Revoking at a different level has no effect. For example, REVOKE SELECT ON appdb.customers does not remove a SELECT granted on appdb.*. Always check the result with SHOW GRANTS:

SHOW GRANTS FOR 'analyst'@'localhost';

+----------------------------------------------------------+
| Grants for analyst@localhost                             |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `analyst`@`localhost`              |
| GRANT SELECT ON `appdb`.* TO `analyst`@`localhost`       |
+----------------------------------------------------------+

Step 5 - Grouping privileges with roles

When several people need the same access, managing grants one user at a time gets error-prone. Roles, available in MySQL 8.0 and MariaDB, are named sets of privileges that you grant to users. Create a read role and a write role:

CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON appdb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON appdb.* TO 'app_write';

Create a developer account, give it both roles and make them active automatically at login:

CREATE USER 'dev_anna'@'localhost' IDENTIFIED BY 'your_strong_password';
GRANT 'app_read', 'app_write' TO 'dev_anna'@'localhost';
SET DEFAULT ROLE ALL TO 'dev_anna'@'localhost';

Without SET DEFAULT ROLE, MySQL grants the roles but does not activate them, and the user gets "access denied" until they run SET ROLE ALL; in each session. To see the privileges a user gets through a role:

SHOW GRANTS FOR 'dev_anna'@'localhost' USING 'app_read', 'app_write';

Changing a role now changes it for everyone who has it. For example, to remove write access from all developers at once:

REVOKE UPDATE, DELETE ON appdb.* FROM 'app_write';

Step 6 - Enforcing password and connection policies

MySQL can reject weak passwords, expire passwords, lock accounts after failed logins and cap connections per account.

Check whether the password validation component is installed:

SHOW VARIABLES LIKE 'validate_password%';

An empty result means it is not. Install it (this is what mysql_secure_installation does when you answer yes to the password question) and require longer passwords:

INSTALL COMPONENT 'file://component_validate_password';
SET PERSIST validate_password.policy = 'MEDIUM';
SET PERSIST validate_password.length = 14;

The MEDIUM policy requires mixed case, digits and special characters. SET PERSIST keeps the values across restarts. From now on, CREATE USER and ALTER USER with a weak password fail with ERROR 1819 (HY000): Your password does not satisfy the current policy requirements.

Limit an account to 20 simultaneous connections, expire its password every 180 days and lock it for one day after five consecutive failed logins. The WITH resource limits must come before the password options:

ALTER USER 'dev_anna'@'localhost'
  WITH MAX_USER_CONNECTIONS 20
  PASSWORD EXPIRE INTERVAL 180 DAY
  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

Password expiry makes sense for people, but not for application accounts: an expired application password takes the application down. Leave application users at the default, which follows the server-wide default_password_lifetime (0, meaning never, on MySQL 8.0).

Step 7 - Changing, locking and removing accounts

Rotate a password:

ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'your_new_strong_password';

Temporarily disable an account without deleting it, for example when someone goes on leave, and re-enable it later:

ALTER USER 'dev_anna'@'localhost' ACCOUNT LOCK;
ALTER USER 'dev_anna'@'localhost' ACCOUNT UNLOCK;

Rename an account or change the host it may connect from:

RENAME USER 'analyst'@'localhost' TO 'reporting'@'localhost';

Delete an account that is no longer needed. DROP USER also removes all of its grants:

DROP USER 'reporting'@'localhost';

DROP USER does not close sessions that are already open. Find and end them with SHOW PROCESSLIST; and KILL <id>;.

Step 8 - Auditing existing accounts

Review accounts periodically, especially on servers that have been running for a while. List accounts that can connect from any host:

SELECT user, host FROM mysql.user WHERE host = '%';

List accounts with server-wide privileges beyond USAGE, which should be only administrators:

SELECT grantee, privilege_type FROM information_schema.user_privileges WHERE privilege_type <> 'USAGE' ORDER BY grantee;

List accounts that are locked or have an expired password:

SELECT user, host, account_locked, password_expired FROM mysql.user WHERE account_locked = 'Y' OR password_expired = 'Y';

For any account that looks unfamiliar, run SHOW GRANTS FOR and either reduce its privileges or drop it.

Differences in MariaDB

The commands in Steps 1 to 4 and 7 work the same on MariaDB 10.11. Roles, password policies and a few other details differ:

PASSWORD EXPIRE INTERVAL, ACCOUNT LOCK, REQUIRE SSL and WITH MAX_USER_CONNECTIONS are supported by both. MariaDB does not create TLS certificates automatically, so configure ssl_cert and ssl_key before using REQUIRE SSL. The locked-accounts query in Step 8 is written for MySQL's mysql.user table; on MariaDB, SHOW CREATE USER 'user'@'host'; shows whether an account is locked.

Troubleshooting

• ERROR 1045 (28000): Access denied for user 'appuser'@'localhost' (using password: YES): wrong password, or no account matches the user and host you connect from. Check SELECT user, host FROM mysql.user WHERE user = 'appuser';. Remember that 'appuser'@'%' and 'appuser'@'localhost' are separate accounts.
• ERROR 1130 (HY000): Host '10.0.0.20' is not allowed to connect to this MySQL server: there is no account for that user and client IP. Create one for the exact IP or subnet as in Step 3.
• ERROR 1142 (42000): INSERT command denied to user: the account connects but lacks the privilege on that table. Run SHOW GRANTS FOR CURRENT_USER(); in the user's session to see what it actually has.
• Privileges from a role do not apply: the role is granted but not active. Run SELECT CURRENT_ROLE(); in the user's session; if it returns NONE, set a default role as shown in Step 5.
• ERROR 1040 (HY000): Too many connections: the server-wide max_connections limit was reached. Find the account holding the connections with SHOW PROCESSLIST; and cap it with MAX_USER_CONNECTIONS.

Conclusion

You created accounts scoped to a single database and a specific host, gave read-only and role-based access, enforced password and connection policies and learned how to rotate, lock, remove and audit accounts. Next, move remote connections to a private network and enforce TLS for all of them, give backup jobs their own minimal account, and review the account list every few months with the queries from Step 8.