OneLinersCommand workbench
Guides
Security / Databases & Data

Manage MariaDB users, passwords, roles, and rotation safely

Create host-scoped accounts, assign roles, rotate credentials without exposing them in shell history, test grants, and retire access.

45 min8 stepsChanges system stateRevision 2
Save or explore
#mariadb#users#password#roles#grants#rotation
Save to collectionCreate a collection in the sidebar first.
0 of 8 steps completed
Goal

Make database access least-privilege, attributable, rotatable, and recoverable throughout the account lifecycle.

Supported environments
  • MariaDB 10.11 LTS, 11.4 LTS
Prerequisites
  • Recovery access and maintenance window Keep console access and schedule write-affecting work so an interrupted package, grant, backup, or replication change can be recovered.
  • Verified backup Create a fresh backup and prove that its format, encryption key, and restore procedure are available before changing production data.
  • Capacity and compatibility Check database version, storage headroom, character sets, application driver compatibility, and expected downtime.df -h; df -i; free -m
Operating boundary

OneLiners never runs these steps or stores secrets. Review placeholders, versions, current state, and change-control requirements before using a command.

Full guide

What you will build

System
  • A production-ready MariaDB implementation of “Manage MariaDB users, passwords, roles, and rotation safely” with explicit identities, configuration ownership, observable checkpoints and a tested reversal path.
  • A before-state, native validation sequence and concrete evidence set covering the database service, client behavior, data or administrative surface affected by this procedure.
  • A least-privilege operational runbook that keeps secrets out of commands and verifies both the required success path and an expected denial or failure boundary.
Observable outcome
  • Make database access least-privilege, attributable, rotatable, and recoverable throughout the account lifecycle. The result is demonstrated through the guide's native verification commands and representative application or restore evidence.
  • Unrelated databases, accounts, listeners and services remain unchanged; operators can identify the exact revision, artifact and rollback action for this change.

Architecture

How the parts fit together

A database identity consists of user name plus permitted client host, authentication method, credential or certificate, role memberships and object privileges. Roles describe job functions; accounts receive roles rather than broad direct grants. Credential rotation overlaps old and new access only for a bounded observation window. Native grant inspection and a real client connection prove both required access and expected denial.

RolesBundle reviewed schema privileges by job function.
User@host accountsBind authentication and network origin to a human or service identity.
Credential lifecycleIntroduces, observes and retires secrets without uncontrolled overlap.
Positive and negative testsProve required grants and expected denials.
  1. Capture effective configuration, versions, listeners, identities, grants, workload health and a recoverable before-state.
  2. Prepare the identity candidate using only official MariaDB interfaces, protected secret delivery and the narrowest necessary scope.
  3. Apply one controlled layer at a time, run the immediate checkpoint and inspect native logs/status before proceeding.
  4. Perform end-to-end success and expected-failure tests; retain rollback evidence and schedule recurring verification.

Assumptions

  • The installed MariaDB family and version are supported by the official sources linked below, and package/configuration provenance is known.
  • A maintenance or canary window, current recoverable backup and console-level recovery path exist before state-changing database work begins.
  • Operator, application, backup, replication and web-administration identities are separate; secrets are supplied by protected option files, environment injection with appropriate controls, or a secret manager.
  • Representative schema size, workload, storage capacity, network policy and recovery objectives are documented for the target environment.

Key concepts

user@host identity
MySQL-family authorization matches both an account name and its permitted client origin, not the name alone.
effective configuration
The final runtime values after packaged defaults and ordered configuration fragments have been combined.
consistent state
Data and metadata captured at one valid transactional or recovery point rather than a mixture of moments.
recovery objective
The acceptable data-loss window and restoration time that drive backup, replication and validation design.
positive and negative verification
Proving an intended operation succeeds and an operation outside the granted/security boundary fails.

Before you copy

Values used in this guide

{{clientHost}}

Operator-reviewed value used by this tutorial for clientHost; derive it from the target environment and never from untrusted request data.

