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 ...
"That's odd," I thought, and I tried again with a slight variation.
Interesting. I can grant all privileges to a database, but not to all databases? Well, there needs to be a way to do it because I will be creating and removing testing databases on the fly and I will need access to create and remove these test databases without logging in as the master account.
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:
Now I can also create more users, such as "frank" and then "
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.