Discover how MySQL 8 enhances security through features like password expiry, password validation, account lock/unlock, privilege management, and dual passwords. Safeguard your valuable data and streamline access control in your MySQL environment.

sukan June 08, 2023

Subscribe for email updates

Summarize with AI: ChatGPT Google AI Perplexity Claude Grok

Introduction

MySQL 8 introduces robust access control and account management features to bolster the security of your database. By implementing these features, you can protect against unauthorized access, enforce strong password policies, and streamline access control for users. This article explores the key functionalities of password expiry, password validation, account lock/unlock, privilege management, and dual passwords in MySQL 8, providing you with the tools to enhance the security of your MySQL environment.

1. Password Expiry

Introducing a robust password management system for MySQL, featuring password expiry and secure user creation. With this system, clients and developers can reset their passwords autonomously, eliminating the need to disclose credentials to anyone else. By enabling password expiry, the system enhances the security.

This feature enables you to establish an automatic policy that can be applied globally to all users or selectively to specific users. By utilizing the “default_password_lifetime” variable, you have granular control over the password expiry policy. The variable can be set to the default value of 0 or adjusted to a desired duration, such as 365 for a one-year password expiry. This ensures enhanced security by prompting regular password updates and reduces the risk of unauthorized access.

Syntax

create user 'appuser'@'localhost' identified by 'Appus3R' password expire;
Query OK, 0 rows affected (0.02 sec)

After setting the expiry, when a user connects to the database, they won’t have the privilege to do anything until they reset the password.

Following error will be returned to the user for those passwords that have expired.

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

2.Password Validation