Example: 192.0.2.44
{{database}}

Application database name; use an identifier approved by the database naming policy.

Example: billing
{{dbHost}}

Operator-reviewed value used by this tutorial for dbHost; derive it from the target environment and never from untrusted request data.

Example: db-primary.example.com
{{newProtectedSecret}}

Operator-reviewed value used by this tutorial for newProtectedSecret; derive it from the target environment and never from untrusted request data.

Example: secret-manager-reference
{{observationSeconds}}

Operator-reviewed value used by this tutorial for observationSeconds; derive it from the target environment and never from untrusted request data.

Example: 900
{{role}}

Operator-reviewed value used by this tutorial for role; derive it from the target environment and never from untrusted request data.

Example: billing_readwrite
{{user}}

Operator-reviewed value used by this tutorial for user; derive it from the target environment and never from untrusted request data.

Example: billing_app

Security and production boundaries

  • Never place database passwords, encryption keys, recovery codes or private certificates directly in reusable shell history or repository files.
  • Do not use `%`, global grants, disabled TLS verification, public administrative interfaces or world-readable dumps as troubleshooting shortcuts.
  • Treat backups, slow logs and phpMyAdmin sessions as sensitive data; encrypt, restrict, rotate and monitor them according to the same or stronger policy as production.

Stop before continuing if

  • Stop if the server version/config family is ambiguous, the backup cannot be restored, secrets would be exposed, or a proposed command affects databases/accounts beyond the declared scope.
  • Do not continue when native validation, representative workload, expected-denial test, TLS identity, replication continuity or storage capacity evidence is incomplete for this procedure.
  • Immediately isolate or revert a change that broadens a listener/grant, breaks application health, introduces replication error, loses recoverability or produces unbounded sensitive logs.
01

instruction

Inventory accounts, hosts, roles, and grants

read-only

Export account metadata and SHOW GRANTS output before changing authentication or privilege inheritance.

Why this step matters

Inventory accounts, hosts, roles, and grants is a separate checkpoint because it establishes one auditable part of make database access least-privilege, attributable, rotatable, and recoverable throughout the account lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Export account metadata and SHOW GRANTS output before changing authentication or privilege inheritance. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Inventory accounts, hosts, roles, and grants”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb
Invokes the principal tool or configuration operation for “Inventory accounts, hosts, roles, and grants”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
Command
sudo mariadb -NBe "SELECT User,Host,plugin,is_role FROM mysql.user ORDER BY User,Host"; sudo mariadb -NBe "SELECT * FROM mysql.roles_mapping ORDER BY User,Host,Role"
Example output / evidence
Accounts and role mappings are recorded.

Checkpoint: Checkpoint: Inventory accounts, hosts, roles, and grants

Continue whenAccounts and role mappings are recorded. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Inventory accounts, hosts, roles, and grants” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
02

command

Create a purpose-specific role

caution

Grant only required schema operations to a named role rather than repeating direct grants on users.

Why this step matters

Create a purpose-specific role is a separate checkpoint because it establishes one auditable part of make database access least-privilege, attributable, rotatable, and recoverable throughout the account lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Grant only required schema operations to a named role rather than repeating direct grants on users. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Create a purpose-specific role”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb
Invokes the principal tool or configuration operation for “Create a purpose-specific role”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}
Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
Command
Fill variables0/2 ready

Values stay on this page and are never sent or saved.

sudo mariadb -e "CREATE ROLE IF NOT EXISTS '{{role}}'; GRANT SELECT, INSERT, UPDATE, DELETE ON \`{{database}}\`.* TO '{{role}}'; SHOW GRANTS FOR '{{role}}';"
Example output / evidence
The role has only approved schema-level privileges.

Checkpoint: Checkpoint: Create a purpose-specific role

Continue whenThe role has only approved schema-level privileges. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Create a purpose-specific role” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
03

instruction

Generate and deliver a secret safely

read-only

Generate a high-entropy credential in a secret manager or protected terminal; never place it in process arguments, tickets, source control, or this guide.

