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 "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.