Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I find the list of privileges provided by MySQL to be a bit overwhelming. I'm not sure who should have what privileges. In my mind there are three typical users for my situation:

  1. root
  2. developer
  3. application

root is self explanatory. For developer this user needs to be able to easily access any database, make adjustments to it, etc. For starters I'm setting this user to this privilege set:

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON

application has an even more limited set. It should just be limited to manipulating a specific database.

I'm not sure what a reasonable set of privileges is to grant. What are a reasonable set of privileges to grant a developer and an application and why?

share|improve this question

closed as primarily opinion-based by Mat, dezso, Mark Storey-Smith, StanleyJohns, RolandoMySQLDBA Sep 15 '13 at 0:32

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

    
The rights given to an application user will probably depend on the design of the application. For example, does the app directly query the database using the user's permissions? Or does the application use a proxy login to handle all work? Do you use stored routines only? Et cetera. Providing more detail for the application design might help get a clearer answer. –  RLF Sep 13 '13 at 15:48
    
Let say, for this example, that all apps are WordPress deployments. I know that you can administer the DB from WordPress itself, but sometimes there's a need to jump onto the server itself. The developer should be able to switch from database to database easily... –  Avery Sep 16 '13 at 2:02
    
Re: put on hold. I think this question is substantially different from an opinion based question. As the comments and the answers submitted already indicate, the answer to this question is dependent on the context. But once the context has been defined, the answer to the set of privileges granted isn't that subjective. –  Avery Sep 16 '13 at 2:11

2 Answers 2

up vote 2 down vote accepted

A typical user should have:

SELECT, INSERT, DELETE, UPDATE, CREATE TEMPORARY, EXECUTE

The first four are pretty obvious -- though you may also set up "read only" users with only SELECT.

CREATE TEMPORARY is also handy and typically harmless: temporary tables can help in optimizing queries, breaking them into smaller & faster parts. They are limited to the executing connection, and are automatically dropped when it is closed.

EXECUTE depends on your type of system. Do you have stored routines? Would you like your users to access them? Make sure you also know about the SECURITY=DEFINER/INVOKER definition for stored routines.

At any case, make sure to apply all the above on specific schemas. Avoid using:

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'some_user'@'some_host';

as the above also grants privileges on the mysql system tables, effectively allowing any user to create new accounts or upgrade their own set of privileges. Instead, do:

GRANT SELECT, INSERT, UPDATE, DELETE ON some_schema.* TO 'some_user'@'some_host';
GRANT SELECT, INSERT, UPDATE, DELETE ON another_schema.* TO 'some_user'@'some_host';
share|improve this answer

In any real scale system (i.e. not a personal project) those user types will vary by environment so it isn't quite that simple.

In all environments the application should have what it needs and no more (generally this is "select/insert/update on all tables" and "exec on all procedures". For larger systems where you may have separate application users for different tasks (for instance one application is responsible for feeding in censor data and another for generating reports) you should separate their privileges so they have the least they need (the reporting user probably doesn't need and write rights). Make sure you replicate this in your test environments if you have them: I've seen code fall over when promoted to Live that worked in test because everything in the test environment was accessing the DB as sa (MSSQL's equivalent to root). Ideally an application user generally should have no privileges that allow it to alter your schema (CREATE, DROP, ...) - there are exceptions to this but they are few and far between.

root is, well, root. In production this is your DBA only and should be rarely used - just for system maintenance (upgrades to the DB design an so forth) and monitoring. For a large system, especially in regulated environments where you must maintain close control of individuals for accountability purposes, you may not allow a single root user at all and try separate out the privileges into smaller rolls (I'm not sure how far you can go with this in mysql, but you can be fairly fine grained in MSSQL, Oracle and so forth).

For developer users: they should have no access at all to your live environments. This is one of the reasons why application users shouldn't have schema affecting rights: someone with access to a developer account could potentially circumvent their lock-down this way. In test environments too they usually have no access: they would submit patches to QA and the DBA (probably using a root user) for QA would apply the updates to the test environment (in fact this is often automated in large organisations, so the QA DBA is actually a set of scripts that controls rebuilding the test environment for each cycle). In development environments, especially if the devs have their own local copies of the running service, these users should of course have full access to everything in order to be able to experiment.

The above is all general notes though: to make any specific recommendations we would need to know much more about your application(s) and the environments they operate in. The target environment is sometimes more important than you might think: depending on your business environment the rights of your users could even be dictated fairly directly be legal regulations, even in development if your clients ever give you access to real data for diagnostic purposes.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.