Opened 11 years ago

Closed 11 years ago

Last modified 11 years ago

#539 closed Feature/Enhancement Request (fixed)

We should provide users with CREATE database privileges.

Reported by: Jamie McClelland Owned by: Jamie McClelland
Priority: Medium Component: Tech
Keywords: database security Cc:
Sensitive: no

Description

This ticket is being opened in response to ticket #537.

I'm not convinced we can do this in a secure way, but I'm opening this ticket to explore that possibility.

It is possible to separate CREATE from DROP - so a member could create new databases without being able to drop other people's databases. However:

  • They still wouldn't be able to drop their own database (or can you give someone drop database privileges limited to a single database?)
  • They would not be able to grant their user privileges on their new database (unless they had the privilege to grant their user privileges to any arbitrary database, which would be a security hole).

Other ideas on how to do this?

Change History (10)

comment:1 Changed 11 years ago by Matt Chapman

Is it an option to run cpanel and use its mysql management tools?

In the meantime, given that Union Web Services is a trusted partner and brings a significantly large portion of your business, perhaps you could consider us an administrative user and ignore the potential for abuse in giving CREATE and GRANT permissions, knowing that we will not abuse such privileges.

comment:2 in reply to:  1 Changed 11 years ago by Jamie McClelland

Replying to http://mchapman2000.myopenid.com/:

Is it an option to run cpanel and use its mysql management tools?

cpanel is proprietary software and is not supported in Debian. Installing software that we can't freely use for such a critical (and security sensitive) task is not really an option for us.

Somehow - this issue has never been entered for our control panel - so I just entered a ticket to add mysql to the services controlled by our members control panel. It is something we'd like to add - just not something we've had a chance to add.

In the meantime, given that Union Web Services is a trusted partner and brings a significantly large portion of your business, perhaps you could consider us an administrative user and ignore the potential for abuse in giving CREATE and GRANT permissions, knowing that we will not abuse such privileges.

Yes - I'll raise this with the other folks here and get back to you. It seems pretty reasonable to me.

comment:3 Changed 11 years ago by Daniel Kahn Gillmor

As long as we meet a few criteria, i see no reason why every MF/PL member shouldn't have database creation privileges.

The criteria that i think are relevant (some of these are trivial, but i think they're worth stating anyway):

  • we must have a convenient way to monitor the number of databases and the amount of space they take up.
  • system administrators should be alerted when system limits are approached, or when unusual trends appear (e.g. 1000 new databases in an hour)
  • members should be able to drop the databases they created (i.e. databases they own), but only those databases.
  • members should be able to grant or revoke privileges to existing accounts, but only for databases that they own.

I know that postgresql has a very clean model for doing something like this. It's as simple as running some flavor of identd for connections on the same host, and it's flexible enough to use pluggable authentication techniques when dealing with cross-host connections. I don't know MySQL well enough to know how to implement it immediately there.

comment:4 in reply to:  3 Changed 11 years ago by Jamie McClelland

Replying to https://id.mayfirst.org/dkg:

As long as we meet a few criteria, i see no reason why every MF/PL member shouldn't have database creation privileges.

I agree and I think your criteria are good - but I think they are criteria for how we should [tickiet:650 implement this feature in the members control panel]. This ticket is asking, given the limitations of MySQL described in the original ticket description, should we make an exception and give UWS database granting privileges?

comment:5 Changed 11 years ago by Daniel Kahn Gillmor

I suppose i'm not seeing the limitations of MySQL described in the original ticket description, and it's not clear to me how we would be able to grant only CREATE privileges for a given user. I acknowledge that i don't know the MySQL syntax terribly well, though, as i'm more comfortable with postgresql.

Reading the GRANT and CREATE DATABASE documentation, it's not clear to me that there's a way to specify "this user can create new databases which they then own" semantics.

Perhaps we would CREATE a PROCEDURE that creates a databased named after the first argument if it doesn't already exist, and somehow grants the invoker privileges on it? If there's a recipe to do what you're suggesting in MySQL without building out the control panel infrastructure, i'm not seeing it. Pointers?

comment:6 in reply to:  5 Changed 11 years ago by Jamie McClelland

Replying to https://id.mayfirst.org/dkg:

I suppose i'm not seeing the limitations of MySQL described in the original ticket description, and it's not clear to me how we would be able to grant only CREATE privileges for a given user. I acknowledge that i don't know the MySQL syntax terribly well, though, as i'm more comfortable with postgresql.

Reading the GRANT and CREATE DATABASE documentation, it's not clear to me that there's a way to specify "this user can create new databases which they then own" semantics.

I think we're in agreement here Daniel - and that's what I tried to convey in the original ticket - that I don't think we can do it securely for the very reasons you raise in this comment and I raised in the original description.

Perhaps we would CREATE a PROCEDURE that creates a databased named after the first argument if it doesn't already exist, and somehow grants the invoker privileges on it? If there's a recipe to do what you're suggesting in MySQL without building out the control panel infrastructure, i'm not seeing it. Pointers?

This gives me a different idea - why not do this with a bash script? Most users not comfortable with the command line can continue requesting databases via the ticket tracking system, but any user comfortable with ssh could run a simple bash script that creates a new database and a new user. The only problem would be: how to prevent someone from creating a database with the same name as a database on another server?

comment:7 Changed 11 years ago by Matt Chapman

What's the status on this? I will be working on several new sites this month, needing db's for Drupal.

I agree that a Command Line based solution is acceptable.

Alternatively, I know phpMyAdmin does support creation of database, if configured to do so. Perhaps this feature could be customized to address your security concerns?

comment:8 Changed 11 years ago by Jamie McClelland

Ok - I should be getting this in place by this afternoon.

comment:9 Changed 11 years ago by Jamie McClelland

Resolution: fixed
Status: newclosed

I just finished the script and uploaded it to all shared servers. It's documented on the newly created create mysql database page. I'm going to close this ticket - please re-open if you have any trouble using the new script.

comment:10 Changed 11 years ago by Jamie McClelland

This ticket is related to #798.

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.