Disabling MySQL root password reset

[mnovakou]

Hi MySQL gurus,

I have what seems to be a bit of a unique requirement to meet.  Unfortunately I can't seem to find anything on the net covering this issue, so it's either probably rare, or my webfoo is just weak.  In any case, what I need to do is this:  I need to DISABLE the ability to reset the root password on a MySQL 5.0 database in WinXP via the script methodology documented here:

 

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

 

I've read countless posts on what to do when you forget your password, and I've done it myself as a test.  Unfortunately, I can't think of a way to make it impossible to do, if you have access to the host computer. (As a sidenote, I'm a total newbie to this sort of DB development-only worked with Access before, and that won't work for this project).

 

In any case, in case the context will help, here's what I need:

-DB supporting a proprietary application running locally (probably on a laptop).

-DB must be unaccessible at ALL TIMES unless the application is currently running and has authenticated the user.

 

Development has led us to think about using MySQL, but if I can't find a way to do the above, I cannot justify MySQL as a secure solution for the system, as anyone that can google "reset mysql root password" can go on a given platform with the application and give themselves root access.  I guess telling the user to not be a dumbass and lock their laptop when they're not using it isn't good enough.

 

Ideas I had, but didn't have the technical expertise to try (easily):

- Delete the root account - can it be done?  Would you be able to then reset the password of other accounts using the script-based reset mechanism?  This might be sufficient, since an attacker would not reasonably be able to guess the username for any user other than root.

- Remove all permissions from the root account - is there a way to make it so these cannot be reset, and result in the ability of an attacker to log into the DB as root but not view any info in the tables?  If an attacker can log in but can't see or do anything, that might be sufficient as well.

 

If there's no way to do what I need to do with MySQL, I was also wondering if anyone had any suggestions for DBs that WILL do what I need.  We've considered going with the embedded MySQL DB version to get around this security issue-does anyone know if doing so really would make the DB only accessable to the code?  Again, newbie, sorry for any stupid questions.  Also considered SQLite, which as you probably know does not have encryption, thus it is a bad choice (noted that we can buy an encrypted version for $2k).  Also looking at the Berkeley DB (no decision there yet).

 

In any case, any help that any of you could provide would be much appreciated.

Marc

[fenway]

There's nothing you can do about any accounts, since you can always start with with "skip grants" and end up with no users (which is how to reset password actually works).  Normally, this isn't an issue, since the server is elsewhere.  I don't actually know about the embedded DB version, I've never used it, nor have I heard about anyone who has.

 

But even if you could, the physical DB files are on the machine... what are you trying to prevent?

[mnovakou]

Thanks for the reply.  To answer your question, our client is specifically concerned about unauthorized people gaining access to the platform the application is running on, but while it's not running.  In other words, if someone got a hold of a laptop with this application on it, and knew how to access the database info (by, say, resetting the root password), that would not be acceptable.  Again, I can't find anyone else on the net with this sort of security requirement, so I'm just trying to figure out if it's even technically feasable before I go back to the client.  Hence the interest in embedded DBs, with the thought that they could remove this problem altogether.

 

Again, thanks for any info you can share.  A definitive "No, you can't make standard MySQL that secure" would be phenominally useful.

[fenway]

But unless you're encrypting all of the data in the tables, you'll still be stuck even if you could do what you describe.

[shoz]

First, when it comes to not being able to physically secure the computer (which is where I think the security issue lies) the database information can be retrieved in any number of ways.

 

To answer your question. If I had to do this I'd probably have the application encrypt the database files as fenway suggests with PGP/GPG on shutdown and then have it request the keys and password to decrypt the database before launching MYSQL on startup.

 

I suppose both the public and private key could be stored in the application and only the password would be requested or you'd store the keys on a thumb drive/floppy etc and have the client insert the media when the application requests them. You'd have to be very clear with the client that if he loses the password or the keys that he would not be able to retrieve the information in the database.

 

EDIT: Btw, depending on the size of the database the encryption/decryption could take some time. You could use a faster encryption algorithm if possible. Though, those would be more susceptible to brute force attacks.

 

