Issue creating MySQL user on RDS Instance (AWS)

MySQL-service-going-up-and-down

Recently I’ve been having issue creating MySQL user on RDS instance, suprisingly it is straight forward task as it seems.

You can do this to create admin-level user for my MySQL databases. It can be used to replace the ‘root’ user with another full-permissions user, for slightly improved security.


I was able to figure out the issue after going through MySQL documentation.

The thing which wont work :

Create User demo@'localhost' IDENTIFIED BY "demo@password";GRANT ALL PRIVILEGES ON *.* TO 'demo'@'localhost' WITH GRANT OPTION;
FLUSH privileges;
Create User demo@'%' IDENTIFIED BY "demo@password";GRANT ALL PRIVILEGES ON *.* TO 'demo'@'%' WITH GRANT OPTION;
FLUSH privileges;
Create User demo@'localhost' IDENTIFIED BY "demo@password";GRANT ALL PRIVILEGES ON *.* TO 'demo'@'%' IDENTIFIED BY "demo@password";
FLUSH privileges;
Create User demo@'localhost' IDENTIFIED BY "demo@password";GRANT ALL PRIVILEGES ON *.* TO 'demo'@'%' IDENTIFIED BY "demo@password";GRANT ALL OPTIONS;FLUSH privileges;

Types of error you will come across :

Error Code: 1045. Access denied for user 'demo'@'%' (using password: YES)
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%'.* TO 'demo'@'%' WITH GRANT OPTION' at line 1

SOLUTION :

After many attempts I was able to figure out the correct syntax :

CREATE USER 'demo'@'localhost' IDENTIFIED BY 'demo@password';

GRANT SELECT,INSERT,UPDATE,DELETE,DROP  ON `*`.* TO 'demo'@'%' IDENTIFIED BY 'demo@password' WITH GRANT OPTION;

FLUSH PRIVILEDGES;

Let me know if this does not work in comments section below.

References : 

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.MasterAccounts.html

Hope this help!

-Bhargav


Blogger & Assc Cloud Architect

Site Footer

%d bloggers like this: