Database User Management Automation

Automated database user management system that handles user permissions across multiple RDS Aurora clusters and AWS accounts using Lambda functions, EventBridge, and MySQL automation.

Project Overview

Managing database user permissions manually across multiple RDS clusters becomes complex and error-prone as organizations scale. This project implements an automated solution that manages database users, permissions, and access control across multiple Aurora MySQL clusters and AWS accounts.

Architecture

The solution leverages several AWS services:

Key Features

Automated User Lifecycle Management

Multi-Account Support

1. Cross-account user management
2. Centralized control plane
3. Consistent user naming conventions

Security and Compliance

1. Least-privilege access principles
2. Audit logging of all user operations
3. Centralized SSO based authentication
4. Role-based permission inheritance

Implementation Details

Event-Driven Architecture

The system responds to updates to SSM Parameter store updates. 

Lambda Function Design

The core Lambda function handles:

1. Reading json configuration for all users in SSM Parameter Store
2. Connecting to each database in it's account
3. Listing all users with specified naming convention
4. Comparing configuration to database status
5. Adding/Deleting/Modifying state in DB's according to configuration

Database Connection Management

def connect_to_database(db_connection_data: dict) -> mysql.connector.connect:
    """
        Connect to the target database
        Parameters:
            db_connection_data (dict): The connection data for the target database
                db_connection_data.host = string
                db_connection_data.user = string
                db_connection_data.pass = string
                db_connection_data.name = string
    """
    try:
        connection = mysql.connector.connect(
            host=db_connection_data['host'],
            user=db_connection_data['user'],
            password=db_connection_data['pass'],
            database=db_connection_data['name']
        )
        logger.info(f"DB CONNECTION: {connection}")
        return connection
    except mysql.connector.Error as e:
        logger.error(f"ERROR: Unexpected error: Could not connect to Serverless {db_connection_data['db_name']} MySQL instance.")
        logger.error(e)
        sys.exit()

def get_secret_value(secret_arn: str, json_key: str) -> str:
    """
        Get a secret value from AWS Secrets Manager

        parameters:
            secret_arn (str): The ARN of the secret in Secrets Manager
            json_key (str): The key in the JSON object to retrieve

        returns:
            str: The value of the key in the JSON object
    """
    logger.info(f'Getting secret value for {secret_arn}, key: {json_key}')

    try:
        response = sm_client.get_secret_value(
          SecretId=secret_arn
        )
        raw_secret = response.get('SecretString')
        secret_json = json.loads(raw_secret)
        return secret_json[json_key]      
    except Exception as e:
        logger.error(f"ERROR: Unexpected error: Could not retrieve for {secret_arn}. Maybe the key {json_key} does not exist.")
        logger.error(e)
        sys.exit()

Permission Templates

Standardized permission sets based on role types:

# Define the permissions for each role
    permissions = {
        'read': 'SELECT',
        'write': 'SELECT, INSERT, UPDATE',
        'admin': 'SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, EVENT, TRIGGER',
        'trusted_admin': 'ALL PRIVILEGES'
    }

Technical Implementation

Cross-Account Access

The system uses cross-account IAM roles to manage users across multiple AWS accounts:

1. Central management account assumes roles in target accounts
2. Target account roles have necessary RDS permissions
3. Database operations performed with Secrets Manager managed role credentials
4. Audit trail maintained in central logging account

Database User Naming Convention

Users follow a consistent naming pattern: ```<company-prefix>_<username> ```

This ensures:

1. Easy identification of user origin
2. No naming conflicts across non-managed accounts

Error Handling and Monitoring

1. Comprehensive error logging
2. CloudWatch metrics for operational visibility
3. SNS notifications for critical failures

Results and Impact

Operational Benefits

1. Massive in manual database user management tasks
2. Consistent permissions across all database clusters
3. Automated compliance with access control policies

Security Improvements

1. Eliminated shared database accounts
2. Implemented least-privilege access
3. Automated access removal for departed employees
4. Complete audit trail of database access changes

Scalability

1. Supports unlimited RDS clusters
2. Handles multiple AWS accounts seamlessly
3. Scales automatically with Lambda
4. Minimal infrastructure maintenance required

Monitoring and Alerting

The system includes comprehensive monitoring:

1. CloudWatch Dashboards for operational metrics
2. Alarm notifications for failures
3. Cost monitoring for resource efficiency

This project demonstrates how serverless technologies can automate complex database management tasks while maintaining security and compliance requirements across multiple AWS accounts and environments.