MySQL Bugs: #45216: configuration changes have no effect unless edited with explicit admin option

Bug #45216	configuration changes have no effect unless edited with explicit admin option
Submitted:	31 May 2009 11:34	Modified:	13 Sep 2012 20:16
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Config Wizard	Severity:	S3 (Non-critical)
Version:	5.1.34 - 64 bit	OS:	Windows (Win7- 64 bit)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	qc

Description:
One more crazy report!

I restart the OS. The log records several instances of this: 

090531 12:57:41    1 Connect	Access denied for user 'root'@'localhost' (using password: NO)

I have not connected with any client. The my.ini has no log specification (general log not enabled in my.ini). But there is an old log file. This keeps on incrementing like this! One such entry added per 2 minute!

How to repeat:
I do not know if it is repeatable on other systems!

Suggested fix:
It could be some 'malware' on my system (some mysql client infected?).

I am sorry to post such rather 'non-exact' report.  But it may be a critical issue (for MySQL and/or for me), so I do anyway.  I will update with what information I find!

C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld, Version: 5.1.34-community-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
090531 12:57:41    1 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 12:57:42    2 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 12:59:42    3 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:01:42    4 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:03:44    5 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:05:44    6 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:07:44    7 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:09:44    8 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:11:44    9 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:13:45   10 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:15:45   11 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:17:45   12 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:19:45   13 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:21:46   14 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:23:47   15 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:25:48   16 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:27:48   17 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:29:48   18 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:31:48   19 Connect	Access denied for user 'root'@'localhost' (using password: NO)
090531 13:33:49   20 Connect	Access denied for user 'root'@'localhost' (using password: NO)

here is my my.ini (created with config wizard)

Attachment: my.ini (application/octet-stream, text), 8.70 KiB.

after 

Microsoft Windows [version 6.1.7100]
Copyright (c) 2009 Microsoft Corporation. Alle rettigheder forbeholdes.

C:\Windows\system32>net stop mysql51
Tjenesten MySQL51 stopper.
Tjenesten MySQL51 er stoppet.

C:\Windows\system32>net start mysql51
Tjenesten MySQL51 starter.
Tjenesten MySQL51 er startet.

C:\Windows\system32>

.. the mysqld.log file is created. System seems to use a cached copy of the my.ini (from when I had general log enabled). Also now the mysqld.og is incremented every 2 minute with the connection error message.

i fail to see how this is a valid bug report... something tries to login with no password on localhost root account. mysqld is logging this failed attempt rightfully.   hm, do you have federated tables, or use event scheduler of mysql ?

as for *what* tries to login without a password, this is something you have to check on the pc.  try changing the port to 3307 and see if anything breaks...

or, start server with --skip-grant-tables option to see what this user
will do if granted access to the database.  maybe then you'll get a clue what
it is.  of course, this assumes it's just a test database, you don't want to do this on any production system!

I think I told it is a crazy report. I also cannot explain it. I agree that this is not in strict terms a 'bug report' .. but then at least an 'issue report'

Two issues:
1) mysqld is started (even after a reboot) with a general log file setting not specified in the current instance of my.ini. This may be an issue with Win7 file system snapshot technology or whatever. I did not claim (but also do not exclude the possibility) a MySQL bug.
2) general log records attempts to connect as root@localhost (note: localhost) with no password. You are probably right that there *are* connection attempt (but even here we cannot be 100% sure - it may be log records recorded erroneously).

But if MySQL servers with 'weak' root accounts are under attack like this by some malware, MySQL does not seem to care about it? If users are not able to configure the server it does not matter either? I do not understand the attitude! If there is a security risk MySQL should *proactivlely* participate. If there is an issue with this non-final Windows OS and the MySQL server, MySQL should be the first to communicate with Microsoft about it (but this is just 'an unsupproted OS' what is completely silly as it will be the most important Windows OS by the end of the year). Instead every posssible and impossible excuse is used to argument that this is not relevant/valid/appropriate etc.

Maybe I should have used some way of discussing than bugs.mysql.com, but then please advice!

Changing the port is an execellent idea and I will do!

I started server with

port=3309

in both [client] and [mysqld] section.  The server listens on port 3306 after restart (I can connect with a client on 3306). Additionally the log file is created as specified in a no longer existing my.ini instance and the error

