These forums are locked and archived, but all topics have been migrated to the new forum. You can search for this topic on the new forum: Search for How to set permissions for mysql - certain DBs ? on the new forum.
Web Hosting and Cloud Computing Control Panels
Hey Alex,
All of this can be done using the Webmin MySQL module, though doing so does take it out of control of Virtualmin to some degree (Virtualmin loosely enforces a "virtual server->databases" type of ownership hierarchy where you can have many databases, but each database has only one owner and it's a virtual server owner account). That said, I make use of the Webmin MySQL module extensively on Virtualmin.com to allow the existence of our development domains, independent access by our license manager, etc., and it's not particularly dangerous to do so (it just means that some of the relationships and permissions are not obvious in the Virtualmin interface, since it doesn't cover things that deeply).
So, to grant access to a database browse to Webmin:Servers:MySQL Database Server, and click on "Database Permissions". Here you can click "Create new database permissions." and build up fine-grained (or unlimited) access rules for any user to any database.
The nice thing about this is you can be very selective (no pun intended). During migration I granted SELECT only access to the scripts I was writing to move data from the old OpenACS PostgreSQL database to the new Joomla MySQL database. That way, I was confident that even if my script did something stupid, it could only do it to the new Joomla database and it couldn't hurt the running OpenACS site data. Pretty nice for a database amateur like me, so I could have the confidence to play and learn without fear of damaging the live site.
--
Check out the forum guidelines!
Hey Joe
Thanks for the detailed response.
I tried doing that... I set it so a user had access to only one database... but when I login using, say, phpMyAdmin, all of the databases are still listed, and not just the one I set the permissions to....
Do I have to apply the settings or restart mysql ?
Alex
I dunno. I'm by no means an expert on phpMyAdmin. I guess I should bone up on it a bit (I've always used the Webmin MySQL and PostgreSQL modules for my database tasks). Sounds like maybe phpMyAdmin is configured with the root MySQL password, or you haven't set a root MySQL password and phpMyAdmin is being logged in as root by assumption because it is on localhost.
--
Check out the forum guidelines!
Awesome, I've been looking for a fix for a LONG time!
Any chance you know how to disable access to information_schema also in phpMyAdmin?
I assume messing up that database will screw up mysql or phpMyAdmin also.
Thanks again for the solution -
Alex
In virtualmin/webmin, I found the setting where you can limit the number of db's a user can have, or make it unlimited. I changed one, new user to say they could make unlimited databases.
So, then I logged into virtualmin, and tried to create a db. MySQL returned an error saying access was denied.
Under the permissions section of the mysql control panel, there is none for "create databases" - so I chose "create tables"... figuring there was some kind of misspell...
Did that, and that fixes the issue, but then in phpMyAdmin that new user can see all of the databases again.
What I'm really looking for is the ability for multiple users to have multiple virtual mysql servers --- so they aren't just limited to one database and they have the ability to drop and create databases they own.
You cannot give the user no permissions. I gave my users a few rudamentary permissions such as create, alter, and drop databases and tables. The two you don't want to give them are Show Databases, and Superuser. Those two will result in the same problem you have now.
You must also go to "Webmin Users", click on the username to edit (it will give you a spiel about not editing this way, but I have had no problems doing so,) then click the link for MySQL Database Settings and give the user permission to create databases. I also check the box to allow drop databases, and the radiobutton for managing their own databases. You also need to set in this panel which databases they are allowed to see. Click save, and then click the box next to the MySQL Database Settings link. Then click Save and try it.
Hi beetle
Thanks for the info. Right, I tried doing exactly what you described but it does not work. I was thinking maybe it was the double save that you need to do - e.g. you save the mysql info for the webmin user and then you have to hit save again, which I wasn't doing before, but that is not the case.
For example, I gave a user the right to create databases, using the webmin settings, and it seems to work. The settings in virtualmin for this user change, and give the user the option to create a db.
But, when I click on "create a new database" I get
"DBI connect failed : Access denied for user 'abby'@'localhost' to database 'mysql'"
This is because although the virtual/webmin user has the right to create a database, the mysql user does not have that right (it is set to no permissions)
When I change the mysql settings and give the user the right to create and manage *tables*, virtualmin gives the user the right(s) to see all of the databases again.
I think there is an error in virtualmin, I'm going to look into setting the permissions on the command line instead.
Solved this issue by upgrading to pro.
I installed phpMyAdmin manually, which is probably the reason it was giving access to whoever logged in.
Used Pro's install script to install phpMyAdmin instead, and it worked.
I am having the same issues with virtualmin. The problem is with how usermin creates users in the mysql server. The proper way to do this in a multi-user enviornment is to create mysql user with no permissions and then create a permissions rule to allow that user to access the database that it owns. Virtualmin does not do this, and it is a big security risk.
Virtualmin when creating a new virtual server with a database, creates a mysql user with ALL permissions. This allows this user to simply upload code that allows them to see other users databases on the system and make any changes to them he or she wants. Virtualmin should change their code that creates the mysql user to setup with no permissions and then create a database access rule for that user to be able to access their own database by default.
To setup your virtualmin server to do this automaticly, goto the MySQL control panel in webmin. Click on the User Permissions icon. Below the current user permissions already setup, there is a section that says "The options below configure synchronization between Unix users created through Webmin and MySQL users." Check the box that says "Add a new MySQL user when a Unix user is added, with permissions ..." and DESELECT ALL of the permissions to the right.
This will setup webmin and thence virtualmin to create proper database user permissions. This will also fix your problems with phpmyadmin showing all databases to a user who dosen't need to see them.
<div class='quote'>The problem is with how usermin creates users in the mysql server. The proper way to do this in a multi-user enviornment is to create mysql user with no permissions and then create a permissions rule to allow that user to access the database that it owns. Virtualmin does not do this, and it is a big security risk.</div>
I'm not seeing this behavior on my Virtualmin systems. I'm not sure what exactly would lead to that kind of openness in a MySQL database--I'm not an expert, but I know that our Virtualmin boxes definitely aren't granting access to all databases to all Virtualmin users. I suspect some sort of configuration issue.
Are you sure you aren't allowing local connections to MySQL to be root without authentication?
--
Check out the forum guidelines!