Allowing Non-Root RDS MySQL Users to View and Kill Queries

This article explains how you can configure non-root RDS MySQL users to view and kill queries. By default, only the root user in Amazon RDS MySQL has the necessary permissions to list and terminate queries. However, you can grant non-root users specific privileges to achieve this without elevating their access level excessively.

Before you do this, please consider all the pros and cons of this action within your environment.

Pros and Cons of granting such permissions:

Pros:

• Improved Query Management: Non-root users can proactively monitor and terminate problematic queries, reducing database performance issues.
• Reduced Admin Workload: Database administrators do not need to intervene as frequently to manage slow or blocking queries.
• Faster Incident Resolution: Developers or support teams can quickly address performance issues without waiting for root-level intervention.

Cons:

• Potential for Misuse: Users with PROCESS or EXECUTE privileges might inadvertently kill important queries, leading to disruptions.
• Security Risks: Granting broader access increases the attack surface, requiring careful monitoring and access control.
• Compliance Considerations: In regulated environments, granting these permissions may violate security policies if not properly audited.

By carefully managing these permissions and monitoring their usage, organizations can strike a balance between security and operational efficiency.

Granting Permissions to View Queries

To allow a non-root user to list running queries, grant the PROCESS privilege:

GRANT PROCESS ON *.* TO 'test-non-root-user'@'10.0.0.0/255.255.255.0';
FLUSH PRIVILEGES;

The user can then run the following command to see active processes:

SHOW PROCESSLIST;

-- or --

SHOW FULL PROCESSLIST;

Alternative Approaches

If granting PROCESS privileges are not preferred, consider these alternatives:

1. Check the Slow Query Log: Identify long-running queries by analyzing the AWS RDS slow query log.
2. Use RDS Datadog Integration: Monitor query performance through Cloudwatch/Datadog/New Relic RDS integration.

Granting Permissions to Kill Queries

To allow a non-root user to terminate queries, grant them EXECUTE permission on the mysql.rds_kill stored procedure:

GRANT EXECUTE ON PROCEDURE mysql.rds_kill TO 'test-non-root-user'@'10.0.0.0/255.255.255.0';
FLUSH PRIVILEGES;

Then run the following command to test it:

CALL mysql.rds_kill(<query-id>)

This enables users to terminate problematic queries without full admin rights, improving database management while maintaining security best practices.