--- title: "How to Manage MySQL and MariaDB Users and Privileges" description: "Create MySQL and MariaDB users on Ubuntu 24.04, grant least-privilege access per database, restrict hosts, use roles and password policies, and audit accounts." category: "Database Management" language: en source: https://cubepath.com/docs/database-management/user-and-permission-management-in-mysql-mariadb updated: 2026-09-25 --- # 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`: ```bash 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: ```sql SELECT user, host, plugin FROM mysql.user ORDER BY user; ``` ```output +------------------+-----------+-----------------------+ | 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`: ```sql 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: ```sql 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: ```sql SHOW GRANTS FOR 'appuser'@'localhost'; ``` ```output +-----------------------------------------------------------------------------------------------------------------+ | 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: ```sql EXIT; ``` ```bash mysql -u appuser -p -e "SHOW DATABASES;" ``` ```output +--------------------+ | 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`: ```bash sudo mysql ``` ```sql 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: ```bash 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`): ```ini bind-address = 10.0.0.10 ``` Restart the service and open port 3306 only for the client's IP: ```bash 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: ```bash mysql -h 10.0.0.10 -u appuser -p -e "SELECT CURRENT_USER();" ``` ```output +-------------------+ | CURRENT_USER() | +-------------------+ | appuser@10.0.0.20 | +-------------------+ ``` `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: ```bash sudo mysql ``` ```sql 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`: ```sql 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: ```sql 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`: ```sql SHOW GRANTS FOR 'analyst'@'localhost'; ``` ```output +----------------------------------------------------------+ | 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql RENAME USER 'analyst'@'localhost' TO 'reporting'@'localhost'; ``` Delete an account that is no longer needed. `DROP USER` also removes all of its grants: ```sql DROP USER 'reporting'@'localhost'; ``` `DROP USER` does not close sessions that are already open. Find and end them with `SHOW PROCESSLIST;` and `KILL ;`. ## 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: ```sql SELECT user, host FROM mysql.user WHERE host = '%'; ``` List accounts with server-wide privileges beyond `USAGE`, which should be only administrators: ```sql 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: ```sql 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: | Task | MySQL 8.0 | MariaDB 10.11 | |---|---|---| | Create and grant roles | several roles per `CREATE ROLE` or `GRANT` | one role per statement: `CREATE ROLE app_read;`, `GRANT app_read TO 'user'@'host';` | | Show privileges from roles | `SHOW GRANTS FOR ... USING 'role';` | `SHOW GRANTS FOR app_read;` | | Activate a role at login | `SET DEFAULT ROLE ALL TO 'user'@'host';` | `SET DEFAULT ROLE app_read FOR 'user'@'host';` (one role per user) | | Password strength checks | `INSTALL COMPONENT 'file://component_validate_password';` | `INSTALL SONAME 'simple_password_check';` | | Lock after failed logins | `FAILED_LOGIN_ATTEMPTS` per account | server-wide `max_password_errors` variable | | Configuration file | `/etc/mysql/mysql.conf.d/mysqld.cnf` | `/etc/mysql/mariadb.conf.d/50-server.cnf` | | Service name | `mysql` | `mariadb` | `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.