Managing MySQL User Logins

16 posts / 0 new
Last post
#1 Thu, 01/06/2011 - 20:42
katir

Managing MySQL User Logins

I find myself to more and more contracting to developers where I need to let them into a single site and have access to a single MySQL database. I have very little experience with MySQL ACL management. OK I created this site:

dev.hinduismtoday.com for a 3rd party developer to do his work in.. for example purposes:

admin user: mango password: smoothie

OK. now: I get a new MySQL User "Mango" with permissions "None"... to access no databases at all since we did not set one up for the new site.

now I need to let this developer have access to the old data base that is live on the old site ... call it

bluesky

I gave the MySQL user "mango" broader permissions, and also the option to come in from any host. But now it seems he can see all the databases... why? not just bluesky. I thought I would have to some how tie the user "mango" to access database "bluesky" first, but instead he sees all the databases.

but, at the same time, (I had created a web min user for mango when creating the new server) if I try to log in to web min as "mango" I do get in and he is restricted to the new domain. but, the MySQL server wants the MySQL Administrator user "usually root" to access the MySQL server. But I don't want to give these people root access, do we?

Sorry if this is muddled. I'm used to dealing with PostGreSQL pghba which seems a lot simpler...

Fri, 01/07/2011 - 05:08
Locutus

Basically there are two ways to do that. Either through the Virtualmin database access permissions (which you can set for each user... under Mail and FTP users -> Other user permissions). There you can only assign databases though that are in the same Vmin vserver.

Or you configure it manually in Webmin's MySQL module, independent from Vmin vserver structures, on several granularity levels. "User Permissions" will assign global rights on all databases, and "Database / Host / Table / Field Permission" will give finely tuned permissions on the respective level.

Does that help? If you have further questions, feel free to ask.

Fri, 01/07/2011 - 08:24
andreychek

But I don't want to give these people root access, do we?

And you're correct, you definitely don't want to give out root access in MySQL... there's almost always another way to solve the problem :-)

-Eric

Fri, 01/07/2011 - 15:23 (Reply to #3)
katir

Thanks for your patient mentoring, yes, questions.

I need to give the user "devtoday" access to the XOOPS mysql database that is attached to a different virtual domain/user.

I think we can get by without letting this developer have access to the MySQL server GUI in Webmin.... as long as I can fine tune his access to specific db's and grant him the permissions he needs. I tend to think we should turn off "execute" unless he really needs to run SQL for the cmd line...

But I don't understand why a user like:

htoday localhost 25fe500405cd1a4a None

has permissions "none" but in the XOOPS php config, where it asks you to enter the dbase name, user and password, that user can still read and write to the database attached to it's virtual domain. How can permissions be "none" by that user still read and write to the tables?

Also, yesterday, in order to let the developer with the new site "dev.hinduismtoday.com" (which I made as an independent virtual server and not a sub-domain, because I have gotten myself into trouble before using sub-domain-server to develop a new version-iteration of the parent...) access the dbase used by htoday.. I had to set the host to "any" so he could get in from his IP in Australia. But then he Skyped me today saying that he needed "devtoday" to be set to "localhost" if the new site was going to access the database attached to the other server. Why doesn't "any" include "localhost" ??

Sat, 01/08/2011 - 04:15
Locutus

Where do you see "htoday" having permissions "none"? As I said, there are several levels of permissions... Global, per-DB, per-table etc. The user might have no global rights, but be allowed rights on a specific DB.

As for the host-access thing, "any" definitely does include "localhost". The problem there must be a different one for you.

Sat, 01/08/2011 - 13:48 (Reply to #5)
katir

htoday is the admin user for the site for which it is "owner" . if I check his users permissions from inside VirtualMin--> www.hinduismtoday.com --> Edit Mail and FTP users we see he has permissions to access databases "All" there, but in Webmin Permissions are "None"

In Virtual Min for htoday, we don't have an option to set the specific MySQL permissions: that list (create databases, execute, superuser, delete tables etc) does not appear for htoday in VirtualMin in the pane "Other Permissions"

Presumably, because, as the primary admin for that site, he gets permission to do anything by default?

As for devtoday who is admin for dev.hinduismtoday.com. I don't have the full picture, but I just created a second user "devtoday" and set him to "localhost" and now it works.

At this point I have no blockers, so, even if I don't understand this very well, everything is working... in IT, sometimes you just have to accept that you can't know everything today...maybe tomorrow. thanks for you help!

Sun, 01/09/2011 - 17:27
Locutus

Again, please: Where exactly in Webmin (the MySQL Server module) do you see "Permissions none" for the user "htoday"? As in, in what part of the MySQL config? "User permissions"? "Database permissions"?

Virtualmin server owners get assigned rights in "Database Permissions", since they don't have rights on all databases, but only those in their virtual domains.

Sun, 01/09/2011 - 20:05 (Reply to #7)
katir

Webmin --> Servers --> MySQL --> User Permissions:

htoday  localhost   XXXXXencryted pswdXXXXXXXXXX    None

Webmin --> Servers --> MySQL --> Database permissions

xoops_108 htoday localhost

Select | Insert | Update | Delete | Create | Drop | Grant | References | Index | Alter | Create temp | Lock | Create View | Show View | Create Routine | Alter Routine | Execute

OK now, maybe I'm getting it. Let me feed back my (mis) understanding:

MySQL allows you to set permissions for a user at a global level, this is done in Webmin-->Servers-->MySQL-->User permissions. if you grant permissions there, then you are in effect giving that user permissions for all MySQL database on your server. Y/N?

(This would explain why, when I log into phpMySQL admin with some users, the can see and edit all the data bases... because I have added them wrongly there...

If you then go to Webmin -->Servers-->MySQL--> Database permissions and add a user to a particular database, you can also set permission for that user for specific database, even though he has already been granted permissions in the User Permissions UI.

Question: Permissions given for a particular user for a particular database does or does not over ride his permissions as set in User Permissions?

VirtualMin --> Edit Users --> Other permissions --> database access. Only allows access to the database, but does not actually offer you any option to edit his specific permissions (the field with the list of "granular" permissions, does not appear there for htoday)

ergo: goal, permit a given user to see only one database and not be able to see or edit any others. You must 1) first add him as a User under User Permissions, but set those permissions to "none", 2) OR add that user in VirtualMin and give him access to databases, in which case

a) he will default to being allow to edit any databases attached to that domain

b) he will appear in the MySQL Users with permissions "None" # yes, tried it and we see him katir.devtoday localhost *****PASSWD******* None

