[Solved] Remote MySQL permissions

[uniherb]

Hi, I'm attempting to set up a database for use by a herbarium for the cataloguing of collection objects.  I want to use software called 'Specify'.  I have had some success setting up locally hosted databases but want to make one more widely accessible, hence my subscription to this service.  I am following the guide here: https://www.sustain.specifysoftware.org/wp-content/uploads/2017/03/Setting-Up-Specify-to-Use-a-Networked-MySQL-Server.pdf.  I have tried to create what they describe as an 'IT User' with all the permissions they mention.

They use the code:

 

MYSQL> GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, ALTER, CREATE, DROP, INDEX, GRANT OPTION ON DatabaseName.* TO 'ITUserName'@'ClientIpAddressRange' IDENTIFIED BY "ITUserPassword";

MYSQL> GRANT RELOAD ON *.* TO 'ITUserName'@'ClientIpAddressRange' IDENTIFIED BY "ITUserPassword";

 

which I have tried my best to implement via the GUI.  Entering it via phpMyAdmin (replacing the user/password/DBname fields where approriate) gives an error saying I do not have permission to grant such permissions.  Login attempts to create the database structure via 'Specify' fail, saying I lack permissions in the database.  Is it possible for my created users to gain such permissions, or is it restricted by this host?  I believe I simply need them for set-up and occasional DB maintenance in the case of major updates, but they are essential for this project to work.

[Sn1F3rt]

Well you should be able to grant those permissions to the user at the time of creating the database.

 

After you create a database, you wanna add a user to it, and that's when you'll be prompted for granting permissions to that user.

[wolstech]

That guide assumes you have a dedicated mysql server...which you do not when hosting here. You're one of thousands on a shared server.

 

You'd need to grant these permissions using cPanel, not phpmyadmin or a command line. Create the database user account in cpanel (it's done on the MySQL Databases page), then assign the user to the database. When you assign it, it'll ask you which permissions to grant to that user on that database. Once you do that, go into the Remote MySQL page and add the IPs that need access (or add % to allow anyone to access it).

 

You won't be able to grant reload because users are not allowed to reload the mysql server. Only root can do that since you're on a shared server. You shouldn't need any of the backup functionality mentioned in there either because cPanel can back up your databases and let you download them.

 

That guide would work if you had a VPS because you get root access and don't share a VPS, but that's not free. With a VPS, you can install mysql on it then follow the guide as written. (If you want to do that, see https://heliohost.org/vps/ , the cheapest option would work for your application).

[uniherb]

Thank you, that's pretty much what I suspected.  I did use cPanel to grant the permissions, but they're not adequate for the application.  Cheers.

[Krydos]

Reading the installation instructions at https://github.com/specify/specify7 it looks like a VPS would be perfect for you. You could do the docker installation and be up and running in probably less than an hour. VPS start at $4.00 per month for the base model, and it's really easy to upgrade later if you need more performance. You can check out the options at https://www.heliohost.org/vps/ I can even give you a free trial for a few days so you can play around with it if you want. Let us know if you're interested.