How to Secure MySQL Database - Percona Community MySQL Live Stream & Chat - Nov 11th

Percona Community Live Stream for MySQL will focus on securing MySQL. This is a part of our bi-weekly meetup to develop open-source database skills. Come up with your questions and get an answer straight forward to all your questions.

VIDEO

TRANSCRIPT

Mario Garcia:
Hello, everyone! Welcome to our stream this week. I am Mario Garcia, Technical Evangelist at Percona. Today we have Marcos Albe, Principal Support Engineer, who will talk about how to secure MySQL. Thanks, Marcos, for joining us today.

Marcos Albe: Yep, thank you for having me. And well, let’s go straight into it. I don’t think I need to explain what security is to everybody. But I still have one very quick slide about it. Let’s go. Coming up. Can you see my screen? Yeah. Yep. Amazing. Let’s start. Okay. Welcome to our security MySQL. I’m not in-depth meant rather, we’re gonna go through what things you need to keep in mind, and what is the smart way to approach things. So big long agenda, though, we’re not going to go through all this. So what is security, and I’m gonna give a very brief definition. So we can see why the things we’re going to discuss later on important. Security is keeping unauthorized persons from accessing sensitive data, I guess that’s, like, very well, preventing denial of service. These are, the three things I believe are the most important and what most people have in mind when discussing security. So when we look at the features and functionalities that we’re going to discuss, they’re all geared towards these goals. Right. So before talking about database security, let’s talk a bit about operating system security, because the database runs in an operating system. So, a layer of protection is in MySQL, we have sorry, in Linux, we have Secure Enterprise Linux, which provides enforcing of contexts and with that, you can prevent many type of security issues. So basic commands are getting forced to make sure it’s in place and set in force. And we have documentation on how to enable SC Linux and still have a working database because many times SC Linux is going to prevent your database from working. So don’t disable it, make sure you configure it properly. And again, don’t disable it. It’s an important bit. How did the oops sorry, I’ll delete the will give you an OS level-out DT demon. And it’s going to record events. For example, SC Linux, when SC Linux forbids something, it’s going to be logged by audit CTL. But you can also log other stuff, you can create audit rules to for example, log who is sending kill to a process or you can send you can log out audit the audit log, what, who is trying to access some file or who’s trying to delete a file. So you basically can create rules that follow certain actions on certain objects and have a log of those actions. Make sure your operating system uses two-factor authentication. I know some two-factor authentication methods can be as preferred, but it’s an additional layer of security. And security is about layers. Make sure you have IP tables properly configured active and with only the necessary ports open. Sorry for that. Limit your sudo capabilities. So don’t let your users run sudo for any command, rather, make sure they only can run the commands that they need to use their services for that, so then, make sure you have an up to date OS make sure you’re applying security patches to yours. Don’t run MySQL D as root. You know that will then allow a user from within MySQL to select five to two access files in the file system that MySQL shouldn’t be accessing. So if MySQL D runs as root, basically MySQL D is root and is capable of destroying files or reading files that can contain sensitive data. And then make sure that valid MySQL D is only accessible by the MySQL user, or whatever user, whatever Linux user, you use to run MySQL. Okay, application security, this is the most important so I’m going to talk about my SQL security. But you know, my SQL security, I will say, if you want to keep your data safe, my sequel, security is like 30%. Application Security is like 70%. So because we’re talking about data safety, I’m going to talk about application security. Most important, sanitize your inputs, I guess everybody knows this. But you know, sanitizing inputs, is the most important thing you can do for security, like there is little else. There are things, there are few things more important than this. Like, I don’t know how to say it. But this is the most crucial thing you can do. Everybody schemes some days, and that’s how they get, you know, attack it successfully. So SQL injections are still the number one security problem for all databases around. Also, when your application gets a warning, or an error, don’t ignore it, you should be capturing those logging them and later handling, you know, making sure that your application and the database are always in agreement, and that your application is always using up-to-date functions and methods within the database. So don’t ignore those warnings, don’t ignore those errors, then rely on a user with only the necessary grants don’t give all the privileges to your application users. So application users should only have the minimum set of grants they require. This depends on the application, of course. And sometimes some applications will require dangerous grants, you know, privileges, you know, I will try to create separate users for those and not like the main application user. But yeah, it’s very, you know, application dependent, but try to reduce the amount of privileges you provide to your application users. Did I mention sanitize inputs, because that is very important. And you have to keep in mind that your configuration files and your configuration tables are also inputs, and you cannot trust those blindly. So, you know, just always verify and sanitize your inputs. Even if you believe it’s your own input, someone might have spoof it that and you know, if you are not sanitizing those, then you will get attacked through that vector. Then use assertions and abort if the data if the inputs don’t comply with the expected format. You have to abort, throw an error and keep a log of these assertions, because these are possible attacks. And really, really, really sanitize your inputs. You know, there’s a very famous joke about Bobby Tables, you know, where someone called their son, Robert, quote, parentheses, drop table. And yeah, it’s little Bobby Tables. And, you know, I hope the school people learn to sanitize your database inputs, because really not sanitizing the inputs is, you know, the easiest road for someone to steal your data or to delete your data and denial of service. So this is the most crucial thing you can do for your security is not on the database. It’s on your application. So go there and start sanitizing. All right now, yeah, on to my SQL authentication. So authentication is proving that we are who we said we are. So basically login, right? This is different from access control, which is, I am Marcos and I was able to prove to the database I am Marcos. But now, do I have the privilege to read this table? So that’s the access control This is the authentication. So authentication, prove who you are. The most well known authentication is local authentication. This is the database itself is doing the authentication, it keeps the user and the passwords. And that’s all the management. I’m going to talk about MySQL eight, zero, I’m not going to go into much details about five, seven. Because it only has one year left of life. So expect everybody is upgrading to zero by now. So in MySQL eight, zero, the default authentication plugin is caching shot to password. Caching is because it keeps cache of established connections, so you can get faster reconnections and SHA two refers to the hashing algorithms. But SHA two is a whole family of algorithms. So that’s why the name. And basically, you know, if you want to make sure you said is using caching SHA to password oops, I have an extra underscore there. Sorry. This is the alternate one to use. Also,