Weak passwords pose a significant risk, as they are easily cracked by hackers. In fact, it takes less than a second for a computer to hack a weak password, as reported by [https://www.security.org/how-secure-is-my-password/].

To combat this vulnerability, MySQL implemented a robust password validation system. By default, the password validation plugin is disabled, but you have the option to enable it. This plugin allows you to define the strength requirements globally, ensuring that no weak passwords are accepted.
This means that neither the database administrator (DBA) nor the user can create an account with a weak password, bolstering the overall security of your system.

Syntax

INSTALL COMPONENT 'file://component_validate_password';
create user 'user2'@'localhost' identified by 'pass';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Validate_password.policy controls the strength of the password

LOW - Checks for password length only
MEDIUM - Checks for length, special character, mixed case count
HIGH - Validates from dictionary file

Remember, strong passwords are the first line of defense against unauthorized access, so let’s prioritize safeguarding our data together.

3. Account lock / unlock

This feature serves as powerful tools to defend against brute force attacks and prevent repeated unauthorized access to your system. By enforcing account lockouts after a certain number of failed login attempts, you can effectively thwart malicious actors from gaining unauthorized entry.
Account lock and unlock functionality not only bolster your system’s security but also ensure compliance with industry-standard security practices.

Syntax:

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'mypass' FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 4;

Failed_login_attemtps - How many consecutive failures before blocking the account.
Password_lock_time - Number of days in which account can be locked

N - Number of days
Unbounded - Permanently locked.

Access denied for user 'appuser'@'localhost'. Account is blocked for 4 day(s) (4 day(s) remaining) due to 3 consecutive failed logins.

To unlock globally, run flush privileges, to unblock specific to user run alter user account unlock

By incorporating these protective measures into your MySQL environment, you establish an extra layer of defense, making it significantly harder for attackers to compromise your system. Don’t leave your data vulnerable to unauthorized access—implement account lock and unlock mechanisms to safeguard your valuable information today

4.Privilege management

Granting unrestricted access to all users in a MySQL environment is a common pitfall that can lead to security vulnerabilities. Application users often end up with excessive privileges, including unnecessary permissions like DROP and ALTER. This not only compromises security but also poses a challenge for database administrators (DBAs) who must allocate specific access rights to multiple users.

Enter roles—the ultimate solution to streamline access management in MySQL. Roles allow you to define specific grants and store them under recognizable names. With this approach, DBAs can effortlessly invoke a role and attach it to the desired user, ensuring precise access control.
By utilizing roles, you can avoid the hassle of individually assigning permissions to each user and minimize the risk of unintentionally granting unnecessary privileges.

Syntax

create role 'application_reads';
grant select on foo.* to application_reads;
grant application_reads to user@'localhost';

If there exists multiple user accounts, we can easily attach the specific roles to the user accounts.

5. Dual passwords

Keeping up with regular password rotation for applications can be a daunting task. User has to modify the password in the DB then in the application which can result in downtime or a new user has to be created and credentials needs to be updated on all the servers.
However, with the introduction of dual passwords, this challenge becomes much more manageable.
Dual passwords enable you to maintain two active passwords simultaneously.
This approach simplifies the password rotation process, eliminating the need for any downtime.
Here’s how it works:

Users update the new password across all applications:
When it’s time to rotate passwords, users update their credentials with the new password in all relevant applications.
Once users have updated their credentials, the applications start using the new password seamlessly. This ensures uninterrupted access to critical systems and services.
Discarding the older password: After all applications have been updated with the new password, the older password can be safely discarded.
This step ensures that there is no residual access granted with the previous password, maintaining optimal security.

Syntax

alter user 'user'@'localhost' identified by 'newpass1' retain current password;
alter user 'user'@'localhost' discard old password;

By implementing dual passwords, you can simplify the password rotation process for applications, reducing the burden on users and minimizing any potential disruptions. Embrace this approach to enhance security and streamline the management of password updates in your organization.

Summary:

MySQL 8 offers a range of access control and account management features to enhance database security. Password expiry enforces regular password updates, while password validation prevents weak passwords. Account lock/unlock defends against brute force attacks, and privilege management simplifies access control using roles. Dual passwords streamline password rotation without downtime. Strengthen your MySQL security with these features and ensure data protection. Contact Mafiree for expert database support services and leverage their expertise to optimize and secure your MySQL environment.

Looking for professional database support services? Trust Mafiree for expert assistance in optimizing and securing your MySQL environment. Contact us today to safeguard your valuable data and enhance the performance of your database system

Author Bio

sukan

Sukan is Database Team Lead at Mafiree with over a decade of experience in database systems, architecture, and performance optimization. He specializes in MySQL, MongoDB, TiDB, and ClickHouse, developing architectural improvements that make data platforms faster, more efficient, and cost-effective. Sukan writes about practical database engineering topics, real-world performance tuning, data replication, and high-scale system design, drawing from extensive hands-on experience solving complex technical challenges.

Name *

Email *

Comment *

Comments

Divine Steve June 09, 2023

Great article! Loved your tips on access control & account management. Dual password authentication Role privilege, Account lock, and expiry keyword are all key. Looking forward to more content like this!

A MySQL deadlock occurs when two or more transactions hold locks that each other needs, creating a circular dependency that prevents them from proceeding. InnoDB detects the deadlock and rolls back one transaction; applications typically receive MySQL error 1213 and should safely retry the transaction. To diagnose a deadlock, start with SHOW ENGINE INNODB STATUS; for recurring incidents, use innodb_print_all_deadlocks and Performance Schema data_locks and data_lock_waits to investigate lock relationships. Common causes include inconsistent transaction ordering, long-running transactions, and inefficient indexing or locking patterns. The most effective fixes are to keep transactions short, access shared resources in a consistent order, optimize the indexes used by locking statements, and implement safe retry handling.

279 views

MySQL Consulting vs In-House DBA: Which Is Right for Your Team?

Choose an in-house DBA when you need continuous database ownership, deep knowledge of internal systems, and day-to-day operational control. Choose MySQL consulting when you need specialized expertise, performance tuning, migrations, complex troubleshooting, or additional database capacity without hiring a full-time specialist. A hybrid model can be the best fit when your team handles routine database operations internally but needs external expertise for complex projects, peak workloads, or specialized MySQL problems. The right choice depends on workload consistency, technical complexity, required coverage, internal expertise, scalability, and total cost—not simply the hourly or salary cost. Mafiree's managed database services provide ongoing MySQL support alongside specialized database expertise for organizations that need more flexibility than a purely in-house model.

217 views

MySQL Consultant: When to Call in a Performance Expert

Most MySQL slowdowns are fixable in-house. A smaller set of problems keep coming back, keep growing, or sit outside what routine tuning can solve — and that's the line worth knowing before an incident forces the decision for you.

302 views

MySQL Performance Issues: 7 Signs You Need Professional Tuning

MySQL performance issues are rarely sudden — they build over time through slow queries, InnoDB buffer pool misses, replication lag, lock contention, thread pile-ups, tablespace bloat, and unstable query plans. This post identifies the seven most reliable signs that your MySQL environment needs professional DBA attention, with diagnostic queries and remediation guidance for each.

13143 views

Column-Level Security: Enterprise Data Protection Without the Infrastructure Overhead

Column-level security is a native database feature that restricts access to specific table columns by user role. Mafiree implemented this for a client as a cost-effective replacement for a planned CDC replication architecture that existed solely to strip sensitive columns. The result: zero additional infrastructure, single source of truth, full GDPR/HIPAA compliance posture, and validated in production with no performance impact.

1132 views

MySQL Schema Migration Without Downtime: A Real Fintech Case Study

Schema changes on large MySQL tables can bring production systems to a halt if not handled correctly. This case study walks through how Mafiree helped a fintech client execute a zero-downtime MySQL schema migration on a 500M+ row production database — covering the real challenges faced, the three-phase tool strategy using gh-ost, pt-online-schema-change, and MySQL 8.0 INSTANT DDL, production configuration settings with performance benchmarks, and best practices for safely evolving your MySQL schema without impacting users

4229 views

MySQL Architecture Explained: Performance Tuning & Troubleshooting Guide

MySQL features a unique tiered architecture that separates query processing from data storage through its pluggable storage engine model. This guide explores the core components—from connection handling and the SQL optimizer to the physical storage of data on disk. By understanding how engines like InnoDB provide ACID compliance and row-level locking, you can significantly improve your database's scalability. We also break down the query execution workflow and provide actionable tips for performance tuning, such as optimizing the buffer pool. Whether you're managing a replica set or a standalone instance, mastering MySQL’s internal structure is essential for building high-performance applications.

2231 views

MariaDB vs MySQL: What's Different in 2026 and Which One Should You Use

Discover how MariaDB 11.x is redefining open-source databases with cutting-edge features like system-versioned tables, native AI-ready vector support, UUIDv7 for scalable inserts, and enterprise-grade security; all in the Community Edition, without the paywall.

1647 views

MySQL’s Connection Control Plugin for Brute-Force Protection: A DBA Security Guide

“Fortify Your MySQL Security: Slow Down Attackers with Connection Control Plugin” Learn how the MySQL Connection Control Plugin helps defend against brute-force login attempts by introducing intelligent, progressive delays—without locking out legitimate users.

13122 views

MySQL 8.4 Migration: Key Changes for IT Leaders

Explore the 8 critical changes in MySQL 8.4 you need to know before migrating your production environment. From authentication updates to InnoDB defaults, this release brings significant operational implications for developers and DBAs.

116 views

Load Balancing in MySQL: Read and Write Traffic Optimization Using MySQL Router

Optimize MySQL traffic with MySQL Router — smart load balancing made easyRouter Architecture image widget

1884 views

Check Constraints in MySQL

Master Database Integrity with Check Constraints: Improve Data Quality and System Performance

13125 views

Guarding Your Account: 5 Essential Tips to Evade Common Password Mistakes.

How to Secure your mysql database with the mysql validate password component

3503 views

Significance of using Invisible Primary key (GIPK) with MySQL 8.0

How Generated Invisible Primary Keys (GIPK) Can Boost Your Database Efficiency

3667 views

Optimize Your Database: Exploring MySQL 8.0 JSON Functions

This blog highlights the some of the important JSON function in MySQL 8.0 with examples. Level up your SQL skills with MySQL 8.0's JSON functions.

5097 views

Best practices on enabling Audit log on MySQL, MariaDB and ProxySQL

Importance of audit log in MySQL, MariaDB and ProxySQL. Best practices to enable audit log

5122 views

High Availability for MySQL using Orchestrator and ProxySQL

Auto slave promotion without any DBAs intervention using Orchestrator and ProxySQL. If you are looking for a HA solution without going for synchronous replication or Aws RDS then Orchestrator with ProxySQL is a great choice.

3343 views

Understanding the Spider Storage Engine

MariaDB Spider is a storage engine that supports federation and sharding across multiple MySQL or MariaDB servers. It helps distribute data and workloads, making it useful for scaling large datasets, balancing traffic, and improving data retrieval through parallel processing.

1702 views

Avoid deadlocks, certification failures on Galera Cluster using ProxySQL

Dealing with deadlocks, certification failures on Galera cluster by running single node writer using ProxySQL

7019 views

MySQL Character Set: UTF8MB4 vs UTF8 and How to Choose the Right Encoding

Choosing the right MySQL character set is critical for ensuring accurate data storage, multilingual support, and proper text comparison. While the legacy utf8 (utf8mb3) character set supports only up to 3-byte characters, it fails to handle modern Unicode requirements such as emojis and certain international symbols. In contrast, utf8mb4 provides full 4-byte Unicode support, making it the recommended standard for modern applications. This guide explains the differences between UTF8MB4 and UTF8, their impact on storage and performance, and provides step-by-step instructions to configure and migrate your MySQL databases safely.

5758 views

Real time data streaming from MariaDB to Columnstore

How real time data from transactional database(mariadb) to analytic database(columnstore) is achieved using MariaDB's maxscale binlog streaming

1442 views

Fixing the MariaDB Galera Bug: WSREP Lock Waits

A MariaDB Galera cluster can experience severe stalls when concurrent updates to the same primary key trigger an unexpected deadlock issue. This blog explains how we reproduced the issue in MariaDB 10.2, why the affected node became unresponsive, and the recovery and upgrade options used to resolve it.

4512 views