I am setting up a new MySQL server in the office. The client app, connecting from the same LAN, is now in beta testing. So I can still change authentication system and other stuff there. Currently I decided to compile the string of private key into the app.

The client first provides the key and gets connected to the root account in DB. Then, a (particular) password hash is requested from the DB, based on the username inputted. If the hash is equal to the one made out of the password-box contents, the actual app starts.

I decided to encrypt all data stored there (use LVM, basically), but my boss is very concerned on the case where the server is stolen together with an employee's laptop (some nice guys have password PostIt's on screen edges).

I would be grateful for any info on:

  • Restricting client connections to a certain timeframe (7AM-7PM, Mon-Fri).
  • Restricting any connections or better, even logging in into CentOS (or any other distribution, if you have suggestions) by the location.

The only thing that came to my mind by now, is that shared printer and a small FTP for keeping scanned stuff can play a role in assuring that the server is in fact still on our network and not stolen and taken from the office. Say, I can verify the name (model) of the printer and check hash-sum of some file I quickly download from FTP.

If it is a suitable solution, please give details on how to manipulate the server security itself. Like, I can make a script that would return 1 or 0 in case of the FTP/printer requirement is met, but how do I use the result of this check?

  • On Linux and many Unices, you can use PAM to limit the times during which the system allows logins, and when the network is up. Additionally you can limit logins by IP address as well (or even physical terminal, if you use those). Is that all you would need, just disabling access based on time and location?
    – forest
    Dec 17, 2017 at 3:10
  • Are you able to use keycards or anything similar? That way you could have employees turn in their keycards when they check out, even if they take their laptop with them and it ends up getting stolen.
    – forest
    Dec 17, 2017 at 5:01
  • Unfortunately, you'll probably have to do that unless restricting access based only on time, and not location (which can be spoofed) is acceptable.
    – forest
    Dec 17, 2017 at 5:40
  • I must have misunderstood you. It sounded like you wanted the (stolen) laptop to report its current location. Though even with this new information, Shannon's maxim says "the enemy knows the system". Do not base your security on whether or not an attacker knows which printer to spool. Regardless, you will not be able to tell if a server has been stolen if it is communicating with your intranet unless you use some fairly advanced integrity measures (such as remote attestation).
    – forest
    Dec 17, 2017 at 6:14
  • @forest Shannon's maxim is great, but all I need is to demonstrate the "heist"-proof to the boss. Once. Imagine a script using lftp (with a separate user) to download a hash in .txt from a local FTP and check it. How can I tie such a script to logging on? Or to starting MySQL server, if it's impossible to mess with logon
    – mekkanizer
    Dec 17, 2017 at 6:57

3 Answers 3


I don't see how restricting the times a client can login has much relevance to the problem you are trying to solve. You are also closing the door on running the service across the internet if you ever expand beyond your LAN.

