When setting up a database user for a Phabricator installation, you should be aware that Phabricator changes its database structure quite a bit. The Phabricator
storage management tool (which you need to run on updating Phabricator) manages the creation and deletion of the necessary databases, but it does not handle updating the permissions for the database user. Unfortunately, the
REVOKE command in MySQL will not remove permissions for a non-existent database. Phabricator uses a single namespace to store all of its databases, though, so the best way to handle access to its databases is with wildcard permissions.
In this post, I'm using
phabric as my MySQL database user and the
phabricator_ namespace for all of my Phabricator databases. I'm also assuming you have a user account with access permissions to the
mysql database. The MySQL database user you're using for Phabricator should only have basic access to the Phabricator namespaced databases. Administration (such as using the
storage tool) should be done with another MySQL user that has higher level permissions.
Fixing Previous Permissions
If you've previously granted permissions to a MySQL user for Phabricator, you should remove them. As mentioned above, the
REVOKE command does not play nice with non-existent databases, so you will have to run a command to manually edit the
mysql database and remove them. The following command remove ALL permissions for your MySQL user.
DELETE FROM mysql.db WHERE USER='phabric';
Great, now we're ready to set-up wildcard permissions for that user.
Setting Up New Permissions
The new permissions structure is simple. Instead of creating a new permission
GRANT for every database, we are just going to
GRANT the user basic access to the namespace. MySQL has two wildcard characters:
%. If you need them to be interpreted literally, just escape them with
\. The following command will grant the necessary permissions to the MySQL user to access and update all the databases in the
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW ON `phabricator\_%`.* TO 'phabric'@'localhost';
Great, now whenever a new Phabricator upgrade comes along that changes the database structure, there shouldn't be much that needs to be done in terms of granting and deleting permissions.