Temporary Access to MySQL with Sym

Temporary Access to MySQL with Sym

Sym now supports temporary MySQL access using a new template in our examples repo! We released an example back in May for setting up temporary Postgres access with Sym, and have had many requests for a MySQL example to complement it. Our new example also takes a different approach to user management than our Postgres example. Instead of granting or removing access to an existing database user, our MySQL example generates temporary users and shares them in AWS Secrets Manager.

We’re going to walk through how the basic integration works, and then highlight how we’re leveraging AWS Secrets Manager to securely deliver the temporary credentials.

Sym’s Lambda Strategy

Sym uses AWS Lambdas deployed into customer accounts to integrate with customer systems that are not on the public internet. No need for firewall rules, you just need to give Sym’s lambda-connector permission to invoke your Lambda function. Your function then needs VPC access to the service that you want to integrate with.

Sym invokes your function with a payload that includes all the metadata you could want in order to act on an escalation or de-escalation request. This includes the requesting user, the approver, the target that the user wants access to, and any other form fields you’ve included in your Flow modal.

Sym also can handle receiving data back from a Lambda function. You can use this data in the Sym SDK to message users, making routing decisions, or trigger other SDK integrations.

Creating Temporary Users

Our example makes it easy to configure what permissions to grant to temporary users once they’re created. For each Sym Target that you configure in your Flow, the Lambda implementation will look for a corresponding SQL file in the targets directory. The Lambda will execute this file, supplying the generated username of the temporary user.

Here’s the Sym Target configuration we include in our example:

# 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

And here’s the SQL file we provide that corresponds to the readonly target:

# 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;

Using Tag Conditions to Access Secrets

To ensure users can only access their own secrets, we’ll use IAM Tag Conditions to limit access to secrets tagged with the current user’s aws:userid. Policy variables like aws:userid can be confusing, especially since they vary depending on the type of AWS User that is currently active.

The aws:username value is only present when the current user is an IAM user. If you’re using federation (such as with Okta or AWS IAM Identity Center), then you need to use the aws:userid value. The aws:userid value has multiple components: a role-id (which is not particularly useful for us) and a name which should identify the user that is making the request.

With an IAM Identity Center session, the aws:userid looks something like the following where the UserID contains a role-id, then a colon, and finally a username:

$ aws sts get-caller-identity
    "UserId": "AROA4XNGR5NVDTUFDDC5X:[email protected]",
    "Account": "123456789012",
"arn:aws:sts::123456789012:assumed-role/AWSReservedSSO_AWSAdministratorAccess_97902d97e6e70b1b/[email protected]"

Now that we know the format of aws:userid, we can build an IAM Policy Condition that tests for it. We will test if a given secret has a sym.user tag value that matches the part of the aws:userid that comes after the role-id, using a wildcard like this: "*:${secretsmanager:ResourceTag/sym.user}":

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

Our MySQL Lambda tags generated secrets with sym.user, and includes a managed policy that you can use to set up the aws:userid tag condition. This will work in most cases where the username that comes in from Sym matches the username in your aws:userid. If they don’t match then let us know, we can help!

Helping Users Access Their Secrets

Our example will DM the requesting user with the info they need to request their secret:

Once the user knows the secret name, they can use the AWS CLI to request it:

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

How to Use the Sym SDK to DM Users

How this works under the covers is that the Sym SDK can work with response data from your Lambda implementation. Using the secret_name that we get from the Lambda, we then use Sym’s Slack SDK to message the user:

output = get_step_output()

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)

Next Steps

We’ll be providing an updated Postgres implementation that also generates temporary users. We’re also interested in how we can help teams more seamlessly integrate access to generated secrets into their workflows, perhaps with CLI extensions. And finally we’ll be looking at alternate spots to share generated passwords, like 1Password.

Please let us know if you have questions on this example and/or want to try it out!

Related Posts