c) He also appears (I thought not, but you have to leave that area and refresh the browser to see the added user) now appears in Database permissions as assigned rights to htoday's databases with permissions "All"

so if you wanted to fine tune his permission you would need to do it there, even though you added him via VirtualMin.

So, clearly, the optimum method will be to add any new users thru the VirtualMin Interface for the particular VirtualServer you want them to collaborate on with you. Then go and turn off stuff like "super user" and "drop tables" etc in Webmin, if you want to constrain them.

If you add a user from the "back end" via Webmin-->MySQL server, then they are not associated with the virtual domain, but will have access to the databases they are assigned to ("None" in User Permissions block global acccess; then adding them in Database permissions give discreet access.)

Hmm, I think I'm close to getting the picture. If you see any flaws in the above, let me know.

Mon, 01/10/2011 - 06:31 (Reply to #8)
Locutus

MySQL allows you to set permissions for a user at a global level, this is done in Webmin-->Servers-->MySQL-->User permissions. if you grant permissions there, then you are in effect giving that user permissions for all MySQL database on your server.

Correct!

If you then go to Webmin -->Servers-->MySQL--> Database permissions and add a user to a particular database, you can also set permission for that user for specific database, even though he has already been granted permissions in the User Permissions UI.

Correct! The access permissions are cumulative. You can e.g. give a user the global right "Select database" and "List databases", but give him write access to only specific DBs.

Question: Permissions given for a particular user for a particular database does or does not over ride his permissions as set in User Permissions?

They are added, but do not override. Since there are only "allow" permissions in MySQL and no "deny", if you write-allow a user on global level, you cannot deny write to specific DBs anymore.

VirtualMin --> Edit Users --> Other permissions --> database access. Only allows access to the database, but does not actually offer you any option to edit his specific permissions (the field with the list of "granular" permissions, does not appear there for htoday)

Correct. Virtualmin has no granularity there. It only sets per-DB access for users, and there it sets "full access to those DBs". If you need finer granularity, you need to edit the permissions manually in Webmin's MySQL module.

ergo: goal, permit a given user to see only one database and not be able to see or edit any others. You must 1) first add him as a User under User Permissions, but set those permissions to "none", 2) OR add that user in VirtualMin and give him access to databases

Yepp, that's correct. User rights in MySQL are of the type "default: deny; if set: allow". So if a user has a "user account", but no rights defined, he has no access.

So if you want a user to have access to all DBs, make a "User Permissions" entry. If you need specific DBs with granular rights, use "Database Permissions". If DB-specific "all access" is sufficient, use the Virtualmin way (in most cases this will suffice).

If you're interested in the internal details: In MySQL, user permissions are set in MySQL itself, namely in the table "mysql". Check that out as root user if you wish to see the exact outcome of your editing. :)

So, clearly, the optimum method will be to add any new users thru the VirtualMin Interface for the particular VirtualServer you want them to collaborate on with you. Then go and turn off stuff like "super user" and "drop tables" etc in Webmin, if you want to constrain them.

You could do that if you wish to restrict their rights, yep. Be aware though that Virtualmin does not know of these changes and might revert them if the user is re-created for some reason. Normally it should not be though, except you re-assign DB rights in Virtualmin.

If you add a user from the "back end" via Webmin-->MySQL server, then they are not associated with the virtual domain, but will have access to the databases they are assigned to

Correct. In this regard, also check out the tab "Import database" in Virtualmin's database list. You can bring existing, manually created DBs under Virtualmin's control there.

Mon, 01/10/2011 - 14:02 (Reply to #9)
katir

Wow the Sun is Out and His Name is Locutus! OK I get it... I REALLY appreciate your patience and sharing. Hopefully this being in the forums will be of some use to others as an FAQ.

OT: how do you get the mark up inside these posts? Is there some place to turn on a markup widget or do yo just have to know what the mark up is?

Mon, 01/10/2011 - 18:35 (Reply to #10)
Locutus

Thanks, I feel quite "shiny" already! ;)

About markup: Yeah, when you go the "Reply" screen and unroll the "INPUT FORMAT" section, you get to choose whether you wish to use "filtered HTML" or "markdown text". For the latter, there's help links.

Mon, 02/07/2011 - 20:08
VMSystems

I've read this entire thread and I (think) I understand, but I'm still missing something on what we are trying to achieve.

We want to access the system remotely to pull data for reports into something like OpenOffice. I'm testing connectivity with an older copy (V1.2.14) of MySQL Administrator first before we try something more challenging. We do this and can connect to our Windoze MySQL server no problem.

I've added a user in webmin named "tester" I also see that the MySQL username is limited to 16 characters, so that makes some sense. The MySQL login is tester.cc.defghi. Password is pretty self explanitory.

When I look at Webmin - Servers - MySQL Database Server - User Permissions, I see that VirtualMin has created 3 sets of permissions.

Problem is that I still cannot gain access. I've modified one set of permissions to allow "ANY" for hosts. I've trying to connect to the IP of the domain and the IP of the Virtualmin box.

I'm missing something.

Tue, 02/08/2011 - 20:29 (Reply to #12)
VMSystems

OK... found it. I just had to sleep on it and get some feedback from others.

  1. firewall (iptables) had to be opened up
  2. MySQL was set to only listen to local (127.0.0.1) so I changed it to any.

Hopefully the firewall is enough to control access.

VirtualMin/WebMin makes managing the MySQL super easy.

THANKS!!!

This made my week!

Tue, 02/08/2011 - 21:30 (Reply to #13)
katir

Opening up the firewall could have security consequences. But I'm no expert.

Of course you probably know this. but in case not:

if your org or company has a static IP on the net e.g. our firewall at HQ in Hawaii out facing has a single IP from the point of view of our web server in San Francisco. Then you can just add one user as an allowed host user from that IP. I do this for all the databases on the server and write desktop apps here in LiveCode to talk to the MySQL and PostGreSQL server on the web box. I can deploy these apps to anyone on the LAN and they are instantly talking to the databases on the web server without any authentication log in requirement, because it is coming from our IP. In theory this is safe. I hope so, it's really "slick" and staff loves it, as the pain of logging to the server or using some clunky web app is all gone and they get good looking GUI that I can put together in day or so versus going crazy writing html-JS-CSS for something that has to run in a browser.

Thu, 02/10/2011 - 06:19
Locutus

I suppose he meant opening port 3306 in the firewall for outside access, which is (along with setting MySQL to listen on ANY instead of LOCALHOST) indeed required. :)

Sat, 02/12/2011 - 03:05
helpmin

for these kind of scenarios it would be probably worth to invest some time in openvpn.

Topic locked