Why this step matters

Generate and deliver a secret safely is a separate checkpoint because it establishes one auditable part of make database access least-privilege, attributable, rotatable, and recoverable throughout the account lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Generate a high-entropy credential in a secret manager or protected terminal; never place it in process arguments, tickets, source control, or this guide. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Generate and deliver a secret safely”. The scope must remain limited to the selected application and supported host.

Syntax explained

umask
Invokes the principal tool or configuration operation for “Generate and deliver a secret safely”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}
Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
Command
Fill variables0/1 ready

Values stay on this page and are never sent or saved.

umask 077; openssl rand -base64 36 > /tmp/{{user}}.password; chmod 600 /tmp/{{user}}.password
Example output / evidence
A root- or operator-readable temporary secret file exists.

Checkpoint: Checkpoint: Generate and deliver a secret safely

Continue whenA root- or operator-readable temporary secret file exists. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when umask reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Generate and deliver a secret safely” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v umask 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
04

command

Create a host-scoped account

caution

Use the narrowest host or network pattern and set authentication from an interactive protected SQL session.

Why this step matters

Create a host-scoped account is a separate checkpoint because it establishes one auditable part of make database access least-privilege, attributable, rotatable, and recoverable throughout the account lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Use the narrowest host or network pattern and set authentication from an interactive protected SQL session. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Create a host-scoped account”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb
Invokes the principal tool or configuration operation for “Create a host-scoped account”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
Command
sudo mariadb --protocol=socket
Example output / evidence
CREATE USER '{{user}}'@'{{clientHost}}' IDENTIFIED BY the protected secret succeeds.

Checkpoint: Checkpoint: Create a host-scoped account

Continue whenCREATE USER '{{user}}'@'{{clientHost}}' IDENTIFIED BY the protected secret succeeds. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Create a host-scoped account” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
05

command

Assign and activate the role

caution

Grant the role and set it as default so privileges are predictable after login.

Why this step matters

Assign and activate the role is a separate checkpoint because it establishes one auditable part of make database access least-privilege, attributable, rotatable, and recoverable throughout the account lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Grant the role and set it as default so privileges are predictable after login. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Assign and activate the role”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb
Invokes the principal tool or configuration operation for “Assign and activate the role”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}
Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
Command
Fill variables0/3 ready

Values stay on this page and are never sent or saved.

sudo mariadb -e "GRANT '{{role}}' TO '{{user}}'@'{{clientHost}}'; SET DEFAULT ROLE '{{role}}' FOR '{{user}}'@'{{clientHost}}'; SHOW GRANTS FOR '{{user}}'@'{{clientHost}}';"
Example output / evidence
The account inherits only the intended default role.

Checkpoint: Checkpoint: Assign and activate the role

Continue whenThe account inherits only the intended default role. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Assign and activate the role” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
06

verification

Test allowed and denied operations

read-only

From the real client path prove an allowed query, then prove administrative or cross-schema operations fail.

Why this step matters

Test allowed and denied operations is a separate checkpoint because it establishes one auditable part of make database access least-privilege, attributable, rotatable, and recoverable throughout the account lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

From the real client path prove an allowed query, then prove administrative or cross-schema operations fail. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Test allowed and denied operations”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb
Invokes the principal tool or configuration operation for “Test allowed and denied operations”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}
Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
Command
Fill variables0/3 ready

Values stay on this page and are never sent or saved.

mariadb --host={{dbHost}} --user={{user}} --password {{database}} -e "SELECT CURRENT_USER(), CURRENT_ROLE(); SELECT 1;"
Example output / evidence
The intended role is active; a separately tested disallowed operation is denied.

Checkpoint: Checkpoint: Test allowed and denied operations

Continue whenThe intended role is active; a separately tested disallowed operation is denied. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Test allowed and denied operations” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
07

command

Rotate the password with overlap or coordinated cutover

caution

