Check user rights before attempting to CREATE DATABASE

Some hosters let your script CREATE DATABASE, some do not, and require you to use their control panels instead. How do I check whether database would let me run this query with given user/password without actually creating any databases?

Because if I don’t have enough rights, I just want to hide the “Create database” button from UI altogether.

I’m using PHP and MySQL. Thanks.

From stackoverflow

• Just create a test script

  <?php
  mysql_connect(...);
  $result = mysql_query('CREATE DATABASE testDB');
  if (!$result)
  {
      //Failed
  }
  else
  {
      mysql_query('DROP DATABASE testDB');
  }
  mysql_close();
  ?>
  

  You could define a variable in the IF then write that to a config file.

  thomasrutter : I was writing an installer that needed to check the same thing, and this is the best solution I came up with too. I added a hash to the end of the table name though so it was even less likely to conflict with an existing table.

  thomasrutter : Or you could add an extra check to see if testDB already exists, to prevent trashing someone else's test database (please note the typo in the second SQL command).

  Unkwntech : Adding some random string may not be a bad idea, if your code will be used on other peoples servers.

• You can obtain rights with:

  SHOW GRANTS FOR CURRENT_USER;
  

  Ilya Birman : Sounds cool, thanks. And how to check whether CREATE DATABASE is allowed? Currently it returns: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION. What else can it look like?

  vartec : It's either ALL PRIVILEGES, or a coma separated list of privileges as defined here http://dev.mysql.com/doc/refman/5.1/en/grant.html

  Ilya Birman : In the list there is a line CREATE - Enable database and table creation. But my task is to “catch” a situation where you *are* allowed to create tables, but *not* databases. I guess, in such case CREATE will be not granted on *.*, but only on SomeDB.*, right?

  Ilya Birman : Thanks once again. Looks like I will have to partially re-implement MySQL query parser in PHP :-)

  vartec : You might try to directly SELECT from system tables (mysql.user and mysql.db). There is column Create_priv.

• As Vartec said, SHOW GRANTS FOR CURRENT_USER is the way to go. I'm just adding this answer to show the output of that statement when you don't have full rights:

  GRANT USAGE ON *.* TO 'myusername'@'%' IDENTIFIED BY PASSWORD '*8D4A4D198E31D6EA9D7997F7B29A2BCA254178B6'
  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE ROUTINE ON `mydb1`.* TO 'myusername'@'%'
  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE ROUTINE ON `mydb2`.* TO 'myusername'@'%'
  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE ROUTINE ON `mydb3`.* TO 'myusername'@'%'