This page describes how to enable and use Cloud SQL built-in authentication.
For an overview, see Cloud SQL built-in database authentication.Before creating users
- Create a Cloud SQL instance. For more information, see Create instances.
- Enable password policies for the instance. For more information, see Instance password policies.
If you plan to use your database's administrative client to manage users, then do the following:
Connect the client to your instance. See Connection options for external applications.
Configure the default user on the instance by setting the password. See Set the password for the default user account.
Set the password for the default user account
When you create a new Cloud SQL instance, you must set a password for the default user account before you can connect to the instance.
For Cloud SQL for MySQL, the default user isroot@%
This signifies a database user with a username of root
which can connect from any host (@%
).
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- Select Users from the SQL navigation menu.
- Find the
root
user and then select Change password from the more actions menu .Consider the listed stipulations for the password, which are derived from the password policy set for the instance.
- Provide a strong password that you can remember and click OK.
gcloud
Use the
gcloud sql users set-password
command as follows to set the password for the default user.
Replace INSTANCE_NAME with the name of the instance before running the command.
gcloud sql users set-password root \ --host=% \ --instance=INSTANCE_NAME \ --prompt-for-password
REST v1
To update the password for the default user account, use a PUT request with the users:update method.
Before using any of the request data, make the following replacements:
- project-id: Your project ID
- instance-id: The desired instance ID
- password: The password for the user
HTTP method and URL:
PUT https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/users?name=root&host=%25
Request JSON body:
{ "name": "root", "password": "password" }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
{ "kind": "sql#operation", "targetLink": "https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id", "status": "DONE", "user": "user@example.com", "insertTime": "2020-02-07T22:38:41.217Z", "startTime": "2020-02-07T22:38:41.217Z", "endTime": "2020-02-07T22:38:44.801Z", "operationType": "UPDATE_USER", "name": "operation-id", "targetId": "instance-id", "selfLink": "https://sqladmin.googleapis.com/v1/projects/project-id/operations/operation-id", "targetProject": "project-id" }
REST v1beta4
To update the password for the default user account, use a PUT request with the users:update method.
Before using any of the request data, make the following replacements:
- project-id: Your project ID
- instance-id: The desired instance ID
- password: The password for the user
HTTP method and URL:
PUT https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/users?name=root&host=%25
Request JSON body:
{ "name": "root", "password": "password" }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
{ "kind": "sql#operation", "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id", "status": "DONE", "user": "user@example.com", "insertTime": "2020-02-07T22:38:41.217Z", "startTime": "2020-02-07T22:38:41.217Z", "endTime": "2020-02-07T22:38:44.801Z", "operationType": "UPDATE_USER", "name": "operation-id", "targetId": "instance-id", "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id", "targetProject": "project-id" }
Create a user
After setting up the default user account, you can create other users.
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- Select Users from the SQL navigation menu.
- Click Add user account.
In the Add a user account to instance instance_name page, you can choose whether the user authenticates with the built-in database method (username and password) or as an IAM user.
- Select Built-in authentication (the default) and add the
following information:
- A User name.
- Optional. A Password. Provide a strong password that you can remember.
- Optional. The user password policy.
- In the Host name section, the default is Allow any host, which means that the user can connect from any IP address. Optionally, select Restrict host by IP address or address range and enter an IP address or address range in the Host section. The user can then connect only from the IP address or addresses specified.
- Click Add.
Users created on instances using MySQL 8.0 or later and Cloud SQL's authentication method
are granted the cloudsqlsuperuser
role automatically and have the following privileges
associated with this role: CREATEROLE
, CREATEDB
, and LOGIN
.
Users created on instances using MySQL 5.7 and Cloud SQL's authentication method are granted all privileges except for FILE
and SUPER
automatically. If you need to change the
privileges for these users, then use the
GRANT or
REVOKE command in the
mysql
client.
For more information about these user accounts and privileges, see Other MySQL user accounts.
gcloud
To create a user, use the
gcloud sql users create
command.
Replace the following:
- USER_NAME: The user name.
- HOST: The user's host name as a specific IP address, address
range, or any host (
%
). - INSTANCE_NAME: The name of the instance.
- PASSWORD: The password for the user.
gcloud sql users create USER_NAME \ --host=HOST \ --instance=INSTANCE_NAME \ --password=PASSWORD
Users created on instances using MySQL 8.0 or later and Cloud SQL's authentication method
are granted the cloudsqlsuperuser
role automatically and have the following privileges
associated with this role: CREATEROLE
, CREATEDB
, and LOGIN
.
Users created on instances using MySQL 5.7 and Cloud SQL's authentication method are granted all privileges except for FILE
and SUPER
automatically. If you need to change the
privileges for these users, then use the
GRANT or
REVOKE command in the
mysql
client.
For more information about these user accounts and privileges, see Other MySQL user accounts.
User name length limits are the same for Cloud SQL as for on-premises MySQL; 32 characters for MySQL 8.0 and later, 16 characters for earlier versions.
When you create a user, you can add user password policy parameters.
Terraform
To create a user, use a Terraform resource.
Apply the changes
To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.
Prepare Cloud Shell
- Launch Cloud Shell.
-
Set the default Google Cloud project where you want to apply your Terraform configurations.
You only need to run this command once per project, and you can run it in any directory.
export GOOGLE_CLOUD_PROJECT=PROJECT_ID
Environment variables are overridden if you set explicit values in the Terraform configuration file.
Prepare the directory
Each Terraform configuration file must have its own directory (also called a root module).
-
In Cloud Shell, create a directory and a new
file within that directory. The filename must have the
.tf
extension—for examplemain.tf
. In this tutorial, the file is referred to asmain.tf
.mkdir DIRECTORY && cd DIRECTORY && touch main.tf
-
If you are following a tutorial, you can copy the sample code in each section or step.
Copy the sample code into the newly created
main.tf
.Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.
- Review and modify the sample parameters to apply to your environment.
- Save your changes.
-
Initialize Terraform. You only need to do this once per directory.
terraform init
Optionally, to use the latest Google provider version, include the
-upgrade
option:terraform init -upgrade
Apply the changes
-
Review the configuration and verify that the resources that Terraform is going to create or
update match your expectations:
terraform plan
Make corrections to the configuration as necessary.
-
Apply the Terraform configuration by running the following command and entering
yes
at the prompt:terraform apply
Wait until Terraform displays the "Apply complete!" message.
- Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.
Delete the changes
To delete your changes, do the following:
- To disable deletion protection, in your Terraform configuration file set the
deletion_protection
argument tofalse
.deletion_protection = "false"
- Apply the updated Terraform configuration by running the following command and
entering
yes
at the prompt:terraform apply
-
Remove resources previously applied with your Terraform configuration by running the following command and entering
yes
at the prompt:terraform destroy
REST v1
To create a user, use a POST request with the users:insert method.
Before using any of the request data, make the following replacements:
- project-id: Your project ID
- instance-id: The desired instance ID
- user-id: The ID of the user
- password: The password for the user
HTTP method and URL:
POST https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/users
Request JSON body:
{ "name": "user-id", "password": "password" }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
{ "kind": "sql#operation", "targetLink": "https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id", "status": "DONE", "user": "user@example.com", "insertTime": "2020-02-07T22:44:16.656Z", "startTime": "2020-02-07T22:44:16.686Z", "endTime": "2020-02-07T22:44:20.437Z", "operationType": "CREATE_USER", "name": "operation-id", "targetId": "instance-id", "selfLink": "https://sqladmin.googleapis.com/v1/projects/project-id/operations/operation-id", "targetProject": "project-id" }
Users created on instances using MySQL 8.0 or later and Cloud SQL's authentication method
are granted the cloudsqlsuperuser
role automatically and have the following privileges
associated with this role: CREATEROLE
, CREATEDB
, and LOGIN
.
Users created on instances using MySQL 5.7 and Cloud SQL's authentication method are granted all privileges except for FILE
and SUPER
automatically. If you need to change the
privileges for these users, then use the
GRANT or
REVOKE command in the
mysql
client.
For more information about these user accounts and privileges, see Other MySQL user accounts.
User name length limits are the same for Cloud SQL as for on-premises MySQL; 32 characters for MySQL 8.0 and later, 16 characters for earlier versions.
When you create a user, you can add user password policy parameters.
REST v1beta4
To create a user, use a POST request with the users:insert method.
Before using any of the request data, make the following replacements:
- project-id: Your project ID
- instance-id: The desired instance ID
- user-id: The ID of the user
- password: The password for the user
HTTP method and URL:
POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/users
Request JSON body:
{ "name": "user-id", "password": "password" }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
{ "kind": "sql#operation", "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id", "status": "DONE", "user": "user@example.com", "insertTime": "2020-02-07T22:44:16.656Z", "startTime": "2020-02-07T22:44:16.686Z", "endTime": "2020-02-07T22:44:20.437Z", "operationType": "CREATE_USER", "name": "operation-id", "targetId": "instance-id", "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id", "targetProject": "project-id" }
Users created on instances using MySQL 8.0 or later and Cloud SQL's authentication method
are granted the cloudsqlsuperuser
role automatically and have the following privileges
associated with this role: CREATEROLE
, CREATEDB
, and LOGIN
.
Users created on instances using MySQL 5.7 and Cloud SQL's authentication method are granted all privileges except for FILE
and SUPER
automatically. If you need to change the
privileges for these users, then use the
GRANT or
REVOKE command in the
mysql
client.
For more information about these user accounts and privileges, see Other MySQL user accounts.
User name length limits are the same for Cloud SQL as for on-premises MySQL; 32 characters for MySQL 8.0 and later, 16 characters for earlier versions.
When you create a user, you can add user password policy parameters.
mysql Client
- To create a user, at the
mysql
prompt, use the following CREATE USER statement:CREATE USER 'USER_NAME'@'%' IDENTIFIED BY 'PASSWORD';
Optionally, add the user password policy parameters.
- You can confirm the user creation by displaying the user table:
SELECT user, host FROM mysql.user;
For a Second Generation instance, the output looks similar to this example:+----------+-----------+ | user | host | +----------+-----------+ | root | % | | newuser | % | +----------+-----------+ 1 row in set (0.01 sec)
- Give the user privileges with the
GRANT
statement. For more information, see Privileges Provided by MySQL. -
Flush the
mysql.user
table to ensure that the change persists:FLUSH TABLES mysql.user;
Set a user password policy
You can set a password policy with the built-in authentication type.
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- Select Users from the SQL navigation menu.
- Click more actions for the user whose policy you want to change.
- Select Edit password policy.
- In the Password policy section, select one or more of the
following options:
- Set password to expire: Specifies the number of days after which the password expires and the user needs to create a new one.
- Lock after failed attempts: Specifies the number of times that
a user can try the password incorrectly before the account is locked.
Supported only on Cloud SQL for MySQL 8.0 and later.
- Require current password when password is changed: Requires users to enter their existing password when attempting to change it.
gcloud
To set the user password policy, use the
gcloud sql users set-password-policy
command.
Use the --password-policy-enable-password-verification
to make it
mandatory for users to enter their existing password when attempting to change
the password. To disable this parameter, use --no-password-policy-enable-password-verification
.
Replace the following:
- USER_NAME: The user name.
- INSTANCE_NAME: The name of the instance.
- HOST: The user's host name as a specific IP address, address
range, or any host (
%
). - PASSWORD_POLICY_ALLOWED_FAILED_ATTEMPTS: Optional: The number of
times that a user can try the password incorrectly before the account is
locked. Use
--password-policy-enable-failed-attempts-check
to enable and--no-password-policy-enable-failed-attempts-check
to disable the check. - PASSWORD_POLICY_PASSWORD_EXPIRATION_DURATION: Optional: Specifies the number of days after which the password expires and the user needs to create a new one.
gcloud sql users set-password-policy USER_NAME \ --instance=INSTANCE_NAME \ --host=HOST \ --password-policy-enable-failed-attempts-check \ --password-policy-allowed-failed-attempts=PASSWORD_POLICY_ALLOWED_FAILED_ATTEMPTS \ --password-policy-password-expiration-duration=PASSWORD_POLICY_PASSWORD_EXPIRATION_DURATION \ --password-policy-enable-password-verification
To remove a user password policy, use the --clear-password-policy
parameter.
gcloud sql users set-password-policy USER_NAME \ --instance=INSTANCE_NAME \ --host=HOST \ --clear-password-policy
To view the user password policy, see List users.
REST v1
To set a user password policy, use a PUT request with the users:update method.
Before using any of the request data, make the following replacements:
- PROJECT_ID: your project ID
- INSTANCE_ID: the instance ID
- USER_ID: the ID of the user
- PASSWORD: the password of the user
- FAILED_ATTEMPTS_CHECK: set to
true
to enable a check for the number of failed attempts to log in after which the account is locked - NUMBER_OF_ATTEMPTS: the number of failed attempts to log in after which the account is locked
- PASSWORD_EXPIRATION_DURATION: the number of days after which the password expires and the user needs to create a new one
- VERIFY_PASSWORD: set to
true
to make it mandatory for users to enter their existing password when attempting to change the password
HTTP method and URL:
PUT https://sqladmin.googleapis.com/sql/v1/projects/PROJECT_ID/instances/INSTANCE_ID/users?name=USER_ID
Request JSON body:
{ "name": "USER_ID", "password": "PASSWORD", "data": { "passwordValidationUserPolicy" : { { "enableFailedAttemptsCheck" : "FAILED_ATTEMPTS_CHECK", "allowedFailedAttempts" : "NUMBER_OF_ATTEMPTS", "passwordExpirationDuration" : "PASSWORD_EXPIRATION_DURATION", "enablePasswordVerification" : "VERIFY_PASSWORD" } }, } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
To view the user password policy, see List users.
REST v1beta4
To set a user password policy, use a PUT request with the users:update method.
Before using any of the request data, make the following replacements:
- PROJECT_ID: your project ID
- INSTANCE_ID: the instance ID
- USER_ID: the ID of the user
- PASSWORD: the password of the user
- FAILED_ATTEMPTS_CHECK: set to
true
to enable a check for the number of failed attempts to log in after which the account is locked - NUMBER_OF_ATTEMPTS: the number of failed attempts to log in after which the account is locked
- PASSWORD_EXPIRATION_DURATION: the number of days after which the password expires and the user needs to create a new one
- VERIFY_PASSWORD: set to
true
to make it mandatory for users to enter their existing password when attempting to change the password
HTTP method and URL:
PUT https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID/users?name=USER_ID
Request JSON body:
{ "name": "USER_ID", "password": "PASSWORD", "data": { "passwordValidationUserPolicy" : { { "enableFailedAttemptsCheck" : "FAILED_ATTEMPTS_CHECK", "allowedFailedAttempts" : "NUMBER_OF_ATTEMPTS", "passwordExpirationDuration" : "PASSWORD_EXPIRATION_DURATION", "enablePasswordVerification" : "VERIFY_PASSWORD" } }, } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
To view the user password policy, see List users.
mysql Client
To set a user password policy, at the mysql
prompt, use the following
ALTER USER statement:
ALTER USER USER_NAME FAILED_LOGIN_ATTEMPTS ALLOWED_FAILED_ATTEMPTS PASSWORD EXPIRE INTERVAL PASSWORD_EXPIRATION_DURATION DAY PASSWORD REQUIRE CURRENT;
Replace the following:
- USER_NAME: The user name.
- ALLOWED_FAILED_ATTEMPTS: Optional: The number of times that a user can try the password incorrectly before the account is locked.
- PASSWORD_EXPIRATION_DURATION: Optional: Specifies the number of days after which the password expires and the user needs to create a new one.
Use the PASSWORD REQUIRE CURRENT
option to make it
mandatory for users to enter their existing password when attempting to change
the password.
To view the user password policy, see List users.
List users
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- Select Users from the SQL navigation menu.
The list shows the
User name
,Host name
, andAuthentication
type for each user.Additionally, for the built-in authentication type, the
Password status
is also indicated.
gcloud
Use the gcloud sql users list command to list the users for this instance:
gcloud sql users list \ --instance=INSTANCE_NAME
The command returns the Name
, Host
, and
authentication Type
for each user.
Additionally, for the built-in authentication type, the password policy settings and status are returned. For example:
NAME HOST TYPE PASSWORD_POLICY user1 BUILT_IN {'allowedFailedAttempts': 2, 'enableFailedAttemptsCheck': True, 'passwordExpirationDuration': '7d', 'status': { 'locked': True, 'passwordExpirationTime': '2022-07-01T19:53:45.822742904Z' } }
REST v1
To list the users defined for an instance, use a GET request with the users:list method.
Before using any of the request data, make the following replacements:
- project-id: Your project ID
- instance-id: The desired instance ID
HTTP method and URL:
GET https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/users
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
{ "kind": "sql#usersList", "items": [ { "kind": "sql#user", "etag": "--redacted--", "name": "sqlserver", "host": "", "instance": "instance-id", "project": "project-id", "sqlserverUserDetails": { "serverRoles": [ "CustomerDbRootRole" ] } }, { "kind": "sql#user", "etag": "--redacted--", "name": "user-id-1", "host": "", "instance": "instance-id", "project": "project-id", "sqlserverUserDetails": { "serverRoles": [ "CustomerDbRootRole" ] } }, { "kind": "sql#user", "etag": "--redacted--", "name": "user-id-2", "host": "", "instance": "instance-id", "project": "project-id", "sqlserverUserDetails": { "serverRoles": [ "CustomerDbRootRole" ] } }, { ... }, { ... } ] }
If a user password policy
was set, the items
section of the response includes a passwordPolicy
section. The following code sample shows the passwordPolicy
section.
{ ... "passwordValidationUserPolicy" : { { "enableFailedAttemptsCheck" : true, "allowedFailedAttempts" : 8, "passwordExpirationDuration" : "7d", "enablePasswordVerification" : true } }, ... }
REST v1beta4
To list the users defined for an instance, use a GET request with the users:list method.
Before using any of the request data, make the following replacements:
- project-id: Your project ID
- instance-id: The desired instance ID
HTTP method and URL:
GET https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/users
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
{ "kind": "sql#usersList", "items": [ { "kind": "sql#user", "etag": "--redacted--", "name": "sqlserver", "host": "", "instance": "instance-id", "project": "project-id", "sqlserverUserDetails": { "serverRoles": [ "CustomerDbRootRole" ] } }, { "kind": "sql#user", "etag": "--redacted--", "name": "user-id-1", "host": "", "instance": "instance-id", "project": "project-id", "sqlserverUserDetails": { "serverRoles": [ "CustomerDbRootRole" ] } }, { "kind": "sql#user", "etag": "--redacted--", "name": "user-id-2", "host": "", "instance": "instance-id", "project": "project-id", "sqlserverUserDetails": { "serverRoles": [ "CustomerDbRootRole" ] } }, { ... }, { ... } ] }
If a user password policy
was set, the items
section of the response includes a passwordPolicy
section. The following code sample shows the passwordPolicy
section.
{ ... "passwordValidationUserPolicy" : { { "enableFailedAttemptsCheck" : true, "allowedFailedAttempts" : 8, "passwordExpirationDuration" : "7d", "enablePasswordVerification" : true } }, ... }
mysql Client
To list MySQL users, at the mysql
prompt, use the following
SELECT statement:
SELECT user, host FROM mysql.user;
For a Second Generation instance with only the root
user account configured,
the output looks similar to this example:
+------+-----------+ | user | host | +------+-----------+ | root | % | +------+-----------+ 1 row in set (0.01 sec)
This example shows the users for an instance with
the root
user. This user can connect from any host (%
). The password field shows the hash of the password.
Change a user password
User passwords can be changed in one of the following ways.
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- Select Users from the SQL navigation menu.
- Click more actions for the user you want to update.
- Select Change password.
- Specify a new password.
Additionally, if you want to continue to use your older password, select the Retain current password checkbox.
- Click OK.
gcloud
Use the
gcloud sql users set-password
command to change a password.
Replace the following:
- USER_NAME: The user name.
- HOST: The user's host name as a specific IP address, address
range, or any host (
%
). - INSTANCE_NAME: The name of the instance.
- PASSWORD: A password. It must meet the requirements of password policies, if set.
Optionally, for MySQL 8.0 and later, you can continue to allow the user to use the older
password with the --retain-password
option. To discard the
older password, use the --discard-dual-password
option.
gcloud sql users set-password USER_NAME \ --host=HOST \ --instance=INSTANCE_NAME \ --password=PASSWORD
REST v1
To change a user password, use a PUT request with the users:update method.
The following request updates the password for the user account
user_name'@'%
. If your user has a different host, you must
modify the call with the correct host.
Before using any of the request data, make the following replacements:
- project-id: Your project ID
- instance-id: The desired instance ID
- user-id: The ID of the user
- password: The new password for the user
- dual-password: One of these enum values:
DUAL_PASSWORD
: The user can continue to use the older password.NO_DUAL_PASSWORD
: The user cannot use the older password.NO_MODIFY_DUAL_PASSWORD
: The dual password status stays unchanged.
HTTP method and URL:
PUT https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/users?name=user-id
Request JSON body:
{ "name": "user-id", "password": "password", "retainedPassword" : "dual-password" }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
{ "kind": "sql#operation", "targetLink": "https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id", "status": "DONE", "user": "user@example.com", "insertTime": "2020-02-07T22:38:41.217Z", "startTime": "2020-02-07T22:38:41.217Z", "endTime": "2020-02-07T22:38:44.801Z", "operationType": "UPDATE_USER", "name": "operation-id", "targetId": "instance-id", "selfLink": "https://sqladmin.googleapis.com/v1/projects/project-id/operations/operation-id", "targetProject": "project-id" }
REST v1beta4
To change a user password, use a PUT request with the users:update method.
The following request updates the password for the user account
user_name'@'%
. If your user has a different host, you must
modify the call with the correct host.
Before using any of the request data, make the following replacements:
- project-id: Your project ID
- instance-id: The desired instance ID
- user-id: The ID of the user
- password: The new password for the user
- dual-password: One of these enum values:
DUAL_PASSWORD
: The user can continue to use the older password.NO_DUAL_PASSWORD
: The user cannot use the older password.NO_MODIFY_DUAL_PASSWORD
: The dual password status stays unchanged.
HTTP method and URL:
PUT https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/users?name=user-id
Request JSON body:
{ "name": "user-id", "password": "password", "retainedPassword" : "dual-password" }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
{ "kind": "sql#operation", "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id", "status": "DONE", "user": "user@example.com", "insertTime": "2020-02-07T22:38:41.217Z", "startTime": "2020-02-07T22:38:41.217Z", "endTime": "2020-02-07T22:38:44.801Z", "operationType": "UPDATE_USER", "name": "operation-id", "targetId": "instance-id", "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id", "targetProject": "project-id" }
mysql Client
- To change the password, at the
mysql
prompt, use the following SET PASSWORD statement:SET PASSWORD FOR USER_NAME = PASSWORD('auth_string');
- Flush the
mysql.user
table to ensure that the change persists:FLUSH TABLES mysql.user;
If a user is locked out due to password policy settings, change the password to unlock them. Ensure that passwords, when changed, adhere to the password policy.
Remove a user password policy
You can remove a password policy from a user that has the built-in authentication type.
gcloud
To remove the user password policy, use the
gcloud sql users set-password-policy
command and the --clear-password-policy
parameter.
Replace the following:
- USER_NAME: the user name
- INSTANCE_NAME: the name of the instance
- HOST: the user's host name as a specific IP address, address
range, or any host (
%
)
gcloud sql users set-password-policy USER_NAME \ --instance=INSTANCE_NAME \ --host=HOST \ --clear-password-policy
REST v1
To remove a user password policy, use a PUT request with the users:update method.
Before using any of the request data, make the following replacements:
- PROJECT_ID: your project ID
- INSTANCE_ID: the instance ID
- USER_ID: the ID of the user
- PASSWORD: the password of the user
HTTP method and URL:
PUT https://sqladmin.googleapis.com/sql/v1/projects/PROJECT_ID/instances/INSTANCE_ID/users?name=USER_ID
Request JSON body:
{ "name": "USER_ID", "password": "PASSWORD", "data": { "passwordValidationUserPolicy" : {} } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
REST v1beta4
To remove a user password policy, use a PUT request with the users:update method.
Before using any of the request data, make the following replacements:
- PROJECT_ID: your project ID
- INSTANCE_ID: the instance ID
- USER_ID: the ID of the user
- PASSWORD: the password of the user
HTTP method and URL:
PUT https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID/users?name=USER_ID
Request JSON body:
{ "name": "USER_ID", "password": "PASSWORD", "data": { "passwordValidationUserPolicy" : {} } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
Remove a user
The default user can remove users.
Before removing a user, you must drop all the objects it owns or reassign their ownership, and revoke any privileges the role has been granted on other objects.
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- To open the Overview page of an instance, click the instance name.
- Select Users from the SQL navigation menu.
- Click more actions for the user you want to remove.
- Select Remove and then select Remove again.
gcloud
Use the
gcloud sql users delete
command to remove a user.
Replace the following:
- USER_NAME: The user name.
- HOST: The user's host name as a specific IP address, address
range, or any host (
%
). - INSTANCE_NAME: The name of the instance.
gcloud sql users delete USER_NAME \ --host=HOST \ --instance=INSTANCE_NAME
REST v1
The request below uses the users:delete method to delete the specified user account.
Before using any of the request data, make the following replacements:
- PROJECT_ID: Your project ID
- INSTANCE_ID: The desired instance ID
- USERNAME: The email address for the user or service account
HTTP method and URL:
DELETE https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID/users?host=&name=USERNAME
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
{ "kind": "sql#operation", "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID", "status": "DONE", "user": "user@example.com", "insertTime": "2020-02-07T22:38:41.217Z", "startTime": "2020-02-07T22:38:41.217Z", "endTime": "2020-02-07T22:38:44.801Z", "operationType": "DELETE_USER", "name": "OPERATION_ID", "targetId": "INSTANCE_ID", "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID", "targetProject": "PROJECT_ID" }
REST v1beta4
The request below uses the users:delete method to delete the specified user account.
Before using any of the request data, make the following replacements:
- PROJECT_ID: Your project ID
- INSTANCE_ID: The desired instance ID
- USERNAME: The email address for the user or service account
HTTP method and URL:
DELETE https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID/users?host=&name=USERNAME
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
{ "kind": "sql#operation", "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID", "status": "DONE", "user": "user@example.com", "insertTime": "2020-02-07T22:38:41.217Z", "startTime": "2020-02-07T22:38:41.217Z", "endTime": "2020-02-07T22:38:44.801Z", "operationType": "DELETE_USER", "name": "OPERATION_ID", "targetId": "INSTANCE_ID", "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID", "targetProject": "PROJECT_ID" }
mysql Client
- To delete a user, at the
mysql
prompt, use the following DROP USER statement:DROP USER 'USER_NAME'@'HOST_NAME';
- Flush the
mysql.user
table to ensure that the change persists:FLUSH TABLES mysql.user;
Update user properties
To update user properties such as host or privileges, you must use themysql
client. For more information, see
MySQL User Account Management in the MySQL
documentation.
What's next
- Learn more about how Cloud SQL works with users.
- Read the MySQL documentation about user creation.
- Learn about connecting to instances.