MySQL Authentication Plugin Migration: Navigating the Shift from mysql_native_password to caching_sha2_password in AWS RDS

SELECT user, authentication_string FROM mysql.user WHERE user = 'testuser';
-- Result: *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29

SELECT user, authentication_string FROM mysql.user WHERE user = 'testuser';
-- Result: $A$005$randomsalt$hashedpassworddata...

-- For MySQL 8.4+, modify the authentication_policy parameter
-- In RDS Parameter Group (the RDS default is '*:caching_sha2_password').
-- Make new accounts default to mysql_native_password; any plugin is still allowed:
authentication_policy = '*:mysql_native_password'

-- 1. Identify current authentication plugins in use
SELECT 
    user,
    host,
    plugin,
    authentication_string,
    password_expired,
    password_last_changed
FROM mysql.user 
WHERE user != ''
ORDER BY plugin, user;

-- 2. Check for applications using mysql_native_password
SELECT 
    user,
    host,
    plugin,
    COUNT(*) as connection_count
FROM information_schema.processlist p
JOIN mysql.user u ON p.user = u.user
GROUP BY user, host, plugin;

-- 3. Identify authentication-related parameters
SHOW VARIABLES LIKE '%auth%';
SHOW VARIABLES LIKE '%password%';

-- Step 1: Create new users with caching_sha2_password
-- This approach allows testing without affecting existing users

-- Create a test user with caching_sha2_password
CREATE USER 'testuser_sha2'@'%' 
IDENTIFIED WITH caching_sha2_password BY 'SecurePassword123!';

-- Grant same privileges as the original user
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser_sha2'@'%';

-- Step 2: Test application connectivity
-- Update application configuration to use the new user
-- Monitor for any connection issues

-- Step 3: Migrate existing users one by one
-- For each user, update the authentication plugin
ALTER USER 'existinguser'@'%' 
IDENTIFIED WITH caching_sha2_password BY 'NewSecurePassword123!';

