MySQL and Amazon RDS DBA user

2
votes

I have a new MySQL setup in Amazon RDS. Created the "Master User" when launching the instance. Able to connect without issue. Then noticed that user was not a DBA. When trying to fix this in MySQL Workbench the following error appears; 

Error changing account MyDBA@%%: Access denied for user 'MyDBA'@'%%' (using  password: YES)

How do I fix this? Is in Amazon RDS someplace? In Workbench? New to MySQL so sorry for the simple question but I cannot find an answer.

edit: Just to be clear MyDBA is the master user created in RDS when the instance created, so I have no other account to log in as

1
mysqlamazon-web-services
You will need to be logged in as root or another DBA to change the privileges on MyDBA - apesa
That is the problem, I am logging in with the user that was created with instance in Amazon RDS - Xaphann
@Xaphann That user is neither root nor a DBA. - ceejayoz

1 Answers

5
votes

User roles are not an actual MySQL Server concept. That's something the Workbench developers just made up, in their characteristically feeble attempts to be "helpful."

To aid in assigning privileges to MySQL Server users, MySQL Workbench introduces the concept of Administrative Roles.

[...]

DBA: Grants all privileges

— http://dev.mysql.com/doc/workbench/en/wb-mysql-connections-navigator-management-users-and-privileges.html

The problem is, "all privileges" are not available on RDS.

In order to deliver a managed service experience, Amazon RDS [...] restricts access to certain system procedures and tables that require advanced privileges.

— http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html

Specifically, you cannot have the SUPER privilege on RDS. Not even the master user has this.

You also cannot grant a privilege to a user that you don't possess yourself, so creating a different user and trying to give them the DBA "role" will not work, either.