Home / server / Questions / 32934
Accepted
LiraNuna
Asked: 2009-06-29 11:34:01 +0800 CST

MySQL Development machine permissions

  • 772

I'm administrating two servers who follow the model of development and production machines.

The production machine is stripped bare-bone software which runs under restrictive set of rules and limited access. The development machine, however, is much more open - developers can ask for mostly anything (with some exceptions) to be installed and they get unlimited amount of MySQL databases for their testing.

However, on both servers, developers have to ask for a new username/password/database combination which is usually accepted on the development machine, and only accepted on the production machine when a web application is finalized, polished, secure and ready to take off.

Is there a way to create each of the developers on the development machine a user with the permission to create (just create, not read/modify/erase) template users with fixed permission set? That way they could create their own new secure set of username/password/database without waiting for me to accept their query.

Our development server is exposed outside (so developers can ssh and work from home) so I don't want to give one user a lot of permissions, due to the experimental nature of the applications hosted on the server. (If the database gets compromised, the best it could do is drop the application database instead of all of the databases user have access to).

mysql security user-management

2 Answers

  1. Best Answer

    MySQL has no concept of "templated permissions". There are some useful sounding permission types provided, but these are not fine grained enough to construct something equivalent.

    There are three steps your database-creation user would have to do:

    • Creating the new database - permitted by granting CREATE to the database-creation user.
    • Creating the new user - permitted by granting CREATE USER to the database-creation user. Unfortunately, the CREATE USER permission also permits the use of DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Fatally, this allows you to run DROP USER 'root'@'localhost'; and similar.
    • Granting read/write permissions. Permissions flow from the database-creation user to the new user, so the database-creation user must have existing permissions on the newly created database. This is possible by granting ALL PRIVILEGES on all databases starting with a prefix (e.g. dev1_ ), and requiring new databases to start with this prefix. The WITH GRANT OPTION permission allows a user to give their permissions to the newly created user.

    As you can see, it all nearly works, apart from the user creation part. A possible compromise is to create per-developer users, which can create and use multiple test databases:

    grant all privileges on `dev1_%`.* to 'dev1'@'%' identified by 'password';
grant create on `dev1_%`.* to 'dev1'@'%' identified by 'password';

    This will create a user "dev1" who can only create and use databases starting with "dev1_".

    Since MySQL cannot provide a solution alone, I'd recommend just writing a program to do the database and username creation steps. Developers could invoke the problem like:

    create_dev_database --comment "xyz app test database" database_name username

    Since your program is controlling database/user creation, you can:

    • Enforce strong passwords (no 'password' passwords, as above :))
    • Enforce access restrictions by hostname (restrict connections to '%.example.com' instead of allowing developers to pick '%')
    • Log the access details and purpose of new databases (don't want 100s of active/dormant databases and no documentation of their purpose)

    Such a program would have to use and protect your MySQL root password. How this would be achieved is platform dependent.

    Good luck!

    • 2

  2. Umm, why not have the developers admin their own servers inside their development VMs, with the same security lock-downs as production (albeit different passwords etc)?

    Am I missing something here? Shouldn't development be the same as production, and shouldn't each developer have their own environment they can play with as much as they like?

    • 0