Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Configure Root Password for MySQL Database Access

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: High (not acceptable risk)
Rule ID: CloudSQL-016

Ensure that your Google Cloud MySQL database instances do not allow anyone to connect with administrative privileges only, without needing a root password. To prevent unauthorized access to your MySQL database instances and follow security best practices, make sure that the administrative user (root by default) has a password set.

This rule resolution is part of the Conformity Security & Compliance tool for GCP.

Security

By enabling "No Password" setting during MySQL instance creation and choose not to provide an administrative (root) password allows anyone to connect to the MySQL database instance using only Google Cloud administrative privileges. To adhere to security best practices, set a root password to ensure only authorized users have these privileges.

Audit

To determine if your MySQL database instances allow anyone to connect with administrative privileges only, without needing a password, perform the following operations:

Note: The "No Password" setting is exposed only at the time of MySQL instance creation. Once the instance is created, the Google Cloud Platform (GCP) Console does not expose the setting to confirm whether a password for an administrative user is set to a MySQL instance. Therefore, checking for the existence of the administrative (root) password using GCP Management Console is not supported.

Using GCP CLI

01 Run projects list command (Windows/macOS/Linux) using custom query filters to list the IDs of all the Google Cloud Platform (GCP) projects available in your Google Cloud account: 

gcloud projects list
	--format="table(projectId)"

02 The command output should return the requested GCP project IDs: 

PROJECT_ID
cc-web-project-112233
cc-mobile-project-123123

03 Run sql instances list command (Windows/macOS/Linux) using custom filtering to list the name and the primary public IP address of each MySQL database instance provisioned for the selected Google Cloud project: 

gcloud sql instances list
	--project cc-web-project-112233
	--filter='DATABASE_VERSION:MYSQL*'
	--format="(NAME,PRIMARY_ADDRESS)"

04 The command output should return the requested database identification information: 

NAME                   PRIMARY_ADDRESS
cc-web-mysql-instance  <instance-ip-address>
cc-int-mysql-instance  <instance-ip-address>

05 Run mysql command (Windows/macOS/Linux) using the primary public IP address of the MySQL database instance that you want to examine as value for the host (-h) parameter, to connect to the selected Google Cloud database instance: 

mysql -u root -h <instance-ip-address>

06 The mysql command should return a password prompt or an "Access denied" error: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is xxx
Server version: x.x.xxx

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
Enabled: False

If the mysql command returns the mysql> password prompt, as shown in the example above, the selected Google Cloud MySQL database instance allows access with administrative privileges only, without needing a password.

07 Repeat step no. 5 and 6 for each MySQL database instance created for the selected project.

08 Repeat steps no. 3 – 7 for each project available within your Google Cloud account.

Remediation / Resolution

To configure a password for the administrative (root) user in order to deny MySQL database access to anyone that is trying to connect with administrative privileges, perform the following operations:

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click inside the Filter tree box, select Type and MySQL then press Enter, to return only the MySQL database instances available for the selected project.

05 Click on the name (ID) of the database instance that you want to reconfigure.

06 In the navigation panel, select Users to access the list with all the users configured for the selected database instance.

07 Choose the root (administrator) user, click on the 3-dot button for user options menu, and select Change password.

08 Inside Change password for user root@% configuration box, enter a strong password in the New password box to set up a new password for the selected database root user. Click OK to apply the changes.

09 Update your database application(s) and client connection string(s) to use your new root password. Note that any existing connections to your MySQL database instance will not be terminated.

10 If required, repeat step no. 5 – 9 to configure the root user password for other MySQL database instances available within the selected project.

11 Repeat steps no. 2 – 10 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run sql users set-password command (Windows/macOS/Linux) using the name and the public IP address of the MySQL database instance that you want to reconfigure as identifier parameters (see Audit section part II to identify the right database details), to configure the root user password for the selected database instance: 

gcloud sql users set-password root
	--instance=cc-web-mysql-instance
	--host=<instance-ip-address>
	--prompt-for-password

02 The sql users set-password command request should return the required password prompt. Type the root user password and press Enter: 

Instance Password:

03 If the root password is successfully configured, the command output should return the following message: 

Updating Cloud SQL user...done.

04 Repeat step no. 1 – 3 to configure the root user password for other MySQL database instances available in the selected project.

05 Repeat steps no. 1 – 4 for each project created within your Google Cloud account.

References

Publication date Apr 12, 2021

Related CloudSQL rules