I see this question asked in #mysql a lot, so I’m just making a post here.
Scenario: You have user a, that needs to be able to create and drop databases at will, but you don’t want to give him full root access.
Solution: Use the GRANT statement, to create a user with full access to a limited set of databases.
GRANT ALL ON `usera_%`.* TO usera@localhost IDENTIFIED BY ’somepassword’ WITH GRANT OPTION;
What this does, is allow usera, to create databases that follow the usera_foo naming convention. This way, you can see all the databases he’s created/dropped etc, and he’s a happy power user.
