Creating A New MySQL User In Amazon RDS Environment
I need to create a new MySQL user with limited permission on an existing Amazon RDS instance. After encountering a couple error messages I was sort of able to do this using the official MySQL Administrator tool and the user now appears in the list. However, I'm unable to assign any schema privileges as all the users are greyed out. I'm logged in as the "master user" created when the instance was launched. Not sure where to go from here. I do have the RDS command line tools installed but wasn't able to track down anything there either. Ideas
Answer
Your best bet is probably to connect to the database with a mysql command line client and call the SQL commands to create a new user and assign him privileges.
For instance, you might run something like this:
mysql -u [your_master_username] -p -h YOURRDSENDPOINT.rds.amazonaws.com
CREATE USER 'jeffrey'@'%' IDENTIFIED BY 'somepassword';
GRANT SELECT ON [your_database].[some_table] TO 'jeffrey'@'%';
On windows you could use the mysql.exe client, wherever that is.
Useful Docs
AWS RDS security groups documentation (a common area of confusion): http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithSecurityGroups.html
User creation documentation: http://dev.mysql.com/doc/refman/5.5/en/create-user.html
Privilege granting documentation: http://dev.mysql.com/doc/refman/5.5/en/grant.html