Automate database user management with AWS Lambda and AWS Systems Manager

Amazon Web Services (AWS) users frequently use multiple accounts, organizing them efficiently with AWS Organizations. This system structures the accounts hierarchically and groups them into Organizational Units (OUs). However, this setup can sometimes add complexity, especially for teams that support the entire organization.

Consider the following example of a database operations team’s predicament. Their task is managing Amazon Relational Database Service (Amazon RDS) databases across various AWS accounts. They assist application owners with creating and managing database user accounts, updating permissions, resetting passwords, and removing accounts. Challenges mount when these databases span across different accounts, requiring the team to switch between them for each task, complicating their workflow.

In this post, we walk you through a solution that you can use to manage repetitive tasks such as creating database user accounts, update user grants, reset passwords, and delete user accounts. This centralized approach aims to streamline operations and reduce the complexities associated with database user management in a multi-account setup.

Solution overview

In this solution, AWS Lambda, equipped with user lifecycle management logic, operates across accounts such as development, user acceptance testing (UAT), and production. Each account has an assumed role for the primary account, enabling it to invoke Lambda and perform tasks in conjunction with AWS Systems Manager for multiple accounts and AWS Regions. This integration facilitates the centralized run of automation documents across various AWS accounts and Regions, streamlining the management of tasks such as database user account creation, updates, password resets, and deletions, thereby enhancing workflow efficiency. To address the complexities of managing multiple AWS accounts, we integrate an AWS Lambda function with automation using AWS Systems Manager to simplify user management operations.

The following diagram illustrates the architecture:

The solution follows these steps:

Invoke the Systems Manager document
The Systems Manager document assumes the role in the Amazon RDS account
The Systems Manager document invokes the Lambda function
The Lambda function connects to AWS Secrets Manager service through the virtual private cloud (VPC) endpoint service
AWS Lambda retrieves credentials to connect to the database from AWS Secrets Manager.
To create the user action, the function creates a new secret in AWS Secrets Manager to store credentials of the new created user
A new user is created in the database

In the following sections, we show how to set up the solution and perform the aforementioned common tasks.

Prerequisites

Before you deploy this solution, make sure to have the following prerequisites:

A multi-account setup with one central (tooling) account that will host the Systems Manager document (Systems Manager document) and one or more database accounts.
A VPC in the database account with Amazon RDS for MySQL or Amazon Aurora MySQL-Compatible Edition deployed in a private subnet. It’s a best practice to deploy the database in a private subnet. However, it’s not a mandatory requirement for this solution.
An AWS Secrets Manager VPC endpoint.
A secret in Secrets Manager that will securely store the database URL, user name, and password that was set up when the database was created. The secret must use the option Credentials for Amazon RDS database. Optionally, it can have automatic credentials rotation enabled.
A Lambda function for secret rotation.
Python version 3.12 or above in Lambda runtimes.

Set up cross-account access

Complete the following steps to set up cross-account access:

In the database account, create a new AWS Identity and Access Management (IAM) role (for this post, called AssumableRoleInDatabaseAccount)

Define the trust relationship to allow the central account to assume this role as follows:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "ssm.amazonaws.com",
                "AWS": "<Central_Account_Number or Central_Account_IAM role>"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

Attach the following policy to the role:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "LambdaInvoke",
            "Effect": "Allow",
            "Action": "lambda:InvokeFunction",
            "Resource": "arn:aws:lambda:eu-west-1:<Database_Account Number>:function:UserManagement"
        },
        {
            "Sid": "SSMAutomation",
            "Effect": "Allow",
            "Action": [
                "ssm:DescribeAutomationExecutions",
                "ssm:GetAutomationExecution",
                "ssm:DescribeAutomationStepExecutions",
                "ssm:StartAutomationExecution",
                "ssm:StopAutomationExecution"
            ],
            "Resource": "*"
        }
    ]
}

For improved security, it’s recommended to specify the Amazon Resource Name (ARN) of the intended resources instead of using the wildcard * in the Resource element of the policy.

Package the Lambda function

The Lambda function performs the following actions:

