 I apologize if that goes up the board a little bit. It looks like HTMI is not necessarily a thing that's caught up with the Pasadena Convention Center. I was never a stickler for punctuality, but I guess now it's better than ever. So my name is John Roman. I work with the RAND Corporation as a Linux system administrator. I've been with the company for about two years, and the name of this talk is Hardening PGP I apologize if it's gone up the slide. It's 1024768, but apparently the wonders of HTMI have not yet made it to the Pasadena Convention Center. So a little background. PGP 101 for those of us who haven't touched it in the last 15 minutes. It's public-private key rings. Public keys can go to the world. They're generated on the machine. The private keys obviously stay with you and they're secure. There's a signing authentication and a cryptography key that you can use with PGP. But some of the pitfalls of this include the private key ring. How private is it? Anybody who's had a red team event or a red team blue team event can definitely attest to the fact that at some point they will find private keys. And at some point someone's going to get blamed for leaving private keys on server A or server B. And it's been a consistent issue. It's not just been a recent thing. It's a consistent issue for about the last decade that private keys, where to store them, and how to keep them safe is sort of on the minds of a lot of people. The portability of these keys is also a consideration. Traditional HSMs get racked in a server room somewhere. You can access them through an API or a proprietary system of binaries that might allow you to generate things from them or to get information from them. It's generally something that if you went to a conference or if you went to, say, a code camp or something you wouldn't be necessarily able to generate signatures or generate encrypted objects using them. And finally, standards compliance. To have a set of private keys around is fine, but a repository of keys, typically an HSM, needs to conform to some sort of FIP standard, that may or may not have been endorsed by the federal government, depending on what industry you're in and your mileage may vary. So the conventional example that I chose to use today is the CAC PIVs. If we have any armed service members, you're very familiar with this. This is a card that's issued to pretty much every branch of the United States military. The common access card's been in service since 2005, and this is basically everything. Guns don't get issued, boots don't get purchased, people don't board submarines, and wars are pretty hard to declare without a CAC PIV. For anybody taking a look at this, you can see a two-dimensional barcode as well as a smart card chip at the bottom of that, and that's essentially what we're going to be focusing on today is the contents and functionality of that smart card chip. And the information on a CAC PIV card for an armed service member or for a member of the Department of Defense includes a number of different things, private key and public key as well as total number of signatures and a lot of other information tracked by that specific chip. It is PIV 201, PIV Federal Information Processing Standard, for anybody who's curious what PIV's meant, I kept saying it. Personal Identity Verification is what PIV stands for, and it's the federal standard by which these cards are issued and two which they are adherent. So there is a standard that you can take a look at, it's downloadable if you wanted to apply as a manufacturer for the specific type of card. It doesn't do us a lot of good though because a lot of it is pretty proprietary. So OpenPGP has an implementation of this, it's been around since 2004, and it's effectively just that. There are nine different vendors for the OpenPGP standard, and there are several form factors. You can have a traditional SIM card form factor, you can have a USB form factor. The third form factor here is the one that we're going to be focusing on, it's the UB key. It's relatively outside of the FSF in Europe, unknown. A lot of people don't know that you can have an HSM implementation of PGP, that there is a hardened implementation, that it is supported by an open community, that the standard is open, the code is open, you can check it out and take a look at it for yourself if you'd like. So if you've joined the FSF Europe, you get what's called a fellowship card, and the fellowship card to the left is pretty much what that looks like. And that gives you access to a PGP private key that you can use for encryption, authentication, signatures, what have you. So our focus of the talk is the UB key, and it supports what's called a hybrid mode. So if anybody's familiar with the UB key, you know it does one time authentication, OTP authentication with either a PGP script or with a dedicated service, the UB key service that you would dial out to, you would get information, there's a handshake, and the OTP is validated for a UB key holder. These are hermetic crush proof. They have a scalable pricing, and I put mine in the dryer more times and I can count, and it still works. These three things are really great if you're in the field a lot, if you're traveling a lot, if you're a road warrior, if it bangs around in a bag or box or gets lost, and you have an NFC option. So for those of you who don't constantly want to be testing how many exact connections and disconnections from your USB port you can make in a lifetime of a laptop, you can simply put it near an NFC sensor. And we'll touch a little bit more on that as far as the applications are concerned. The general concepts with anyone who's not familiar with an HSM or not specifically familiar with this implementation of an HSM is that what you're looking at is a CPU. It is a computer. It has a firmware. It has an open firmware. Keys are loaded into slots, or if you'd like you can generate them on the card. Encryption, decryption, and signature are commands. These are not necessarily things that you would load in. You don't copy keys onto the card. You don't cat keys out into a file. You issue commands against this specific piece of hardware and it gives you data back. Once these keys are loaded, the private keys are sacrosanct, which means they never leave. You can't get them back out. You can't ask for a plain text dump of your private key. If it's encryption, signature, authentication, or the attestation key, which we won't be talking too much about, these keys stay on the card for the life of the card, certain circumstances apply, see store for details. So UV key only accepts commands once again, and it only returns data. It's never going to return keys, which is one of the highlights of an HSM. A traditional HSM is only ever going to allow you to interact with the cryptographic data. It's not going to, say, give you a private key by which you could do nefarious things with. So this specific HSM, as well as the captive card for the federal government, is similar in that it has a PIN number. And that PIN number, if anybody has a European credit card you're familiar with, it would be chip and PIN. You'd put the credit card in, you'd type your PIN in, and it would complete the transaction for you. This PIN number is similar. It's between four and six digits, and there's a three-strike system. So if you enter your PIN incorrectly three times, say, for example, under duress, or if you've entered it accidentally three times incorrectly, the CPU on this implementation, this UV key implementation of open PGP at the HSM level, will lock that chip. It will lock that card. You're no longer allowed to issue any more commands. It simply will not accept anything, not even a query command, to determine the state of those slots. The PIN, and this is great, can be unlocked with a security officer PIN. So this is a second PIN that might exist alongside the hardware. It's never really used on a day-to-day basis. You wouldn't use it to access the keys. But if, say, one of your users comes to you and says, hey, I was on the road, I wasn't paying attention, I fact-fingered this four or five times. I'm not necessarily certain how I'm getting back into it, but I know I can't sign my Docker images this week. You would send in your UV key to your security officer, or to your security team, or to your SOC, or maybe to yourself, and you would use the security officer PIN to unlock that for them. And you would say, well, here you go. You can use it again. Please don't fact-finger your code again. Keep it in your mind. However, three strikes against the SOPIN is a death sentence. This card will stop working while I say card. This device will quit working entirely after three strikes against the security officer PIN. You're all done. You can't get the regular user PIN anymore. All three slots are bricked, and you cannot interact with the card's security officer interface. So that means it's just a piece of plastic. Whoever picks it up isn't going to be able to get any information out of it. Once again, the PIN like the 6 to 8 characters, some implementations are huge. And the original 6 to 8 or 4 to 6 characters was based on ATM PINs, which was only ever 4 characters in a lot of implementations because the original implementation of the 4 character password was effectively based on the, if I'm correct, the life of the ATM firmware's inventor said no one was going to remember anything longer than four digits. We have that. To place the card in a hybrid mode, and I guess to give a little bit of background about this, when you get a YubiKey, when you actually get that $40.00 HSM, it's originally only ever going to give you the ability to do OTP. So you do have to enter, you have to enter firmware. You have to use the YK personalized tool, which is on the YubiKey site. Not very difficult to use at all, and well documented for that matter. This is just an example of why I don't trust live demos. I've seen a few today. It was interesting. So to use YK personalized, what you're essentially doing is you're telling the firmware that you now want to put it into hybrid mode, which means you can use the one-time password feature and keep your keys. So you can use OpenPGP with this device, too. It'll be recognized by the OpenPGP subsystem as, hey, I have a smart card. I can use that. But if you push that button on the YubiKey, it responds just the same as a normal YubiKey would. You'll be able to request YubiKey information from like a local PGP instance or all the way out on the Internet from Yubi. If you don't commit, it doesn't set the mode. And unfortunately, the mode is not incredibly graceful. It doesn't look great. 0x82 is a hex mode that you're passing to that card. So unfortunately, this may be a little bit illegible, but I'll try to go through it as best I can. These keys were loaded from an air gap system using the key to card command. To give you a little background on key to card, that's going to take keys that you generated on an air gap system, and it's going to send them to the card. And remember, when you interact with this card, you're just interacting with commands. So one of the commands is to load a key. How do you get the key back out? You don't. So in the case of escrowing, if you wanted to make sure that you had a centralized escrow, and YubiKey manufactures a key store, like an HSM that will hold 1,024 of these, so if you wanted to escrow these, you would definitely want to do it from an air gap system in a secure location, load it onto the key, and then escrow those keys as necessary. So GPG card status will give you a little bit of information about it. The application ID is the specific application ID of the firmware running onto YubiKey. Version, the vendor, the name of the card holder in this case, John Roman. So every card is going to have a card holder set, if you'd like. Language preferences, if you want to base your applications, your API is on language preference calls to the GPG card status command, or to pulling information from GPG. The login information as well. For example, if you tried to login using a different name, or if you tried to login using your first and last name, but the card knows that your login data is a specific data, you can auto-log users in with that login data if you choose to. So key attributes, as we can see here, all 4096-bit keys. We'll get into a caveat later about keys and size. But in this case, they are 4096-bit RSA. And the maximum pin length for all of these, you see triples here, actually. You see 496, 496, 496. This is for signature, authentication, and encryption. So these are three different keys loaded into three different slots on the card. Pin retry counter is going to count how many times for a specific pin I have attempted to enter that pin. So as we can see, I have three retries on the first, zero retries on the second, and three retries on the third. Which means that the second slot may be locked out. In fact, I believe it is locked out. So the general key information gives you a good idea, John Roman, another lonely soul on the Internet, as well as sub-key information. A lot of you who work with GPG on a regular basis may not be familiar with this, but those carats or those little ellipses next to SSB, that implies it's a stub. That that private key for encryption purposes does not exist in the key ring on the system that you're working with. But you're going to need to find the card with a specific serial number. As you can see, about four lines down. That serial number has to be present in order to load keys for your specific user. So if you wanted to modify any of this information before you sent the card out, or before you decided to use the card for anything, you can go into an admin mode. Admin mode allows you to set things like the URL to retrieve the key. The public key, of course. Fetch information. So if you issued the fetch command, it would pull down the public key and automatically associate your UB key, or associate your HSM with the public key. So you wouldn't have to do a lot of work in the background to determine a user name, to determine which profile is using this key. You can assign the sex of the card holder if it's something you'd like to do. Signature force pin is interesting because depending on the API and depending on the application you use, you can actually set this to where if it were entered, and if your application requested it, it would force a signature to be generated on installation or on detection. So the password is to change or unblock the pin. Password is actually just your SO, your security officer code. If you wanted to unblock a code, verify it to verify the pin and list all the data. So you would enter your user pin at that point. You're just, you know, your everyday user pin. Your 4, 6, 8, 127 character pin depending on how much you like somebody or not. And it would give your information. So your unblock code, again, is just another iteration of the password to change the unblock pin. So what can we use this for? We can essentially do anything that's GPG enabled. So if you have PAM, you can integrate this with PAM. If you have an application that runs on a web stack with Tomcat, Tomcat can use GPG to request key-based authentication or signature-based authentication from the UV key. Can you do multiple layers of security with a single device? Yes, you can. You can use one-time-pad. You can use a certificate and a password. Or you can use a combination of all three. PAM can request a password. PAM can request that it matches your certificate with your public certificate on file and that that certificate is valid in OCSP or valid with the certificate store. And it can check to make sure that the OTP is valid as well. And one of these can fail the authentication attempt. So multiple cards per key with each one having a unique subkey. And I guess what I'm basically touching on there is the fact that if you had a team of developers and they were all in charge of building a docker container, for example, because docker is this year's theme, your developers could each be given a subkey. They could be given a signature subkey. For example, your R&D development wouldn't be able to sign a docker container for production. Conversely, your production environment would never have a signature for a docker container in R&D. And those would all be signature subkeys that would be assigned to individual Yubikis that would be carried around by your developers if they had to sign code. Or you can give them individual master keys and subkeys based on their roles. So at this point, we've already seen the applications as far as PAM is concerned. We've already seen the applications as far as the GPG implementation is concerned. It's pretty powerful. Can we do anything else with this? And we do have an NFC option, so why not hold it up to the back of our phones in the middle of the crowded airport and see if we can send an email. Open Keychain is an application for Android and iPhone that can be used to integrate the Yubikis OTP as well as its open PGP, again, multi-factor, hybrid multi-factor, into a smartphone application. And currently the one that I use the most is K9 Mail. So if anybody right here is running K9 Mail, you can certainly integrate this with K9 Mail. Use the NFC sensor feature and you can encrypt, sign, or even authenticate with a remote mail server based on your Yubikis and based on Open Keychain and Android and iPhone. Keys need to be generated by the user, and this is sort of a double-edged sword. Depending on what user you're going to give it to, you're going to have to have a high-level trust that they can use this, but it is a very user-friendly application. The downside being, in this case, you wouldn't be able to escrow the NFC. So this is definitely a consideration. If you want to escrow NFC by all means, if not, and they lock themselves out, you can use your security officer pin or if they lock themselves out. You can simply elect to expire their old key and assign a new one. This only supports a 2048-bit key. And your mileage may vary again. This is going to be something that you want to discuss with your teams and determine an application strategy that's specific to a 2048-bit key. Maybe you don't want this thing to encrypt data. Maybe this should only be used for signatures and only in the development. For example, developers can use an NFC to sign packages. Production cannot because of the 2048-bit key limit. This is being worked on again. This is a limitation of the fact that NFC and key-based cryptography had to be fit onto a single device. What if you want to deploy 450 of these things? What if you want to deploy a lot of these things? What if you want everybody to use these things? Because, as we all know, all of our users love multi-factor. You're going to need entropy. GBG doesn't rely on E-random. It doesn't rely on pseudo-random. You don't want fresh random entropy in the entropy pool from the kernel. Your options are the FST01 from the FSF store. Plugging those guys. FST01 is a wonderful implementation in both USB as well as MMC. It works in laptops, works in USB, and allows you sources of a relatively quick entropy. It's definitely not going to be great if you're code-signing things constantly or if you're signing hardware drivers, for example, and you have to load those drivers dynamically. That might not be a good implementation, but for, say, provisioning 100 or 200 or 300 of these, you won't run out of entropy anytime soon. The other one is the RTL digital TV dongle. If anybody's familiar with the amateur radio track upstairs, RTL will allow you to harvest entropy from the air around you. You can use FM, AM, single-side band. It doesn't matter what you want. You can plug in a frequency, and what RTL will do is convert that atmospheric noise into pure entropy and fill the kernel with entropy for you. That's about $20. Maybe spend $15, $20. The question was, did the Ubiqui implement a key generation command as well, and that is correct. If you wanted to, you could issue a, say you don't want to create these on your machine, if you wanted the Ubiquis to generate the keys. If you don't care about escrow, and you wanted to create the keys on the cards, you could. But you have to remember that these cards are not necessarily erase cards. They're not fast. So your limitation at that point is the onboard CPU of that card. How fast can it crank out data? How fast can it find fresh entropy? And how fast can it append that data into the card storage? OpenPGP doesn't include support for OpenSC, the smart card demon in Red Hat 7. Anybody using Red Hat 7? CentOS 7? Okay. This is something you're going to have to take a look at yourself and if you're interested in compiling it yourself, OpenSC is definitely not a difficult undertaking, but could affect your ability to scale this out to the enterprise if you are a Red Hat customer. You do still have the ability to do multi-factor in Ubiqui with Red Hat. That hasn't been eliminated or impacted at all. Your ability to interact with the cards on a Red Hat system however has. So if you wanted to authenticate using the Ubiquis OpenPGP implementation, this would not work out of the box with Red Hat. E-Pel may be your friend in this case. NFC users, it really depends who you want to use this specific technology. If you have C levels or if you have management and you'd like them to sign their emails before they send them out, you might run into the problem that a lot of your users just don't like to hold that NFC up or that for example the HTC One doesn't pick up that NFC very well. So you have to do it over and over again. Pretty soon you run into an issue where the signatures just aren't there and the encryption just isn't there. Again, destroyed cards. This is probably the ugliest part. If you do manage to brick an SO, Security Officer PIN, the PIN that you use to recover a PIN, you actually have to do a firmware reset on the Ubiqui, which wipes all the data out entirely. So all the user data has to be regenerated from your Ansible scripts. The keys have to be regenerated. The old ones have to be revoked. The escrow has to be updated. It's really kind of painful. The keys should definitely be stored in a secure undisclosed location and escrow. And the card work considerations that I touched on a little while ago on how you would necessarily generate keys on the card or how you would generate public and private keys on the card, it's a function of how fast that CPU is going to work at the end of the day. So if you want to write a 4096-bit key to the card, my personal testing is revealed it takes about 15 minutes. 2048 takes about 7 or 8 minutes. It's really not that bad. You can pull out hundreds of these or thousands of these and you had a USB bus doing it. You may want to reconsider the wisdom of that. An FST01 with a full entropy pool can generate private keys in seconds. So you would have a handful of these private keys generated across the USB bus. Bob's your uncle at the end of the day. Those keys are done. You don't have to wait around for them to all quick blinking. The native PIV card has the same limitation as well. So it really depends on the PIV card, for example, in the government's purvey. Older PIV cards are restricted to 2048-bit. So generating a 2048-bit signature on an older PIV card, if anyone's familiar with the... So the question was, am I talking about generating on the card or popping it into the card for 7 to 15 minutes? That's the time it would take the CPU on the card when it's issued the command to generate a key. So the question was, the speed of the USB bus, does that necessarily improve your ability to generate keys on the card? Unfortunately, it doesn't. Again, we're not working with an incredible CPU here. We're literally looking at something that can generate keys, that can respond to keys, and that can encrypt data in real time. To expound upon that, if you wanted to... the IO consideration specifically, if you wanted to encrypt a 50-gigabyte file, that 50-gigabytes of data is going to have to be made available to the UB key because the encryption operation is actually going to be happening with the UB key. So if you feed 50-gigabytes into it, you're going to need to wait for 50-gigabytes to come out. At that point with USB 2 and 3, your bottleneck is not necessarily going to be USB IO. It's just as fast as the CPU can run the encryption functions on the data and return it to you. So again, with the card itself, you can only issue commands with it. You can only provide it things to do. So the keys, the private keys that you loaded in there or that you generated, once they're there, they're there. And they're only ever accessible on the CPU on the UB key. So if you wanted to encrypt data, you would have to start the command to say, hey, I'd like to encrypt some data. The UB key will say, go ahead and send me data. Correct. And the card will respond back with the data as it's encrypted. You're not going to get a copy of the private key. Correct. As far as the private key, signing, encryption, or authentication is concerned. And that's one of the main benefits of having an HSM, that private key is never something that if you leave it in an airport, it's a three strikes rule. It's never going to be something where you have that key and it's laying around. And Red Team comes to you a week later and says, hey, I found this key. So that is a consideration. If you wanted to encrypt something that's 50, 100, 200, 300 gigabytes, you're going to be looking at a long wait. So maybe that is an air gap system that's dedicated to encrypting those things. I don't believe so. Was the question a Simeer Schnerer? Right. So the question was, can we have a system in which it requires more than one person, if I'm correct, with a UB key to generate keys for UB keys? Like, for example, if you're generating 400 of these things, maybe don't entrust it to just a single person. I'm not aware of a system or a process. Correct. If you wanted to, you could have a 16-digit or a 32-digit or 64-digit pen and simply split it up amongst two people. The implementation specifics of it are definitely going to be something you want to talk about. Absolutely. If you wanted to, you could encrypt it twice. You could have one person with one pen and another person with another pen. They both come together. One or two empowers activate and you now have the ability to log into the system and encrypt and decrypt UB keys as you wish. I guess it's worth mentioning again that the system to log in or the system to generate these keys isn't necessarily restricted to a single user. You have multi-factor authentication. You can definitely lock this down using PEM. Something you have and something you know, of course, but the password, the OTP itself, meaning you have the device in hand and the pin that you know or the pin that somebody knows or the pin that's available somewhere through a hashing function or something. I guess with that I'm pretty early. Any other questions? Right. The question was, if a card is lost, if you lose your UB key somewhere, is that data lost forever? And the question is, did you escrow or would you like to escrow those keys? So if you did lose a UB key and you did not escrow that data, then the UB key is the only thing with the private keys on it. And the only way to interface with those private keys is with your pin. Three strikes and all of those slots, one or more of those slots would be wiped out. Copying the file or copying the key into the UB key doesn't take very long at all. It's actually a few... One of the advantages of generating the UB key on the key and the question was, why would we do that? Why would we tell the UB key to generate its own key? If you trust the firmware, if you trust the software, you don't want to have to worry about an escrow, for example. So if you're in a hostile foreign nation, for example, and you need to either generate one of these quickly, you can do it using NFC. You can do it on the card itself if you don't trust the device. And that data, when it's being created, it never actually enters the system. So it's all self-contained. You simply use the command to generate a key. It generates the key itself and never communicates it with the system. Okay, so the question was, what is the nitro key in relation to the UB key? And what was the controversy with the UB key that caused a lot of people to switch over to nitro key? Nitro key is essentially the same implementation. It's open PGP, you have your slots, you have a fourth attestation slot, and you have the ability to store passwords as a password safe. The controversy between UB key and nitro was when UB key went to a closed firmware. You could no longer check the firmware out and see it. You can still, if you wanted to, open the device up and re-image the firmware. You just have to contact UB key to get information in order to issue the key that would allow you to open the firmware and write it yourself. Whereas nitro key allows you to do that by default. You're allowed to get in, edit the guts of the card. If you wanted to run your own version of open PGP, you could on the card or if you wanted to run someone else's fork of open PGP on the card, you could. In a what partition? In a lux partition. This is interesting because I actually tried it and it's not necessarily applicable for a lux partition. Lux partitions would like a random blob of just U random data for their key. The keys for PGP are a little bit larger and they adhere more to a PKI hierarchy, so it's not really... Yes, if it's PAM enabled, you can issue it back and forth with PAM. There's an open PGP implementation for Windows as well and not necessarily specifically aware of how it works. The question was, can we determine how a key was generated? If it was generated on system or if it was generated and then imported? And unfortunately you can't. If you can take a look at the keys here, I'm not sure they're actually visible. It's kind of small font. Signature key, encryption key and authentication key will tell you the time and date stamp of when they were created but will not necessarily divulge how they were created. And we'll give you a little bit of information if you've imported the UB key onto your system of what type of keys you're looking at. So the SSB4096R, the carrot indicates that there's subkeys but it's not going to tell you if it was generated on the card. Yes, absolutely. Subkey expiration dates and subkey... Well, subkey generation dates are sacrosanct but subkey expiration dates, if you wanted to, you could update the subkey expiration date if you had it escrowed, reload it on the card and you would have a key that wouldn't expire that year. All right, so the question is, do we support elliptical or elliptic curve ciphers or any sort of elliptic cryptography as far as signatures are concerned? Unfortunately, no, not right now. These are relegated to ciphers that are loaded into the card's firmware that the card is capable of doing itself using its CPU. In the future, certainly, I can definitely see a use case in which a lot of these vendors would update to say, hey, EC25519 keys are definitely a thing. They're smaller. They occupy sparser space in the memory so you definitely have more room to do more things with the card. The side store keys. But as of right now, again, the key limitations, for example, 2048 bit key in NFC implies a certain latency as far as hardware development is concerned. Can we do 4096 bit keys? Can we do elliptic curve? Yes, but the NFC implementation for UV key was certainly about getting the product out to market first and then maybe working on making it harder and harder or hardened as time goes on. I wish there were. It really depends on the application. Currently, I'm only using it for email for signatures and encryption. I have used it to sign code before. I've used it to sign binaries before. I've used it to authenticate. Yes, it would be signing a hash. So the question was, how does key expiration work in the context of the UV key HSM? Once the specific key is on the HSM, it's essentially based on the functions that you have here. So this is the admin panel for the card. To change any information about the key after the time of creation on the card, as far as I'm aware, is not possible. If you wanted to have rolling cards, if you wanted to have cards with slots that expire every 90 days, if you wanted to update those expiration dates, you would need to have an escrow point by which you could load those out of the escrow, update the escrow keys, load them back in. Not necessarily. When you transfer your key to the UV key using key to card, it will always prompt you to do an escrow. So it will always ask you if you want to make a copy of that private key before you send it to the card. As far as the most recent GPG, it has been a thing for a while. Just so that you know that once you do load that key onto the card, GPG will give you the opportunity to escrow. It will say, do you want to make a copy of the private key? It's definitely a good thing. Using key to card, when you select the key that you want to send, when you generate the key, yeah. I believe so. If you use key to card, you should have the opportunity to go ahead and create a backup, or if you create that key specifically in GPG, and it's going to a card. At some point, you do get prompted or warned. Once again, if you go back to the carrot there next to SSB, stub keys are not real keys. So if you didn't have that card, it's going to prompt you for the serial number of the card that it needs, or it's not going to sign it all. So just to be doubly sure, maybe don't back up the entirety of your GPG, but definitely create those private keys as a single step, back them up, and then use key to card to send them out. Assuming you wanted to use the same private keys outside of an HSM, or if you wanted to escrow those private keys. So the, I guess the allegory, the pitfalls that I made originally about private key rings and how private it is. When you're looking at a private key ring, you don't necessarily know offhand if that data at rest is encrypted. You don't know who's accessed at last. You don't know who has access to those files, or if there's an ACL for some reason, or say they're shared on a network drive, where they've been and who has them. So I guess to put it a little bit more bluntly, private keys have a habit of migrating. You have one developer that wants to go ahead and sign code. He's on holiday and he wants to go ahead and give his private keys to another developer so that they can sign code for the weekend. Well, what happened to that private key? So the chain of custody becomes a concern at that point. You now have a key that's on two systems, and then four, and then six, and then eight, and then 10. So when you look at a single key ring, to assume that it is orthodox, for lack of better description, that it hasn't been anywhere and that it's not used anywhere else, that was my main concern with the private key ring as it exists in a traditional application in Linux. For the UB key implementation, perhaps, again, there are nine vendors of these. So if you wanted to go with a different vendor, for example, if you were happier with Nitro, the one at the center there is the Nitro key. In the future, it's certainly possible that they would, but I'm not necessarily aware of any vendor-specific upgrades that are taking place in the near future. That's a good question. The question was, how do we know we can trust the firmware of the UB key? Now that it's closed, now that we don't have access to it, now that we can't take a look at it, how do we know there's trust? The CEO of UB key came out in the last two years with a public statement as to why he did that or as to why that decision was made as part of the strategy for UB key and as part of the strategy for OpenPGP because for a lot of engineers and a lot of security professionals, if you can't see it, it's as good as insecure. It's certainly a point that you want to take into consideration if you're looking at these applications. Again, with the 2048 Big Keys as another consideration, do you really trust the 2048 Big RSA key and to what extent? So it's all based on, at the end of the day, trust. That UB key put it out in the past, at least for me, speaking personally, the fact that they put the source code out in the past and that I have seen the source code in the past gives me no cause to believe that they are in any way nefarious. For example, RSA, the secure ID tokens, have never been public. No one, except for FIPS, has ever audited them. So it'll never be something that you get to see that, hey, this is how the salt is generated. You just have to trust that their salt is okay. So I think in the case of UB key, some data is better than no data. And I tend to err on the side of historians that they were a company that specialized in cryptographic devices and that they went out of their way and at least showed us the source code in the past. Doesn't necessarily mean that they won't do it again in the future, but it's comforting enough for me. So another your mileage may vary if you trust the device or not. Okay. The question was, where can you learn more about these? UB key has some surprisingly nice documentation when it comes to these. It's not anywhere near the level of complexity of, hey, you now have to compile this firmware and now you have to find a way to interface with the card. They've done a lot of the heavy lifting. So when it comes to the firmware of the card, that's already there. It is a version of OpenPGP. It's forked from the main. The OpenPGP mailing list might be a good place to start if you wanted more information on how to apply PGP, how people are using PGP. That community is certainly a reference. I'm not certain what... So the question is, how do I know the source code that is loaded onto the device is the source code that is performing according to the fork that's available? How do you verify the fact that the firmware for OpenPGP that UB key says is on there is the actual firmware? Or you could load it yourself if you wanted to with the nitro key. I guess for me that's a little bit of tinfoil hat, but I can certainly see applications where there would definitely be a concern if that were the case. If you ship a tray of 500 of these, you would definitely be waiting at the loading dock to receive them. You wouldn't wait for over the weekend then to sit around in an office room unlocked. I think validation of the UB key firmware is definitely a concern. True. And with the UB key application, YK Personalize can give you information as to the revision, as to the version, and you can certainly look online as to which revision and version is released. You can contact the developers, which is nice about UB key for YK Personalize. It is a GitHub project, so if you wanted to, you could talk to the guys that loaded the firmware and asked for means by which attestation of the firmware could be completed independent of UB key, which I'd recommend. The question was where would I have an application for 4096 big keys versus 2048 big keys? If I suppose in an enterprise environment if I were to say a 2048 big key was sufficient, I would say it's for signatures. Say I have C-levels, I have high-level management that are capable of making directional or leadership decisions based on the data in my company. And one of them immediately sends out an email and says shut down the Northeast Data Center. How do you know that's real? 2048 big signature I believe is for me comfortable enough to know that if I can verify that signature from that CEO in the LaGuardia airport at 3.30 in the morning, that I'm going to instruct the engineers or the engineering team to shut that data center down. For example, signing code, it really sort of depends. If you wanted to use that PGP to, as a security officer, sign off on somebody's docker container that it had to have your signature attached to it before it would run in the CI, maybe 4096, but it depends on the docker application. So it's highly subjective. Where do you feel comfortable with 2048 big keys versus 4096 big keys? And keep in mind the 2048 is going to, it's going to perform faster than the 4096 too. The current NIST recommendation? Yes. So in the future, will we see 3K keys, will we see 4096 bit more and 2048 bit less, or will we see more emerging elliptic curve, EC25519 for example, perhaps. It's a natural leap to say we don't want to do these extended keys anymore. We'd rather just have the ability to generate strong ones that are small and fast. Right. And again, the limitation or the key limitation in this specific implementation is that CPU on the card. RSA and DSA, yes. So in this case, if you wanted to select an encryption technique or if you wanted to issue an encryption command to the card, it's going to be the encryption commands that are supported by both the hardware, the implementation of OpenPGP. I haven't found any deviants personally between GPG and between the card when generating keys, but whichever one you select. The question was, which one is it going to use? And during the GPG encryption process, you were presented with a number of options. Cypher's key length, expiration date. So you get to configure this all interactively for the keys or for the cards. If you wanted to script this in a DevOps style, it might be an Ansible recipe that goes across the USB bus and says, hey, everybody gets 4096 big keys. Everybody gets RSA because that was our FIPS validation and our audit team said we need to have this specific type of encryption. Everybody gets a 170-character password because everybody needs to spend all week trying to type this thing in. Anything else? Yes, it definitely works. Do I need to use this? It helps because everything is being reported. So I'm going to replay the... Yep. Hello. I do. I do very much like your style. Very cool. We started in, like, two minutes, right? I don't worry about it. It's fine. Now I can see your eyes. Are you enjoying your scale? First time at all. Good. So we expect to see you back next year then. Oh, oh yes, there's tomorrow. How can I forget? It's already the third day of scale and I feel quite wiped out. That's good. Yes. Not quite the standard security talk for this track. I gave one yesterday up for my SQL. Yeah, the dark side. I do like your shirt. So we have Shwag at our booth as well. Please come by and grab the stickers. Okay. Are you recording? Yes. The bottle openers might be useful to open up soda bottles. Okay. Yeah. So should I... They're recording it as well, right? I'll start now. Okay. Yep. I did this once yesterday already, so... Not the same talk, but... All the rules are a little bit different. True. Okay. Yeah, so this one I think there is a sticker wire that's not shielded, so if you try to plug in your power it doesn't actually... If you can't use power basically. Otherwise it just sends a lot of feedback. Okay. All right. Good evening everybody. Thank you for coming to the last session for today. I think it is the last session. Okay, I'm going to introduce Colin, but just to let you guys know that the last speaker for the day had a family emergency and he wasn't able to come to scale. So this will be the last session of today for the security track for scale and we'll continue tomorrow morning. Thank you. And here's Colin to go through the MySQL Security Essentials. All right. Thank you. And first question is, how many of you here used MySQL? All right, so that's good. What about MariaDB? For Kona server? MongoDB. We can talk about MongoDB security at the booth because this talk is not about that, but we can totally talk about MongoDB security too. So I'm here. I'm Colin and I'm here to talk to you about MySQL and MariaDB Social Security Essentials. I myself have spent a long time working on MariaDB and MySQL. So I started off working on MySQL nearly 15 years ago and I'm currently Chief Evangelist in the CT office at Prokona Inc. Where we do 100% open source software and make enhancements to both MySQL, MongoDB, and make lots of tools that you could also use. And before that, I was on the founding team of MariaDB Server, so I'm quite aware of all the changes we've made. So MySQL, historically, I want to say historically I'm referring to anything that's been based off MySQL 5.5, so that includes every MariaDB server that's out there today. Basically, it has no password for the root user. You have a very poor default, so there is also a test database which means you can do things like insert stuff there. You can also obviously find passwords through config files. The super user account by default normally has no password, so you could just type MySQL-Uroot and log in. It's quite magical really. You get started really quickly, but it also means that if you happen to open up Port 3306 to the Internet, you get host really quickly as well. Also, some people need to type. They need to compile software themselves, and it turns out your data directory permissions are not secure. While if MySQL could be read by anybody, and sometimes the MySQL permissions are wrong, this is usually fixed if you use Linux and a packaging system, or even, say, BSD ports. But if you end up having to compile your own software with your own tool chains, this could be a problem. Also, you can run strings on the MySQL user file. You can actually find passwords. If you happen to set a root password because you are proactive, if you typed it on the shell, MySQL-Uroot-ProotMe, then your password is also now saved in the shell history. There are multiple ways to get into your system. And of course, if you compile, the other common error people make is they run MySQL-D, not with the MySQL user, but the root user. So again, use packages, and this can actually save you from a lot of grief. Also, you can find privileges. So if you have access to the server, you can do select and see who has more access than otherwise. So this is, again, having... If you have weak defaults, this could be a problem for you because anyone could do this. And I think it's very poignant that we talk about this now, largely because I think in the press, maybe this week, there were lots of MySQL servers that got rooted with your data encrypted and then asked for, like, bitcoins for Ansem. And bitcoins are now more valuable than gold. So there are obviously more things to think about. Replication is extremely easy to set up, but by default, if you read, you know, basic how-to guides, they tell you to create and have global permissions for the replication user. This is not good practice. If you can obviously start and stop the MySQL-D process, this also means you can reset root passwords. You can also then start MySQL-D with dash dash, skip run tables, which basically allows you to log in as root and then update the root password. So if you have permissions to start and stop MySQL-D, this is obviously an issue. If you can edit the My.cnf file, so the My.cnf file has incorrect permissions, you can run an SQL file and just have the init file to say you'd like to skip run tables. So more crafty ways to get into your system. And SQL mode. This is more on the side where you think about preserving data integrity and in 5.6, the default has been no-agent substitution, which means that when you do a create table, the default engine is used, and otherwise a warning will occur if the desired engine is unavailable. So this is important. And of course, SQL mode, you can keep on adding things to SQL mode to make it a lot stricter than before. In fact, with 5.6 and 5.7, MySQL is acting more like a real SQL database that follows SQL standards. So there are more stricter rules that you should follow. So, you know, an old, a quick roundup of very common signs of poor security. You wouldn't believe this, but people even right up until the end of 2016 said, we can't migrate to new MySQLs because we need all passwords apart. We have customers still running on old passwords and they don't want to change the applications. Old passwords, if you're using MySQL old passwords, this is extremely easily crackable. It doesn't take someone probably more than a couple hundred tries to get in and brute-force the old passwords. In fact, we'll talk a little bit more about the native password, and that is also, you know, kind of brute-forceable nowadays, or can be subject to collision. The users without passwords, very common problems. Privileges, all privileges for users. This is what, you know, if you go and download some software package and it'll tell you, just grant everything to the user. Don't do that. If someone hacks that PHP application, they've got full access to your MySQLs. So, yeah, the other common one is people love to disable the firewalls. Don't do that. Disable S Linux because it's easy. With 2017, you should all be running with Azure Linux in forcing mode, or app armor even. So, one easy way to fix common problems. So, if you're running any MySQL 5.5, 5.6, you don't really need to do some 5.7, or any MariaDB write-up until today, so that's 10-1 in production and 10-2 soon. Run MySQL secure installation because it'll remove all anonymous users. It'll get through to the test database. It also removes non-local host root users. It forces you to set a root password, and then it reloads the grant tables. This is such a simple thing to do, and I encourage everyone to do this. And if you install this via package, so Debian allows you, Debs allow you to actually have interactivity, so you are now forced via Debian to also provide a root password, which is great. This is not true if you install via RPM because there you need non-interactivity, so you still have to run something like this. So, the extremely lazy way to create users, and I'm sure we've all done this, is to create user foo, and then grant all to set user. Yeah, so this is not good, but many, many projects tell you this is the best way to create users to get started, right? Everyone wants to get started in 5 minutes and under. MySQL wanted this. Applications that dependent MySQL want this as well. So you don't want access to all tables in the database, as well as access from any external location. This is bad. So you really don't want access to do things like shutdown and so forth. So super privileges. These are special kind of privileges that allows you to bypass even a read-only server. And typically, why do you need a super user? Because you've got, say, 300 maximum connections. They're all long-running queries, and now your database is inaccessible, so the super user can log in and actually do, it can make one extra connection and kill long-running queries, for example. Super users can also bypass, obviously, the read-only. You can bypass and it connects. Now, if you bypass and it connects, you can do something like, you know, where, you know, basically you say set names UTF-8 to use UTF-8 communication between all clients and data, but because you are a super user, you can bypass and it connects and say you'd like to use Latin 1, and this could actually cause problems for you, too. You can disable binary logging. So you can disable, you know, replication or if you're binary logging and sending said data to other data sources. So you want to definitely limit super privileges to local host only, and there's a nice new tool called the super read-only mode that's available in MySQL 5.7, Percona server 5.7, and I believe also Percona server 5.6, but it's still missing from MariaDB, is the super read-only mode, which basically prohibits client updates for everyone, including the super user. This is extremely important when you want to do automated failovers. So if you're doing an automated failover and you want to make sure even the super user can't do any right, and this is how you tighten things up going forward as well, and this will help when you're preparing a server for, you know, moves or upgrades and so forth. You'd never want the administrator to make mistakes. So general advice is to always only give users what they need, so passwords, accessibility, which database and schema to access and the appropriate privileges one needs to have. This is fairly standard stuff. Just read the documentation and, you know, plan ahead and have a plan, obviously. When it comes to applications, you know, some applications are just there to view data and they should really only have select privileges. Some need read-write access. So, you know, your WordPress installation, for example, needs read-write access, but it doesn't need access to shut down the database server. I mean, that's kind of crazy. So you need to be smart about that. And, of course, if you're a DBA, you have more access as well, and then you can tie this in with, say, audit logging, for example, and this could be so much better. We are at scale, so I'm going to presume all of you use Linux in production. Yes? Anybody not using Linux in production? What do you use in production? Windows? Combination. Okay. So, I don't really have much advice for Windows, unfortunately. So, because I haven't used it personally in maybe 20 years. So, sorry. So, yeah. Anyway, if you use Linux, more often than not, Linux distribution senders ship outdated releases of software, which is a typical Linux distribution problem. So if you have something like, at least like Red Hat, you get something like software collections now as well. Also, when you use Linux distribution, you nowadays get MariaDB server even when you ask for MySQL server. So this is something you really need to note. And if you use Debian and Ubuntu, you get not only MariaDB server, but you get something known as MariaDB server with the odd socket plug-in thrown in for free, which means you don't have to run MySQL secure installation, and you don't even have to enter a root password any longer. So if you use the actual Debian and Ubuntu packages, why is that? That's because later we talk about odd socket, but odd socket allows you to authenticate via the socket, which is the fastest method to make an authentication. So if you are the root user, you get to log in. But this actually trips people up because when you log in and you type MySQL-eroot, this doesn't work because you're not supposed to only type MySQL. If you're the root user, you get to log in. So you're not going to be disabling odd socket or learning how to configure odd socket, which is yet another thing you have to learn to do because things are different. However, if you happen to install via the app repositories that Oracle provides and Protona provides, odd socket is not turned on by default, and there you will get Deb Helper to ask you to enter a password. Of course, I highly recommend you not to disable Ask Linux. So don't do that. In fact, we go through great pains to package things and make sure the Ask Linux contacts are right. So we make sure the types and the domains are accurate and they should work. So there's no reason to disable it in my opinion. The only reason people do disable it usually is because they're compiling and then things are different and then you should learn Ask Linux. They've made it so easy to learn. They've made a coloring book even so they may be able to get at the Red Hat booth. They were giving it away at some stage before. So another fairly useful thing to do is to enable log warnings when you're running MySQLD and this actually will keep track of access denied messages. And this is important because if people are trying to attack you, you'd like to know why they're attacking you and it's like a brute force. You can then think about making additional rules and it's worth noting that of course MariaDB has to be a little different in the sense that there are additional bits of information that's available like in MariaDB 10 you'll get to see that statements that were unsafe will also be logged inside the bin log so you'll get to see things like bin log flag, unsafe statement printed and so forth. So well worth taking a look at these resources they're not exactly the same. So MySQL 5.5 we consider to be now quite old MySQL 5.6 is probably a good baseline how many people are running MySQL 5.5 here? And then the rest of the MariaDB users are probably using the 5.5 base so 5.5 is in extended level support at Oracle as well and 5.6 has been around for quite some time maybe 2010ish 2012 and 5.7 has been around for a little over a year and a half and 8.0 is going to come out sometime this year so at which point 5.5 will completely be out of support and 5.6 will be kind of in extended support so anyway 5.6 may be having great improvements in the sense that you can have password expiry available so you can actually say the password will be expired after a certain period of time so one of the things that 5.6 brought was the validate password strength so you now can actually force people to provide a password that is useful like they can't enter a password that is abc12345 any longer because validate password strength is an SQL function which will take a password as an argument and will return an integer between 0 and 100 where 0 is weak and 100 is strong so abc123 can actually be rejected because they come back as like 2 or something something small so 5.6 brought improvements in the sense that you have expirations you have the ability to do it via an SQL command to validate the strength now it's worth noting that validate password strength does not work on MariaDB it only works in my SQL 5.6 in greater as a password expiry at the moment 5.6 also brought on something called the which basically encrypts the .mylogin.cnf file so that it cannot be read as clear text it's stored in your home directory and its content is decrypted by client programs when used inside of memory this is kinda smart also it provides a random root password upon install so you go to home mysqlsecret at 5.6 and you actually get a root password when you install it this is better than previously where you get no root password so this is one thing that will trip people up who use mysql5.6 however if you upgrade to mysql5.7 it will trip you up even further because there are now even further changes where 5.7 puts the password inside when you install the server it puts the password inside the log file so you have to grep the log file to find the first password so if you have scripts that install mysql automatically for people your scripts now have to be updated to go and look for this password field to get the root password to then change the root password so things are moving to become more secure but also possibly more annoying to people who have used mysql possibly for 20 years also it has improved password for one when you install mysql5.7 automatic password exploration is set this triped a lot of people up after 6 months because by default the default password lifetime is set to 180 so after 6 months you can't log in again hmm so well worth noting that this is something that is set as a default so the defaults are changing to become more secure as opposed to what they used to be you can also now configure that it's based on calendar yeah so if you are shipping your mysql log the first initial password upon install will be shipped to log stash but the good news is that password that you grep for the moment you log into the server it forces you to change it there's no other way to log into the server and that password is a one-time use password so the only risk is if you happen to you know copy it start the server paste it and then use the same password but the bonus is it got smarter that it won't allow you to use the same password now the initial versions did allow that but what GA versions don't but you can add I mean people humans can just add one to the end humans are the biggest weakness for security yeah so mysql is becoming more secure by default inefficient possibly if you're used to how the old ways were but more secure and it's only going to get better of course the other one this one I personally dislike I don't like policies that say every 90 days change your password because again humans like to just add one to the end of it two to the end of it three to the end of it but policies nowadays sometimes say that you need to do this you have maybe you know written information security policies and so forth they say you must change passwords every 90 days to satisfy someone and you can now actually say look I'd like to expire it every 90 days so you can log in on day 90 or day 95 even they'll tell you your password your password has expired please enter a new one now and again this is why I said most people probably just add a one to it two to it because it seems to be the easy thing to do good news is if you combine this with something like the audit plugin which we'll talk about later you can track passwords as well so you can track password changes to ensure they don't use the same password of course there's also this option that now you can lock and unlock accounts this could be useful if you sacked someone and you need to lock them out immediately but not get rid of the data and so forth how many of you use MySQL in the cloud how many of you replicate across data centers how many of you are using SSL replication RDS is within yes so that's okay if you're setting up replication manually so you set up a server any server or somewhere else the caveat about replication is that all this happens in clear text so anyone could read stuff so you definitely need to set up SSL and this is the only good way to actually make sure replication works setting up SSL is interesting because you can use Yazzle or Wolfer SSL as it's known nowadays they keep on changing the name from Yazzle to Cazzle to Wolfer SSL but if you use MySQL Enterprise Edition and certain versions of MariaDB server that are compiled against OpenSSL there can be differences with SSL between those versions and the Yazzle versions and they actually sometimes prevent and cause problems as well so it's worth noting that if you do run into problems this could be one of those reasons 5.7 includes a new tool because setting up SSL is actually not the easiest process it could someone laughed ok so setting up SSL for replication keeping the server 10 files and the client files this can take even a competent person like 15 minutes of time to set up so now there is the MySQL SSL RSA set up tool which will create the SSL certificate and key files the RSA key pair files secure connections for SSL secure password exchange using RSA over unencrypted connections so you don't have to use OpenSSL and fiddle with things any longer you can just use this tool and it will make your life so much easier again this is how we're going to help with automation going forward so this can help make things better ok so if you came from older MySQL users and that's how you initialize your data directory but that has been deprecated in 5.7 and it will be removed going forward so now MySQLD itself will handle initialization and by default it initializes in a secure mode and does everything I told you before but for some reason you decide I want to script it I don't want to have all these problems I want it to work like how it worked in 5.6 or 5.5 yes question you don't want your password in your logs yes but it's a one-time use password in your logs and once you log in with said password it goes away it's never there again but yes I don't know if there's a better way to do this I mean the initial way was putting it in the home directory and that seems to not work for people so that it went to the logs and maybe this will change in 8 it's really hard to say I guess it's bare with us while we make MySQL better and more secure or we can follow the MorgerDB way which is don't change it until there is a real solution but that also means that there are lots of insecure installs out there so it's chicken and egg problems and I don't know a good solution to this because every time we do something and then we write bugs and we'll complain and then we improve but I don't really know a better way to do it at the moment so this is important like initializing MySQL because otherwise the system tables don't get populated and then you obviously can't use MySQL so it's obviously worth doing and I recommend just doing MySQL to initialize initialize insecure only if you know what you're doing or why you want to be insecure I mean MySQL has an I am a dummy mode you could type dash dash I am a dummy like I am I dash M a dummy and it will actually start and be very user friendly more user friendly than it's supposed to be in fact it's probably from the man pages as well as an option for what it's worth that was MySQL now we've got to talk about MariaDB because MariaDB handles things differently for one password validation only came to MariaDB in 10-0 and the password validation is of course different from how it's handled in MySQL why is it different? because there is now a simple password check so as I said it doesn't have to validate password strength SQL function but it does have validation at least and simple password check is the one you should probably load as a default which will enforce minimum password length so abc12345 is a good minimum so I think it sets it at 8 characters it preferably would like to also have uppercase and lowercase so abcde123 capital A hash digits and punctuation characters now the difference with MariaDB and MySQL is that you can chain load plugins so again back to the I presume you're going to be using linux you can now have you can chain load not only simple password check but you can also have cracklib password check loaded and that will actually check against cracklib so you use spam cracklib and if you did enter abcde123 hash it would reject the password so this is actually a good usability improvement rejecting weak passwords but it's not it's not similar to the MySQL way but you can also reject passwords except here you now need to chain load now if you're running Windows I actually don't know how this would work frankly speaking so maybe you don't you just have a simple password check because there's no spam on Windows to be fair I think 99% of our customers run linux or BSD in production so Windows so today you create user foo localhost identified by password all authentication for what it is worth since 5.5 has been done by authentication plugins so they're not built in anymore now if you do select plugin you can actually see two plugins so I took this one out of MariaDB because MySQL old password is removed from MySQL MySQL old password is the old pre-4.1 hashing algorithm which is extremely easy to break into if you have people saying they need it for some reason and it's like web hosting companies those are the ones that should be really afraid of it's like how could you allow people to do this still till today now MySQL native password uses the SHA-1 algorithm oops just last week so just last week it's been proven that there are collisions you can create collisions but don't worry we have plans we have plans so MariaDB only has native password and old password I highly recommend you never use old password even though MariaDB still supports it it's removed in MySQL MySQL has native password and something else which we'll talk about soon but we'll get to that soon there are some differences with MariaDB and MySQL you can set usernames because now people are doing things in a more automated fashion pre-MySQL 5.7 the username limit was 16 characters MySQL 5.7 allows 32 character usernames MariaDB since 5531 so that was not quite the GA release it was GA plus many changed it so that you could go from 16 characters to 80 characters but you have to reload the grants table now you may ask why 80 why such an arbitrary number because we tried 128 but then we found we could break indexes in the user table which was not a good idea and it would actually break login so we reduced the limit arbitrarily and 80 would seem like a good limit to have so MariaDB 80 characters MySQL 5.7 Installing plugins basically in MySQL and MariaDB you can type install plugin plugin name, so name and the plugin itself in MariaDB because MariaDB loves to work with plugins storage and so forth it gets tiring to type all that so if you feel a little lazy you can type install so name and the plugin name and it will just load it so you can save maybe so out socket I promise we'll talk about this now this is of course also available inside of MySQL and Precona server but it's not loaded on by default but in MariaDB in Davion it is loaded on by default and this authenticates against the Unix socket file this is the simplest plugin out there so if you're ever learning how to write authentication plugins you can think of this as the skeleton of the CSV storage and it's very basic, very easy to use of course you need to as I said load it install it's life as Unix socket and it will refuse any other connections unless said user is allowed to log in via out socket and really this one does trip up Davion users and Ubuntu users a lot because it's turned on by default and people go why can't I log in with MySQL-eroute it's supposed to just work so since we know SHA-1 has collisions MySQL at Oracle already figured that this may be a problem a while back when I was talking about it but it was never proven a good work there is this thing called SHA-256 password available in 5.6 and 5.7 so that's in MySQL-Pretona server but not yet in MariaDB that there is an open JIRA ticket for this since last February that I created so if you go to JIRA and you look for SHA-256 it says ported but it's not ported yet so what this means is in MySQL you can now run and your SSL is built in to use default authentication from native passwords to SHA-256 and you can also use with private keys as well as public keys you can also set up an RSA key value you can also work with SSL so that it will be sent in clear text of an SSL encrypted connection or if SSL is not used but the RSA is available it will send a password unencrypted and then be RSA encrypted and if there is no SSL or RSA available the connection will actually fail so I believe that after last week this is going to be a lot more popular plugin nowadays and of course this works with all the MySQL connectors so you can use this as a Percona server and all the connectors you get from Dev.MySQL.com however if you happen to need an LGPL client library so you need to use MariaDB's network connector this will not work so there are many open JIRA tickets and this will get more important from a MariaDB standpoint as well to make this plugin available because people are going to ask for it now that the SHA-1 collisions have happened and you can vote for it on JIRA as well so go to JIRA.MariaDB.org and say it's important to you so again a plugin authentication module extremely common there are three different PAM plugins available MySQL has an enterprise PAM authentication plugin Percona has OSPAM and OSPAM Compat important so that you can work with PAM clear text and MariaDB has also a PAM plugin I guess everyone here knows that PAM is a plugin well what PAM is you can also use PAM LDAP you can use PAM to work with Google Authenticators so you can get two-factor authentication and so forth so the PAM plugin supports all these wonderful things so if you ever need a two-factor authentication to MySQL you can with the PAM plugin and all PAM plugins so what it works I don't talk about the MySQL PAM plugin because it's basically the same but you have to pay money for it so I don't like that so Percona Service Percona Service PAM allows you to obviously read from Etsy Shadow so make sure that MySQL user can read Etsy Shadow it also does support supplementary groups for PAM authentication so it does actually check against Etsy groups as well MariaDB Server has a slightly different implementation in the sense that it supports PAM service names as well now if you want to be compatible fully with MySQL you need to use the PAM use clear text plugin so because it doesn't make use of PAM so normally so when Percona makes things we always want to make sure that everything is fully compatible with what's available upstream as well we don't just say we think our implementation is better but we must be compatible so this is important most common errors we see with regards to people using PAM or logins is the connectors don't support it and that usually requires a recompilation to a live MySQL client and then the other one that's common is people don't flush privileges no matter what authentication you choose you always have to flush privileges how many people think Kerberos authentication is important anybody use Kerberos? Kerberos fairly industry standard for large client service systems user network authentication protocol using tickets to allow nodes to communicate over non-secure networks to improve identity in a secure manner so MariaDB has a Kerberos plugin available and Kerberos plugin also works on Percona server and MariaDB and MySQL so you create a user identified by Kerberos Realm and it works fairly easily but the added bonus of GSS API support this plugin is actually a GSS API support plugin is that it also has SSPI support which means that it also does work against Windows Active Directory and you could consider this the open source version of authenticating against Windows Active Directory why say the open source version because there is the enterprise version that MySQL sells for money as well MySQL has a PAM and Active Directory plugin in the open source world we have PAM, Kerberos and Active Directory so well worth noting if you are using this and actually funny thing, funny story about this one is this plugin was actually written in conjunction with a Google summer of code student who spent three months writing this so thank you Google MySQL 5.7 has one more plugin it's called MySQL node logins prevents offline connections it started live on a blog and it has a lot of accounts that maybe need to run a stored procedure or view with elevated privileges without actually exposing those privileges to ordinary users or if you have a proxy account that you never ever permit a direct login this is a good one it prevents logins so this is available in 5.7 only this plugin should be able to run on MariaDB as well if you compile it audit plugins so the SQL error login plugin which comes inside of MariaDB but also works with MySQL and Prokona server logs all client errors to a file that you can analyze later so when you have your typical WordPress application it may sometimes say error connecting to the database server but that's because it encapsulates one message is actually received from database server so you can actually have that in a log and read it in your own time and realize what was going on this only allows you to put it in a file you've got to rotate it yourself so it's a very basic audit plugin and think of this as the most basic audit plugin if you want to write better audit plugins but there is also an audit plugin because there is the MySQL audit plugin, the Prokona audit plugin and the MariaDB audit plugin so many audit plugins you can log server activity so who connects to the server what queries they run and so forth everything can be logged via the audit plugin except connections served via the query cache the query cache for what it's worth has been disabled for fairly long time across all distributions so the right size of the query cache is zero you should use something else in front of MySQL to have it so MariaDB was actually the first to extend the audit API so that you could filter via users then Prokona thought that was important but Prokona said look we can't extend the audit API because it's not an API we want to control and become incompatible with MySQL so Prokona extended it in such a way that you can also do user filtering but they've added user filtering inside the audit plugin itself now of course the audit plugins that both Prokona and MariaDB have in latest releases can also track password changes earlier releases of the audit plugin from MariaDB used to actually write the password into the audit log which is obviously not a good idea so upgrade continuously and this is actually none of these were the first audit plugins out there the first ever audit plugin came from McAfee McAfee still maintains an audit plugin for the MySQL ecosystem and there is one added feature that Prokona's audit plugin has that's quite interesting for me anyway is that you can have it output in multiple formats so you may not want it to be in the standard XML format which is what MySQL uses MySQL audit plugin uses the XML format and there are tools that allow you to use it to like that MySQL that allows you to grab audit logs that MySQL provides so you may not want standard XML you may want to be logging it to syslog or you may want to log it to JSON and ingest it somewhere else Prokona allows XML to be syslog so multiple formats we figure a good thing and then there is an audit log strategy because auditing is not free every time someone logs in you have to write the disk call fsync calling fsync is expensive writing the disk is expensive so there are methods you can choose in the Prokona one where you can choose to asynchronously write the disk there is a performance mode a semi-synchronous writes to disk maybe you want to log it somewhere else but there is another option which is call fsync calling fsync is expensive that's why we create a group comment in the binary log in the server because otherwise even the bin log would be extremely slow compared to calling not calling fsync instead rules not much to talk about because it's a SQL standard it is available inside of MariaDB since 10.0 it is written by google summer of code student so extremely useful roles coming to mysql8 so if you need to use roles today MariaDB is your only choice if you need to use roles in a few months mysql8 is also a possibly good option and it's fully sql standard encryption wow I'm between you and beer because this is the last part of this track okay so encryption let's talk about MariaDB encryption first because MariaDB server was the first server to come out of encryption then it knows mysql8 well MariaDB didn't write encryption in 10.1 the encryption code came from google so you can trust that it's fairly robust because presumed they run it in production on their MariaDB 10.0 servers they support full table space encryption MariaDB extended it to also support table level encryption uses the AS algorithm with rolling keys we recommend obviously to use table space encryption not table encryption because table space is what the google patch came with and that's also what mysql8 actually certainly has but the one added bonus of MariaDB is that you can also encrypt log files encrypt the bin log the caches and so forth so you can see that there are a few plugins to get you started so if you obviously use file key management this is the easiest way to get started but not the most secure because if I break in I'll walk away with the key but you probably want to get started with that just to see if it works also if you're just starting with file key management you need to obviously create keys using open ssl as I said these keys are obviously sitting on the table you need to actually load a lot of correct entries so if you happen to miss one of these entries encryption may break in ways that are not pleasant also it's worth noting that once data is encrypted if you've lost the keys you can't recover the data so at least we know it's secure so don't lose the keys if you are going for full encryption you actually need to specify what the page encryption key is going to be based on that table and as I said this gets a lot more complex especially if you're doing this on the with the file key management plugin it's a lot easier to use a key management solution that actually generates these for you so I recommend the key management solution which I'll talk about later of course you can rotate keys you encrypt temporary tables and so forth and how many threads you're going to dedicate to encryption is also important there's also table space that's well worth noting so that you can write over the data that you've deleted so as I mentioned earlier instead of MariaDB server those options are quite long and if you miss one you may get into trouble but what I would do is I would enable the encryption using the enable encryption preset use the preset because it basically runs in fully encrypted mode if you use the preset so just load it in your mitem cnf and then key management there are no real open source key management servers out there every key management server out there costs some amount of money in this proprietary including this ePri gateway for databases so when encryption came out a year and a half ago Vault wasn't quite there yet so Procona is actively looking at Vault to be another key management solution so you're not hearing new things that are not quite public yet but we are definitely actively looking at Vault and the other one that is open source is the Amazon key management server plugin that is included inside of MariaDB and it's also usable with Procona server and it's useful when I say MariaDB Enterprise in this example it's because when you pay MariaDB they compile a plugin for you but I'm sure everyone here can compile plugins themselves so this is not complex it's fully open source and at Procona we're also considering integrating this and obviously providing it to you in a compiled fashion so the caveat to this is of course you probably want to be running in Amazon EC2 because it does have to make a network roundtrip with a key management server and of course you need a credit card or a backup to make sure that there's a key management service available so that's open source as well now in terms of it because it encrypts the logs in MariaDB MySQL binlog has no way to decrypt said logs so you get a replication problem if replication breaks for some reason and it can break for a multitude of reasons you can't run MySQL binlog any longer because it can't decrypt encrypted files even the binlog is encrypted so this is something to note as a caveat of running with log encryption and if you use MariaDB 10.1 and greater which comes with Gallera cluster integrated so if you choose to change to write that replication the GCash is not encrypted so that will actually leak data and if you need extra backup to work with MariaDB's encryption that does not work Procona extra backup does work with encryption that ships with Procona server and MySQL but Procona extra backup does not work because the log files are encrypted with MariaDB so if you're using Gallera this can pose a problem so my advice is don't use Gallera if you're going to use encryption yet wait till all these problems are fixed in 10.2 and then you use it with Gallera so at the moment if you're going to run Gallera don't however MySQL 5.7 introduces innerDB table space encryption which encrypts the entire table space but not the logs so Procona actually the cluster out of the box is as encrypted as you can get because it doesn't have to worry about the log encryption because it's not making any additional changes so to speak you'll be as encrypted as MySQL can be and it doesn't have the MySQL bit log problem because the log is not encrypted you have to use innerDB file per table but this is not a problem because innerDB file per table has been the default since 5.7 and possibly even 5.6 you can't encrypt the general table space the system table space the under log table space in the MySQL version and of course the external key management that it refers you to use is Oracle Key Vault and that probably cost a bomb so the Procona solution is hey that's this KMS that we like and we are now working on Vault and we are also actually working on porting the encryption that you see in MariaDB which we call the Google encryption into Procona Server so Procona Server will very soon offer you two sets of encryption the MySQL way so that is coming and that should be coming I think we will probably announce it as a GA in April why April because we have this big conference that we like to talk about our achievements and other people's achievements so preventing SQL injections MySQL enterprise firewall is actually available that you have to pay for so it's an application level firewall that will allow you to deny SQL statements from matching white lists or statement patterns now if anyone tells you they can pause SQL with regular expressions they are actually lying to you you can't pause SQL with rejects you need a SQL parser and MySQL actually has the ability to do pre-checks as well as post-checks so they have managed to build a little tool around it and build the firewall into the server the other solutions that don't cost you money actually that's a lie MySQL used to be a database firewall filter that you could use for free as in it was open source but anything after version 1.4 so version 2 of MySQL is now business source license so it's not open source that means you have to pay for license fee so you can see the source code but you have to pay a license usage fee if you use it for more than 3 servers so MySQL is not quite an open source solution either in that sense so what do you use? Use proxy SQL Proxy SQL is a fully GPL proxy GPL v2 you can use it it has a database firewall filter built in you can also provide hints and that is probably the best solution now that is open source there are a whole bunch of others that are actually closed source but today I think the best solution is proxy SQL or if you happen to use an older version of MySQL that's fine as well but yeah I wouldn't use enterprise firewall either another Italian following is you got to make sure database server access is also prevented you want to of course restrict user access to the database server you also got to think about separate OS and database accounts so it's always the root user logging in you want to restrict Pseudo Pseudo is so common nowadays I can't imagine why people don't use this basic things actually one of the most basic things that I think is important is you don't open your port 3306 if you use MySQL 5.7 with the MySQL S8 you don't open port 3306 either because there is another port open for running connections via via javascript basically so MySQL 5.7 introduces the MySQL shell which allows you to write javascript like queries to make MySQL more no SQL like so if you don't want to use the MySQL client you can use the MySQL shell client and perform all these operations without SQL because maybe you like writing javascript like queries which is perfectly okay so then you have to now make sure you don't open port 3306 and port 3306 of course I don't have to tell you but run IP tables naturally and obviously look at file log off log regularly so this is more on the linux side don't open those ports and you know just also it was actually just last week so last week was not a good week for MySQL because we knew the native passwords possibly could get collision and this happened there was this ransomware going about where they asked you to make bitcoin payments and it turns out that this is easy to prevent right this is easy to prevent if you didn't open port 3306 on the internet because if you know that there is a test database and you know there is a privileged user because the person didn't run MySQL secure installation it's not hard to brute force so this is I don't know what $235 is what 0.2 bitcoin is but they were inserting messages and they were telling you make sure you at least have the data to get back so I mean we were with Robert Treat and Peter because this happened with MongoDB too right MongoDB was like ransomed so many MongoDB instances were ransomed and we believe like MongoDB users are probably here in terms of advancement MySQL users are probably here in terms of advancement because Robert was kind of worried that a postgres user is going to be next this is going to happen but we figured that the postgres users are like here in advancement so this probably shouldn't be a problem for postgres users the same thing applies is don't put your database open to the internet that just doesn't work but the postgres users tend to be smart I don't know that was just a joke this didn't happen to all MySQL servers either this is like a smaller amount compared to the MongoDB thing but this was a bad week for MySQL we found the native passwords are hosed and now this every other week if I give this talk next week there's probably some other tragedy that happens next week do you want to run a security audit there is this oak security audit written by Shlomi Noa it's an excellent tool highly recommend you to run it you can think of it like MySQL tuner for security also do you want to know how Google implemented encryption a couple years ago well two years ago Jeremy Cole and Jonas Orland the people who developed the encryption against MariaDB 10 gave an excellent talk at Percona Live and their slides are extremely advanced and dense about how they chose design decisions and why they did things and as a bonus they have a cute cat a ceiling cat that looks at you at every slide so it's also cute but this is basically the design document for encryption so if you're ever wondering how Google encryption works in MariaDB and soon Percona server this would actually be the design document to look at and for whatever new stuff that's coming out in the MySQL and open source database world we have this other conference site that we'll add and if you use the scale 15 code you get 15% off if you feel 15% is not cheap enough and you need further discount you can email me maybe I can make something happen also you can say hello to us at the expo hall we're at booth 219 I think so you should pop by and yeah I'm nearly out of time so I gave that for 58 minutes and thank you for listening and I'm open to questions oh and the slides will be at that very tiny URL at the bottom and the scale website I guess questions thoughts comments beer yes, yes yes I'm encrypting MySQL data at Google and I realized that URL died I hope not another common problem on the internet things just go missing yes it's definitely on the internet on the Pocono website Pocono's website is online yeah you're right slide share slide share.net it seems to be really small I apologize let me make it larger small even for me much better sorry these pesky slide deck things no the speaker for the 6 o'clock session had a family emergency he will not be showing up so this room is going to be dead yep take advantage and go to dinner and go to game night sorry the speaker for the 6 o'clock session had a family emergency so he won't be presenting today yeah I know I'm a speaker two people yeah the last minute we tried to get the mayor to do another talk we're trying to get in touch with them yeah so we're trying to book in as a backup they will use our backup but he'll be available yeah the 6 o'clock session has been canceled yeah alright yeah we tried to get just the mayor as a backup that just didn't work yeah how you doing Dave okay okay