090531 15:14:06    6 Connect	Access denied for user 'root'@'localhost' (using password: NO)

.. continues to appear for every 2 minute.  Ideas appreciated!

I am scanning my system now with a couple of AV/Spyware scanners and will try to replace my security software package once this scan has finished.

OK .. the connection error is a scheduled script that fails to connect with .ini changes.  Sorry for overlooking this.

The remaining problem is that changes to my.ini have no effect after a server restart. Same was also the issue here:  http://bugs.mysql.com/bug.php?id=44992.  So that should be marked as a duplicate of this!

I found the problem: If I explicitly start my editor (PSPAD) with 'run as administrator' option .ini changes have effect. If not they only have for current user and not for 'system' user(s) and mysql runs as a service (as system user).

I did not find a 'VirtualStore' folder in this Windows with my settings.  Looks like Win7 hides it for users completely!

So I conclude that this is an issue with Window/UAC.  But I think MySQL docs should make this clear.  And I also would consider moving the default position of my.ini from 'Program Files' to 'AppData'.  You will lots of similar report with this, I think!

Sorry for confusion ... but in the other report they could just have told it! :)

Synopsis changed according to this conclusion!

Thank you for the feedback.

Marked as duplicate of bug #44992.

Regarding to hiding directory, have you tried to check option "Show hidden files and directories"?

Yes .. I always use the setting 'show hidden files' from Control Panel .. Folder Settings. I am also able to see 'AppData' what is a hidden folder.

There are some files that Windows does not expose (at least not from Windows Explorer - maybe there is a command line switch) even with this setting - and either that is also the case with 'VirtualStore' files in Win7 or I missed to see where they are now!

If I edit a file in 'Program Files' as a user (also as an admin user) without explicitly starting the editor with 'run as administrator' option the changes have only effect for that particular user and not for system. That also applies if I start the editor by doubleclicking the file - what lots of Windows users will do - that editor does not have amin privileges! That is nothing new (I think same in Vista) - what is new is that VirtualStore is not exposed what made me think that there was no VirtualStore with the UAC setting I used.

Actaully we moved the configuration files of our programs to 'AppData' around two years ago becuase of UAC problems with Vista. But I even think it was worth considering to install MySQL and other programs originally designed for Unix to another posisition than *Program Files* due to this (what users may do of course) - what would also make it possible to have the /datadir in same folder with UAC on.

I only came across this as I was preparing testing our own programs on Win7 and I turned UAC on what I not normally not do myself (but some of our users do, mostly I think becasue they simply do no know about it!). And then all the problems came with getting the test environment with MySQL configured.

There are PRO's and CON's to every solution, but I should not spend so much time as I did and MySQL supporters not either. If the issue was known and documented a proper reply that could make me stop fooling around like this would have taken less than 5 minutes for each of us!

my mistake again!

the folder 'C:\Users\Peter\AppData\Local\VirtualStore' exists and the 'my.ini' instance that I edited as user 'Peter' is there.

(C:\Users\Peter\AppData\Local\VirtualStore\Program Files\MySQL\MySQL Server 5.1\my.ini)

I agree that putting file that is meant to be modified (my.ini) into folder that lacks write privileges even for administrators (Program Files) and requires starting the editor elevated is a usability issue.

Text editor that lies to user by  saving files in virtualized folders is yet another usability issue.

I set the bug back to verified. Otherwise it stays a "Duplicate" of a non-reproducible bug and won't ever be fixed.

I just have noticed the problem description does not match the bug title :)  To understand problems people would need to read the whole conversation..

The text editor can't help it in my understanding.  It specifies 'Program Files ...' but Windows 'virtualizes' the storage to 'VirualStore'. The is not the editor that is an issue but the privileges of the user invoking it (and the new thing in UAC is that even an admin user will need *explicitly* to specify 'run as administrator'). Maybe it is in principle not much different from some 'sudo' implementations on Unix. Or at least intensions are the same, I think. Only on Windows it common for a single user to be 'administrator' for daily work (almost nobody uses a limited account on Windows!). It is not common to be 'root' on Unix for daily work! Unix is not *more correct* than Windows in my opinion. But people thinking 'unix-way' overlook the differences (well .. also I did!). 

