Accounts, Authentication Plugins, Password Policy, Locking, Expiry, and Lifecycle

Learning outcomes

ServiceHub is preparing a production rollout. Developers currently share one database login, a reporting script connects as an administrator, and an old migration account still exists even though nobody owns it. The database is “working,” but identity is ambiguous: MariaDB cannot reliably distinguish a human operator from an application, a credential leak grants more access than necessary, and offboarding requires guessing which sessions or jobs depend on a shared password.

MariaDB security begins before privileges. An account is an authenticated server principal identified by both a user name and a host component. Authentication answers “which principal is connecting?”; authorization, taught in Lesson 2, answers “what may that principal do?” Password policy, account locking, expiry and credential rotation are lifecycle controls around that principal.

1. Build a disposable identity lab

Use a dedicated local schema so account experiments cannot touch real data. The application table is intentionally simple; the lesson is about identity rather than schema design.

DROP DATABASE IF EXISTS servicehub_security_lab;CREATE DATABASE servicehub_security_lab;USE servicehub_security_lab;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY,  customer_name VARCHAR(120) NOT NULL,  severity ENUM('low','medium','high','critical') NOT NULL,  internal_note VARCHAR(255) NULL,  status ENUM('open','assigned','closed') NOT NULL DEFAULT 'open') ENGINE=InnoDB;INSERT INTO tickets VALUES(2001,'Northwind Clinic','high','call security contact first','open'),(2002,'Caspian Foods','medium','billing dispute exists','assigned'),(2003,'Atlas Lab','critical','after-hours escalation','open');SELECT VERSION() AS server_version;SHOW VARIABLES LIKE 'version_comment';

Record the server version before evaluating authentication or password-policy behavior. A command that works on one release or package does not prove the same plugin exists on another machine.

2. MariaDB account identity is user@host

The host portion participates in account matching. 'svc_api'@'localhost', 'svc_api'@'10.20.30.%' and 'svc_api'@'%' are different principals even though the visible user name is identical. The connection identity exposed by USER() describes the supplied client identity, while CURRENT_USER() identifies the MariaDB account actually matched for privilege evaluation.

DROP USER IF EXISTS 'svc_api'@'localhost';DROP USER IF EXISTS 'analyst_alice'@'localhost';DROP USER IF EXISTS 'retired_migrator'@'localhost';CREATE USER 'svc_api'@'localhost'  IDENTIFIED BY 'LabOnly-ServiceHub-Api-42!';CREATE USER 'analyst_alice'@'localhost'  IDENTIFIED BY 'LabOnly-Alice-42!';CREATE USER 'retired_migrator'@'localhost'  IDENTIFIED BY 'LabOnly-Migrate-42!'  ACCOUNT LOCK;SHOW CREATE USER 'svc_api'@'localhost';SHOW CREATE USER 'analyst_alice'@'localhost';SHOW CREATE USER 'retired_migrator'@'localhost';

For a local lab, explicit localhost is safer than teaching % as the default. In production, use the narrow host/network identity that matches the deployment and works with your name-resolution policy. Host patterns are not a firewall; network segmentation is still required.

SELECT USER() AS client_identity,       CURRENT_USER() AS matched_account,       CURRENT_ROLE() AS active_role;SHOW GRANTS;

Run the observation after connecting as each disposable account. If USER() and CURRENT_USER() differ, investigate which account pattern MariaDB matched before changing grants.

3. Authentication plugins are executable security mechanisms

An authentication plugin defines how MariaDB proves the client controls an accepted credential or external identity. Do not choose a plugin from memory. Verify that the server plugin is active, the client/connector supports the protocol, and the operating-system or external identity prerequisites are present.

SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARYFROM information_schema.PLUGINSWHERE PLUGIN_TYPE='AUTHENTICATION'ORDER BY PLUGIN_NAME;SHOW CREATE USER 'svc_api'@'localhost';