Create a database user – This action creates the desired user in the database with read, read/write, or admin privileges.
- The admin role grants full control (GRANT ALL PRIVILEGES ON `<DB_NAME>`.* TO '<USER_NAME>'@'%';) over the database, including creating, altering, and dropping tables.
- The readwrite role allows reading, inserting, updating, and deleting data (GRANT SELECT, INSERT, UPDATE, DELETE ON `<DB_NAME>`.* TO '<USER_NAME>'@'%';).
- The readonly role only permits reading data from the database (GRANT SELECT ON `<DB_NAME>`.* TO '<USER_NAME>'@'%';).
A new secret is created, which stores the user credentials.
Delete a database user – This action deletes the user from the database. The secret is also deleted.
Reset the password of a user – This action resets the password of an existing user and updates the corresponding secret with the new password.
Modify user privileges – This action modifies the user privileges—for example, from read to read/write.

This solution doesn’t handle the case where multiple user accounts with the same username but different hosts exist in the MySQL server. If you need to handle user accounts with the same username but different hosts, you will need to modify the Automated-Database-User-Management-Aurora-MySQL code accordingly.

To package the Lambda function, download the code to a folder on your local machine. In our example, the folder name is usermgmt.

$cd usermgmt
$mkdir lambda_function
$ pip3 install "--target=lambda_function" -r requirements.txt
$cd lambda_function
$zip -r ../lambda_function.zip .
$cd ..
$zip lambda_function.zip *.py

Create the Lambda IAM role

Complete the following steps to create the Lambda IAM role:

In the database account, create a customer-managed IAM policy using the following policy document (for this post, the policy name is mastersecret-read). This policy provides the Lambda function with the permissions needed to read the secret in Secrets Manager that stores the Amazon RDS user credentials. These are the credentials that are set up when creating the database. Replace the ARN for the resource with your database credentials secret ARN:
```
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "GetSecretAccess",
      "Effect": "Allow",
      "Action": "secretsmanager:GetSecretValue",
      "Resource": "arn:aws:secretsmanager:*:<your account number>:secret:<Secret Name>"
    }
  ]
}
```

Create a customer-managed IAM policy using the following policy document (for this post, the policy name is secret-create-rotate). This policy provides the Lambda function with the permissions to create a new secret, rotate the secret, add a resource policy, and tag the secret:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "SMAccess", 
      "Effect": "Allow",
      "Action": [
        "secretsmanager:PutResourcePolicy",
        "secretsmanager:CreateSecret",
        "secretsmanager:RotateSecret",
        "secretsmanager:TagResource"
      ],
      "Resource": "arn:aws:secretsmanager:*:<your account number>:secret:*"
    }
  ]
}

Create a customer-managed IAM policy using the following policy document (for this post, the policy name is lambda-invokefunction). This policy provides the Lambda function with the permissions to rotate a secret:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "LambdaInvoke",
            "Effect": "Allow",
            "Action": "lambda:InvokeFunction",
            "Resource": "arn:aws:lambda:*:<your account number>:function:*"
        }
    ]
}

Create an IAM role for your Lambda function. In the the Roles page of the IAM console, choose Create role, and choose Lambda service. Attach the IAM policy created in the previous step.

AWSLambdaBasicExecutionRole – This AWS managed policy enables permissions for the Lambda function to write to Amazon CloudWatch
AWSLambdaVPCAccessExecutionRole – This AWS managed policy includes permissions that allow the Lambda function to manage network resources. This is required for a function to connect to VPC resources such as an Amazon RDS database connection.
AWSLambdaENIManagementAccess – This AWS managed policy provides minimum permissions for the Lambda function to manage elastic network interfaces (create, describe, delete), which enables the Lambda function to access VPC resources.
mastersecret-read – This policy, which you created earlier, provides the Lambda function with permissions to read the Secrets Manager secret that contains the Amazon RDS database user credentials.
secret-create-rotate – This policy, which you created earlier, provides the Lambda function with permissions to create a new secret that holds the credentials of the newly created user. This policy also provides the permissions to rotate the new secret.
lambda-invokefunction – This policy, which you created earlier, provides the Lambda function with the permissions to set up secret rotation on the newly created user secret.