However I find that very few program that have been updated in the last couple of years store configuration files in 'Program Files' (of those that use configuration *files* for saving configuration and not Registry). The default .ini position should have been moved too, when the /datadir was around 1 year ago!

Also MySQL Administrator has some problem -

Attachment: ma.jpg (image/jpeg, text), 49.67 KiB.

When MA is invoked with administrator option this error does not occur.  But when it is not I do not know why MA does not 'silently' save to VirtualStore and encounters an error instead!

In my understanding the Synopsis is OK - that "configuration changes have no effect unless edited with explicit admin option" is the culprit of the problem from user's point of view.  If you want to describe or classify differently then do!

This is not a bug but a problem that needs to be tracked as a support issue

Bug or not a bug ...

It would be preferable to have the .ini in AppData what also most Windows program have. Even SUN understands this when it comes to JAVA (JRE/JDK)!

config file is user updatable data.
Our documentation says this. There are numerous places where it says "edit config file,change this or that parameter". It does not say "Start Config wizard and change this or that parameter" (for most parameters it is impossible) neither it says "Start Notepad in elevated mode and change this or that parameter."

Current behaviorhas a WTF effect on windows users. MySQL does have power users, it is a server application that normal users (say grandmas/grandpas) don't need at all.

However, you remark about "the file should not be changable by just anyone" and this is
true.

We do have a security problems on Windows that we for example do not have on Unix at all.
What follows does not directly has to do with the described problem, but it needs to be
said somewhere, so I just hijack this bug report. It is what I believe would be the "right
thing to do" and what I believe we're doing wrong installer-wise at the moment.

- mysqld service runs under SYSTEM user (analog to root user on Unix) and this is a no-no,
SYSTEM has far too many privileges that mysqld does not need (in fact it does not need any
privileges at all besides what standard user has). Why it is bad : If someone manages to
pwn mysqld, it can not only damage all mysql related data, it can format disks on your
box. Note also mysqld does not run as root on Unix as a security measure.

Therefore ,security aware admin will not want to run mysqld it with administrative
account , but with minimal privileges, as a service.

What I think needs to be done is similar to what is described in the installation
instrunctions for binary distribution on Unix. I.e one can create a group mysql ,create
user mysql in this group. It can/should be a very weak user, non-admin, without
interactive logon right. The only purpose of this user is to run mysqld service under
"mysql" account. Installation would setup ACLs on the data directory such that only people
in the mysql group can access and modify the files, add Administrators to the mysql group
and maybe the guy who started the .msi installer to this group as well.

The effect will be :
- person who installs MySQL would be able to modify config files without need to elevate.

- mysqld runs under a weak account and is no more a security threat.
- Elevated administrators have full access to config and database files.
- You can add more accounts or groups to the mysql group so they can access database files
and settings.

This schema is not what I just invented - my former employer needed to implement all this stuff to pass WS2003 certification (even then running services as SYSTEM was considered harmful)

Well .. also on Windows you may create a 'mysql' user and assign what privileges you want to him, I think. Actually Cygwin SSHD 1.7 does something like that (I think user is named 'privileged server'). Cygwin 1.5 SSHD service won't start on Windows after Vistas SP1/SP2 (not sure) but 1.7 runs fine on my Win7 system

I agree that porting Unix program to Windows has some problems - in particular after Vista+.  MySQL is not the only one. Most notably Apache has exactly the same problems on Windows (SYSTEM privileges, configuration stored in 'Program Files').

Basically I believe Unix vendors providing Windows ports have not understood that with recent Windows fine grained privileges control is possible - almost like on Unix.  MySQL understanding of Windows is still more or less based on W98 and NT4.

However this bug report was primarily about the confusion occuring if UAC is ON and configuration changes have no effect if not configuration is edited with explicit admin privileges.  For that reason I think that my.ini should be in same position as /datadir.

The question we should be asking here is:

Should all local users on the server's machine be allowed to edit it's configuration file?

