I was recently put in charge of migrating some data to Amazon's RDS. This whole cloud thing has got some serious potential, and I like how Amazon has set things up. However, when I tried to grant all privileges to my user ... mysql> GRANT ALL ON *.* TO fidian@`%`; ERROR 1045 (28000): Access denied for user 'masterAccount'@'%' (using password: YES) mysql> GRANT ALL PRIVILEGES ON *.* TO fidian@`%`; ERROR 1045 (28000): Access denied for user 'masterAccount'@'%' (using password: YES) mysql> GRANT ALL ON *.* TO myUser@`%`; ERROR 1045 (28000): Access denied for user 'masterAccount'@'%' (using password: YES) After much Googling and reading of the MySQL grant syntax, I assured myself that I'm not totally crazy and that Amazon just has their RDS instance locked down to prevent me from going in and mucking things up. I also read this tiny gem: The “_” and “%” wildcards are permitted when specifying database names in GRANT statements that grant privileges at the global or database levels.Fantastic! Let's put this into action: mysql> GRANT ALL ON `%`.* TO fidian@`%`; Query OK, 0 rows affected (0.00 sec) GRANT ALL ON `frank\_%`.* to frank@`%`; " in order to allow this user full access to just his tables.I always like knowing the root cause of why problems exist, so I felt I should be able to explain why * fails and `%` succeeds. It turns out that your master account doesn't have all privileges on the database. It has most privileges (not FILE, SUPER, REPLICATION_SLAVE nor CREATE_TABLESPACE). I'm not positive, but I believe that since the master account doesn't have SUPER, the mysql.* tables are considered off-limits. Since I don't have access to the mysql.* tables, I can't grant permissions on *.* since that would match mysql, and `%`.* appears to not match those system tables. So, the moral of the story is to use `%`.* instead of *.*. And, come to think of it, not granting permissions to the mysql table for all of your users is probably a good thing anyway. |