2

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?

Anders
  • 64,406
  • 24
  • 178
  • 215
mekkanizer
  • 123
  • 4
  • 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 '17 at 03: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 '17 at 05: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 '17 at 05: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 '17 at 06: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 '17 at 06:57
  • I'm not sure I understand you. Would it be the stolen laptop which has this FTP? Would it be the one that verifies it? I mean running an arbitrary script when logging into a *nix system is easy (e.g. with PAM), but I fail to see what checking a hash from a FTP server would give you, especially if the attacker can steal both the laptop and the server, in which case the server _belongs to him_ and he can make it do whatever he wants, including behave as if it were never stolen. Perhaps you really _do_ need remote attestation. – forest Dec 17 '17 at 07:10
  • @forest This FTP is just a separate thin client. It's also inside this LAN and has a static IP. The case is small and I really doubt the potential thieves would even know of this device's existence. So, if you can describe using PAM to bind login possibility to a custom script I'd be grateful – mekkanizer Dec 17 '17 at 07:17
  • Look into the [`pam_exec`](http://man7.org/linux/man-pages/man8/pam_exec.8.html) module. But _do not_ assume that thieves will not know about its existence. It sounds like you're trying to homebrew your own remote attestation/integrity scheme. Anyone who is going to steal a server is going to know at least the basics of checking network topography. – forest Dec 17 '17 at 07:27
  • If you edit your question to provide a bit more information, I may be able to give a more complete answer. You may be falling prey to the [XY problem](http://xyproblem.info/) with your homebrew solution. Yes, PAM can execute a script at login, but does that really allow you to solve the problem? – forest Dec 17 '17 at 07:33
  • @forest what information regarding what should I provide? PAM can execute a script, but does that help me? Good question, I also asked it to myself. I currently have no idea how running a script helps me. You see, I don't need info on running a script, I know that it's configured through login manager's startup sequence. However, if it is possible to **react** to a script (e.g. expect an output which means "login granted"), it may help – mekkanizer Dec 17 '17 at 08:05

3 Answers3

0

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.

William Sandin
  • 336
  • 1
  • 6
0

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,

ndrix
  • 3,206
  • 13
  • 17
  • 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 '17 at 00: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 '17 at 00: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 '17 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 '17 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 '17 at 17:56
0

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.

symcbean
  • 18,278
  • 39
  • 73