MySQL database password protection

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

MySQL database password protection

Post by Mifa » Thu Sep 20, 2012 6:03 pm

Is there a way to protect a mysql database with a password? I would like to avoid that someone not authorized copy our database on a usb stick for example and leaves with sensible information that can be easily restored and used anywhere. The root password in MySQL seems to be stored at the server level, and not with the database from what I have seen.

Thanks.

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: MySQL database password protection

Post by jordansparks » Fri Sep 21, 2012 4:06 pm

Yes. http://www.opendental.com/manual/mysqlsecurity.html
But you would also need to:
1. Physically restrict access to the server.
2. Not share the mysql folder on the network.
3. Use our web service middle tier: http://www.opendental.com/manual/webservice.html
It's not that hard, but it does take some effort and there's a lot that can go wrong. Smaller offices don't usually bother.
Jordan Sparks, DMD
http://www.opendental.com

Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: MySQL database password protection

Post by Mifa » Mon Sep 24, 2012 7:12 am

Thanks Jordan for your detailed reply. One more question though:

What would happen if someone copied one of the database backups for example (or simply copied the Open dental data folder): would it then be possible for them to restore the database on another machine equipped with MySQL and access the data using Navicat for example, and thus bypass the Myssql password in the xml file mentioned in the security page?

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: MySQL database password protection

Post by jsalmon » Tue Sep 25, 2012 1:06 pm

I believe that scenario is possible. So it's important to keep the access to the database files nice and tight. However, if they were to copy over your grant tables and overwrite their own, the password would still stand (at least I'm pretty sure... they might have to restart their service for that to be true). Moral of story, don't go to bed at night with your garage door open. If done correctly, steps one and two of Dr. Spark's post will let you sleep soundly at night.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: MySQL database password protection

Post by jordansparks » Mon Oct 01, 2012 7:06 pm

How would they copy those files in the first place? They don't have the key to the server room (see #1 above), and they can't get to it via a shared folder (see #2 above). So yes, you do need to protect that folder and your backups, both physically and digitally.
Jordan Sparks, DMD
http://www.opendental.com

User avatar
Jorgebon
Posts: 502
Joined: Mon Jun 18, 2007 2:25 pm
Location: Mayaguez, PR
Contact:

Re: MySQL database password protection

Post by Jorgebon » Tue Oct 02, 2012 12:23 pm

Do you know of any dongle or usb hardware that can be used to protect the software? I remember a database software I had that used a dongle. If the dongle wasn't connected the software would not run. I was wondering if there was such a thing that would impede copying the database folders or even running the software.
Jorge Bonilla, DMD
Jorge Bonilla DMD
Open Dental user since May 2005

teethdood
Posts: 267
Joined: Sun Jul 29, 2007 12:39 am
Location: Visalia, CA
Contact:

Re: MySQL database password protection

Post by teethdood » Tue Oct 02, 2012 2:34 pm

Jorgebon,

I think that is one of the things that make OpenDental great: no lock-ins, dongle being one of those lock-ins.
You lose or break the dongle, you gotta call in and pay $1000. A thousand bucks a bit much you say? Do you have a choice?

Besides, having a dongle isn't going to make me feel any more secure. What if your disgruntled employee steals your dongle? You'd have down time then right? So the solution would be to physically secure your server with said dongle right? You are right back to square one.

Just sayin'
Philip H. Doan, DDS
http://www.kaweahdental.com/

teethdood
Posts: 267
Joined: Sun Jul 29, 2007 12:39 am
Location: Visalia, CA
Contact:

Re: MySQL database password protection

Post by teethdood » Tue Oct 02, 2012 5:08 pm

Possible solution. See if this works for your needs:

1) Put your database in a Truecrypt container (truecrypt.org)
2) Create a job in the Task Scheduler to run Truecrypt at 8AM (or whatever time you open the office) which opens up the database (you can do this automatically or require manual password input
3) Task Scheduler also runs mysql so OD can work
4) Task Scheduler shuts down mysql, shuts down the Truecrypt container at office closing time.

This way your physically unsecured server's database could only be copied during work hours by those who physically touch that server.

I don't see why this wouldn't work although I didn't test it.

Another thing, truecrypt is open source. That means Jordan and co. could include it in OD to encrypt/decrypt the database on the fly.
Philip H. Doan, DDS
http://www.kaweahdental.com/

User avatar
Justin Shafer
Posts: 596
Joined: Sat Jul 28, 2007 7:34 pm
Location: Fort Worth, TX.

Re: MySQL database password protection

Post by Justin Shafer » Sat Oct 06, 2012 5:26 pm

I have been looking at these.. They look cool.
http://biocryptodisk.com/

Post Reply