Grant permission on RDS read replica

Question

I have an RDS read replica instance. I have set the read_only flag to 0 to make any write operation possible. But where I am getting blocked is that the user in the read replica has only SELECT privilege. When I'm running grant privileges on the existing user it gives me error: Access denied for user.... How to grant?

Answer 1

as your database's admin user, you can grant access to your other user using the GRANT command and FLUSH commands below. i'm using 'widgets' as the db name and 'widget_user' as the user you're granting access to, just for example:

db> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON widgets.* TO 'widget_user'@'%';
db> FLUSH PRIVILEGES;

this GRANTs the widget_user the ability to select data, insert data, update data, delete data, drop tables/columns/indexes, alter tables/columns/indexes and create indexes on the widgets database.

you can find more detailed explanations and examples in this tutorial: http://www.mysqltutorial.org/mysql-grant.aspx