Limiting user database creations

kevvy

I have a webhosting control panel that allows you to create databases. You can set the amount of databases per user which I set to 10. How ever a user could SSH in and create a database via console. Is there a way to limit user database creations within mysql itself?

piersk

Could you just not give them access to the database control when they log in using SSH?

kevvy

Originally posted by piersk
Could you just not give them access to the database control when they log in using SSH?

They could still execute a mysqladmin create command through php/cgi unless I disable mysql itself. I just need a way to limit users database creations in mysql itself. So that any other 3rd party scripts cannot create db based on mysql limit.

piersk

The look in the MySQL manual for the bit that tells you how to give certain users access to certain things in the db. I know it can be done, but I cant remember where abouts in the manual it is...

siwis

Surely if you have unique names for your database users, if you set the permissions correctly for that user ie disallow CREATE then even from the command line they would be unable to create any more databases that way. I'm assuming that your db admin cp runs using the root mysql username and password for actual creations but with all other functions using the log-in username/pw combo???

I could of course be barking up totally the wrong tree here.

kevvy

Originally posted by siwis
Surely if you have unique names for your database users, if you set the permissions correctly for that user ie disallow CREATE then even from the command line they would be unable to create any more databases that way. I'm assuming that your db admin cp runs using the root mysql username and password for actual creations but with all other functions using the log-in username/pw combo???

I could of course be barking up totally the wrong tree here.

The mysql runs on its own ownership/group. The CP adds the database name via username_dbname.

siwis

You missed my point.
Anything that is able to create a database in MySQL has to have ROOT MySQL priveledges. BUT individual users should be given their own usernames and have unique permissions for their own databases ONLY such that they cannot access anyone elses.

Your CP must have ROOT priveledges within MySQL or it wouldn't be able to CREATE a database. But when one of your users logs in to that CP, they should be restricted according to their own priveledges (hense you as admin can restrict how many databases that user is entitled to create.

SO, as it is the root priveledges within your CP that is used to CREATE the db's, why not simply run restricted permissions on all users effectively barring the CREATE unless it is carried out via the CP.

Does that make sense?????

PS Shell Access???? Braver man than I!

piersk

Originally posted by siwis
PS Shell Access???? Braver man than I!

Yeah, do they REALLY need shell access? Could you not write php scripts so that they can do stuff via forms? That way you can put even more control over what you do.

siwis

Standard GRANT command for MySQL:

GRANT
select, insert, update, create, alter, delete, drop
ON
username_.
TO
username@localhost
IDENTIFIED BY
'password';

I'm not sure whether the widlcard will be allowed after username_ but it might be worth a shot.