Creating and managing MySQL users

This page describes how to do the following for Cloud SQL instances:

  • Configure the default user account.
  • Create, delete, and update other user accounts.

For more information about how users work with Cloud SQL, see MySQL users.

Before you begin

Before completing the tasks below, create a Cloud SQL instance. For more information, see Creating instances.

If you plan to use your database's administrative client to manage users, you must have configured the default user on the instance. For more information, see Configuring the default account.

Additionally, you must have configured an administrative client connected to your instance. For more information, see Connection Options for External Applications.

Configuring the default user account

When you create a new Cloud SQL instance, you must configure the default user account before you can connect to the instance.

For Cloud SQL for MySQL, the default user is root@%.

To configure the default user:

Console

  1. In the Google Cloud Console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Click the instance name to open its Overview page.
  3. Select Users from the SQL navigation menu.
  4. Find the root user and then select Change password from the more actions menu More actions icon..
  5. Provide a strong password that you can remember and click Ok.

gcloud

Set the password for the default user:

gcloud sql users set-password root \
--host=% \
--instance=INSTANCE_NAME \
--prompt-for-password

REST v1

The following request uses the users:update method to update the root user account.

('root'@'%').

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

The following request uses the users:update method to update the root user account

('root'@'%').

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"
}

Creating a user

Console

  1. In the Google Cloud Console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Click the instance name to open its Overview page.
  3. Select Users from the SQL navigation menu.
  4. 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.

    • If you select Built-in authentication (the default), then add the following information:
      • A User name.
      • A Password (optional).
      • 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.

    • If you select Cloud IAM, then in the Member field you need to enter the email address of an existing IAM member of your Google Cloud project to associate with the user.
  5. Click Add.

Users created on instances using MySQL 8.0 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.

gcloud

Create the user:

gcloud sql users create USER_NAME \
--host=HOST \
--instance=INSTANCE_NAME \
--password=PASSWORD

Users created on instances using MySQL 8.0 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.

User name length limits are the same for Cloud SQL as for on-premises MySQL; 32 characters for MySQL 8.0, 16 characters for earlier versions.

REST v1

The request below uses the users:insert method to create a user account 'user_name'.

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

User name length limits are the same for Cloud SQL as for on-premises MySQL; 32 characters for MySQL 8.0, 16 characters for earlier versions.

REST v1beta4

The request below uses the users:insert method to create a user account 'user_name'.

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

User name length limits are the same for Cloud SQL as for on-premises MySQL; 32 characters for MySQL 8.0, 16 characters for earlier versions.

mysql Client

  1. At the mysql prompt, create the user:
      CREATE USER 'USER_NAME'@'%'
         IDENTIFIED BY 'PASSWORD';
      
  2. 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)
    
  3. Give the user privileges with the GRANT statement. For more information, see Privileges Provided by MySQL.
  4. Flush the mysql.user table to ensure that the change persists:
      FLUSH TABLES mysql.user;
    

Changing a user password

To change a user password:

Console

  1. In the Google Cloud Console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Click the instance name to open its Overview page.
  3. Select Users from the SQL navigation menu.
  4. Click more actions More actions icon. for the user you want to update.
  5. Select Change password, specify a new password, and click OK.

gcloud

Update the password:

gcloud sql users set-password USER_NAME \
--host=HOST \
--instance=INSTANCE_NAME \
--prompt-for-password

REST v1

The following request uses the users:update method to update 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 password for the user

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"
}

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

The following request uses the users:update method to update 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 password for 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"
}

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

  1. At the mysql prompt, change the password:
    SET PASSWORD FOR USER_NAME = PASSWORD('auth_string');
    
  2. Flush the mysql.user table to ensure that the change persists:
    FLUSH TABLES mysql.user;
    

Updating users

To update user properties such as host or privileges, you must use the mysql client. For more information, see MySQL User Account Management in the MySQL documentation.

Listing users

To list users:

Console

  1. In the Google Cloud Console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Click the instance name to open its Overview page.
  3. Select Users from the SQL navigation menu.

gcloud

List the users for this instance:

gcloud sql users list \
--instance=INSTANCE_NAME

For a complete list of parameters for this command, see the gcloud sql users list reference page.

REST v1

The request below uses users:list method to list the users defined for an instance.

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"
        ]
      }
    },
    {
      ...
    },
    {
      ...
    }
  ]
}

REST v1beta4

The request below uses users:list method to list the users defined for an instance.

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"
        ]
      }
    },
    {
      ...
    },
    {
      ...
    }
  ]
}

mysql Client

At the mysql prompt, list the MySQL users:

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 where the user'root'@'%' was added. The password field shows the hash of the password.

Deleting users

To delete a user:

Before deleting 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

  1. In the Google Cloud Console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Click the instance name to open its Overview page.
  3. Select Users from the SQL navigation menu.
  4. Click more actions More actions icon. for the user you want to delete.
  5. Select Remove and then select Remove again.

gcloud

Delete the user:

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
  • user-id: The ID of the user

HTTP method and URL:

DELETE https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/users?host=&name=user-id

Request JSON body:

{
  "name": "user-id",
  "host": ""
}

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
  • user-id: The ID of the user

HTTP method and URL:

DELETE https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/users?host=&name=user-id

Request JSON body:

{
  "name": "user-id",
  "host": ""
}

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

  1. At the mysql prompt, delete the user:
    DROP USER 'USER_NAME'@'HOST_NAME';
    

    For more information about the DROP USER statement, see the MySQL documentation.

  2. Flush the mysql.user table to ensure that the change persists:
    FLUSH TABLES mysql.user;
    

What's next