caching SHA two password, it’s RSA basic. So even if you don’t use secure connections, even if you don’t use SSL TLS to encrypt your connections, the password itself will be still encrypted. So it’s an additional layer of security. Then, when you are authenticating to MySQL, you know, always the user have a user part. And they have host part. So user app example. Example is the host name in this case, MySQL is going to try to out verify that the host matches with the IP address to which MySQL can resolve that hostname. So that is, in a sense, an added security measure. It’s also a potential performance problem. So you know, there’s a trade off in this case about you know, being more secure and having less performance potential performance issues. This is of course, a business decision. In a secure environment, I will try to stick with the I will avoid escape name result. Apologies right throat. So, in a secure environment, I will not use the skip name resolve. And also, host names can include wildcards. So, I could name my host like example dot percent, and any host that starts with example will be valid and it will not get skipped name resolution. It will not get name resolution because you can not resolve like a partial host name. So this is also something to be careful with right like Don’t abuse wildcards because they can allow people to sneak in. Another important part of authentication is having safe passwords having a healthy password update policy. And to help with enforcing policies. MySQL introduces password expiration. Basically, we have a way to say okay, this user’s password has will never expire. This user password will expire in 90 days. Or this user password will expire in the default amount of time, which is defined by default password lifetime. And then we can decide what to do. Once we find the user with an expired password. Should we disconnect or allow it to connect to MySQL in the sandbox mode where he can go ahead and update his password? Marcos? Yep,

Mario Garcia:
I have a question where we talk about privacy and security one of the best practices and most people recommend is to change our password every certain amount of time. Would this apply to databases, good deeds, we recommend? Databases. Yeah,

Marcos Albe: This theater password expiration is precisely for that is what allows you to enforce that it’s not that the users are going to have a chance. Rather, we as the administrators are going to tell, okay, you have to change your password every 90 days or every X amount of days. Because we want people to keep refreshing their passwords. So yeah, it’s an important security measure, certainly. And, you know, because a password leak, or a brute force attacks, or, you know, someone was able to saw you typing your password or whatever, changing your password often makes it safer. And this is going to force people into doing that, whether they want to do it or not. So yep, exactly. This is for that. Thanks. Sure. Okay, continue continuation of local authentication. You know, sometimes when you are rotating passwords, and you have hundreds of application servers, rotating the password is not so easy. And there is a feature that makes this much, much easier, which is dual passwords. Basically, I can have a user that has two valid passwords. And so I can create a new password, I will do ALTER USER identified by the new password, and then I will say, retain current password. And with that, my application servers are going to be able to log in with the password they still have. And then I can go and deploy the password change on the application with a new password. And that password will also be valid at the same time. And when I am done updating all my application servers with a new password, I can go to the database again, and do discard old password. And now you know, like I was able to rotate my password with zero downtime and zero impact to the application. This is such a simple feature. But it’s so important. And it helps precisely with this with enforcing rotation of the passwords. And without making it such a pain in the neck for application owners. So you know, if you’re enforcing password expiry, please, you know, be mindful and also find a way to use dual passwords so people can more easily rotate their passwords.

Mario Garcia:
Marco, there is a question in the comments. Is it possible to automate password rotation?

Marcos Albe: Yeah, well. Yes. I mean, yeah, sure, why not? Like you can automate everything and just you know, you will have a script that generates the password, then it does the ALTER USER, you know, identified by new paths retain current password, then updates your applications. So they use the new password and then comes here and as the discard the old password, you should never be waiting until your password expires, right? Like if your password expires in 90 days. The automation should do it on day 89. Right. But yeah, it’s possible. I’m not sure where you are going to store the generated password. I guess you could have something like hashey Corp Vault, which is a secure vault and you could keep your passwords in there. Oops, my my bad apologies hit escape accidentally. So yep, you know, it’s totally possible. It’s not part of the database like that’s something you will do with your own scripts like Chef Ansible, puppet or whatever you use. I hope that answers the question and recap. And then, password validation. We not only want people to rotate the password, often. We also want them to use safe passwords. For example, passwords that don’t contain their own username, or password that don’t contain words from a dictionary file. passwords that have a minimum length, that they have uppercase, that the minimum of lower and uppercase letters, a minimum of X numbers. I can’t remember who or what policy was, I guess its strict, I can remember top of my head what policies I’m sorry. And a special char count is just that how many special characters non number or nonalphanumeric characters your password was. So again, you know, I guess you will want to combine all of them expiring password, validating passwords and dual passwords, that are functions that will allow you to verify that your generated password complies with the policies imposed by the component for the Validate password component. So yep, this is also an important bit, because, you know, dictionary attacks are well known, easy to implement, and have had great success in the past. So if you keep using dictionary words, you’re gonna keep being vulnerable to this type of tax. Okay. Finally, we were talking about local authentication, we should now talk about remote external authentication. Basically, in big enterprise, where you have your own LDAP, or your own Active Directory, people don’t want to have more users. So this is LDAP. Active Directory is kind of enterprise single sign-on for internal use. And basically Percona server has this out Pam plugin. And with the outbound plugin, you can use the PAM stands for Pluggable Authentication module. And it’s a Linux facility that allows authentication in a pluggable way, so there is not a single way rather, you combine plugins and modules to build up the authentication you need. In this simple example, I have here I am using Pam Unix, which basically authenticates against slash et Cie to the shadow file. So this is not safe for production. This is just a silly example, you know, but basically, you will have, et cetera, from MySQL D, and you will have the settings. And then you will have a user on the user when creating the user. Instead of doing identify it with caching SHA two password, you will do identify it with out Pam. And this is going to trigger the pump again, the pump plugin is going to go to insert it upon the MySQL D, and it’s going to find this file, it’s going to parse it, and it’s gonna apply these authentication methods. So we have several blog posts about how to implement it with LDAP and Active Directory, and we provide support on how to do this, it’s not the easiest thing to set up. But once you have it working, is just like any regular login, it might require an additional layer where to type in a safe password because the password input for MySQL used to be plain text. So to avoid plain text input, the output plugin also provides dialog dot S O, which is a dialog where you type in a password safely. So you could also like you could also write whatever authentication plugin you want for PAM, configured in Pam, the MySQL D, and then use it like you could implement your own if you want. So that kind of stuff. Then up now, yeah, that was authentication. That was proving we are who we are. And now we have ACL access control list, which is I am Marcos Can I do this? So my sequel access control is composite. you compose the access control by granting Link privileges on certain objects. So I have some examples here, for example, I can do grant, insert, update, delete on this database on all the objects of this database. But, you know, you could go to the detail of granting select only on certain columns of table. And that might be important if you have, like users that you want them to, you know, read from certain tables, but you don’t want them to access sensitive data that isn’t there, then you could just block provide them select only for the non sensitive columns. And as we mentioned it before, on the application side, you have to make sure that you’re only providing the bare minimum necessary for each user, you know, just security’s about that provide the bare minimum to access on an as needed basis, don’t provide full access before it’s needed. And then, well, especially some grants are normally unnecessary for applications like super file process, like 95 99% of applications will never need this, just, you know, avoid those are dangers. And then, you know, to simplify, grants management, you can create roles. And when you create a role, and then you grant the privileges you want to that role, and I forgot to show how to be in the role to the user. But basically, there is one more create command for the user or alter command for the user, where you assign the user to one or more roles. And then there is one more access control level, which is called Definer. And when you create a view, when you create a stored procedure, when you create a routine, you can set the finer. And you can tell that the security context for that view, or routine is the definer. And what this means is that you might not have access to table x, but the definer does have access for table X. And you will have access to execute the view or execute the routine. And then you will have indirect access to table X through the definer privileges. So basically, when you run when you do select from this view, and that view was defined by the privileged user, you are running with the privileges of that user for the context of that view. So this is another way to, you know, limit privileges for people, while at the same time allowing them to access somewhat privileged data in a controlled way. Right. MySQL encryption, I guess, this is what everybody has in mind when they hear MySQL security. SQL encryption is a convoluted area of the code has changed it, the SSL libraries change. And in all honesty, I’m not a security expert. So I can just say that it takes time to implement or it takes some trial and error. Because again, there are many, many guides out there. And each guide is for a different version in a different OS and for a different distribution of MySQL, you know, and so MySQL, Percona Server and MariaDB, they all have small little differences in how they link and which library they have compiled against, based on that, what TLS version you can use, and so what you might need to create different certificates and whatnot, but basically, MySQL encryption is about that. It’s about encrypting all the data to make it not accessible. So there are multiple levels of encryption, I’m not going to again, I’m not going to talk about how to implement these, I’m just going to talk about what they are and what they are important. So over the wire, this is like the most basic, and what we see most people using is basically you create certificates for your server, you create certificates for the user, those certificates should be created by using the same certificate authority. And with that, the client and the server can establish secure communications, which avoids eavesdropping, right, so avoids a man in the middle attack or absurd someone sniffing traffic. So over the way in encryption with TLS, is to avoid traffic sniffers, or, you know, man-in-the-middle attacks, then Transparent Data Encryption is the encryption for your tables. And it’s called transparent because your application will never realize that the data is being encrypted behind. Basically, when you create the table, you will say Create Table black, encrypted. And that’s all or outer table black and clipped. And that will encrypt the table. If you alter a table and encrypt, it is going to recreate the whole table. Because you know, otherwise, the data will be encrypted. So and that that will make the table the data not accessible. When you use transparent data encryption. The data is not accessible to someone outside the MySQL server. But if you are inside the MySQL server, if you have a user and that user can read from the table, then the table is not encrypted. So this is to prevent stealing data from the file system level. Apologies, I have a bad throat to date. So that’s what Intel encryption is for that is to prevent stealing data. At the file system level, like if I steal your IBD files, you know, if I steal your table spaces, I won’t be able to use those on another MySQL, because I don’t have the encryption keys. And this brings me to key management. So depending we have two plugins keyring file, or keyring both gearing file is going to keep the keys on a file on the file system. So you could keep that file on a separate volume. And later, you could add more volume and request a password to mount it again, that will make it fairly secure. Still, we don’t recommend hearing file for production deployments. What we recommend is key ring Vault, which again uses hashey Core Vault as the plugin as the key storage. And this is much safer, and the vault is always protected by password. And well, then you also have to define your key rotation. There’s a whole heap of literature about this, I’m not going to go into this again, this is a really deep topic. So I’m just going to stay on this transparent data encryption is to prevent people from stealing your files and have them be useful. Other stuff you can or you should encrypt because people could also steal those files and still have useful data is the IP data one, which is your system tablespace. Your binary logs, it’s also important to be encrypted. You should encrypt your redo logs, your right ahead logs, those also contain your data. Your undo logs does also contain your data. Your temporary files also contains your data and finally the double write buffer. This has been deprecated on later eight zero as it’s now always encrypted for tables that are encrypted and it’s always not encrypted for tables that are not encrypted. So the double write buffer is not going to be needed to be handled separately. It’s going to be handled automatically in the future. So another form of transport then encryption is at the disk level. Up, sorry, before we go into there, one note about data encryption, when you are encrypting data, and then you want to decrypt it, you know, you want to access the data, you’re using more memory, you’re keeping the encrypted data in memory, you just read from the disk. And then when you decrypt it, now you have a decrypted copy. And now you’re using more memory. So when you implement transparent data encryption, it has a price for CPU, for memory, and possibly for storage, encrypted data tends to be larger tends to have a bigger footprint than non-encrypted data. So again, you know, keep that in mind. And if your boss is asking you to implement it, make sure to mention the extra cost. And now we get to the next level, which is this level encryption. This is the operative system. I could have mentioned it before, but I thought it fit better here. And there’s something called Luke’s the Linux unique cryptographic X key system, I can’t remember this the acronym for looks. But it’s basically the de facto industry standard for disk encryption in Linux. These make things very slow. That’s all I’m gonna say like, it works. Sure. And it’s easy to implement, because, you know, you just encrypt the disk, and you don’t have to do anything else. And you know, the double write the temporary, the undo the redo, the being logs, and everything is going to be encrypted.

But this is low. And also, it’s not very safe, because while the disk is mounted, and at that point is decrypted, this is more geared towards someone stole my laptop, or someone stole my server, or someone stole my storage drives. And you know, like, I don’t want them to be able to mount those drives without having the password. So it’s secure. While the disk is shut off, once someone is able to mount the disk and has the right password to mount it, then it’s no longer secure. So while the database is running and operating, this is not providing security, this is just providing encryption. In case someone later goes ahead, and, you know, steals your drives. So I’m not sure it’s my favorite method of encryption, I will not implement this. Then column level, sadly, this does not exist, there is no such column-level encryption. So what am I mentioning here, I’m mentioning because it brings us to application-level encryption. Again, this is also could have been mentioned it at the application on the application slide. But I think it makes more sense to mention it here. Sometimes, you truly don’t want your data to be unencrypted anywhere. And the way to do that is to actually encrypt it on the application. If you truly don’t want that data to be insecure at any stage, then encrypting on the application is the right thing. So bad this is like very silly. So the code idea of how you will do it, you know, you have your data super secret content, then you’re gonna get your data key. From your key management system, you’re gonna generate a key, blah, blah, you’re gonna give that a name, to identify it and then encrypt it, you’re going to encrypt the data. So crypt, encrypt my, my data with that key. And then I’m gonna get the ciphertext, which is the piece of the key. So this is a two parts key and one has to be extorted along with the data. And so I will insert into my table, the idea of the row that’s the data I want to keep secret and this ciphertext and then when I retrieve the data, I will pull the SQL data and the key. And then I will use my key management system decrypt. And I will use the key. And then to get back the data key C. And with that I can do actually crypto dot decrypt. So first I decrypt the key with the piece, I started on the table, and then I decrypt the data with the key is decrypted. So it’s a two-step decryption. Oh, Matt young COVID. What a pleasure to have you here. What an honor. I’m glad you’re having fun. I’m glad you’re having fun. So okay, this is like the paramount of security. Like, if you are working on some super secret project, you probably want to do something like this. It doesn’t come without problems, right? Like, how do you index this? How do you actually search this? Well, no, you’re gonna have to perhaps associate your search tags with the IDs and give them elsewhere. Or you’re gonna have to figure out ways to, you know, associated records, with the potential ways you want to look for them. But certainly, you, there is no way for my sequel to decrypt the data to keep it indexed. So nope, that’s the main disadvantage of this, but it’s the most secure method. So skip symbolic links can be an important thing. Because if I put a symbolic link inside my data directory, and I, you know, my symbolic link points to slash mount slash other disk. And, you know, if someone goes and replace slash mount, slash another disk with their own content, then MySQL is going to be reading from someone else’s content, and not the one we intended. So, you know, not using symbolic links is safer, because it avoids target swapping, skip grant tables, you know, make sure you never have this on your receipt, because that’s gonna basically escape all the security Mexican provides. So it’s only using it for emergency recovery. Secure File brave, it’s telling my sequel, where is it safe to write, because, you know, if I do SELECT INTO outfile, slash etc, slash hosts, you know, I’m changing the host file, or if I do into out file, slash, etc, slash shadow, then I am overriding the unique security file. So defining a secure location for my secrets, right? It’s also important. This is a very little known one skip showed database. It’s like when you’re into the tinfoil paranoia level, you will go with not allowing people to do show databases, because, you know, that’s how phishing and well-prepared security attacks work. They get little pieces of data, you know, that you can easily recognize and that you believe, only familiar people familiar with operations, and the company will have, you know, if you have like a weird database name, and I can gain access to the database name, I can tell you, Oh, hey, can you give me the password to this database name, database name, and you will say, Okay, if he knows the name, you know, he’s probably an employee, or, you know, it helps me impersonate others or it helps me build that impersonation of someone else. And it’s an it could be important data, so skips your database. It about avoids an outdated insight of what could be sensitive data to be one attack. low load local dive and, you know, usually, load data in file only allows us to load files from the MySQL data directory. The local modifier allows us to load data from anywhere, most people will want to use, load local, but it could also mean they can try to read files for which my sequel has an authorization of reading, and that the user should not have authorization for reading. So you might want to keep that disabled as well. And I forgot to put the variable name I’m super sorry is now loading file, I can remember perhaps Martijn COVID knows, I see questions Is it a way to since I will get into the brute force part right now. So Max user connections is to avoid like it will limit the number of connections, username can have active, it’s kind of to avoid a single user hogging the server, which is a way to create a denial of service attack. And that’s one of the things we want to prevent with security. So that’s important. Mac’s connection errors. This is the most simple brute force attack prevention. You know, it’s, it’s just gonna after X amount of failure of consecutive failed connections, is going to block that host. So it’s going to block the IP address and not the user. because it assumes that someone from that IP address is trying to attack. This is a bit brutal, because it really prevents any further connection from that host. So perhaps, another user, I can’t remember if it will apply to another user, I think yes, I think it applies to the IP, and the host does block it for any user. So if user A had 100, failed attempts, and then user B comes later and tries to access from the same hose is going to be blocked.

Then we have access control plugin, and it’s more refined. And basically, you can define how many connections before we start doing something, then what we’re going to do is add a delay to each connection attempt. And this is going to make brute force attacks virtually very much impossible, because you know, brute force attacks are rely on being able to try 1000s or millions of passwords. If I add just like a one second delay, then you know, I’m making your life harder, if I have 15 seconds, then I’m going to make your life impossible. So that’s just a way to control, brute force attack. And you should keep an audit log of we’re gonna get to the audit plugin later. But basically, you have to keep a log of failed authentication attempts. Then, set password for root accounts. Alter tables, oh my god, ALTER USER, I type faster than I think obviously. And what it says ALTER TABLE it’s, it should say ALTER USER. And you know, like, make sure you set a password for root. In older MySQL versions, this was empty by default, which was a security disaster. So don’t do that. Also remove root accounts with remote access. So delete from MySQL user, what is the root and host empty because you don’t want root to connect from anywhere else or actually, I guess a better delete will be and host not equals local host instead of empty. And then you do flush privileges. Also remove anonymous accounts. And this ad accounts with an empty string on the user. You don’t want to have those. And finally, you don’t want to have the test database because it’s a well-known default database and like anything default tends to be insecure. I will correct this slide before we share with the general public apologies for the small mistakes Okay, I’ll log in so the plugin is was originally created by Maria DB then improve it by Percona. And you know, I didn’t go to produce like a full configuration detail here but it’s super simple like this will get you working out the plugin. Like just that will get your work and added plugin And then you can configure, you know, what actions get audited, what users get audited, what schemas or databases get audited. And it’s really necessary piece of security. Because when bad things happen, you want to have record of what was going on to, you know, do a post mortem analysis. At the very least, you should audit logins. And this is what Kiki was asking. Yeah, you know, like, you have to audit the logins. And it’s going to show you if it was successful or not. And also, you should audit all the data manipulation language and select for critically sensible tables. And, you know, to make these a reasonable thing, what we want is to lock to a remote syslog. Like, if it’s a crucial, like, if your security requirements, mandate auditing, you probably want to, you know, make that audit log, live in a external remote location. Because if you keep it in the same machine that has been attacking, it’s likely that the attacker has the capability to also delete the log, you know, it’s a possibility, and we don’t want to leave out of possibilities. So remote syslog is the right way to go. So yeah, audit plugin, also an important bit of security.

Mario Garcia:
Yeah, there is a comment related with the slide where you show how to set up a password for a root account?

Marcos Albe: Yeah, it’s the same. Yes, it is. The script is been deprecated at some point, and you know, some people won’t know the name of the script. And at least knowing what the script does is useful. But yeah, it is the same MySQL secure installation, if I recall correctly, as the name of the script. Yes, sir. Right, and then we did our audit. Okay. And also, why do we want to do our audit plugin, and we don’t do you know, like, the general log, or the slow query log. And the reason is, the audit plugin is a buffer that plugin. So the IO impact of the audit plugin is far less compared to the general log or slow log. That’s another big advantage of the audit plan. So also, the audit plugin supports different supports strict to the outputs, which the general log or the slow log don’t have. And with restricted it, I mean, it will output JSON or XML or comma-separated values. File. And with that, it makes it easier to have tools to process the audit logs. So that is that backups, you know, again, we said that one of the goals of security is to prevent data loss. So backups are part of that strategy. I’m not gonna say much, I’m just gonna say the bits that are important to security. One is stored them externally, in glassier, or s3, or whatever you want, somewhere where you can recover from ransomware. Ramzan always has always two parts, right? Like, have your data do you want your data back? And you can say, No, I’m gonna flip the bird to you, and just, you know, keep the data, I have a copy, you know, whatever. But the problem is, they get to keep the data. And as they keep getting the date, as they get to keep the data, they can, you know, leak the data, or sell it or whatever. So, once you’re hit by ransomware, you’re in a very bad spot. But yeah, at least if you can get your data back, it’s something it’s going to be better than not getting your data back and getting the data leak in both at the same time. Remember that backups should also be encrypted, especially if you’re going to start them externally. You need to keep the keys to grip the backup as well, but don’t keep them along the backup. This makes things Haiti and combo Would it Yes, but you know, security’s like that. And please verify your backups, you know, don’t even if the encrypted compresses, and they are huge. You have to go through 48 hours process to decompress, decrypt and test them, believe me, the 48 hours are nothing, when compared to data loss, like 48 hours of data recovery is probably not going to get you very far. Testing your backups is going to get your data back. So just make sure you automate backup verification, pretty please just, you know, do it. And then data masking, this is just a simple think, you know, when you have to provide your developers or your support company, with a sample of the data you’re using, or with a copy of the data you’re using, and your data is sensitive, you want to do data masking. But Ghana has a data masking plugin. And it simply will convert it has some functions to convert the credit card number to just the last four digits, or to convert an email address to a fake email address, you know, and that kind of rewrites are very easy to do via proxy SQL query, where you can just for the sensitive tables, if like, you could intercept the MySQL dump queries, and rewrite those, so they will apply the data masking. It’s, again, something few people need. But you know, if you need it, you know, you be aware that you there is a data masking plugin, and that you can do it via ProxySQL, which make things consistent and easier to repeat. And finally, oh, well, I had one more data disposal. This is also few people think about this. But once you drop the table, is it gone? Is it like very, very gone? Or is it still on memory? Or is it still somewhere in the disk because it was not you know, overwritten so that the scrubbing will take care of that. It’s also fair to refer conserver. Dropping the BFS cache will guarantee it’s not in the operating system cache. And you also need to think whether you need to purge from the backups. And this is super hard. Like, you know, this is something you have to policy around and like you have to come to business agreement on. If I drop it this table from here, do I want to go back and you know, get rid of the backups? How far back? How long we’re going to retain those backups that still has this table. So this is something that you need to think about there is no single simple solution. You know, everybody has different needs. And each business has different mandates regarding security. So this is purely business decision of how to deal with it. And finally, people you know hacks don’t get done by Super geniuses that can crack your 4096 bit RSA key. Rather, they are done like suggested in on the right with a wrench and people just intimidating you physically and this. There have been many cases in recent times about people with millions of dollars in crypto wallets for with Bitcoins that have been kidnapped, just to get the damn Bitcoin hashes. So this is how it works people is the ultimate barrier and is the weakest barrier. So good security training for your personnel. Good security. mindset. You know, like, it’s about the mindset is, how do I store my passwords? How I do I connect to you know, public networks when I’m outside home? It’s really painful. And it’s just a big burden. But we users at all levels in the company are the ultimate warrior, and possibly the weakest per year. And freely phishing, and spear phishing and other phishing methods are, by far, some of the most efficient tools. Hackers have, you know, so-called social engineering. It’s a real thing. It’s been done hundreds and 1000s of times. And, you know, we gotta be careful. So, there is nothing I can advise regarding this other than comply as close as you can, or comply with your security mandates. Be careful and be mindful. And we are at the top of the hour, I hope you have fun like my friend, Martijn COVID suggested and I could take one or two questions. Again, I’m not a security expert. So be gentle with me.

Mario Garcia:
Yeah, if anyone has any questions, please leave, leave a column wherever you’re watching the stream.

Marcos Albe: Alright, if none, then Oh, thank you. I also like people that participates in this. So thank you very, very much for your participation. All right, then we thought was a pleasure that you join me. And thanks for having me, Mario. And thanks, indeed. And thanks, Matt. Young COVID. Again, was such a pleasure that you still hang around with us here. Are you gonna do one? Or are we still going to do the barbecue show one day? Matt? Would you invite me to one of those? How to balance between performance and security? Convince your boss to buy bigger machines? I mean, is your boss asking for security? I’m sure. So asking for bigger machines. That’s all like, that’s all I can suggest that there is no balance, the security is a cost. And you know, it’s a sunk, it’s a sunken cost, it’s like it’s gonna be there, you’re not gonna get rid of it. And there is no magic way to make things faster. You have to take more memory, take more disk and process double the data in CPU. So and many of these things can be CPU intensive. So bigger machines are gonna buy you some performance. Also, it might need to be more linearly scalable. Like, you know, if you were running Okay, in 24 cores with 32 gigs of RAM. It’s possible that the 48 cores with 64 gigs of RAM, it’s too much, or depending on your workload, it might be that it’s not enough, it’s quite a bit workload dependent, because it depends on your data set on your active data set. And, yep, so if you do very large table scans, those are going to be super costly queries when you are encrypting. So there is really no easy way to balance and also if you do like, tons of updates, and you have to, you know, disposing the caches unencrypted version of the row in memory, then you’re also you know, inducing more work into the database. So that’s, that’s not something that can be done, let’s say like, I don’t see a way to get rid of the cost of security, security has a cost, and your boss has to pay for it. Alright, then with that, I guess we call it a wrap, folks. Thanks so much for joining us. Have a good time, everybody.

Mario Garcia:
Thanks, everyone, for joining us. And thanks, Marcos, for sharing your expertise on this topic. So we will see in two weeks.

Marcos Albe: Yeah, I think so. I’m about to travel. We have meetings. Yeah, two weeks. I will see you, of course. I’m still here. Alright. So yeah, two weeks. Yeah. Bye, everybody.

Mario Garcia:
Hi, everyone. Thank you for joining us. ∎