-- Step 1: Create a migration script
DELIMITER //
CREATE PROCEDURE MigrateUsersToSHA2()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_name VARCHAR(255);
    DECLARE user_host VARCHAR(255);
    DECLARE user_cursor CURSOR FOR 
        SELECT user, host FROM mysql.user 
        WHERE plugin = 'mysql_native_password' 
        AND user NOT IN ('root', 'mysql.sys', 'mysql.session');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN user_cursor;
    
    read_loop: LOOP
        FETCH user_cursor INTO user_name, user_host;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Reset password with new plugin
        SET @sql = CONCAT('ALTER USER ''', user_name, '''@''', user_host, ''' IDENTIFIED WITH caching_sha2_password BY ''', 'TempPassword123!', '''');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- Log the change
        INSERT INTO migration_log (user, host, migration_date) 
        VALUES (user_name, user_host, NOW());
        
    END LOOP;
    
    CLOSE user_cursor;
END//
DELIMITER ;

-- Step 2: Create logging table
CREATE TABLE migration_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user VARCHAR(255),
    host VARCHAR(255),
    migration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 3: Execute migration during maintenance window
CALL MigrateUsersToSHA2();

-- Step 1: Configure MySQL to support both plugins
-- In MySQL 8.4+, set authentication_policy in the parameter group
-- '*:caching_sha2_password' allows either plugin for the single factor
-- and uses caching_sha2_password when CREATE USER names no plugin:
-- authentication_policy = '*:caching_sha2_password'

-- Step 2: Create dual users for testing
-- Keep existing mysql_native_password users
-- Add new caching_sha2_password users with different names

-- Step 3: Update application configuration gradually
-- Use feature flags or configuration management
-- Switch applications one by one

# Terraform configuration for RDS Proxy with authentication settings
resource "aws_db_proxy" "mysql_proxy" {
  name                   = "mysql-proxy"
  engine_family          = "MYSQL"
  auth {
    auth_scheme = "SECRETS"
    secret_arn  = aws_secretsmanager_secret.mysql_secret.arn
  }
  
  # Specify authentication type for MySQL 8.4+
  auth {
    auth_scheme = "SECRETS"
    secret_arn  = aws_secretsmanager_secret.mysql_secret.arn
    # For older applications requiring mysql_native_password
    auth_type   = "mysql_native_password"
  }
  
  target {
    db_instance_identifier = aws_db_instance.mysql.id
  }
}

# Secrets Manager configuration
resource "aws_secretsmanager_secret" "mysql_secret" {
  name = "mysql-proxy-secret"
}

resource "aws_secretsmanager_secret_version" "mysql_secret" {
  secret_id = aws_secretsmanager_secret.mysql_secret.id
  secret_string = jsonencode({
    username = "proxyuser",
    password = "SecurePassword123!"
  })
}

#!/bin/bash
# RDS Proxy migration script

# Step 1: Create new proxy with caching_sha2_password
aws rds create-db-proxy \
  --db-proxy-name mysql-proxy-sha2 \
  --engine-family MYSQL \
  --auth AuthScheme=SECRETS,SecretArn=arn:aws:secretsmanager:region:account:secret:mysql-secret \
  --target DBInstanceIdentifier=mysql-instance

# Step 2: Test connectivity with new proxy
mysql -h mysql-proxy-sha2.proxy-xyz.region.rds.amazonaws.com \
  -u proxyuser -p testdatabase

# Step 3: Update application configuration
# Use DNS CNAME to switch between proxies
aws route53 change-resource-record-sets \
  --hosted-zone-id Z123456789 \
  --change-batch file://dns-change.json

// Modern approach with caching_sha2_password
String url = "jdbc:mysql://mysql-instance.region.rds.amazonaws.com:3306/database" +
            "?useSSL=true" +
            "&requireSSL=true" +
            "&allowPublicKeyRetrieval=true" +
            "&serverTimezone=UTC";

Connection conn = DriverManager.getConnection(url, "username", "password");

import mysql.connector

# Connection with caching_sha2_password support
config = {
    'host': 'mysql-instance.region.rds.amazonaws.com',
    'user': 'username',
    'password': 'password',
    'database': 'database',
    'auth_plugin': 'caching_sha2_password',
    'ssl_ca': '/path/to/rds-ca-2019-root.pem',
    'ssl_verify_cert': True,
    'ssl_verify_identity': True
}

conn = mysql.connector.connect(**config)

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'mysql-instance.region.rds.amazonaws.com',
  user: 'username',
  password: 'password',
  database: 'database',
  ssl: {
    ca: fs.readFileSync('/path/to/rds-ca-2019-root.pem')
  },
  authPlugins: {
    caching_sha2_password: () => () => Buffer.from('password')
  }
});

import (
    "database/sql"
    "crypto/tls"
    "crypto/x509"
    _ "github.com/go-sql-driver/mysql"
)

func connectMySQL() (*sql.DB, error) {
    // Configure TLS for caching_sha2_password
    rootCertPool := x509.NewCertPool()
    pem, err := ioutil.ReadFile("/path/to/rds-ca-2019-root.pem")
    if err != nil {
        return nil, err
    }
    rootCertPool.AppendCertsFromPEM(pem)
    
    mysql.RegisterTLSConfig("rds", &tls.Config{
        RootCAs: rootCertPool,
    })
    
    dsn := "username:password@tcp(mysql-instance.region.rds.amazonaws.com:3306)/database?tls=rds&allowNativePasswords=false"
    
    return sql.Open("mysql", dsn)
}

-- Set strong password validation
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- Create user with strong password
CREATE USER 'secureuser'@'%' 
IDENTIFIED WITH caching_sha2_password BY 'SecureP@ssw0rd123!';

-- Require SSL for all connections
ALTER USER 'secureuser'@'%' REQUIRE SSL;

-- Require specific cipher suites
ALTER USER 'secureuser'@'%' REQUIRE CIPHER 'ECDHE-RSA-AES256-GCM-SHA384';

-- Require client certificates
ALTER USER 'secureuser'@'%' REQUIRE X509;

import boto3
import mysql.connector
import secrets
import string

def rotate_mysql_password(secret_arn, mysql_endpoint):
    # Generate new password
    alphabet = string.ascii_letters + string.digits + "!@#$%^&*"
    new_password = ''.join(secrets.choice(alphabet) for _ in range(16))
    
    # Update Secrets Manager
    secrets_client = boto3.client('secretsmanager')
    secret = secrets_client.get_secret_value(SecretId=secret_arn)
    secret_data = json.loads(secret['SecretString'])
    
    # Test connection with old password
    conn = mysql.connector.connect(
        host=mysql_endpoint,
        user=secret_data['username'],
        password=secret_data['password'],
        auth_plugin='caching_sha2_password'
    )
    
    # Update password in MySQL
    cursor = conn.cursor()
    cursor.execute(f"ALTER USER '{secret_data['username']}'@'%' IDENTIFIED BY '{new_password}'")
    conn.commit()
    
    # Update Secrets Manager
    secret_data['password'] = new_password
    secrets_client.put_secret_value(
        SecretId=secret_arn,
        SecretString=json.dumps(secret_data)
    )
    
    print(f"Password rotated successfully for user {secret_data['username']}")

-- Monitor authentication attempts and failures
SELECT 
    DATE(FROM_UNIXTIME(VARIABLE_VALUE)) as date,
    COUNT(*) as auth_attempts
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Connections'
GROUP BY DATE(FROM_UNIXTIME(VARIABLE_VALUE))
ORDER BY date DESC;

-- Check authentication cache hit rate
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME LIKE '%auth%cache%';

-- Monitor failed authentication attempts
SELECT 
    DATE(event_time) as date,
    COUNT(*) as failed_attempts
FROM performance_schema.events_statements_history_long
WHERE sql_text LIKE '%Access denied%'
GROUP BY DATE(event_time)
ORDER BY date DESC;

// HikariCP configuration for caching_sha2_password
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://mysql-instance.region.rds.amazonaws.com:3306/database");
config.setUsername("username");
config.setPassword("password");

// Optimize for caching_sha2_password
config.addDataSourceProperty("useSSL", "true");
config.addDataSourceProperty("requireSSL", "true");
config.addDataSourceProperty("allowPublicKeyRetrieval", "true");
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

// Pool settings
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);

HikariDataSource dataSource = new HikariDataSource(config);

-- Temporarily use mysql_native_password for incompatible clients
ALTER USER 'oldclient'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

-- Or upgrade client driver to support caching_sha2_password

# Download RDS CA certificate
wget https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem

# Configure client to use the certificate
mysql --ssl-ca=rds-ca-2019-root.pem \
      --ssl-mode=VERIFY_IDENTITY \
      -h mysql-instance.region.rds.amazonaws.com \
      -u username -p

// Java connection string fix
String url = "jdbc:mysql://mysql-instance.region.rds.amazonaws.com:3306/database" +
            "?useSSL=true&allowPublicKeyRetrieval=true";

-- Check authentication cache performance
SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key_requests';
SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key_requests_total';

-- Monitor connection time
SELECT 
    THREAD_ID,
    PROCESSLIST_TIME,
    PROCESSLIST_STATE,
    PROCESSLIST_INFO
FROM performance_schema.threads
WHERE PROCESSLIST_STATE LIKE '%auth%';

-- Optimize authentication cache
SET GLOBAL caching_sha2_password_auto_generate_rsa_keys = ON;
SET GLOBAL caching_sha2_password_private_key_path = '/path/to/private_key.pem';
SET GLOBAL caching_sha2_password_public_key_path = '/path/to/public_key.pem';

import unittest
import mysql.connector
import time
from concurrent.futures import ThreadPoolExecutor

class MySQLAuthenticationTest(unittest.TestCase):
    def setUp(self):
        self.config = {
            'host': 'mysql-instance.region.rds.amazonaws.com',
            'user': 'testuser',
            'password': 'testpassword',
            'database': 'testdb'
        }
    
    def test_caching_sha2_password_connection(self):
        """Test basic connection with caching_sha2_password"""
        config = self.config.copy()
        config['auth_plugin'] = 'caching_sha2_password'
        
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor()
        cursor.execute("SELECT 1")
        result = cursor.fetchone()
        self.assertEqual(result[0], 1)
        
        conn.close()
    
    def test_concurrent_connections(self):
        """Test multiple concurrent connections"""
        def create_connection():
            conn = mysql.connector.connect(**self.config)
            cursor = conn.cursor()
            cursor.execute("SELECT CONNECTION_ID()")
            result = cursor.fetchone()
            conn.close()
            return result[0]
        
        with ThreadPoolExecutor(max_workers=10) as executor:
            futures = [executor.submit(create_connection) for _ in range(10)]
            results = [future.result() for future in futures]
        
        # Verify all connections were successful and unique
        self.assertEqual(len(results), 10)
        self.assertEqual(len(set(results)), 10)
    
    def test_connection_pooling_performance(self):
        """Test connection pooling performance"""
        start_time = time.time()
        
        # Create 100 connections
        for _ in range(100):
            conn = mysql.connector.connect(**self.config)
            conn.close()
        
        end_time = time.time()
        avg_time = (end_time - start_time) / 100
        
        # Should be able to create connections in under 100ms on average
        self.assertLess(avg_time, 0.1)
    
    def test_password_complexity(self):
        """Test password complexity requirements"""
        weak_passwords = ['password', '123456', 'qwerty']
        
        for weak_password in weak_passwords:
            with self.assertRaises(mysql.connector.Error):
                conn = mysql.connector.connect(**self.config)
                cursor = conn.cursor()
                cursor.execute(f"CREATE USER 'testuser2'@'%' IDENTIFIED BY '{weak_password}'")
                conn.close()

if __name__ == '__main__':
    unittest.main()

#!/bin/bash
# Load test authentication performance

MYSQL_HOST="mysql-instance.region.rds.amazonaws.com"
MYSQL_USER="testuser"
MYSQL_PASS="testpassword"
MYSQL_DB="testdb"

# Test function
test_connection() {
    mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" -D "$MYSQL_DB" \
          -e "SELECT 1" > /dev/null 2>&1
    echo $?
}

# Run load test
echo "Starting authentication load test..."
start_time=$(date +%s)
successful_connections=0
failed_connections=0

for i in {1..1000}; do
    if test_connection; then
        ((successful_connections++))
    else
        ((failed_connections++))
    fi
    
    if [ $((i % 100)) -eq 0 ]; then
        echo "Completed $i connections..."
    fi
done

end_time=$(date +%s)
duration=$((end_time - start_time))

echo "Load test completed in ${duration} seconds"
echo "Successful connections: ${successful_connections}"
echo "Failed connections: ${failed_connections}"
echo "Success rate: $(echo "scale=2; ${successful_connections}/1000*100" | bc)%"
echo "Average connection time: $(echo "scale=3; ${duration}/1000" | bc) seconds"