Create and manage users

This page describes how to enable and use Cloud SQL built-in authentication.

Before creating users

  1. Create a Cloud SQL instance. For more information, see Create instances.
  2. If you plan to use your database's administrative client to manage users, 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 SQL Server, the default user is sqlserver.

Console

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

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Select Users from the SQL navigation menu.
  4. Find the sqlserver user and select Change password from the more actions menu More actions icon..
  5. 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 sqlserver \
--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
  • 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

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

Create a user

After setting up the default user account, you can create other users.

Console

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

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Select Users from the SQL navigation menu.
  4. Click Add user account.
  5. In the Add a user account to instance instance_name page, add the following information:
    • A Username.
    • A Password (optional).
  6. Click Add.

gcloud

To create a user, use the gcloud sql users create command.

Replace the following:

  • USER_NAME: The user name.
  • INSTANCE_NAME: The name of the instance.
  • PASSWORD: The password for the user.
gcloud sql users create USER_NAME \
--instance=INSTANCE_NAME \
--password=PASSWORD

User name length limits are the same for Cloud SQL as for on-premises SQL Server.

Terraform

To create a user, use a Terraform resource.

resource "random_password" "pwd" {
    length = 16
    special = false
}

resource "google_sql_user" "user" {
    name = "user"
    instance = google_sql_database_instance.instance.name
    password = random_password.pwd.result
}

Apply the changes

To apply your Terraform configuration in a Google Cloud project, complete the following steps:

  1. Launch Cloud Shell.
  2. Set the Google Cloud project where you want to apply the Terraform configuration:
    export GOOGLE_CLOUD_PROJECT=PROJECT_ID
    
  3. Create a directory and open a new file in that directory. The filename must have the .tf extension, for example main.tf:
    mkdir DIRECTORY && cd DIRECTORY && nano main.tf
    
  4. Copy the sample into main.tf.
  5. Review and modify the sample parameters to apply to your environment.
  6. Save your changes by pressing Ctrl-x and then y.
  7. Initialize Terraform:
    terraform init
  8. 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.

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

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

  1. To disable deletion protection, in your Terraform configuration file set the deletion_protection argument to false.
    deletion_protection =  "false"
  2. Apply the updated Terraform configuration by running the following command and entering yes at the prompt:
    terraform apply
  1. 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"
}

User name length limits are the same for Cloud SQL as for on-premises SQL Server.

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

User name length limits are the same for Cloud SQL as for on-premises SQL Server.

List users

Console

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

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Select Users from the SQL navigation menu.

gcloud

Use the gcloud sql users list command to list the users for this instance:

gcloud sql users list \
--instance=INSTANCE_NAME

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

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

Change a user password

User passwords can be changed in one of the following ways.

Console

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

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  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.
  6. Specify a new password.
  7. Click OK.

gcloud

Use the gcloud sql users set-password command to change a password.

Replace the following:

  • USER_NAME: The user name.
  • INSTANCE_NAME: The name of the instance.
gcloud sql users set-password USER_NAME \
--instance=INSTANCE_NAME \
--prompt-for-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.

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

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

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

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

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Select Users from the SQL navigation menu.
  4. Click more actions More actions icon. for the user you want to remove.
  5. 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.
  • INSTANCE_NAME: The name of the instance.
gcloud sql users delete USER_NAME \
--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"
}

Update user properties

For information related to database-level roles in SQL Server, see Working with Database-Level Roles in the SQL Server documentation.

What's next