ansaurus

Question

Check user rights before attempting to CREATE DATABASE

Answer 1

+1 A:

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.

Unkwntech 2009-03-04 09:35:43

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 2009-03-04 09:44:17

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

thomasrutter 2009-03-04 09:45:19

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

Unkwntech 2009-03-04 13:16:56

Answer 2

+3 A:

You can obtain rights with:

SHOW GRANTS FOR CURRENT_USER;

vartec 2009-03-04 09:49:14

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?

Ilya Birman 2009-03-04 09:55:36

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

vartec 2009-03-04 09:58:43

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 2009-03-04 10:14:04

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

Ilya Birman 2009-03-04 11:06:25

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

vartec 2009-03-04 11:14:31

Answer 3

+1 A:

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'@'%'

nickf 2009-03-04 10:22:21

ansaurus

tags:

views:

answers:

Check user rights before attempting to CREATE DATABASE

related questions