MariaDB supports alternate authentication rules in modern releases, but a production security design should remain comprehensible. Multiple fallback mechanisms can improve compatibility yet also broaden the attack surface if an older weaker path is left enabled unintentionally.

4. Password expiry, lock state and policy are lifecycle controls

ACCOUNT LOCK prevents new client connections but does not terminate existing sessions. Password-expiry clauses can force password renewal according to per-account policy. These controls are useful for human accounts, break-glass identities and decommissioning workflows, but service accounts require planned secret rotation so an expiry does not create an outage.

ALTER USER 'analyst_alice'@'localhost'  PASSWORD EXPIRE INTERVAL 90 DAY;ALTER USER 'retired_migrator'@'localhost' ACCOUNT LOCK;SHOW CREATE USER 'analyst_alice'@'localhost';SHOW CREATE USER 'retired_migrator'@'localhost';-- Re-enable only as part of an explicit rollback/ownership decision:ALTER USER 'retired_migrator'@'localhost' ACCOUNT UNLOCK;ALTER USER 'retired_migrator'@'localhost' ACCOUNT LOCK;

The number 90 is a lab example, not a universal recommendation. Production lifetime should follow your organization’s threat model, secret-management capability and service-rotation process. For non-password mechanisms, the relevant lifecycle may live partly outside MariaDB.

Password validation plugins

MariaDB offers password validation plugins, but they are optional and packaging differs. First inspect availability; do not write a mandatory lab that assumes a plugin library exists.

SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_LIBRARYFROM information_schema.PLUGINSWHERE PLUGIN_NAME IN ('simple_password_check','cracklib_password_check','password_reuse_check');SHOW GLOBAL VARIABLES LIKE 'default_password_lifetime';

If your target policy requires one of these mechanisms, install and configure it only after verifying the exact package, plugin library, privilege and restart requirements for the target server. A database password validator also cannot enforce the security of secrets stored in CI variables, desktop files or application configuration.

5. Deliberately wrong: create a broad shared account

A common shortcut is a single account such as 'admin'@'%' shared by people, deployments and services. This makes revocation hard, audit attribution weak and credential compromise severe. It also confuses host matching: a more-specific account may be selected instead of the wildcard account you thought was in use.

CREATE USER IF NOT EXISTS 'shared_admin'@'%'  IDENTIFIED BY 'LabOnly-Shared-Do-Not-Use-42!';-- The danger becomes obvious before granting anything:SELECT User,HostFROM mysql.userWHERE User='shared_admin';DROP USER 'shared_admin'@'%';

The repair is to split identities by purpose: named human accounts, dedicated application/service accounts, a controlled migration identity and a separately governed break-glass administrator. Authorization then becomes reviewable per principal.

SELECT User,Host,is_role,default_roleFROM mysql.userWHERE User IN ('svc_api','analyst_alice','retired_migrator')ORDER BY User,Host;

Direct reads of mysql.user require administrative privilege and expose security metadata; use them only from trusted administrative sessions. For most day-to-day account verification, prefer SHOW CREATE USER and SHOW GRANTS.

6. Production lifecycle and verification

Monitor failed authentication, unexpected source hosts, dormant accounts, accounts with no clear owner, changes to authentication plugins, locked/expired state and credential-rotation failures. Keep an inventory that maps each MariaDB account to a human/team owner and a consuming system.

1. Confirm every lab account with SHOW CREATE USER.
2. Connect as the intended principal and compare USER() with CURRENT_USER().
3. Verify the locked account rejects new sessions.
4. Record which authentication plugins are active on this exact server.
5. Remove the lab accounts and database when finished.

DROP USER IF EXISTS 'svc_api'@'localhost';DROP USER IF EXISTS 'analyst_alice'@'localhost';DROP USER IF EXISTS 'retired_migrator'@'localhost';DROP DATABASE IF EXISTS servicehub_security_lab;

Lesson 2 keeps these identities separate and adds authorization: privileges, roles, defaults and delegation.