Name: Using the Data Masking Feature in SAP HANA Cloud
Uploaded: 2024-2-14
Duration: 638 s
Description: Data masking provides an additional layer of access control that can be applied to tables and views in the SAP HANA database. This video showcases the powerful capabilities of data masking within the SAP HANA Database, demonstrating how it can be effectively leveraged.

Share feedback

<p><u><strong>Planned maintenance of Practice Systems</strong></u></p>

Planned maintenance of Practice Systems

Why create an SAP account?

Enhance your learning experience

SAP Learning

Choose your subscription

Manage your subscription

Live Sessions

Hands-on Practice

Certification

My Learning

Ready to start learning?

<div><div>Data masking provides an additional layer of access control that can be applied to tables and views in the SAP HANA database. This video showcases the powerful capabilities of data masking within the SAP HANA Database, demonstrating how it can be effectively leveraged.</div></div>

Using the Data Masking Feature in SAP HANA Cloud

In this video we're going to look at data masking in HANA Cloud. What that means is if you might have a social security number or a credit card number being stored in a HANA table, what you want is that people can't see the actual value that it gets masked or maybe part of that value gets masked to make it unidentifiable. Now, this is something that can be done automatically at the database level. The actual storage will be using the original value, but anytime anyone wants to access that value, it will be masked according to the criteria that you set. And of course, there can be super users that have the unmasked capability to see the actual raw real data values, should you want that. So from the online documentation for HANA Cloud, there's a security guide and that's where you'll find all the details about data masking. In fact, there's a section on data masking available there. It gives lots of explanation and some examples. But we're going to go through that and look at it now in this video. So in travel.txt you want to scroll down to about line 278 and there we have a nice little set of example code. So I'm going to copy that and then we'll paste it into the Database Explorer. So for our HANA Cloud instance I'm going to open up the Database Explorer. So we'll open a SQL console and paste the code in. Now notice that I'm connected as the developer user, the dev user because that's the user that created the travel schema that has the full access rights to do things on that travel schema. So the first thing we might do is just create a table. Now you can actually do data masking either at the view or at the table level. So in this case I've chosen to do it with table. It's very simple. It's the same for a view or a table. You simply add a with mask clause to your create table or create view statement. So we're going to use with mask and then give the column or column names that you want to be masked and then use which kind of mask. So you can have for the credit card number we're going to mask it out using these hash/pound sign characters. So if we actually go ahead and do that and then we'll insert some rows after. Let's run that. We've created a table and we can see the results. Now as we are the user that created this schema we actually have full data access rights which means we see the unmasked data. However we've also got the end user so let's just open up another SQL console. We'll connect as the end user and now what we can do is the end user we can maybe try doing a select from travel.customer and this time we see that the data is masked so our main user doesn't get to see the actual credit card values. So that's a very very simple example. Something a little bit more complex might be to add a mask with a detailed kind of formula. So in this case what we're going to do is we're going to alter our table. We're going to add a new mask for the social security number. This time we're going to do something more complex. We're basically going to mask some of it but not all. We're going to leave the last two characters visible. So what we can do is this alter table and go ahead and now if we look at it as our end user we see that the social security number is masked out except for the last two characters. So that's a very simple example of using some kind of formula but it's also possible to define HANA functions and use those for masking. So in this case we can see we create a function, we'll call it mask for the credit card, what we'll do in SQL script is we'll put in a more complex function You know it could be many lines really could be whatever you want. So in this case what we're going to do is we're going to take the first two characters mask all of them up until the last three characters. So we're going to keep the first two and the last three visible but mask all of the others. So let's go ahead and create the function and then of course we'll need to alter the mask that we've created. So you can as well as doing add mask you can do alter mask for the credit card and we're going to say use our function instead of what we previously defined. So let's go ahead and create the function and alter our mask. And now as the end user we see that for the credit card we get the first two characters and the last three but we don't get any of them in the middle. Of course it's up to you completely how you want to define that mask function. You can define whatever character you want to be used as the mask. It's completely up to you. Now something that's quite useful with masking is to actually get an understanding of all the different masks that exist. What you can do, you can use something called the effective mask expressions system view. So if you do a select on that you'll need to be a suitably authorized user and then specify for example your schema and your table or view name, specify the columns that are masked and also specify the user that you are simulating. You can actually kind of see how it will look for that user without being the user. So If we run that, we can see that we've got all sorts of information pulled back. And what's quite important is it tells you for that user, this is the mask expression and yes, it is being applied for that user. If I was to change that and put my developer user and we run it again, because we actually have the rights to see the unmasked data, you can see that it says not applied. Now should you in some case want to actually provide unmasked access to a given user then you can do that using the grant statement. Grant unmasked on scheme or table or view to that username. So if we grant that let's just double check as our end user. Okay it's masked data. Now let's grant unmasked to the user and now we'll run the query again as end user. You can see now the end user gets the raw data values shown. So obviously use that very very carefully but it's an option you have available to use as and when you should need it. Now if you want to see more examples and more detail of what you can do with masking then do look in the security guide at the reference for it here. Now there's another aspect to data masking we've just been using that we've been showing that using SQL but it might be that you're creating for example account view doing that in web IDE and you want to do data masking directly as part of that calculation view not a regular classical SQL view or in a table definition. It's actually quite straightforward, but you do that using the CalcView editor. So for that, we'd be using Web IDE full stack. We've seen in previous videos how we can set that up. So I might just create a new example project from template, and I'm going to go to the Cloud Foundry environment. I'm going to create a HANA database application. We can call it travel, hit next. Notice that the database version is set to HANA Cloud, hit next, and finish. And what we can do is we can create some files in our application and you'll find the code for those in GitHub. So we're going to take the definition of our table and we're going to create a new entry. We'll call it, I'm going to create a table, Database Artifact. We'll call it customer and the type will be HDB table. Now we can paste in and save it and then we need to go ahead and build that to turn this design time definition to a runtime definition. So let's go ahead and build. So the build is now completed. So we actually open up the HTI container in Database Explorer. We can go to the tables, see that the customer table exists. Now let's just add some rows using a bit of SQL. that in GitHub. So let's paste and run that. So if we look at the raw table we can see we get the raw figures out because we didn't define a mask when we actually did the table creation. But what we want to do is to do that in a calc view. So we can now go back that we've built it and we can create a new calc view in the source folder. So a new and calculation view. Give it a name and we can create. Now I'll need to set the aggregation so we'll actually pull in the underscored the underlying table. So we'll find our table and select it, hit finish and of course we'll need to select which columns we're going to have well we can go with all of them. And then in the semantics node that's where we can specify data masking. So let's just make that a little bit bigger. We can see we've got the option to do data masking here. So for example for the credit card column. We'll select the row and then we'll choose the data masking icon. Now there are a number of ways that you can define your masking. You can define fairly complex functions but what I'm going to do is just take a little bit of script that I've got already made. So for the credit card we'll take just the first part. So basically you're going to use the concat function. It's a little bit different to the syntax we used in the HANA function because it's a calc view. We're basically going to keep the last three visible but not the rest. So let's go ahead with that and we're going to do something similar. Notice that data masking is now shown for credit card. So for the social security number we're also going to define something. So let's go ahead and choose the data masking and this time we'll take the snippet that I've got. What we're going to do here is just keep the right hand two characters and mask out all the rest. So again we can see data masking is defined for both of these columns. So we need to save it and of course to turn it from design time to runtime we would go ahead and build it. So the build is now finished so if we go back to our database explorer and we can look at the column views and here we've got our account view for customers and if we open data on that, now we're using a runtime user on that HDI container, what we should see is if we put for example the credit card number in, maybe the number of nights. We're seeing masked values and again if we put the Social Security number in as well again we can see that the both the credit card number and the Social Security number are masked according to what we set up in the calc view. So anyone using this calc view is going to see the masked values unless they've got that unmasked privilege assigned explicitly which is not the default. So in this video we've seen how we can do data masking. We can define it in a calc view as we've just seen using the data masking in the UI for the semantics and also we can define it in SQL on a table or view as we saw earlier on. So that's data masking.