Securing Data in Your OLTP Database Scenario | DataOps Day

Hello everyone. I'm so happy to give this talk about how to do the data security about your databases, especially how to protect your data when you, your data in the Postgres well, or MyQ sq. So that's the basic idea of today.

Uh, I'm Trita Pan, now I'm the co-founder and the C t o. So my profession area, it's around the distributed database system, around the database on the cloud and about the open source because actually, uh, we, I maintain, uh, one of AP top lab projects that will name, uh, Shelene Sophia, AP Sophia. And today we will consider how to use this project to help do the data security stuff around our databases to help us solve the, the data issue.

Yeah. So apart from that, sometimes I'll post some of the articles around the database, around the, uh, data security or about the open source commercial stuff. Uh, if you're interested in some topics or you have some questions, you can just, uh, tag me or contact me on my Twitter or LinkedIn.

Yeah. So today, uh, the following items, we will, uh, talk about each of them. First, I will give a little introduction about the data life cycle management, uh, because you know that if you want to protect your data, you first to know that the pro precise or the life-cycle of your data.

And second one, let us know more about the common or popular technologies around the data security, and then we'll pick up some of them to consider how to do the demo show or how to use the open source project to help us do the data security, especially around the data masking dynamic data, masking around the data encryption, all the stuff. So we, I will give the complete solution, and if we have some time, I can introduce some the demo show. Uh, if we have no time, I, I mean, you can just refer to this slides and do it by yourself.

Yeah. So about the, uh, data lifecycle monument, when we talk about this topic, actually, uh, it's about the following phases. The first one, you need to connect or generate your data, right?

And second, uh, you need to know how to manage or store your data in your data source. Uh, the next one about how to share this data to other departments where you, the data to create the value to do the analytics, right? And then, then the final phase for data management is about how to delete the or archive the, yeah.

So during the whole process, we need to consider how to protect our data. For example, maybe at the first stage, you need to do the data source management. You need only to allow the trusted data source can come here or the data can come here, right?

And you know that there are some good data in, in inspector or detector, all the tools that can help you to do the data source management. And if you think that you need to give more attention here, I mean, you can just Google it to find some of the good tools. The second one, that's the, um, I mean the key point here today, because we will use some of the technologies to do that part, how to manage and store your data, right?

So basically, we will adopt the data encryption in transit and at rest such strategies to help us to manage our data. Because, you know, if you want to gather our data or you want to move your data, you need to protect your data in transit in this process, right? A second one, if all the data come here, how to protect the, so you need to do the data encryption at rest.

That's all the, the popular or common strategies to manage or store your data. And that's worries about how to use or share your data because you know that you need to use of them, you need to do the analytics or testing or, or to do the, uh, you found the value from your data. So you, we need to protect the data at this phase.

So you will use the data masking technology or technique, or do the authority cation. You need to, I mean, uh, um, pick up some privileges, the minimal privileges to give someone to allow them to visit your party of your data. And there are some, the good tools or the op, uh, open source purchase can do the S Q L or query fail wall.

That means if, if they found that there are some dangerous actions or, uh, operations, they will prevent them to protect your data. Uh, and sometimes some of the open source projects, for example, like showing Sophia, it can help you log such dangerous or the, um, and knowing operations in logging file. Therefore, the next step for you that you can do the logging audit, all the, the good, I mean the actions or operations to help you to, uh, protect your data at this space.

And that's why about, I mean, I can just introduce one useful or common technique that the t t l time to leave, because you know, your data, three sources or data, data, this, and they, they, they, they, they cannot manage them for manage your data forever, right? You need to, um, make some of the, and use and use the data to be deleted in the future. So the time to leave such a mechanism allowed you, I mean, to it can help you, uh, automatically to delete your data or ica your data or, uh, expire your data.

So you don't worry about that one day that your data sources cannot help you manage the tremendous data, all of the useless data, right? You hope all of the useless data to be deleted. Yeah.

So that's the, I mean, the popular or common techniques can help us to deal with the data security in different phases. Oh, also, uh, about this encryption, I mean, today I will give a more introduction about that part because later on we will use data encryption and data masking two techniques to do today's demo or to introduce today's solution to make you quickly set up some, the, the projects and to, uh, help you predict your data. So the first one about the data, data encryption in transit, transit, this part I suggest use the SS s l, this, I mean, it's the secure socket layer, or it's a critical to, to just, um, some of the open, uh, some of the databases or the projects.

They can, they have some of the features. You can just do the configuration and to use the features to pro to encrypt your data. Uh, one data is transferred to one place to another to protect 'em.

Uh, the second one that, so when we see the encryption, actually we will consider about how to do the data encryption when your data stored in one data source or database. We'll use some the mature data, uh, data encryption algorithm to, I mean, to convert your plain text your data into the cipher one and store all the cipher text in your database databases. So you can see here, this picture will show you the cipher text, uh, uh, encrypted by some of the projects or some tools and data masking.

It's, uh, it's a technique to, uh, scrap your data and to, uh, to make the data, uh, become another type of the style. So all the data can be used for analytics or training or testing. So the next part, we will introduce how to use all the, the basic, uh, technique to do this solution.

Uh, here, uh, when we talk about how to use this technique for our production, uh, environment, we need to consider your deployment and architecture architecture here. So that's your application. That's your database.

You can leverage the data masking or data encryption in different liable or in different part. For example, if you just have a single case, single case, right? And just the letter developers to do some, the coding changes to your application, uh, to do the data encryption, that means you do that part at the application level.

But I know, uh, many companies, they have the thousand of macroservices or applications. You can not do them one by one, right? That's tedious.

So you can consider use the proxy, the database proxy or database gateway. It can automatically help you to do the data encryption or data masking. Therefore, just let your application visit this database proxy.

The data proxy can help you automatically decrypt data and encrypt data. So that means all the data in the databases are decrypted. That's so great.

So today we will introduce this proxy level solution. Plus actually you can also consider, because some of the databases, they also provide some such good features from themselves. For example, the, uh, SQL server or Oracle, they provide some features and, um, but you know that because, uh, sometimes they will just, um, uh, encrypt your data or dere data as a whole.

So for example, in some of your cases, they just don't want to, uh, percolate. I mean, encrypt is part of the data of your, um, uh, uh, scenarios. For example, you think that you just want to automatically encrypt, decrypt your user privacy, not about the other information or other tables, but maybe for some the, the, the cloud level or disco level or databases level, they just, um, they have, they think every data it's the same.

There's no different. See. So they will encrypt the data at the, all, all of them and encrypt everything there.

So there is the no custom scenarios, there's no user defined cases. There are d they have different props and cons for each of the solutions. So, but today I will introduce the proxy one because there's no code changes and you can define your, um, your, the, the decrypt or encrypted liable.

For example, we just want to, uh, encrypt some of the columns or some of the tables or some of the databases right here, sharing Sophia. Yeah, this project, it's the one of the AP software foundation top layer project, and that's actually a distributed S Q L engine for data sharing, data scaling and data encryption, all the features. So data encryption is just one of its wonderful features here.

You can also use this project for another cases, but today we'll just use data encryption. And with this one of these client Char Sophia proxy, because you can see here, actually provides two clients for you to choose for Java application. You can choose a Australian severe G D B C, but for other, for example, the rust application or go log, or you just don't want to use this proxy at the database server, then you can consider your austral severe proxy until will prepare itself at the Postgres QL database server or mice QL database server.

So your application just with shorting Sophia Pro added the width of Postgres server or mice q l server and shortening Sophia Pro. It can help you automatically dec equip or encrypt your data here today in this case. But actually, because I already shared, uh, his data sharing feature to other, uh, conference.

So if you are interested touch features, you can just have a try. And today, uh, we will, uh, uh, it's about, uh, this one, this slide is about, because, you know, some of the, your application, uh, it's living in Kubernetes, right? So shorting Sophia also provides a lots of help charts or operators to help help you to one click deploy it and to operate it.

Yes. So it's very convenient for you. I mean, to set up.

So today, assuring Sophia, we will use this Inuring Sophia process to help us to automatically do the data encryption and data. This proxy is for database. So it understands each of your query, each no and each no.

And it can also pass your sql, rewrite your sql, and to change the readout to do the data encryption, data decryption and return the result to your application, right? So here you can see, for example, um, for example, we want to do the data masking to this column, this column named user name, uh, user telephone number, right? You cannot to tell others that, Hey, my telephone number is that all the stuff, it's the privacy.

So we want to do the data masking on this column, user telephone of this table, user, user table. And plus, we also want to do the data encryption on user js. You know, I cannot tell people that my my J right?

That's all of the, uh, sensitive information here. So this case, we want to do the data masking on Ute telephone column and do the data encryption on ER address column here, because you know that you just want your application you to tell shortening Sophia which column of which table will be decrypt or encrypted. We need to use the SS Q L to talk with your database.

And here because Charlene, Sophia, it'll work as a database server. So you also need another language, another SQL dialogue to communicate with with it. That is a distributed SQL here.

So you can use the distribute SQL to tell short and Sophia, Hey, I want to, uh, do the, uh, I want to encrypt, uh, user's address. I want to, uh, encrypt user's telephone number, all the stuff. So actually it's similar with your standard SS Q L downloads, for example, MySQL or PostgreSQL.

So for example, here, if you want just to create some, a common table where normal table, you can just create a table here. But today we want to create a table or we want to applying a encrypt J or data masking J or data masking strategy to this table. So we use the distributed SQL here, you can see here create encrypt Z for this table of here.

It's the user table, right? And we use the a e s encrypt algorithm to tell shortening pro to encrypt automatically encrypt our data and decrypt our data. So here, this demo show, you can, uh, I just did actually do the by myself.

We download this project and install it, deploy it, and you can just, um, log into with your password and the account. And here you can see we created this, uh, encrypted loop for this table, user table. And we hope that address this column and the name, this column, uh, to be encrypted, decrypt, and data musk here.

So we just run all of the distribute ssl and the next part we can see here, we, we insert some of the test data and do the test. Uh, if you just, uh, get, uh, do the query rings through the shorting of your proxy, you will see this, the data, like this image, all of the user telephone number, it's in data market here, you can see, but you can get the plain tagt of the address. However, if you log in your Postgres or database instance, you'll see here all of the address are cyber attacks, right?

That means if some, the people they do the cyber attacks and the video, your database, uh, do some generous, they don't know what's the meaning of this column because all the data here are cyber attacks. Yeah. So I mean, that's the, uh, all of the content here because I have no enough time to, um, to invite you to see all of the download show.

And if you have other questions, we can talk later. Yeah, thanks. That's all.