I am trying to create an auto install script for a popular PHP web application. I am trying to create a database, create a user and grant privileges to the user all in PHP. I use the following code to try and do this...
mysql_query("create database ".$db);
// make and grant privs for db user
mysql_query("GRANT ALL PRIVILEGES ON ".$db.".* TO '".$db_user."' IDENTIFIED BY '".$db_user_pass."'");
My problem is all of the DBs that are created are listed as having the host as % in the 'mysql' database on the server. When I look at the privileges tab when viewing the database in phpMyAdmin I see this:
username_database % wildcard: username_dbusername
Instead of % it should be localhost and instead of wildcard: username_dbusername it should say database-specific. When viewing databases in CPanel it lists the database and the user but it does not show that the user was added to the database.
Bear in mind that in order for this to function you need to run the GRANT syntax as the root user. I'm sure you're aware of this and are probably connecting as root to your database, but I would urge you to be VERY careful about how you go about this. Especially if you allow someone to input information directly from a web browser to set this up. Escape your input and sanatize it, the last thing you want is someone with root mysql privileges adding their own users and messing up databases/tables.