Wesley's Tool-Box

MySQL documentation gives some examples of creating users in MySQL. Among the examples are creating users that only have access to a specific database, or 'tied-to-database', as I say. This is preferred from a security standpoint because you can avoid using root to access services like s9y and use this 'limited' user instead. Let's look at one of the examples in there.

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO 'custom'@'localhost' IDENTIFIED BY 'obscure';

This is supposed to create a user called 'custom' on the 'localhost' domain that has the password of 'obsecure' and can only access 'bankaccount' database. Now this looks alright, but it poses a bit of problem with s9y.

It seems like the GRANT statement gives just enough amount of privileges to the user for using the database it is assigned to. In most cases, this is true. However, the given privileges prove insufficient when updating s9y itself, or just some plugins. Specifically, it does not give the user the privilege to create new index in the existing table.

Some plugins expand features by adding some more indices and storing new parameters. The recent update of s9y did the similar thing. But if the indices are not added, things fall apart. And with a user created like the example above, this is precisely what happens.

The solution is simple, fortunately. You need to add the INDEX privilege to the GRANT statement. So the above example would be changed to:

GRANT INDEX,SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO 'custom'@'localhost' IDENTIFIED BY 'obscure';

Or, if the user was already created, do this:

GRANT INDEX ON bankaccount.* TO 'custom'@'localhost';

If anyone's using s9y with a tied-to-database user, make note of this, before doing any updates.