Update the secret manager and application through a controlled deployment; use a second account for zero-downtime rotation when dual passwords are unavailable.

Why this step matters

Rotate the password with overlap or coordinated cutover is a separate checkpoint because it establishes one auditable part of make database access least-privilege, attributable, rotatable, and recoverable throughout the account lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Update the secret manager and application through a controlled deployment; use a second account for zero-downtime rotation when dual passwords are unavailable. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Rotate the password with overlap or coordinated cutover”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb
Invokes the principal tool or configuration operation for “Rotate the password with overlap or coordinated cutover”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}
Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
Command
Fill variables0/3 ready

Values stay on this page and are never sent or saved.

sudo mariadb --protocol=socket -e "ALTER USER '{{user}}'@'{{clientHost}}' IDENTIFIED BY '{{newProtectedSecret}}';"
Example output / evidence
New sessions accept only the new credential after the coordinated cutover.

Checkpoint: Checkpoint: Rotate the password with overlap or coordinated cutover

Continue whenNew sessions accept only the new credential after the coordinated cutover. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Rotate the password with overlap or coordinated cutover” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.
08

command

Lock, observe, and drop retired access

caution

Lock the account first, observe failed dependency checks and application health, then drop it after the rollback window.

Why this step matters

Lock, observe, and drop retired access is a separate checkpoint because it establishes one auditable part of make database access least-privilege, attributable, rotatable, and recoverable throughout the account lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Lock the account first, observe failed dependency checks and application health, then drop it after the rollback window. Read the complete command or configuration before execution, replace the documented placeholders, and compare the target host with the supported environments listed at the beginning of this tutorial.

Capture the before-state and the exact output in the change record. If this step modifies a file or service, validate the candidate with its native checker before any reload; a successful process exit alone does not prove that the application uses the intended value.

Use a representative staging or canary host first. Repeat the stated checkpoint immediately, because proceeding with ambiguous evidence makes the later end-to-end verification and rollback materially harder.

System changes

  • This step may affect the files, packages, identities, services, or runtime policy named in “Lock, observe, and drop retired access”. The scope must remain limited to the selected application and supported host.

Syntax explained

mariadb
Invokes the principal tool or configuration operation for “Lock, observe, and drop retired access”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
{{…}}
Marks an operator-supplied value. Replace every placeholder deliberately; do not paste untrusted text or leave braces in the production command.
Command
Fill variables0/3 ready

Values stay on this page and are never sent or saved.

sudo mariadb -e "ALTER USER '{{user}}'@'{{clientHost}}' ACCOUNT LOCK;"; sleep {{observationSeconds}}; sudo mariadb -e "DROP USER IF EXISTS '{{user}}'@'{{clientHost}}';"
Example output / evidence
The retired account is removed after a clean observation period.

Checkpoint: Checkpoint: Lock, observe, and drop retired access

Continue whenThe retired account is removed after a clean observation period. Treat this concrete evidence as the minimum success signal and also confirm that unrelated services and the previous healthy path remain unchanged.

Stop whenStop before the next step when mariadb reports an error, the output differs materially from the example, an unexpected package/service/path is affected, or the required before-state and rollback artifact are missing.

If this step fails

The command for “Lock, observe, and drop retired access” fails, produces no useful evidence, or the expected service/configuration state is absent.

Likely causeA placeholder targets the wrong environment, the installed version uses another path or unit name, permissions are insufficient, or an earlier prerequisite was not satisfied.

Safe checks
  • command -v mariadb 2>/dev/null || true
  • systemctl --failed --no-pager 2>/dev/null || true
  • journalctl -p warning -n 50 --no-pager 2>/dev/null || true

ResolutionDo not improvise a privileged workaround. Re-read the environment and variable table, inspect the official source for the installed version, restore the candidate file if necessary, and repeat only this checkpoint.

Security notes

  • Keep secrets out of shell history, process arguments, screenshots, logs, and tutorial placeholders. Elevate only the narrow command that requires it and preserve package signature and TLS verification.

