Minimal MySQL permissions for non-root database users
  • Vote Up0Vote Down Daniel JamesDaniel James
    Posts: 844Member, Sourcefabric Team

    Hi Campsite devs,

    I'm writing the manual page on setting up the database for a new
    Campsite install, and for security reasons I don't want to recommend
    that people do a 'GRANT ALL on *.*' for their Campsite database user
    account, or anything as lax as that.

    With 3.5.0-rc1, I've found I can create a new database 'campsite' in the
    installer by previously setting up a non-root MySQL user account
    'campsite_user', with:

    mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,LOCK TABLES,ALTER
    -> ON campsite.*
    -> TO 'campsite_user'@'localhost';

    Are these permissions sufficient for a production Campsite server? Can I
    get away with even less, for instance not granting the DROP privilege?

    Cheers!

    Daniel
  • 3 Comments sorted by
  • Vote Up0Vote Down Andrey PodshivalovAndrey Podshivalov
    Posts: 1,526Member, Administrator, Sourcefabric Team
    hi Daniel,

    all permissions should be applied. Drop can be used while database upgrade.
  • Vote Up0Vote Down Holman RomeroHolman Romero
    Posts: 261Member
    Andrey is right, DROP is used when upgrading.
  • Vote Up0Vote Down Daniel JamesDaniel James
    Posts: 844Member, Sourcefabric Team

    Hi Holman,

    > Andrey is right, DROP is used when upgrading.

    Thanks for that :-)

    Using:

    mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,LOCK TABLES,ALTER
    -> ON campsite.*
    -> TO 'campsite_user'@'localhost';

    I ran into an 'unable to connect to database' problem at the 'Load
    Sample Data:' step of the install, if selecting the radio button to load
    the sample templates. Even selecting the 'no thanks' radio button caused
    the final step to fail

    Revoking that grant and making a new one:

    mysql> GRANT ALL
    -> ON campsite.*
    -> TO 'campsite_user'@'localhost';

    enabled the install to complete successfully. If there are any
    suggestions for a less generous GRANT that would still be fully
    functional, I'd like to put those in the manual.

    Cheers!

    Daniel
Add a Comment