Name the role UserManagementLambdaExecutionRole and describe it this way: “Allows Lambda functions to call AWS services on your behalf.”
Choose Create role to create the IAM role for the Lambda function.

Create the Lambda function

To create the function, complete the following steps:

On the Lambda console, create a new Lambda function and choose the Python 3.12 runtime, as shown in the following screenshot. After the Lambda function is created, follow these steps:
- Select the Code
- In the Code source pane, choose Upload from.
- Choose .zip file.
- To upload the .zip file, do the following:
  - Select Upload, then select the lambda_function.zip file in the file chooser.
  - Choose Open.
  - Choose Save.
If the primary user secret has rotation enabled and you want the same for the new secret that stores the credentials of the new user, use the ARN of the rotation Lambda in the Environment variables section with the key as ROTATION_LAMBDA_ARN, as shown in the following screenshot. You can also provide a value for the rotation days.
In the Advanced settings section, select Enable VPC, choose the VPC, and choose all the subnets in the subnet group containing the database, as shown in the following screenshot.
In Change the default execution role section, select Use an existing role and choose the role that you created earlier, as shown in the following screenshot.
Select a security group for the Lambda function. Configure the Amazon RDS security group to allow incoming requests from the Lambda function’s security group, as shown in the following screenshot.
Create the Lambda resource policy to grant access to the tooling account to run the function. Choose Add permissions, as shown in the following screenshot.

Set up the central tooling account

To create a Systems Manager document that assumes the role from the database account and invokes the Lambda function, complete the following steps:

In the central account, open the Systems Manager console
Create a new Systems Manager document with the document type Automation
Provide a name for the document
Choose Editor and enter the document from the GitHub repo

Now that both the tooling and database accounts are set up with the necessary components, you can initiate the Systems Manager automation to perform the desired actions.

Create a database user

Complete the following steps to create your database user:

On the Systems Manager console in tooling account, navigate to the automation document you created
Choose Execute automation
Choose Multiple accounts and Regions to run it across accounts or Simple execution to the same account
- If using the cross-account option, enter the account numbers of the database accounts and their respective Regions
Enter the name of the role that you created in the database account for cross-account access, as shown in the following screenshot.
Enter the input parameters when prompted, as shown in the following screenshot.

During its run, the Lambda function creates a user in the Amazon RDS database and stores the credentials in Secrets Manager. It also references the primary user’s secret to inherit the secret resource policy, handle secret rotation, and apply associated tags.

You can monitor its progress on the Systems Manager console, as shown in the following screenshot.

If the Lambda function has been provided with the secret rotation Lambda ARN, then the new secret that gets created to store the credentials will have secret rotation enabled, as shown in the following AWS CLI command output..

{
    "ARN": "arn:aws:secretsmanager:eu-west-1:*******:secret:rds-testdb-test-adt-ry98-secret",
    "Name": "rds-testdb-test-adt-ry98-secret",
    "Description": "Credentials for 'test-adt-ry98' on RDS database 'testdb'.",
    "RotationEnabled": true,
    "RotationLambdaARN": "arn:aws:lambda:eu-west-1:*******",
    "RotationRules": {
        "AutomaticallyAfterDays": 30
    },
    "LastRotatedDate": "2024-04-09T08:44:00.947000+00:00",
    "LastChangedDate": "2024-04-09T08:44:00.947000+00:00",
    "LastAccessedDate": "2024-04-09T00:00:00+00:00",
    "NextRotationDate": "2024-05-09T23:59:00+00:00",
    "Tags": [
        {
            "Key": "create_by_user_automation",
            "Value": "true"
        },
        {
            "Key": "creation_timestamp",
            "Value": "2024-04-09 08:43:57"
        }
    ],
    "VersionIdsToStages": {
        "21b2e401-c1c4-4d50-b511-618111db8129": [
            "AWSCURRENT",
            "AWSPENDING"
        ],
        "fl6bf8fe-d5db-49a3-b9b1-0fcd3420b4c9": [
            "AWSPREVIOUS"
        ]
    },
    "CreatedDate": "2024-04-09T08:43:57.179000+00:00"
}

To test the newly created user, connect to any Amazon Elastic Compute Cloud (Amazon EC2) instance that has connectivity to the RDS instance and connect to the database with newly created user.

