The default user created with the ‘database’ and set in virtualmin has grant everything. Additional users can be given access but they are also GRANT everything. VM allows you to execute SQL as that user, so using that you can create additional users with CREATE USER X@Y IDENTIFIED BY… and so on, then grant permissions GRANT whatever_permissions ON X@Y IDENTIFIED BY… The syntax is fairly basic and with this you can lock users down to just certain tables or actions (like SELECT only but not UPDATE etc). MySQL Docs are a good place to start for this.
I think this may be a phpmyadmin issue because when I assign a user to a database it does indeed get…
GRANT USAGE ON *.* TO `user@domain.uk`@`localhost` IDENTIFIED BY PASSWORD '*'
GRANT ALL PRIVILEGES ON `user`.* TO `user@domain.uk`@`localhost` WITH GRANT OPTION
I have of course obfuscated the actual user, but it does what it should do. Fine tuning those access rights will need the user of either an additional tool, or using the GRANT option. (And fwiw, limiting access to just what is needed is the correct way, but on a server where everything is only @‘localhost’ its not as critical as a server exposed to the outside world)
@tpnsolutions I only showed the screenshot of “webmin-section” as this is the only visual place I know in Virtualmin/Webmin which seems to indicate, that the user don’t have any permissions.
If I edit the user in the Webmin-Section and give im permissions like Create-Table, then it also work in phpmyadmin.
If I got you right, it would be enough to go to Edit Users and Grant access to database, like shown in my screenshot above?
I wonder why this only allow to access the db (means login via phpmyadmin) but don’t be able to doing anything.
@GENLTD I think something is wrong with my configuration. I can not believe, that I need to do some complicated steps to only let the user do stuff like select, create, etc. at their database after creating them.
I have to be honest I’m not sure what the operation of adding users via Webmin is going to be on the configuration in Virtualmin, but if you add the users in Virtualmin, it should (and does for me) grant them everything in that database. Whichever way you do it, just make sure you keep it secure, strong passwords and always @localhost.
Adding the permission to a “user” added through “edit users” should grant “all” permissions, not just read only as far as I’m aware.
Managing anything via the Webmin modules is for advanced use and cares where the Virtualmin module doesn’t handle the task.
Ex. Managing a service not controlled by Virtualmin but has a Webmin module.
@GENLTD, we appreciate the assistance being offered however let’s make sure to guide people through Virtualmin options before suggesting Webmin options as not unintentionally mislead or confuse users.
I apologise, I didn’t realise you were Virtualmin staff, it should be made clearer I think. I assure you I shall contribute no more and leave the public forum for the staff to handle, I didn’t realise this was how it worked sorry.
No, that’s not what I was saying. You are welcome to, and we do encourage discussion within the forums. What I meant is, we want to try to steer people to using a solution that works with Virtualmin first before doing it within the Webmin modules directly. This ensures that the solution works with Virtualmin as intended.
As noted, there is a Virtualmin way to accomplish the task, and we recommend this method “first”. Working with the Webmin modules directly should only generally be done if the task cannot be done within Virtualmin and by those with a bit more understanding of what they are doing.
In this case, setting up users, and assigning permissions to a database can be done within Virtualmin and for the most part would/should accomplish what was intended.
Don’t be discouraged, and I apologize if you read into my response as a negative. It was meant more as constructive in nature.
Don’t quit. Plesae, go on with contributing via the Forum.
I (and I think I also can say WE) appreciate your support und your helping hands.
Your suggestions are also good and give some deeper insights to the mysql permission system.
I think beside the usage of GUI, questions are very often also related to the “behind the scene working”.
