Problem with limited MySQL user and s9y

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:


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.


Trackback specific URI for this entry

This link is not meant to be clicked. It contains the trackback URI for this entry. You can use this URI to send ping- & trackbacks from your own blog to this entry. To copy the link, right click and select "Copy Shortcut" in Internet Explorer or "Copy Link Location" in Mozilla.

Wesley's Tool-Box on : Stubborn stuff slows server swap

Continue reading "Stubborn stuff slows server swap"
The old (Mac mini) and the new (iMac) With marriage, I moved into the new home. This meant moving everything out, including the servers. However, I was not sure of the quality of internet connection that the new place would provide, so I relocated the KT


Display comments as Linear | Threaded

No comments

Add Comment

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

Copyright (C) 1996-2024 Woo-Duk Chung (Wesley Woo-Duk Hwang-Chung). All rights reserved.