If we store the configuration file in the DATADIR instead of INSTALLDIR, all USERs on that machine will be granted write access to the configuration file. The configuration file for a server process should not be left in such a vunerable location. We store this file in /etc/mysql by default on *nix which requires admin privileges to access in most cases. By allowing this configuration file to remain in it's current location on Windows, we properly limit write access to only Administrators of the local machine.
A power Windows user will have a firm grasp on User Access Control and have no problem properly editing the configuration file no matter if they have grand children or not ;). Our manual clearly states that the configuration file is stored in INSTALLDIR/my.ini on Windows. The manual also doesn't say "sudo vim /etc/mysql/mysql.cnf", instead it says "Edit the configuration file" and leaves it to the user to work out how to start a text editor with elevated privileges in their operating system of their choice.
The current config file storage location is in-line with our behavior on Linux and the suggested change to "store the configuration file in the DATADIR directory" is deprecated for 5.1 plus it would expose MySQL servers to greater vulnerability in the long term. I agree some users may have a WTF moment but as the bug reporter suggests, it seems to be because of a the new UAC differences and can be addressed with documentation update.

Hi Iggy,
The question we should be asking here is:

Should all local users on the server's machine be allowed to edit it's configuration file?

The answer is no...

But the question should be also: 
Are only elevated Admins entitled to edit the config file?
The answer is again "no".. 

The way that it is handled on Unix is creating a group, a user and setting up the ACLs (with chown/chmod). Why can't we do the same on Windows and just for the sake of user friendliness add the person who installs MySQL to this mysql group?

It is not obvious to me why config file can't be stored in the datadir for security reasons. If someone has write access to datadir, this someone can change/delete all data anyway he's powerful  enough to destroy the whole database. I would guess the security of database files is as important or even more important than of my.ini. How exactly storing my.ini in datadir would compromise security?

Guys, I think the often cited misconception in this comments is that it is ok to create security boundaries by requiring admin access to resource. Security boundaries are created with ACLs. A proper group, let's say. It does not mean that this group needs to be "Administrators". In contrary the mantra that Microsoft has repeated many times in the last 6 or so years was - do not give your program more privileges than it really needs, otherwise you risk your machine if someone exploits buffer overflows in your program. Still, despite the fact that mysqld does not need any of the administrative privileges, we're  running the service under LocalSystem. And articifically restrict access to the config files, so it requires a lot of knowlegde and try and fail to make a change in the config file.

Guys

I have just reviewed this bug report and generally agree with Iggy.  The main difference is that on Linux if you try to edit the conf file improperly you will get an access denied error.  On Vista and 7 (by default) it will fail silently (through filesystem virtualization).  This deserves and requires some blurb in our manual.

Yes, we could install and create a new mysql/mysql group and user and this might not be a bad idea for future versions.  But we also need a comprehensive configuration tool that gives me pull downs and checkboxes for every conceivable option.  This tool would auto-elevate and update the my.ini based on my choices.  I would say that some database owners know all of the possible options but my guess is that most do not and would welcome such a config tool.  The _vast_ majority would use that tool and not worry about hand editing the config file.

Reggie,
that we need a comprehensive my.ini GUI tool does not change the fact that our security is as broken as it can be. Wrong permissions on my.ini is only a symptom for this.  And even if one does write a wonderful GUI tool (I doubt given current priorities), it still needs access to my.ini and would pop-up an ugly UAC dialog.

Also guys, MySQL does not install my.cnf on Unix. The installation procedure described in the documentation would start mysql_safe without any config file at all.

I don't like UAC anymore than the next guy but all of the OS have it.  Linux and Mac both do the same thing.  Performing administration of mysql is no different than administration of the box (which requires elevation).

Can our security model be better? Sure.  But for now some documentation of the effects of file system virtualization would go a long way.

there are two options (for a here-and-now solution):

1) either move my.ini default position to a user-writable position
2) or document current behaviour
I prefer 1) as msot users will probably never see that doc passage. MySQL seems to think that once something is documented it is no a problem.  It wil lremain the same problem with or without docs, I think.

As regards SYSTEM privileges' problem Reggie is probably right that most Windows users don't care much. But that is becuase they have development/test servers only (and not production servers) on Windows.  Those user that run a live site with Windows Server 2003/2008 and MySQL should care.  But it would be very nice if major software vendors sharing this problem could find a uniform solution.  If looks that Cygwin (and PostgreSQL?) found their 'private' solution.

This has been documented, thank you for the report. The MySQL Installer grants full permissions for my.ini to the user that executes it.