# mysql -h demo-dev-aurora-mysql.cluster.amazonaws.com -P 3306 -u audit_ro -p
Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| userdetails        |
+--------------------+
2 rows in set (0.002 sec)

Delete a database user

To delete a database user, complete the following steps:

On the Systems Manager console, navigate to the automation document you created
Choose Execute automation
Choose Multiple accounts and Regions to run it across accounts or Simple execution to the same account
Provide the necessary input parameters. For this post, the action is set to delete_user, as shown in the following screenshot
Specify the target accounts and Regions, as shown in the following screenshot

Update a database user

To update a database user, repeat the same steps and choose the update_grant action while running the Systems Manager document, as shown in the following screenshot.

You can validate the user privileges by connecting to the RDS instance, as shown in the following output.

Before:

MySQL [(none)]> show grants for audit_rw%;
+-----------------------------------------------------------+
| Grants for audit_rw%                                      |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `audit_rw`@`%`                      |
| GRANT ALL PRIVILEGES ON `userdetails`.* TO `audit_rw`@`%` |
+-----------------------------------------------------------+
2 rows in set (0.001 sec)

After:

MySQL [(none)]> show grants for audit_rw%;
+-----------------------------------------------------------+
| Grants for audit_rw%                                      |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `audit_rw`@`%`                      |
| GRANT SELECT ON `userdetails`.* TO `audit_rw`@`%`         |
+-----------------------------------------------------------+
2 rows in set (0.001 sec)

Reset a database user password

To reset the database user, complete the same steps and choose the reset_password action while running Systems Manager document, as shown in the following screenshot.

You can validate the password by connecting to the RDS instance and running the password_last_update query, as shown in the following output.

MySQL [(none)]> select user, password_last_changed from mysql.user where user = 'audit_rw';
+----------+-----------------------+
| user     | password_last_changed |
+----------+-----------------------+
| audit_rw | 2023-11-03 15:56:55   |
+----------+-----------------------+
1 row in set (0.001 sec)

You can observe that the timestamp matches in both the Lambda logs and the database.

Clean up

If you no longer need the resources you created as part of this post, delete them to prevent incurring additional charges.

Database account
- Access the AWS Lambda console and navigate to the Functions page
  - Select the function created earlier to delete
  - Delete the Lambda function
- Open the IAM console and navigate to the Roles section
  - Select the roles AssumableRoleInDatabaseAccount and UserManagementLambdaExecutionRole
  - Delete the selected IAM roles
- Open the Secrets Manager console and delete the secrets created as part of this automation
Central account
- Open the Systems Manager console and navigate to the Documents section
  - Select the document created earlier and delete

Conclusion

In this post, we discussed how you can use a Lambda function and Systems Manager automation to simplify and streamline Amazon RDS user management in a multi-account setup. We also walked through how to set up a Lambda function and a Systems Manager document to create, modify, and delete database users.

Give it a try and let us know your feedback in the comments section. For more information about supported database authentication methods, refer to Database authentication with Amazon Aurora.

About the Authors

Satish Kamat is a Senior Lead Consultant, GCCI with AWS Professional Services. He works closely with customers in their cloud transformation and migration journeys across various verticals like BFSI, automotive, and telecom.

Rupankar Ganguli is an Engagement Manager, GCCI with AWS Professional Services. As an enterprise strategist with over 12 years of experience in leading transformative initiatives, Rupankar leverages his expertise to engage with enterprises across diverse industries, executing transformative, data-driven turnkey solutions.

Vivek Thangamuthu is a DevOps Consultant, GCCI at AWS Professional Services, is passionate about infrastructure as code and automations. He has expertise in supporting customers in the financial services, automobile, and telecom sectors. He helps customers on their transformative journeys, facilitating DevOps adoption and enabling the adoption of cloud operating practices.

Shanmugam Shanker is a Lead Consultant, GCCI with AWS Professional Services with expertise in Migrations, Disaster Recovery, Cloud Infrastructure and DevSecOps. He is passionate about implementing innovative Cloud-based solutions using AWS services to help customers achieve their business objectives.

AWS Database Blog