Create MySQL user on AWS RDS server

This post explains how to create a user account on AWS RDS servers and assign privileges to access tables. I did this on Maria DB server and I assume this process would work for Aurora DB instances too.

1. First you need to logon to the DB. You can use mysql utility for this.

   mysql -h your-dbserver.us-east-1.rds.amazonaws.com  -u username -p

   You can find full name of your cluster from the instances page in AWS RDS console.
   username – The username specified while creating the DB instance. You can find this from the details page for the instance.

   If the command is not successful(runs longer and does not return), check the security group rules for your DB server. You need to change inbound rules to include the IP of the host from where you are running the mysql command from.

2. Now create a user account using the below command

   CREATE USER 'dbuser'@'client-host-ip' IDENTIFIED BY 'password';

   Here use the IP of the host from where your application will be connecting to the DB.

3. Next assign privileges for the user to access/update the DB.
   Granting all priviliges did not work as shown below.

   mysql> GRANT ALL PRIVILEGES ON * . * TO 'dbuser'@'123.45.67.89';
   ERROR 1045 (28000): Access denied for user 'dbadmin'@'%' (using password: YES)

   I had to run the command separately for each privilege I wanted to assign.

   mysql> GRANT SELECT ON * . * TO 'dbuser'@'123.45.67.89';
   Query OK, 0 rows affected (0.01 sec)
   mysql> GRANT INSERT ON * . * TO 'dbuser'@'123.45.67.89';
   Query OK, 0 rows affected (0.00 sec)
   mysql> GRANT DELETE ON * . * TO 'dbuser'@'123.45.67.89';
   Query OK, 0 rows affected (0.00 sec)
   mysql> GRANT CREATE ON * . * TO 'dbuser'@'123.45.67.89';
   Query OK, 0 rows affected (0.00 sec)
   mysql> GRANT UPDATE ON * . * TO 'dbuser'@'123.45.67.89';
   Query OK, 0 rows affected (0.01 sec)
   mysql> GRANT ALTER ON * . * TO 'dbuser'@'123.45.67.89';
   Query OK, 0 rows affected (0.00 sec)

I did the above to migrate my wordpress database to RDS and could successfully do this.