Why can't I manage the "reporting" user in my Aurora MySQL database?
I have an Aurora MySQL instance with a user that I can't manage. There is a "reporting" user that I can't change the password for, nor can I remove the user to recreate it. We have another, similar instance, that didn't have this user and I was able to create it and manage it just fine.
The only thing that I can see is that along with the "reporting" user is a "reporting_dms" user. This tells me that AWS DMS created these users at some point in the past. However, I still don't know why I wouldn't be able to manage them.
We did experiment with AWS DMS for a bit but decided not to use it. Does DMS create un-manageable users for some reason? If so, how can you remove them?
- Newest
- Most votes
- Most comments
You're right - the "reporting" and "reporting_dms" users were likely created by AWS Database Migration Service (DMS) when it was previously used with your Aurora MySQL database.
DMS creates these users to facilitate replication and they are given privileges that prevent them from being dropped or modified even by a database admin. This is done to prevent errors/interruptions if the DMS tasks are still active.
Since you're no longer using DMS, the proper way to remove these users is:
-
Delete any active DMS replication tasks associated with the Aurora database.
-
Reboot the DB instance to clear any DMS connections.
-
The "reporting" and "reporting_dms" users should now be deletable and you can drop them.
-
You can then create a new "reporting" user as needed for your purposes.
So in summary - deactivate/delete DMS tasks, reboot the database, then the DMS users can be dropped and recreated as regular users you control. This is the recommended way to clean up after DMS.
Relevant content
- asked 7 months ago
- asked 6 months ago
- asked 2 months ago
AWS OFFICIALUpdated 9 months ago- AWS OFFICIALUpdated 7 months ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago
I didn't have any DMS tasks or connections but I did have some DMS endpoints and subnets hanging around. After removing those DMS resources I tried to remove the "reporting" users but was unable to.
As this is a production system I'm not at liberty to reboot the DB very often (if at all). Will these DMS connections eventually time out and free up the users in question, or is it required to reboot the DB?
If you had DMS endpoints and subnets configured previously, even without active replication tasks, that could still result in DMS creating those special users in your Aurora database.
Since you've now removed all the DMS objects on the AWS side, the DMS connections to your database should eventually timeout after a period of inactivity. However, that timeout period can sometimes be weeks or longer.
So unfortunately, a reboot is likely still required to immediately drop those DMS users in your situation. The reboot clears out any remaining DMS connections or sessions that may be preventing you from managing those users.
Some options to consider for rebooting:
Schedule a maintenance window for the reboot to minimize impact.
Create a read replica and reboot that first to verify the process before rebooting the primary.
Consider using AWS Database Migration Service (AWS DMS) again temporarily to migrate data to a new Aurora instance. This would create a new clean database without the DMS users.
But in summary - no, simply waiting for timeouts is likely not practical, a reboot is still the most direct way to reclaim control of those users. Maintenance windows, read replicas, and DMS migrations can help make that process smoother.
Good news! I was (finally) able to remove the "reporting" users. It turns out that any DMS resources linked to your database will cause the connections to stay open. Removing absolutely everything DMS related from my system and waiting several days (a week?) was the trick.
Great news. I am glad you were able to fix it.