OneLinersCommand workbench
Guides
Security / Databases & Data

Manage MySQL 8.4 users, passwords, roles, and rotation

Create source-scoped accounts, use roles, enforce TLS and password policy, rotate without command-line leakage, test denial, and retire access.

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

Operate MySQL identities with least privilege and a reversible credential lifecycle.

Supported environments
  • MySQL 8.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 MySQL implementation of “Manage MySQL 8.4 users, passwords, roles, and rotation” 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
  • Operate MySQL identities with least privilege and a reversible credential 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 MySQL 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 MySQL 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

{{caFile}}

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

Example: /etc/mysql/ca/organization-root.pem
{{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
{{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, plugins, lock state, and grants

read-only

Export CREATE USER and SHOW GRANTS for affected accounts before altering credentials or inherited roles.

Why this step matters

Inventory accounts, plugins, lock state, and grants is a separate checkpoint because it establishes one auditable part of operate mysql identities with least privilege and a reversible credential lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Export CREATE USER and SHOW GRANTS for affected accounts before altering credentials or inherited roles. 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, plugins, lock state, and grants”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Inventory accounts, plugins, lock state, and grants”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
Command
mysql -NBe "SELECT User,Host,plugin,account_locked,password_expired FROM mysql.user ORDER BY User,Host"; mysql -NBe "SELECT * FROM mysql.role_edges ORDER BY FROM_USER,FROM_HOST"
Example output / evidence
Account and role state are recorded.

Checkpoint: Checkpoint: Inventory accounts, plugins, lock state, and grants

Continue whenAccount and role state 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 mysql 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, plugins, lock state, 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 mysql 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 schema-scoped role

caution

Grant only application operations to a named role, keeping administration outside application credentials.

Why this step matters

Create a schema-scoped role is a separate checkpoint because it establishes one auditable part of operate mysql identities with least privilege and a reversible credential lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Grant only application operations to a named role, keeping administration outside application credentials. 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 schema-scoped role”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Create a schema-scoped 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.

mysql -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 the reviewed schema privileges.

Checkpoint: Checkpoint: Create a schema-scoped role

Continue whenThe role has the reviewed schema 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 mysql 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 schema-scoped 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 mysql 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 store the credential safely

read-only

Generate through the secret manager or a protected 0600 file, never in process arguments, environment logs, or source control.

Why this step matters

Generate and store the credential safely is a separate checkpoint because it establishes one auditable part of operate mysql identities with least privilege and a reversible credential lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Generate through the secret manager or a protected 0600 file, never in process arguments, environment logs, or source control. 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 store the credential 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 store the credential 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; stat -c '%a %U:%G %n' /tmp/{{user}}.password
Example output / evidence
A mode-600 temporary secret is ready for protected entry.

Checkpoint: Checkpoint: Generate and store the credential safely

Continue whenA mode-600 temporary secret is ready for protected entry. 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 store the credential 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 TLS-required account

caution

Run CREATE USER in an interactive protected MySQL session, use the narrowest client host, and require SSL or X509.

Why this step matters

Create a host-scoped TLS-required account is a separate checkpoint because it establishes one auditable part of operate mysql identities with least privilege and a reversible credential lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Run CREATE USER in an interactive protected MySQL session, use the narrowest client host, and require SSL or X509. 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 TLS-required account”. The scope must remain limited to the selected application and supported host.

Syntax explained

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

Checkpoint: Checkpoint: Create a host-scoped TLS-required account

Continue whenCREATE USER '{{user}}'@'{{clientHost}}' IDENTIFIED BY the protected secret REQUIRE SSL 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 mysql 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 TLS-required 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 mysql 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

Grant and activate the role

caution

Grant the role, set it as default, and inspect effective role state.

Why this step matters

Grant and activate the role is a separate checkpoint because it establishes one auditable part of operate mysql identities with least privilege and a reversible credential 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, set it as default, and inspect effective role state. 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 “Grant and activate the role”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Grant 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.

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

Checkpoint: Checkpoint: Grant and activate the role

Continue whenThe account receives 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 mysql 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 “Grant 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 mysql 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 forbidden actions

read-only

Connect from the real source with TLS, perform an approved query, and prove a cross-schema or administrative operation is denied.

Why this step matters

Test allowed and forbidden actions is a separate checkpoint because it establishes one auditable part of operate mysql identities with least privilege and a reversible credential lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Connect from the real source with TLS, perform an approved query, and prove a cross-schema or administrative operation is denied. 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 forbidden actions”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Test allowed and forbidden actions”; 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/4 ready

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

mysql --host={{dbHost}} --ssl-mode=VERIFY_IDENTITY --ssl-ca={{caFile}} --user={{user}} --password {{database}} -e "SELECT CURRENT_USER(),CURRENT_ROLE(); SELECT 1;"
Example output / evidence
TLS identity verification succeeds, the role is active, and the denial test fails as expected.

Checkpoint: Checkpoint: Test allowed and forbidden actions

Continue whenTLS identity verification succeeds, the role is active, and the denial test fails as expected. 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 mysql 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 forbidden actions” 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 mysql 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 with dual passwords or a second account

caution

Use ALTER USER ... RETAIN CURRENT PASSWORD where policy permits, deploy the new secret, test new sessions, then discard the old password.

Why this step matters

Rotate with dual passwords or a second account is a separate checkpoint because it establishes one auditable part of operate mysql identities with least privilege and a reversible credential lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Use ALTER USER ... RETAIN CURRENT PASSWORD where policy permits, deploy the new secret, test new sessions, then discard the old password. 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 with dual passwords or a second account”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Rotate with dual passwords or a second account”; confirm that it resolves to the expected packaged executable or file before using elevated privileges.
Command
mysql
Example output / evidence
ALTER USER '{{user}}'@'{{clientHost}}' IDENTIFIED BY the new protected secret RETAIN CURRENT PASSWORD succeeds.

Checkpoint: Checkpoint: Rotate with dual passwords or a second account

Continue whenALTER USER '{{user}}'@'{{clientHost}}' IDENTIFIED BY the new protected secret RETAIN CURRENT PASSWORD 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 mysql 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 with dual passwords or a second 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 mysql 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 and remove retired access

caution

Lock first, monitor dependencies, revoke the role, and drop only after the observation and rollback window.

Why this step matters

Lock and remove retired access is a separate checkpoint because it establishes one auditable part of operate mysql identities with least privilege and a reversible credential lifecycle. Performing it independently keeps a failed prerequisite, syntax error, or compatibility mismatch from being hidden by later actions.

What to understand

Lock first, monitor dependencies, revoke the role, and drop only after the observation and 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 and remove retired access”. The scope must remain limited to the selected application and supported host.

Syntax explained

mysql
Invokes the principal tool or configuration operation for “Lock and remove 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/4 ready

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

mysql -e "ALTER USER '{{user}}'@'{{clientHost}}' ACCOUNT LOCK; REVOKE '{{role}}' FROM '{{user}}'@'{{clientHost}}';"; sleep {{observationSeconds}}; mysql -e "DROP USER '{{user}}'@'{{clientHost}}';"
Example output / evidence
The retired identity is removed after a clean observation period.

Checkpoint: Checkpoint: Lock and remove retired access

Continue whenThe retired identity 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 mysql 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 and remove 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 mysql 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

Least privilegemysql -e "SHOW GRANTS FOR '{{user}}'@'{{clientHost}}';"Only the intended role, TLS condition, and account policy appear.
Plaintext cleanuprm -f /tmp/{{user}}.password; test ! -e /tmp/{{user}}.passwordNo temporary plaintext secret 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

Restore the prior account state from protected SHOW CREATE USER and SHOW GRANTS records.

  1. Before DROP USER, unlock and restore the retained password if application cutover fails.
  2. After removal, recreate the exact host-scoped account, TLS condition, role grant, and default role.
  3. Rotate any credential exposed during recovery.

Evidence

Sources and review

Verified 2026-07-25Review due 2026-10-23
MySQL 8.4 account management statementsofficialMySQL 8.4 using rolesofficialMySQL 8.4 encrypted connectionsofficial