Alternatives

  • When the host layout or software version differs, use the vendor-supported equivalent in a disposable staging environment and document the mapping before touching production.

Stop conditions

  • Stop if the action broadens network exposure, permissions, package sources, writable paths, or service privileges beyond the tutorial's declared architecture.

Finish line

Verification checklist

Grant reviewsudo mariadb -e "SHOW GRANTS FOR '{{user}}'@'{{clientHost}}';"Only the approved role and connection requirements appear.
Secret cleanuptest ! -e /tmp/{{user}}.password && echo 'temporary secret removed'No plaintext temporary credential remains.

Recovery guidance

Common problems and safe checks

The native client reports access denied even though a similarly named account has grants.

Likely causeDatabase identities include a host component and authentication plugin; the connection matched another user@host row or transport.

Safe checks
  • mysql -e 'SELECT USER(), CURRENT_USER();'
  • mysql -e "SELECT User,Host,plugin FROM mysql.user ORDER BY User,Host;"
  • mysql -e 'SHOW GRANTS FOR CURRENT_USER;'

ResolutionIdentify the exact matched account and create or modify only the intended user@host identity; do not broaden it to '%' as a shortcut.

TLS is enabled but the client still connects with an unexpected identity or without hostname verification.

Likely causeThe client uses encryption-only mode, connects by an address absent from the SAN, trusts a broad system CA set, or another option file overrides policy.

Safe checks
  • mysql --print-defaults
  • openssl s_client -starttls mysql -connect db.example.com:3306 -servername db.example.com </dev/null
  • mysql -e "SHOW STATUS LIKE 'Ssl_%';"

ResolutionUse the documented hostname, intended CA and VERIFY_IDENTITY-equivalent mode; remove conflicting client options and retest expected failure with a wrong hostname.

A change passes syntax checks but application errors, lag, latency or disk use increases.

Likely causeThe candidate is syntactically valid but workload, lock, cache, rotation, privilege or version assumptions are wrong.

Safe checks
  • mysqladmin ping
  • mysql -e 'SHOW PROCESSLIST;'
  • journalctl -u mariadb -u mysql -p warning --since '-10 min' --no-pager

ResolutionStop promotion, preserve evidence, revert the smallest changed configuration/account/routing layer and reproduce with a safe representative workload.

After the procedure

Alternatives and next steps

Consider these alternatives

  • Use a managed database only after private connectivity, identity, encryption, monitoring, backup restoration and responsibility boundaries are proven.
  • Use immutable configuration automation and a disposable recovery environment when it produces stronger review, repeatability and rollback than manual host edits.

Operate it safely

  • Automate health, capacity, certificate, replication, backup and restore-test alerts with an owner and response runbook.
  • Review accounts, grants, package support, encryption material and recovery evidence after every major release and at least every 90–180 days.
  • Practice the rollback or restore from recorded artifacts on an isolated host so the documented time and dependencies remain credible.

Reference

Frequently asked questions

Does a successful command mean the procedure is complete?

No. Database tools may return success before application compatibility, replica replay, restore integrity, TLS identity or expected denials are proven. Use every checkpoint and final verification.

Can an administrator use the application account for convenience?

No. Separate identities make least privilege, rotation, audit and incident containment possible; the application should not hold administrative capabilities.

Why test restoration or negative access separately?

A backup that cannot restore and a grant that was never tested for denial are assumptions, not controls. Isolation makes those tests safe and repeatable.

Recovery

Rollback

Recreate or unlock the prior host-scoped account from the protected grant record during the rollback window.

  1. Before dropping, unlock the account and restore the previous secret through a protected SQL session if cutover fails.
  2. After dropping, recreate the exact account, role grant, default role, and TLS requirement from the exported statements.
  3. Rotate any secret that was exposed during recovery.

Evidence

Sources and review

Verified 2026-07-25Review due 2026-10-23
MariaDB account management SQL statementsofficialMariaDB security guidanceofficialMariaDB secure connections and TLSofficial