Restrict Drop Database, Not Table

[Jon T]

Is it possible to restrict my users from Dropping their database, but still allow them to drip their tables and data?

Getting tired of restoring Databases because some one dropped the wrong item. ;)

[raf]

Can you give a bit more info?

Do you mean through an adminsection (where all users will probably use the same mySQL account, and where you'll need to take care of it inside the application-layer) or through the mySQL account --> if each user gets a mySQL account and thus interfaces with the mySQL server through a db-front or the commandline ?

[Jon T]

Hey thanks for the quick response as always raf!

:thumbsup:

through the mySQL account --> if each user gets a mySQL account and thus interfaces with the mySQL server through a db-front or the commandline ?

Their are actually two interfaces,

1. A CP (ControlPanel) that allows them though the web to Create a Database, view information, update user information, and test select statements and Delete the Database. ( This could all be done though the ROOT user as it is a web interface and the cust needs to do their own login to even see it.

2. Third Pary Applicaton like MySQLFront and MySQLCC to manage their own data, but we want to prevent them from Deleting their Database from here. We can use Root from the web interface to run the deletes, but do not want them to be able to delete their Databas's from any controlcenter other then the web one we have.

Looking for that second layer of permissions that does not allow the customer to DROP their database, but DROP everything else like Tables and Data.

[raf]

Errr ... I don't get it.

So you've got CP. Ok. And if i understand it correctly, they are permited to drop the db from here?
But not through other db-clients.
?We can use Root from the web interface to run the deletes, but do not want them to be able to delete their Databas's from any controlcenter other then the web one we have.
But how could they use another client, since they don't have the mySQL root username or password? You can't login to MySQLFront without username and pwd, so just don't disclose these to the users.

I'm not axactly getting the problem :confused:

[Jon T]

Actually you can login with any user to MySQL-Front. The From Host field needs to be %. This permits them to login using their server name / IP or any valid server name we provide them with.

( Not sure if this opens us to hackers though )

So, we have a web based ControlPanel for the customer to manage their domain. They can create SQL or MySQL Databases though this web ControlCenter, but not manage their data, only add or delete a database.

Next we need to allow them to use an appliation like MySQL-Front, or MySQLCC or even MyPHPAdmin to manage their tables and data, NOT the database.

Now granted, in MyPHPAdmin, I do not see a way for the USER to drop a database, so we may be forced to use this.

[raf]

Actually you can login with any user to MySQL-Front. The From Host field needs to be %. This permits them to login using their server name / IP or any valid server name we provide them with.
I have MySQLFront on my development and i most definitely need a mysql -username ad password to connect to a db

You aither did not set a pwd for the root account (which is about tbe most biggest securityrisk you will ever be able to take) or i don't understand your situation. Check out http://www.mysql.com/articles/ddws/7.html for a second opinion. Relying on the IP or referer is very insecure since they can easely be spoofed. There is realy no excuse to not create a useraccount that requires pwd-authentication.
( Not sure if this opens us to hackers though )
I'm pretty sure that anyone can do whatever he wants with your dataserver.
Next we need to allow them to use an appliation like MySQL-Front, or MySQLCC or even MyPHPAdmin to manage their tables and data, NOT the database.
MySQL Front has a quite comprehensive useraccount section where you can specify the permissions on both db and table level (+ alowed operations) and where you can create MySQLFrot accounts as well. (it's just a matter of unchecking 'drop' )
<edit>You can even use this with your 'identify by IP' accounts to accomplish your goal.(but there still need to be seperate mySQL accounts for each user or usergroup (= IP or server)</edit>

But i suppose your next minute should be spent on checkin/setting a pwd for your rootaccount ...

[Jon T]

heh, I may be a designer, but Im not that daft. :D

Yes, root is set, it was the first thing I did after install. :thumbsup:

Attached are some screens, the first is showing the admin login with all the databases on the left, next is user login with ONE database on the left, and finally, the user prefs page with % showing for the Host.

As you can see, with the root user name and password set, as long as I change the host to %, the user can login using any valid ip / domain on that web server, which, in turn, giving them the ability to Delete their Database.

Now, the funny thing is, the user can not CREATE a new database, but if they delete their database, they can re-create it, as long as they use the EXACT same name.

:thumbsup:

I am guessing I will need to do some further research here...

[Jon T]

Oh! ...and if applications like MySQL-Front and MySQLCC are strictly for Admin use, then what is usable for the end user? MyPHPAdmin?

:confused:

[raf]

I am getting more and more comfused.

I'm not sure what you want to tell with the screenshot.

My point was that
- you should not maintain mysql-accounts without pwd requirement. There is no way that you will be able to secure the server if you rely on the refere of the request.
- you should not use the root-account for the webserver ! Inside your php or whatever web-dbfrontend that you use, you should use another mySQL account to access the db. One with only the permissions that your webapplication requires
Only the DBA should have acces using the root account.

Oh! ...and if applications like MySQL-Front and MySQLCC are strictly for Admin use, then what is usable for the end user? MyPHPAdmin?
phpMyAdmin is also for db administration ... (You don't need to be an 'admin' to do basic administration)
I don't know what you mean by end-user. If you mean a user that just needs to get limited acces to his (or a limited set) of db's, and should only be allowed to perform a very limited set of operations through webaccess, then you could write your own small interface.
But i don't see why you shouldn't be able to use MySQLFont, unless you are not satisfyed with the permission-options you have when you create the mySQL and MySQLFront account.

About the create db issue --> beats me. Probably because the db isn't removed from all systemtables yet. Or because there was still an active connection that required that db or so. There is not much i can say about it. You would need to chack your logs for that.
Or you'd need to look inside the mysql/data directory to see if the files and diretory are removed after you dropped the db.

[Jon T]

- you should not maintain mysql-accounts without pwd requirement. There is no way that you will be able to secure the server if you rely on the refere of the request.

Their is a user account for each DB, and each user has their user name and password associated with only their DB's. I have checked this in the MySQL Tables / Permissions Tables.

End-Use, is in effect the customer as you say, user with limited permissions for just using a single DB that only THEY have permissions to.

[raf]

Like is said before : if you create a mySQL usreaccount, then you can specify which actions they have permission to. If you don't give them permission to drop databases, or to drop one specific db or whatever, then they can not do this when they connect to the mySQL server with that account.
End of story. I realu don't understand what extra issue there is, since you seem to have created their accounts :confused: Like i said : MySQL front has a usermanagement section that lets you set up/alter their permissions, so log in as root, and open it (it's the icon with the 2 small faces, near the right border of your screenshot)

And it doesn't matter which db-frontend they use. The mySQL permissions stay the same.
If you build a webfront, then they have the options that you supply inside your webfront. Or, worst case scenario, if your username and pwd is disclosed, they'll have the permissions of the account that you use inside your php pages (which shouldn't be allowed to drop the db's)

So all you need to do is make sure that each user (or usergroup) can be identified (with your mySQL accounts username and pwd) and that they can acces the server (by specifying the host) --> using a wildcard hor the host is realy unsave. You better specify a list a servers/IP's.

Personally, i would write a small interface. Itis justthe easiest and most secure way.
It o course depends on what useractions the user should be allowed. If they just need basic stuff like creating a table and altering it a bit, then it's no problem to write it yourself. I wrote a page last week to create a table/add columns/delete columns/drop table, which took me about 2 hours. I should expand it to create indexes, rename variables, reorder columns etc, but it's not all that dificult.