Function.cs

using System.Collections.Generic;
using System.Threading.Tasks;
using Amazon.Lambda.Core;
using Amazon.SecretsManager;
using Amazon.SecretsManager.Model;
using CloudFormationCustomResourceEvents;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using JsonSerializer = Amazon.Lambda.Serialization.Json.JsonSerializer;

// Assembly attribute to enable the Lambda function's JSON input to be converted into a .NET class.
[assembly: LambdaSerializer(typeof(JsonSerializer))]

namespace CustomResourceBase
{
    public class SetupDatabaseUserProps : CloudFormationResourceProperties
    {
        public string RootSecretArn { get; set; }
        public List<SetupUserGrants> Users { get; set; }
    }

    public class SetupUserGrants
    {
        public string SecretArn { get; set; }
        public List<string> Grants { get; set; }
    }

    internal class DatabaseSecret
    {
        public string Username { get; set; }
        public string Password { get; set; }
        public string Host { get; set; }
        public uint Port { get; set; }
    }

    public class Function : CloudFormationCustomResourceFunction<SetupDatabaseUserProps>
    {
        public override Task<CloudFormationResponse<SetupDatabaseUserProps>> FunctionHandler(JToken input,
            ILambdaContext context)
        {
            // You can remove this function entirely. It's just here to show that the FunctionHandler for the lambda is handled in the base class. :)
            return base.FunctionHandler(input, context);
        }

        private async Task<DatabaseSecret> GetDbSecret(string arn)
        {
            using var sm = new AmazonSecretsManagerClient();
            return JsonConvert.DeserializeObject<DatabaseSecret>(
                (await sm.GetSecretValueAsync(new GetSecretValueRequest {SecretId = arn})).SecretString);
        }

        private async Task<MySqlConnection> GetOpenConnection(string rootArn)
        {
            var rootInfo = await GetDbSecret(rootArn);

            return new MySqlConnection(new MySqlConnectionStringBuilder
            {
                UserID = rootInfo.Username,
                Password = rootInfo.Password,
                Server = rootInfo.Host,
                Port = rootInfo.Port
            }.ConnectionString);
        }

        private async Task DeleteUsers(List<SetupUserGrants> users, MySqlConnection db)
        {
            foreach (var user in users)
            {
                var userData = await GetDbSecret(user.SecretArn);
                using var existsCmd = db.CreateCommand();
                existsCmd.CommandText =
                    $"SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = '{userData.Username}`)";

                if ((bool) await existsCmd.ExecuteScalarAsync())
                {
                    using var delCmd = new MySqlCommand($"DROP USER '{userData.Username}'@'%'", db);
                    await delCmd.ExecuteNonQueryAsync();
                }
            }
        }

        private async Task CreateUsers(List<SetupUserGrants> users, MySqlConnection db)
        {
            foreach (var user in users)
            {
                var userData = await GetDbSecret(user.SecretArn);
                using var createCmd = db.CreateCommand();
                createCmd.CommandText =
                    $@"CREATE USER '{userData.Username}'@'%' IDENTIFIED BY '{MySqlHelper.EscapeString(userData.Password)}';
GRANT {string.Join(", ", user.Grants)} ON *.* TO '{userData.Username}'@'%';
FLUSH PRIVILEGES;";

                await createCmd.ExecuteNonQueryAsync();
            }
        }

        protected override async Task<object> Create(
            CloudFormationCustomResourceCreateEvent<SetupDatabaseUserProps> evt, ILambdaContext context)
        {
            using var db = await GetOpenConnection(evt.ResourceProperties.RootSecretArn);
            await db.OpenAsync();

            await CreateUsers(evt.ResourceProperties.Users, db);

            db.Close();
            return new
            {
                Success = true
            };
        }

        protected override async Task<object> Update(
            CloudFormationCustomResourceUpdateEvent<SetupDatabaseUserProps> evt, ILambdaContext context)
        {
            // NOTE: Need to open the connection on the *old* resource properties
            // This ensures that when we're moving stuff around, we connect to an "old" server, and update on a "new" server for instance.
            var db = await GetOpenConnection(evt.OldResourceProperties.RootSecretArn);
            await db.OpenAsync();
            await DeleteUsers(evt.OldResourceProperties.Users, db);
            db.Close();
            db.Dispose();


            db = await GetOpenConnection(evt.ResourceProperties.RootSecretArn);
            await db.OpenAsync();
            await CreateUsers(evt.ResourceProperties.Users, db);

            db.Close();

            return new
            {
                Success = true
            };
        }

        protected override async Task<object> Delete(
            CloudFormationCustomResourceDeleteEvent<SetupDatabaseUserProps> evt, ILambdaContext context)
        {
            using var db = await GetOpenConnection(evt.ResourceProperties.RootSecretArn);
            await db.OpenAsync();

            await DeleteUsers(evt.ResourceProperties.Users, db);

            db.Close();
            return new
            {
                Success = true
            };
        }
    }
}