[mnovakou]

Thanks again for the feedback, it was a great help.  Just one clarification if I could (sorry, again, newbie):

 

-If I had a standard MySQL server running on a laptop, and I encrypted the directories for the databases which I cared about in the MySQL directory, would that in any way break MySQL for any non-encrypted databases which were already on the server?  (There's a high potential in my case for computer-savvy users that might already have a MySQL database installed on their laptops for whatever reason.  I probably can't mandate that mine is the only one, but I can ask.)

[shoz]

-If I had a standard MySQL server running on a laptop, and I encrypted the directories for the databases which I cared about in the MySQL directory, would that in any way break MySQL for any non-encrypted databases which were already on the server?  (There's a high potential in my case for computer-savvy users that might already have a MySQL database installed on their laptops for whatever reason.  I probably can't mandate that mine is the only one, but I can ask.)

 

I've never done this so I can't give a definitive answer. I'd guess that It wouldn't be an issue but if it is you should be able to work around it by saving the encrypted versions of the databases outside of the data directory and only put the decrypted versions there when ready. Remember that the speed of the encryption and decryption could be a problem.

 

Btw, based on this quote

 

DB must be unaccessible at ALL TIMES unless the application is currently running and has authenticated the user.

 

I assumed that other applications should be allowed access to the database, provided the application that you refer to is running. If it is only your app that should have access to the database then you may want to consider encrypting the data on INSERT and decrypting it on SELECT.

 

If you encrypt all data you're going to take a significant performance hit doing most operations and that's provided you used encryption that MYSQL itself could perform. If you used some other kind of encryption, then without MYSQL being able to decrypt the data itself you wouldn't be able to make use of a number of MYSQL's functions at all.

 

As an example you wouldn't be able to use aggregate functions, date functions etc. You could encrypt only the data you thought needed it but it's very possible that you'd find that this creates an issue later on, for instance if the encrypted data needs to be manipulated in some new way.

 

Not that you wouldn't do this but before committing to use MYSQL you should test whatever method you'd like to use. It shouldn't take much time and would save you (to some degree) from finding out later on that there are problems that you hadn't considered.

 

I should also mention that I took a cursory look at how SQLite handles the encryption you mentioned and even though I haven't looked for a MYSQL solution that does what SQLite appears to do, I wouldn't be surprised if some sort of extension were available.

[mnovakou]

This has been monumentally helpful.  The answer to your question about access is indeed the latter, that only the application itself needs (and should have) access to the data.  As such, upon further, further review (and consulting with the client) the direct intert/select encryption method you proposed might be the way to go (granted we'll have to check how bad the performance hit is-I hope not too bad, we're only looking at each insert/select to handle a row of < 10 fields, it's primarily a data entry application~also we should be able to keep a cache of unencrypted data if necessary).

 

As a (yet another) point of clarification, after reading up in the MySQL documentation, to do what you suggested, I would do something along the lines of the following:

 

INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));

 

Is this correct?  Again, many many thanks for all of the help.

 

[shoz]

As a (yet another) point of clarification, after reading up in the MySQL documentation, to do what you suggested, I would do something along the lines of the following:

 

INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));

 

Is this correct?  Again, many many thanks for all of the help.

 

That is the correct syntax, yes.

[fenway]

Definite performance hits, so beware... I've used similar application-only encryption for certain "senstitive data", and the amount of work that the database can't do because it can't see the values is quite substantial.

[mnovakou]

Thanks again for all the input.  We're actually going to use straight java encryption on the sensitive data and just put the encrypted data into the database.  This way our application is not dependant on the MySQL specific encryption so we can skirt the licensing issues.

 

As for performance issues, the only time performance is a concern is when we'd be doing standard inserts, and we can take the data to be encrypted and encrypt/insert it in a separate thread, so we're hoping it's not an issue.  Also, it's an interactive gui-style data recorder, not a data cruncher (for this piece), so the amount of data being encrypted/inserted should be small.  The component that Will be doing data crunching has no major performance requirements.

[fenway]

That is what I was referring to... but if the crunching is minimal, you should be ok.