Issue creating MySQL user on RDS Instance (AWS)

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