 Today I have the pleasure to present to co-present with Rene so you have seen all his work today and For the people that was here yesterday on Proxiesquiel So he's the founder the main developer and the one like if you so call in slides that spend all his night Developing it's him. So you can say thank you for for all his work Yeah, and I am the one that makes him up during the night to fix stuff, but so it's a shared Work First I want to ask you guys who knows what that I'm asking is Not much okay, so from the guy who knows it who needs that I'm asking Some of them yeah, okay, so First let's say that the this is the most fun part of the of the presentation the most interesting one So everything I say here is just you know Yeah, it's for example you can test it don't try to go to prediction with what I said and so but Fred says it's like that Yeah, this is for you need to test if you want that Be sure of it. This is just let's say I want to show you something about Proxiesquiel Everything that guy says it's written in stone So you have to to follow him and this is so this safe harbor is only for me not for him So let's start by who we are so this is Rene. I will let him introduce himself Hi Rene Okay, so you can follow me on Twitter and he's the Proxiesquiel founder, right? And you also work at Dropbox So this is me you can follow me on Twitter for people who likes Twitter. Sorry and So I'm managing my skill for a long time and then developed the believer But nothing to do with that today. So no worries if you don't like DevOps So first let's talk about what is Proxiesquiel So this is the mass scale data Stargate That's it. Yeah, this is all you need to know if you need to know a bit more of details Yeah, so basically I mean was I already introduced in the previous session so for who was not here The idea is that a proxy has to be a middle layer between the application and the database server So my vision is really it's a gate for that communicators those two layers And the wirestay in the middle between those two layers is able to do a lot of things like query routing Processing of the query like blocking them or forwarding somewhere else rewriting them caching and of course the session This session is about that I'm asking. So again, this is one of the options that you can do having a middle layer in the middle Thank you, Rune. So yes this session is not about everything in Proxiesquiel. You saw the previous session It's more about like the queries rewriting that you're gonna do So why do would like to use Proxiesquiel as that I'm asking solution because it's open source and free like in beer So who knows other solution for my SQL to do that. I'm asking nobody Zveta Yeah, you can find some on Google like green SQL for example, but this is quite costly, right? They are they are quite expensive. So it depends for what you need, but let's first talk about that. I'm asking I didn't Think about making a slide on that but the What do we want to? Let's say to resolve with this data masking What's the problem the problem is that sometime you have developers and you have production data and Developer what needs a copy or just they need to access in read only the data the production data But maybe your data is sensitive you have sensitive information there that you don't want that any developers in your In your let's say enterprise have access Maybe you have students that works there or whatever and so you say, okay? Maybe we have data that we need to mask So the data should be there to performance to see okay need to retrieve you need to sort or whatever But it should be masked. You should not see it, right? So this is just about that I Will talk about that also later, but currently there is no There is none very good solution on data masking right now. So this is one option that we did So the other solution are very expensive or they are just not working or not working properly and for the price Sometimes it's not that good This one is not worse than the other solutions But because they are none are perfect So if you have seen a bit of a Joro if you want to discuss about Joro, he has a talk previously Is there a security expert in my SQL? Yeah, it's always possible to access or to to deal with security. So watch out So the best solution if you really wants to have a data masking It would be to integrate this inside of the server For example just after the end layer API So when the you get the storage engine gets the data that you modify the data there before exposing it to to the client, but yeah, this is I think this is a lot of work and Currently it's not in our roadmap to do so maybe in the future So what is the concept of data masking using? Proxy SQL So what we're gonna do is that we're gonna use regular expression. I'm very sorry about that because I will show you the regular expression after We are very let's say we are old in the 80 world. So we are using pearl regular expression. So Sorry for people who hates that But you will see they are very easy So what we want to do it's we get the data and before we send the data to the client We want to modify it. So we want to say, oh, we have some rules and for example an easy one We don't want that the developer or this user cannot see the credit card numbers Makes sense that you don't want to Share all your credit card numbers or something like that Yes Yeah, this is just No, my example is not It's just with critical numbers. So I know you're not allowed like I said, there is a you remember what I said It's just for example and the numbers you will see are not real. So don't try them So what I mean any data you want emails or whatever you don't you if you have some data that you don't want to Share with the developer you need to hide it. So in this case when the developer will do a select we need to find Oh, this the column we need to hide and we need to hide it and then return it So this is what we're gonna do in fact So first how we do that. So in Proxy SQL, we need to create a user So it has so managing Proxy SQL. Maybe it was not obvious Or it was too fast or you were not in the previous sessions about Proxy SQL But the admins admin interface is just SQL Almost standard SQL with some keywords just specific for Proxy SQL, of course, but it's very easy So if you want to create a user, you're gonna do insert into MySQL user and give a User and a password. So in this case we create a user name called devil First thing we need to do like I told you earlier. We want to hide this column, right? the non credit card column we want to hide it and so So what you're gonna do is to check for this column, but people can do select star, right? So we want to avoid that too. So if the developer do select star, we need to avoid Select star you cannot do it on this table, right? So we need to create some rules to to stop this and all variant of select star So if the table is a part of many tables We need to do the same. So hide this name for every table. This is how it works But this is also a constraint currently so it will Let's say you want to hide an email in I don't know in user table, but you want to hide an email same column name in another table Here in this case it won't work. They will both be hidden. This is one of the limitation we have So the second rule we need to mask the field, right? So when the field is selected, we will need just to replace In my example what I will do is I will show the column and I will just show the first two Characters or numbers or whatever you want just to and then put X's Right to to mask but at least why I played the first two characters. It's in case you want to sort In your example in your query or whatever. So you can do it You want also to keep the column name because if you do okay select Email and then you do a lot of changes there You want that in the result you still have email So because what we do we rewrite a query. So we will send a query to my SQL and the query we sent it's About changing already when we send it to my SQL. So we need but we need to return the the name So an example this number will become like this So if we want to mask This CC num from the table customer. We will need seven rules So this is the rule number one So as you can see Yeah, you see a very nice mouse pointer So as you can see Yeah, the first one. It's quite easy to understand. We are looking for this CC num, right? And if you have it, we will replace it So this is not Complicated but we need to find because people and I will discuss that after They put some quotes. They put some Parentheses they will do everything it's possible to get that information So we need to to try to to focus on all that Then it becomes a bit more complicated so Yeah, like I told you I like Pearl So this is also we will try to find characters to find our What we are looking for and then we will make a concat in fact So what we're gonna send to my SQL is just okay We do not tell it give me the CC num just give me the two first Characters of CC num plus concatenating with with the x we want okay, so we don't send the Full request to my scale. So this is a rewrite of the query in fact Yes Okay, so Basically as this is a completely rewrite of the query one of the important thing to notice if you're familiar with regular expression Of course this you have those that are nothing more than reference to the matching pattern So this refers to this one this one to the other one and so on so you can and I think this is not possible with My SQL right plug in doing back reference and you cannot do that also with Regular expression you need the power regular expression because we have also case less and global here So we want to do it every time it happened. So this is So if you are you familiar with Proxy SQL, maybe you have never seen this and this is because I ask Renee to To do that and it will be released soon, right? So you don't yeah, you can compile the code, but there is no packages with this information yet So then we have extra rules again, but I won't detail them all as you can see There it's fun So also what we're gonna do here is that if somebody do a select everything from the customer. We're just gonna send to the To the user sending the query that is not allowed To get that information you can put everything you want there, of course But so this is the message every time somebody will go the developer that user will do a select star for example In this case we receive. Okay, you are not allowed to do that So we return an error Again same information because we need to take care of several different cases that I will show So what are the limitation the limitation is this this is supported only with Proxy SQL Big or equal to 1.4 Not the previous version right So all field in the same name of the same name This is what I was saying before will be masked even if you say okay This is for this table. We don't check about table in this case because maybe you can join several tables and it will be Very complicated if you find a very nice regular expression to do it Feel free to send it to me and we'll be very very happy But currently so all the fields with this name will be masked and However is the name of the table So of course regular expression are not let's say always safe and Maybe the seven rules I give you here are not enough. Maybe somebody will come with a fancy solution I hope so so we can improve it, but this is already what we did until now so Yeah, if you want to create this all these rules is quite painful or I think it's painful and need to remember them So I made a just small batch script that You call it like that so you give the column name you want and if there is a table that you want to avoid select star from it You just do it like that it creates the rules for you so you can add as many rules So how does it looks like? Yeah, so we Select star from I will show you are after on a life if we have time But select star from customer. It was easy what we're gonna have here. We're gonna have okay. You are not allowed to do that, right? Select first name last name ccm from customer also easy what we will have we will have the first name the last name and the ccm value that will be You call it masked, right? Then more difficult and then here I need to thank East Thomas here again is a way he was he was here so I Made some blog post and I got some contribution or by emails or by comment to say yeah But if you do this query what will happen? So this query is in two lines, right? My first rules of course it failed completely and You had the content so now this is also by the query you have seen we take care of that So if the query is in multiple lines, it will work if you make this ccm twice in the same this is Really okay in my school, right? You can do that my the first rules we did without having this global So when Proxy SQL was only doing the regular expression and not the per regular expression This one was even but not the other one. So now we take care of that Now yeah, there is a space here, so I forgot but when we put Some with the application there no or was it on it doesn't see yeah You don't see it because there are quotes in fact and this they are removed by the the syntax It's highlighter, but here you see it's a bit of different fun So you have quotes the back quotes so back quotes single quotes double quotes All are all that is is taking care And yeah, this one also in two lines. It takes care of it So this one is on two lines. This one is just because it's too long. It is when you have Some function before the field it works, too Again extra example that we that the rule so all these with the rule I sent you It will work. I mean you won't have the data. It will be a mask. So Yeah, when you give us T1 for example and here you you add an aliases This is taken in a consideration if you use CC num and then you give an alias of it You want to see threat and not CC num or not CC num a concat CC num or whatever. So it will work Same when there is not the has Yeah, here again, you have the quotes that you don't see but there are some quotes on the on the slides It takes care of it Again here this one has quotes. It takes care of it and here with the quotes, too That's worked too. So all these case are Taken in consideration by the seven rules you have seen extra so Yeah, this is taking consideration when you put comments everything it will work. You won't see the data This also you will take care of it. So it won't work. This one will be blocked This one will be blocked. So all these cases are handled by the proxy SQL with the rules So it's quite already. Let's say it goes far. They've been not enough for everybody. I don't know And this is what we would like to We need you for feedback So do you have some ideas of queries that won't work? Yeah, but your prepared statement will be already Yeah, I need to check I need to test to see what we can do If you have a if you find this fancy ID Send it send it to us, please and we're gonna test it. I mean this the idea of that I'm asking is also to To not make exactly strict rules of then the queries that need to be executed because another approach is just to block any Sort of query with exception of whatever is what listed But of course it's become more complicated Yes, if you can send us an email we will try it Yes, but there is not there Yeah, we started the slides. We're saying Making this in the database server in the just after the end layer API for example will be the best But this is not the case right now. So currently we don't have Yeah, but you need to write that information So you need to to write that and see so interfere with was the the storage engine sends you and then verify Oh this match what we don't want to see and But this is quite also a lot of work to add it there for and so yeah Yeah, it sounds easier for sure. Yeah, this sounds easier It's not easier. This one is just you have it You can do it if you need that now today you can do it like that and first maybe make some tests and parse all the queries your user are doing and just allow this one block the other one and mask the one that you know and But you have it now if you have to wait for my SQL to implement it. I cannot give you Yeah, it will be next release So this is the difference here. You have it is doesn't cost you anything But maybe it's not perfect Yes, TV. So the question is what is the column name and the table name is the same Yeah, didn't write That you are it's a more crazy than me. So I will test it and I will see But yeah, I will test it or you can test it This is this is Proxy SQL Tickle on github compile it and test it if if you need it other questions Yes Yeah, right the responses So yeah, the question here is why do we don't rewrite the response we get from my school instead of Writing the query we send to my school and I will let Rene explain you Actually, this was a request I had I think more than one years ago and To be able to do this the proxy need to understand the query and currently it does not understand the query because there is no parsing So it doesn't understand what the query represent and what exactly the query is asking So it doesn't understand what is a columns put is a table and so on Having if the proxy will understand this then what we'll do is that once the server replies It will take the results that make the modification and send to the client. It will be way greater overhead anyway Because right now whatever the server is responding is because it's already processed There is responding the proxy just storing temporary and then sending row by row or in batches depending from from the protocol to the client So there is no extra processing doing extra processing will be way more costly. Yes Yes, but you can have alias so you can run a select CC in CC num as whatever blah and So the proxy won't know Here we process it once when we send the data, right? So we do it once. Let's say you do a select and you have thousand records or You know Thousand hundred hundred thousand records stuff like that you will need for each rows modified And this is too much over it for Proxy SQL in this way. The data never leave the database server. I Don't know if they makes any difference when it comes to security constraint, but the data will never leave the database server Let's see the plan. Oh, well, you have so the question was what if the same proxies use also for a Protection traffic and not just for development So the first thing can is going to go back So here you see you have the username. So all this transformation apply only to that user That might be the user used by the developers Yes, this Yes, I mean the question was about how to try to upload the main proxy in case there is too much Processing here and of course you can always Create multiple layer of proxy. So one proxy is sending traffic to the other and you can for example decide do the processing In just one of the proxy while the other one is just forwarding traffic and not be affected by the times required for the processing By the way, the proxy also collect metric on how much time she spent processing those rules So it is easy to identify if processing those rules is a time consuming operation or not so thank you very much and Let's call the next speaker. Thank you, Rene