Adding a simple rest server would provide an easy way to apply abstract access rules to the application (indeed I'm struggling to understand why you would design a solution against specific hardware rather than at least modelling the access around a cloud based model, even if you end up implementing it on your own hardware).

People being overly precious (particularly in small software companies) about their intellectual property is a recurring theme here and elsewhere on the internet; undermining your legitimate customers but ignoring the real threats of intellectual property theft. If the question is really about the security of the data being exchanged after this Byzantine handshake, then you should be talking to someone who understands this a lot better rather than posting a question on the internet about something only tangentially related to the real problem.

The client first provides the key and gets connected to the root account

OMG, no.

I am really struggling to understand why on any application you would consider using root as a service account, let alone one where you are so concerned about security. MySQL has the facility to devolve permissions in a very granular way along with a privilege separation mechanism.

Any controls you apply via iptables, stored procedures or within the application logic of a REST server on the host are easily bypassed if someone has physical control of the device. Disk encryption solves this. I presume that your mention of post-it notes and laptops means that you expect to give out the disk encryption passphrase to lots of people. That's easily solved: don't give anyone the password. You could go and buy an expensive product like CyberArk AIM to serve the passphrase on demand, but why? It's trivial to put the passphrase somewhere else and write a 10 line shell script to retrieve the passphrase via SSH / Https (or even another MySQL instance accessed via a tunnel) validating the encrypted host using a keypair/client cert and applying your time/location rules at the passphrase server host. And the passphrase server should reside in a different physical location to address your stated threat model.

If this sounds similar to your FTP server model, then pay attention to the difference - the passphrase server is elsewhere and the authentication method between the mysql server and passphrase server is secure. If someone can steal the mysql server and a laptop, they can steal the FTP server.

  • you're right, I've changed root to the one which can't mess with tables
    – mekkanizer
    Feb 16, 2018 at 14:46
  • by the way I've just told boss to order a VPN with MySQL hosted on it, so I only have to secure app authentication
    – mekkanizer
    Feb 16, 2018 at 14:47

Suggestion to your problem:

  • Make sure the timezone is configured correctly and that you run ntpdate/ntpd on your machine to keep the time in synch.
  • Apply iptables rules based on time and day.

iptables -A INPUT -m time --timestart 07:00 --timestop 19:00 --weekdays Mon,Tue,Wed,Thu,Fri -j ACCEPT

On another note I'd suggest you to enable Google Authenticator (phone/push notifications) or Yubikey integrated with PAM, to achieve a multi-factor authentication scheme.


Having a cleartext HDD in your server, will have someone detect what scripts are being run upon startup, and your printer hashing detection solution will be circumvented in no time.

If a server is physically stolen, it often is switched off. If you have full HD encryption, which needs a key to be punched in when started; no one will be able to boot your serves; that eliminates the risk of someone extrapolating any data form there. Enable full disk encryption, would be my first recommendation.

I think that the treat model of your client/server architecture is inherently flawed; someone could steal a laptop, (or have remote code execution without having to physically take a laptop) extract the key and have root access to your MySQL DB; and also any data in there (usernames, password/hashes, customer data, etc...).

I'd recommend some SSL based login, whereby each user has his/her own cert pair (increases auditing and accountability), and if you think there's some foul play; you can revoke the cert. SSL logins can be found at https://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html

Hope this helps,

  • I never said it would be plaintext (see paragraph 3 of post). I really would like it to auto-login, so I have to understand how to do time checks and printer / FTP network queries before the session start. Somehow, your advice on SSL based login sounds worse than using a SINGLE key, that is compiled into the client. Ok, the laptop is infected or stolen, but how are they going to decompile a Qt app? Contrary to server's HDD, employees' laptops are not encrypted, so the SSL key that you proposed will be stolen no problem
    – mekkanizer
    Dec 18, 2017 at 0:20
  • In short, what I would like to avoid on the client side is storing any keys. However, the key idea itself is great, because combined with users/passwords I use to determine in-app privileges, it won't let the hacker access the data from client side.
    – mekkanizer
    Dec 18, 2017 at 0:24
  • Sorry I missed the paragraph 3, when you say "... it to auto login", are you talking about the server? Or the client? Sharing keys (and having a single key) is generally a pain to manage. If one of your clients is compromised, rolling over the key on all other clients is a pita.
    – ndrix
    Dec 18, 2017 at 18:27
  • It seems you're using mysql as an (anonymous) API - i.e, a login feature for your app. But you're using it with mysql root privs; which may be a dangerous thing; assuming users can write to tables, etc.
    – ndrix
    Dec 18, 2017 at 18:29
  • even the privileged users only have r/o privileges on important stuff, and editing on non-critical. About autologin - I'm talking about server, I want it to check if the time is right and location is office and then log in. Talking of root - I will definitely create one more non-root user, which can't drop/truncate tables and delete anything that can't be deleted from client anyways
    – mekkanizer
    Dec 19, 2017 at 17:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .