MySQL on AWS

Use our prebuilt template to manage MySQL escalations with Sym’s AWS Lambda strategy.

How it Works

With Sym’s MySQL integration for AWS Lambda, users request access to a target database using Slack or the Sym API. Once approved, Sym creates a temporary user using a Lambda function in your AWS account. Users access their temporary credentials using AWS Secrets Manager.

916916

Full Example in the Examples Repo

📘

mysql_temp_user_strategy example

Because so many of our customers ask for a MySQL integration we’ve built an end to end template that Just Works out of the box. If you want to go deeper on understanding the various moving parts of setting up a Sym Flow, follow along with our AWS Lambda Strategy tutorial.

Preparing your MySQL Database for Sym Approvals

Connecting to the Database

Sym’s Lambda function needs to be attached to an AWS VPC that can reach your database. Our example module provides variables for the VPC subnets and AWS security groups to put the Lambda function in. Our module also lets you optionally create a VPC and database to test out the integration in a sandbox.

Set Up a Bot User

Sym will need a user that can connect to your MySQL database and create users. Beyond the CREATE USER permission, the user also needs to have the permissions that you plan to grant to temporary users when you create them. Here’s an example that grants the bot user access to an example database named symdb:

CREATE USER 'symbot'@'%' IDENTIFIED BY 'abcdefg1234'
GRANT CREATE USER ON *.* TO 'symbot'@'%' WITH GRANT OPTION
GRANT SELECT, UPDATE ON symdb.* TO 'symbot'@'%'

Set Up AWS Secrets Manager

Our Lambda template function stores generated credentials in AWS Secrets Manager. You need to configure access so that users can access their own secrets but no-one else's.

Our template includes a Managed Policy that grants users access to secrets that are tagged with their aws:userid. This approach works when your users are logging in as federated users, from AWS IAM Identity Center (AWS SSO), Okta, or another identity provider. If you are using standalone IAM users, then you should use the aws:username variable instead.

The important component of this policy is the condition that the requesting user ID should match the value of the sym.user tag on the secret that user is trying to access:

{
  "Action": [
    "secretsmanager:GetSecretValue",
    "secretsmanager:DescribeSecret"
  ],
  "Condition": {
    "StringLike": {
      "aws:userid": "*:${secretsmanager:ResourceTag/sym.user}"
    }
  },
  "Effect": "Allow",
  "Resource": "arn:aws:secretsmanager:us-east-1:123456789012:secret:/symops.com/*"
}

You should take the managed policy that is included in our template and ensure that the users that you want to use the Sym MySQL flow have access to it. You can do this by attaching a customer-managed policy to a permission set in AWS IAM Identity Center.

Provisioning your Sym Flow

Now that you have your database ready, let’s configure your Sym Flow! First get your basic Sym Installation set up. Then copy the mysql_temp_user_strategy module into your Sym Terraform configurations. The module is designed to let you get started with minimal additional configuration.

Configure Sym Targets for your Temporary MySQL Users

Users request access to specific access targets using a select menu in your Sym Flow. Each target will create a temporary user with a different set of privileges. To configure an access target, you need to add a Sym Terraform resource as well as a SQL file.

The starter template includes the Sym configuration for a readonly target:

# A target AWS Lambda that will be invoked on escalate and de-escalate.
# The `name` will be used in the lambda to decide which resource to manage access to
resource "sym_target" "readonly" {
  type  = "aws_lambda_function"
  name  = "readonly"
  label = "Read Only"

  settings = {
    # `type=aws_lambda_function` sym_targets have a required setting `arn`
    # which must be the ARN of the AWS Lambda that will be invoked on escalate and de-escalate
    arn = module.mysql_lambda_function.lambda_function_arn
  }
}

The corresponding SQL configuration for the readonly target goes in the lambda_src/targets directory. You put SQL files in this directory that are named with the same name as each target name. The SQL file gets executed after Sym's Lambda creates your temporary user:

# For the readonly target, users can read anything in the symdb database.
# The lambda function provides username as a named variable you can use.
GRANT SELECT ON symdb.* TO %(username)s;

Configure your Database and VPC

If you have an existing VPC and database that you want to connect to, then provide:

  1. The VPC subnets and security groups to put the lambda function in.
  2. The database connection details.
db_config = {
  "host" = "symdb.abcdefg.us-east-1.rds.amazonaws.com"
  "pass" = "CHANGEME"
  "port" = 3306
  "user" = "symbot"
}
subnet_ids = [
  "subnet-0cf3122cb5211dare",
  "subnet-04646525ab2ed36d4",
  "subnet-08df0bc9a901eb0ba",
]
security_group_ids = ["sg-01af63b9abc51f647"]

📘

Don't want to connect Sym to an existing database or VPC yet?

Instead of providing the VPC and database configurations, simply set the db_enabled Terraform variable to true. The VPC and database configurations will be provisioned and automatically configured without you needing to supply any additional Terraform variables.

We also provision an EC2 instance that you can tunnel to the database with using AWS Systems Manager Session Manager. Use the tunnel.sh script to forward database traffic to a port on localhost, so that you can connect to and administer the new database.

Accessing Temporary Credentials

Communicate the Generated Secret Name to Users

Our Lambda function generates temporary credentials and stores them in a secret in AWS Secrets Manager. We use Sym's ability to work with Lambda responses to get the generated secret name and message the user. First we respond with the secret_name in the response body:

secret_name = user_manager.create_user(user_event)
body = {"secret_name": secret_name}

On the Sym side, we take the incoming payload from our Lambda invocation and then DM the user with the details:

@hook
def after_escalate(event):
    # `get_step_output` defaults to current event if none is passed.
    # This is equivalent to `get_step_output("escalate")`
    output = get_step_output()

    # If there are errors, Sym will DM the user for us so
    # we don't want to do anything else special here
    if output["errors"]:
        return

    # Otherwise use the body returned by the Lambda function to tell the user
    # what AWS Secrets Manager Secret their username and password are stored in
    secret_name = output["body"]["secret_name"]

    message = (
        f"Your generated username and password are stored in AWS Secrets Manager.\n"
        f"Secret Name: {secret_name}\n"
    )
    slack.send_message(event.get_actor("request"), message)

Users get a message with the generated secret name that looks like this:

13661366

Access the Generated Secret

Make sure that your users have access to the managed policy we discussed above that grants them access to their own secrets. Once the managed policy is in place, users can access their generated credentials using the AWS CLI or one of the SDKs. You could build a wrapper script for this that matches your team's setup:

$ aws secretsmanager get-secret-value \
  --secret-id /symops.com/sym-mysql/[email protected]/73506a42-2c64-420f-8382-e18f11ae13fc \
  --query 'SecretString' --output text | jq
{
  "host": "symdb.123456789.us-east-1.rds.amazonaws.com",
  "port": 3306,
  "username": "jonb71b41c09e5159cd4b56dfae",
  "password": "abcdefghijk"
}

Testing in the AWS Console

We've provided example payloads in our template that you can use to iterate on your function in the AWS Console. Copy the test events from lambda_src/test and then save them as test events in the console!

Password Management

The mysql_temp_user_strategy module provisions an encrypted AWS Systems Manager Parameter Store parameter for the bot user’s database password. Because Terraform manages the parameter, you must ensure that your Terraform state is managed securely.

Many teams manage production passwords outside of Terraform state. You can update the password configuration to pull the password from another secret store, such as Vault. You can also supply a placeholder value for password, and use the ignore_changes lifecycle argument. Uncomment the ignore_changes block in the parameter declaration. Then you need to ensure the correct password value is set for the password using an alternate approach, like the CLI.

resource "aws_ssm_parameter" "mysql_password" {
  name  = local.db_password_key
  type  = "SecureString"
  value = local.db_config["pass"]

  tags = var.tags
  
  // Ignore changes to the password value
  lifecycle {
    ignore_changes = [value, version]
  }
}