MySQL/MariaDB on AlmaLinux series, part 2. Part 1, Install MariaDB and MySQL on AlmaLinux 10 and Secure It, left the server listening on 127.0.0.1 only with anonymous users and the test database removed. This part builds on that server.
A fresh MariaDB or MySQL server has exactly one useful account: root, reachable only from the server itself. Before an application can use the database you need an admin account for people, narrow accounts for each application, and a deliberate path from the client network to port 3306. This guide covers how MySQL-family accounts are identified (the user@host pair trips up almost everyone once), creating and changing users, GRANT and REVOKE at every privilege level, roles, and then opening the server to an application subnet with bind-address, firewalld and SELinux, with verification and troubleshooting for each step.
Applies to: AlmaLinux 10.x with MariaDB 10.11 or 11.8 from AppStream; the SQL works the same on any MariaDB 10.5 or later. Where MySQL 8.4 differs (mainly roles and host masks), a note says so. The examples use the server db1.example.lan (192.168.1.40) and an application server app1 (192.168.1.50) on 192.168.1.0/24.
Prerequisites
- A MariaDB or MySQL server installed and secured as in part 1, including
skip-name-resolve = ONin/etc/my.cnf.d/zz-local.cnf. - Root or sudo on the database server, so you can reach the database root account with
sudo mariadb(MySQL:sudo mysql -u root -p). - A second machine on the client subnet for the remote test, with just the client installed:
sudo dnf install -y mariadb(ormysql8.4). - A password manager or secrets store for the passwords you generate.
openssl rand -base64 24makes a good one.
Step 1: Understand how accounts are identified
In the MySQL family an account is not just a user name: it is a user name plus a client host, written 'appuser'@'192.168.1.50'. 'appuser'@'localhost' and 'appuser'@'%' are two completely separate accounts with separate passwords and separate grants. When a client connects, the server picks the matching account with the most specific host, then checks that account’s password.
| Host part | Matches | Typical use |
|---|---|---|
'localhost' | Connections over the Unix socket (/var/lib/mysql/mysql.sock) | Admins and apps on the same server |
'127.0.0.1', '::1' | TCP to the loopback address | Apps that insist on TCP, with skip-name-resolve on |
'192.168.1.50' | One client IP | A single application server |
'192.168.1.%' | Any address starting with 192.168.1. | An application tier on one /24 |
'192.168.1.0/255.255.255.0' | A subnet given as base address and netmask | Subnets that are not on an octet boundary |
'%' | Any host | Avoid; let the firewall do this job only if you must |
Because skip-name-resolve is on (part 1), host names such as app1.example.lan in an account never match: the server only sees IP addresses. MySQL 8.0.23 and later also accept CIDR notation like '192.168.1.0/24'; the netmask form works on both MariaDB and MySQL, so it is the portable choice. List the accounts that exist right now:
sudo mariadb -e "SELECT user, host, plugin, account_locked FROM mysql.user ORDER BY user, host;"On a MariaDB install secured as in part 1 you should see root@localhost, mysql@localhost (the OS mysql user, socket-only) and mariadb.sys@localhost (a locked system account that owns the mysql.user view; leave it alone), plus the appuser test account if you kept it. On MySQL 8.4 the system accounts are mysql.sys, mysql.session and mysql.infoschema, all locked.
Step 2: Create an admin account for people
Keep database root on socket authentication for emergencies and give each DBA (or one shared admin, if you must) a named account. Named accounts show up in the process list and the audit log, and you can lock them individually when someone leaves.
sudo mariadb <<'SQL'
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'Paste-A-Long-Random-Password';
GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' WITH GRANT OPTION;
SQL
mariadb -u alice -p -e "SELECT CURRENT_USER(); SHOW GRANTS;"ALL PRIVILEGES ON *.* gives every privilege except the ability to grant, which WITH GRANT OPTION adds. An admin bound to localhost must first SSH to the server, which is what you want; if a GUI tool needs TCP, tunnel it (ssh -L 3306:127.0.0.1:3306 db1) and create the account as 'alice'@'127.0.0.1' rather than opening admin accounts to the network.
You do not need FLUSH PRIVILEGES after CREATE USER, GRANT, REVOKE or ALTER USER: these statements update the in-memory privilege tables themselves. FLUSH PRIVILEGES is only needed if someone edited the grant tables directly with INSERT/UPDATE/DELETE.
Step 3: Create an application account with least privilege
An application normally needs to read and write rows in its own schema and nothing else. Create the database, then an account for each client host that will connect:
sudo mariadb <<'SQL'
CREATE DATABASE IF NOT EXISTS shopdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- runtime account used by the application servers on 192.168.1.0/24
CREATE USER 'shop_app'@'192.168.1.%'
IDENTIFIED BY 'Paste-A-Long-Random-Password'
WITH MAX_USER_CONNECTIONS 50;
GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO 'shop_app'@'192.168.1.%';
-- migration account, used only by deploy jobs, allowed to change the schema
CREATE USER 'shop_migrate'@'192.168.1.50' IDENTIFIED BY 'Another-Long-Random-Password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, REFERENCES,
CREATE VIEW, SHOW VIEW, TRIGGER
ON shopdb.* TO 'shop_migrate'@'192.168.1.50';
SQLSplitting the runtime account from the migration account means a SQL injection bug in the application cannot DROP TABLE. MAX_USER_CONNECTIONS caps how many sessions one account can hold, so a connection leak in one app cannot exhaust max_connections for everyone.
A detail that catches people: in a database name inside GRANT, _ and % are wildcards. GRANT ... ON shop_db.* also matches a database called shopXdb. Escape the underscore (`shop\_db`.*) when the name contains one and you want an exact match.
Step 4: Know the privilege levels
| Level | Syntax | Example |
|---|---|---|
| Global | ON *.* | GRANT PROCESS ON *.* TO 'monitor'@'localhost'; |
| Database | ON db.* | GRANT SELECT ON shopdb.* TO 'report'@'192.168.1.60'; |
| Table | ON db.table | GRANT SELECT, INSERT ON shopdb.orders TO ...; |
| Column | priv (col1, col2) ON db.table | GRANT SELECT (id, name, city) ON shopdb.customers TO ...; |
| Routine | ON PROCEDURE db.proc | GRANT EXECUTE ON PROCEDURE shopdb.close_month TO ...; |
Column grants are a tidy way to give a reporting account access to a table without exposing sensitive columns. A reporting user who can read customers but not their email or phone number:
sudo mariadb <<'SQL'
CREATE USER 'report'@'192.168.1.60' IDENTIFIED BY 'Paste-A-Long-Random-Password';
GRANT SELECT ON shopdb.orders TO 'report'@'192.168.1.60';
GRANT SELECT (id, name, city, created_at) ON shopdb.customers TO 'report'@'192.168.1.60';
SQLWith that grant, SELECT * FROM shopdb.customers fails with ERROR 1143 (42000): SELECT command denied ... for column 'email', while selecting the granted columns works. Global privileges worth knowing by name: PROCESS (see all sessions), RELOAD (FLUSH), SHUTDOWN, FILE (read and write server files; never give it to applications), SUPER and, on MariaDB 10.5+, the finer split such as BINLOG MONITOR, CONNECTION ADMIN and REPLICATION SLAVE ADMIN.
Step 5: Inspect and revoke privileges
sudo mariadb <<'SQL'
SHOW GRANTS FOR 'shop_app'@'192.168.1.%';
SHOW GRANTS FOR 'report'@'192.168.1.60';
-- who has what on a given schema
SELECT grantee, privilege_type FROM information_schema.SCHEMA_PRIVILEGES
WHERE table_schema = 'shopdb' ORDER BY grantee;
-- who has global privileges
SELECT grantee, privilege_type FROM information_schema.USER_PRIVILEGES
WHERE grantee NOT LIKE "'mariadb.sys'%" ORDER BY grantee;
SQLREVOKE mirrors GRANT and must name the same level: revoking at the database level does nothing to a table-level grant, and vice versa. SHOW GRANTS tells you which level each privilege was granted at.
sudo mariadb <<'SQL'
-- take DELETE away from the runtime account
REVOKE DELETE ON shopdb.* FROM 'shop_app'@'192.168.1.%';
-- strip everything (account still exists and can log in, with USAGE only)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'report'@'192.168.1.60';
SHOW GRANTS FOR 'report'@'192.168.1.60';
SQLChanges to table and column privileges apply to a connected session from its next statement, and database-level changes from its next USE. A session that is already connected keeps its global privileges until it reconnects, so after revoking from someone you no longer trust, also kill their sessions (Step 7).
Step 6: Use roles for groups of privileges
Roles let you define a privilege set once and grant it to many accounts, which is easier to audit than repeating grants per user. MariaDB has had roles since 10.0; the syntax for the default role differs from MySQL, so both are shown.
sudo mariadb <<'SQL'
CREATE ROLE shop_read;
GRANT SELECT ON shopdb.* TO shop_read;
CREATE ROLE shop_write;
GRANT shop_read TO shop_write; -- roles can contain roles
GRANT INSERT, UPDATE, DELETE ON shopdb.* TO shop_write;
CREATE USER 'bob'@'localhost' IDENTIFIED BY 'Paste-A-Long-Random-Password';
GRANT shop_read TO 'bob'@'localhost';
SET DEFAULT ROLE shop_read FOR 'bob'@'localhost'; -- MariaDB syntax
SQL
mariadb -u bob -p -e "SELECT CURRENT_ROLE(); SELECT COUNT(*) FROM shopdb.orders;"Without a default role the user has to run SET ROLE shop_read; in every session before the role’s privileges apply, which is the most common reason roles “do not work”. MariaDB allows one default role per account, so build combined roles (as shop_write contains shop_read) rather than granting several. See which roles are active and granted with SELECT * FROM information_schema.APPLICABLE_ROLES;.
MySQL 8.4 differences: role names are accounts too ('shop_read'@'%' by default), the default role is set with SET DEFAULT ROLE shop_read TO 'bob'@'localhost'; (TO, not FOR, and several roles or ALL are allowed), and setting activate_all_roles_on_login = ON activates every granted role at login. If you came from the PostgreSQL series, the model is close to PostgreSQL group roles, except that MySQL-family roles never own objects.
Step 7: Change passwords, lock and drop accounts
sudo mariadb <<'SQL'
-- rotate a password
ALTER USER 'shop_app'@'192.168.1.%' IDENTIFIED BY 'New-Long-Random-Password';
-- force a person to choose a new password at next login
ALTER USER 'bob'@'localhost' PASSWORD EXPIRE;
-- expire a person's password every 90 days
ALTER USER 'alice'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
-- disable an account without losing its grants
ALTER USER 'report'@'192.168.1.60' ACCOUNT LOCK;
-- rename (for example after an IP change) keeps the grants
RENAME USER 'shop_migrate'@'192.168.1.50' TO 'shop_migrate'@'192.168.1.51';
SQLDo not expire passwords on application accounts unless your deploy process rotates them, or the application will start failing with ERROR 1820: You must reset your password at a random moment. To remove someone completely, find and kill their sessions first, then drop the account:
sudo mariadb -e "SELECT id, user, host, db, command, time FROM information_schema.PROCESSLIST WHERE user = 'bob';"
sudo mariadb -e "KILL CONNECTION 42;" # id from the query above
sudo mariadb -e "DROP USER IF EXISTS 'bob'@'localhost';"Optional password quality checks on MariaDB: INSTALL SONAME 'simple_password_check'; rejects short or simple passwords for CREATE USER, ALTER USER and SET PASSWORD from then on (it does not check existing passwords). MySQL uses the validate_password component set up in part 1.
Step 8: Open the server to the application subnet
The accounts are ready, but the server still listens on 127.0.0.1 only. Change bind-address in the local config file from part 1 to the server’s own LAN address. Binding to the specific IP rather than 0.0.0.0 keeps the server off any other interface (a backup or management network, for example):
sudo sed -i 's/^bind-address *=.*/bind-address = 192.168.1.40/' /etc/my.cnf.d/zz-local.cnf
grep -E '^(bind-address|skip-name-resolve)' /etc/my.cnf.d/zz-local.cnf
sudo systemctl restart mariadb
sudo ss -ltnp | grep -E ':3306\b' # expect 192.168.1.40:3306Socket logins (sudo mariadb, accounts at localhost) keep working, because the Unix socket does not depend on bind-address. Apps on the same server that connect over TCP to 127.0.0.1 will stop working, though: either switch them to the socket or set bind-address = 0.0.0.0 and rely on the firewall.
Now let only the application subnet through firewalld. If you created the dbclients zone in part 1, confirm it; otherwise create it now. A source-based zone means 3306 is open to 192.168.1.0/24 and closed to everything else, including the rest of the public zone:
sudo firewall-cmd --get-zone-of-source=192.168.1.0/24 || {
sudo firewall-cmd --permanent --new-zone=dbclients
sudo firewall-cmd --permanent --zone=dbclients --add-source=192.168.1.0/24
sudo firewall-cmd --permanent --zone=dbclients --add-service=mysql
sudo firewall-cmd --reload
}
sudo firewall-cmd --zone=dbclients --list-all
sudo firewall-cmd --zone=public --list-services # mysql must NOT be listed hereIf you need SSH from that subnet as well, add it to the zone too (--zone=dbclients --add-service=ssh), because a source-based zone takes over all traffic from those addresses. SELinux needs no change on the standard port 3306, which is already labeled mysqld_port_t. For another port, run sudo semanage port -a -t mysqld_port_t -p tcp 3307 and use --add-port=3307/tcp in firewalld.
Step 9: Verify from the application server
From app1 (192.168.1.50), test the network path first and then the account:
# on app1
timeout 3 bash -c '</dev/tcp/192.168.1.40/3306' && echo "port open"
mariadb -h 192.168.1.40 -u shop_app -p shopdb -e "SELECT CURRENT_USER(), @@hostname;"
mariadb -h 192.168.1.40 -u shop_app -p shopdb -e "CREATE TABLE nope (id INT);" # must FAILCURRENT_USER() should return shop_app@192.168.1.%, which proves the subnet account (not some other match) authenticated you, and the CREATE TABLE must fail with ERROR 1142 (42000): CREATE command denied. Then prove the negative from a host outside 192.168.1.0/24: the TCP test should time out or be refused by the firewall before MariaDB is even reached. Finally, to keep the password out of shell history and process lists on the application server, put it in a protected option file instead of on the command line:
# on app1, as the application's OS user
cat > ~/.my.cnf <<'EOF'
[client]
host = 192.168.1.40
user = shop_app
password = Paste-The-Password-Here
database = shopdb
EOF
chmod 600 ~/.my.cnf
mariadb -e "SELECT 1;"Verification checklist
SELECT user, host FROM mysql.user;lists only named accounts with specific hosts or subnets, no'%'and no empty user names.SHOW GRANTSfor every application account shows privileges on its own schema only, no*.*beyondUSAGE, and noFILE,SUPERorGRANT OPTION.ss -ltnp | grep 3306shows the LAN address you chose, andfirewall-cmd --zone=public --list-servicesdoes not includemysql.- The application account connects from the client subnet, gets
CURRENT_USER()with the expected host part, and is denied anything outside its grants. - A connection attempt from outside the client subnet never reaches the server.
Troubleshooting tips
ERROR 1130 (HY000): Host '192.168.1.50' is not allowed to connect to this MariaDB server: the TCP connection worked, but no account matches that client address with that user name. Check the exact source IP the server sees (NAT, a second NIC or IPv6 can change it) withSELECT host FROM information_schema.PROCESSLISTfrom a working session, and compare it withSELECT user, host FROM mysql.user. Remember that host names never match withskip-name-resolveon.ERROR 1045 (28000): Access denied for user 'shop_app'@'192.168.1.50' (using password: YES): an account matched but the password did not, or a more specific account than the one you meant matched first (for example an old'shop_app'@'192.168.1.50'with a different password, or an anonymous''@'192.168.1.%'). List every row for that user name and drop the stale ones.ERROR 2002or2003 (HY000): Can't connect to server on '192.168.1.40'with (111) or (115): the connection was refused, so nothing is listening on that address, sobind-addressis still 127.0.0.1 or the restart failed. (113) “No route to host” from a client usually means firewalld rejected it: checkfirewall-cmd --get-zone-of-sourceand that the client really is in the subnet.- Grants seem to be ignored for a role: the role is granted but not active. Check
SELECT CURRENT_ROLE();in the user’s session and set a default role (Step 6). Host '192.168.1.50' is blocked because of many connection errors: the client hitmax_connect_errors(often a health checker that opens TCP and closes without logging in). Fix the checker, then clear the cache withsudo mariadb-admin flush-hostson MariaDB orTRUNCATE TABLE performance_schema.host_cache;on MySQL.GRANTfails withERROR 1133: Can't find any matching row in the user table: the account (with that exact host part) does not exist. Create it withCREATE USERfirst; do not rely onGRANTcreating accounts implicitly, which MySQL 8 removed and MariaDB blocks under the defaultNO_AUTO_CREATE_USERSQL mode.- Application connections fail right after a password rotation: existing sessions keep working and only new ones use the new password, so pools fail gradually. Rotate in two steps: create a second account with the new password, switch the application, then drop the old account.
Quick reference
| Task | Command |
|---|---|
| List accounts | SELECT user, host, plugin FROM mysql.user; |
| Create user | CREATE USER 'u'@'192.168.1.%' IDENTIFIED BY '...'; |
| Grant on a schema | GRANT SELECT, INSERT, UPDATE, DELETE ON db.* TO 'u'@'192.168.1.%'; |
| Show grants | SHOW GRANTS FOR 'u'@'192.168.1.%'; |
| Revoke | REVOKE DELETE ON db.* FROM 'u'@'192.168.1.%'; |
| Create and assign a role | CREATE ROLE r; GRANT r TO 'u'@'h'; SET DEFAULT ROLE r FOR 'u'@'h'; |
| Change password | ALTER USER 'u'@'h' IDENTIFIED BY '...'; |
| Lock account | ALTER USER 'u'@'h' ACCOUNT LOCK; |
| Drop account | DROP USER IF EXISTS 'u'@'h'; |
| Listen on LAN | bind-address = 192.168.1.40 in /etc/my.cnf.d/zz-local.cnf |
| Open to subnet | firewall-cmd --permanent --zone=dbclients --add-service=mysql |
Next in the series
Part 3 covers backups with mysqldump and mariadb-backup, including a dedicated backup account with just the privileges those tools need, scheduled jobs and tested restores. Earlier: part 1, install and secure MariaDB/MySQL on AlmaLinux 10.
Official references: MariaDB: CREATE USER, MariaDB: GRANT, MariaDB: SET DEFAULT ROLE, MySQL 8.4: Specifying Account Names, MySQL 8.4: Using Roles and RHEL 10: Using MariaDB.