Access Control & Account Management In MySQL 8

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

1. create user 'appuser'@'localhost' identified by 'Appus3R' password expire;
2. 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.

1. 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

1. INSTALL COMPONENT 'file://component_validate_password';
2. create user 'user2'@'localhost' identified by 'pass';
3. 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:

1. 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.

1. 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

1. create role 'application_reads';
2. grant select on foo.* to application_reads;
3. 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

1. alter user 'user'@'localhost' identified by 'newpass1' retain current password;
2. 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