Mysql – Privileges discrepancy

MySQL

When I am logged in as rds_user and have following privileges:
GRANT SELECT, INSERT, UPDATE, DROP ONnews.* TO 'rds_user'@'%'

My code runs INSERT command just fine, but when it gives ProgrammingError it executes UPDATE instead. However, UPDATE command errors out with
1142 (42000): UPDATE command denied to user 'rds_user'@'XX.XX.XX.XX' for table 'data'

data is a table in db news

I tried granting privilege again and flushed privileges, still same error. I am using Python's sql connector to send the commands. Genuinely confused on what is going

Best Answer

You're logged in as rds_user, but you may not actually be logged in as 'rds_user'@'%'.

Privileges are actually granted not just per-username, but per username/host combination. When you log in, you may be logging in as rds_user associated with a specific host, and that user may have less privileges.

Log in and run SELECT CURRENT_USER(); to see which user you're actually logged in as.

Run SELECT * FROM mysql.user WHERE user = 'rds_user'; to see all the users you could be logged in as.