GRANT ability to CREATE new tables, but not databases

Databases
1

Okay… I’m looking through the MySQL documentation and it looks like:

GRANT ALL PRIVILEGES ON `db` . * TO 'user'@'localhost';

gives ‘user’ the ability to CREATE not only new tables, but to create new databases as well

The ALL or ALL PRIVILEGES privilege specifier is shorthand. It stands for “all privileges available at a given privilege level” (except GRANT OPTION). For example, granting ALL at the global or table level grants all global privileges or all table-level privileges.

The CREATE privilege enables creation of new databases and tables.

Is there a (simple) way to give a user all privileges on a particular database (create tables, etc. etc.) without the ability to create new databases?

This seems like a security hole, but I’m assuming it wouldn’t be allowed if it were…

Thanks,

Monte

2

I just tried, and wasn’t able to create a new database.

When logged in as that user, do you get a different value for


select user(), current_user();
3

No, I don’t.

I really should have saved that database when I had it set up originally (since deleted). Using phpMyAdmin I had created a database, then added a user with all privileges on that database, then logged out as root and back in as the new user. Right there on the front page was the option to create a new database, and when I tried it, it created a new one titled ‘elvis?store’ (the original was ‘elvis_store’, which I was then able to add tables to, etc. The new user couldn’t DROP the new database, though. Whatever I did, it was somewhat repeatable, as I logged back in as root to delete the database(s) and recreated them the same way twice.

Now… the front page on phpMyAdmin shows ‘Create Database’ with ‘No privileges’ right underneath and won’t let the new user create a new database.

I had cut-n-pasted the SQL output from phpMyAdmin before…


CREATE USER 'elmer'@'localhost' IDENTIFIED BY '***';

GRANT USAGE ON * . * TO 'elmer'@'localhost' IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT ALL PRIVILEGES ON `elvis_store` . * TO 'elmer'@'localhost';

And here is what I get this time through…

CREATE USER 'bar'@'localhost' IDENTIFIED BY '***';

GRANT USAGE ON * . * TO 'bar'@'localhost' IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT ALL PRIVILEGES ON `foo` . * TO 'bar'@'localhost';

Not sure I can figure where I went astray. Now things seem to be working more or less the way I’d anticipated… but I have no idea what changed?!?

4

I’m not a dba. But, if I were to guess, you have an anonymous account for localhost. You could then log in as bar, omit supplying a password, and you will have the privs of the anonymous account. The anon account probably has the the global create privilege.

I’m not sure how this all ties in with phpmyadmin(I didn’t bother trying, too hard to know what the hell it’s really doing), but I tried from the command line client.

Theres some info here
http://dev.mysql.com/doc/refman/5.1/en/connection-access.html
http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html

5

Hmmm… what I have is XAMPP installed on a usb stick, using PortableApps to launch the XAMPP control panel to start Apache and MySQL. Generally I use phpMyAdmin rather than fight the stunted Windows command line environment - I don’t consider myself a command-line commando, but it sure is a lot easier to find information on stuff on a system with man or info :wink:

Before now I’d never really bothered to try using mysql from the command line; rather I used the interface in phpMyAdmin or used its SQL window, primarily because the paths keep changing depending on what machine I’m logged on - I finally sat down and figured it out so I’ll try doing some of my admin stuff from the mysql CLI in the future.

Thanks,

Monte

6

Think I may have figured something out… not sure if its a bug in phpMyAdmin or what. Basically, if I create a database with ‘’ in the middle of the name, such as ‘elvis_store’ or ‘foo_bar’, and then create a user and grant him privileges on that database… when I log back in as that user, I then have the ability to create other new databases. If I create a database without '’ in the middle, then a new user, then that user does not have the ability to create new databases (as expected).

Actually… I wonder if it’s a MySQL thing, not a phpMyAdmin thing. I duplicated the above steps from the mysql console (Windows command line terminal) with the same results: a db with ‘_’ in it allows a user some funky privileges, even though ‘SHOW GRANTS’ doesn’t look any different?!?