Security of data in Querona virtual databases

Introduction

Applying strict security rules in one of the oldest known problems in IT. Nowadays it is especially important because data leaks have grown to become one of the biggest challenges for modern companies, often resulting in significant financial losses.

Data security in Querona

Querona comes with easy to use mechanism for ensuring data security in both a column-based and a row-based manner using arbitrary rules. As an example, we will show how to limit data visibility based on the role assigned to the given user. We will base on the sample setup described in one of the previous posts about using Integrated Authentication. Let’s assume we have a standard table of customers:

We want to limit the visibility of entries on a per-row basis, so the logged on user only sees customers assigned to his country.

Row-level, role-based security

Let’s begin by adding a new role. Go to Administer -> Role Management -> Add Role:

Let’s call the role “Germany-Manager”:

Let’s assign our user to the role:

And finish by clicking Create. Now go to Databases -> Spark-Northwind -> Tables & views -> ‘default’.’customers’ and click Edit. In the ‘Filter’ field, enter the following formula: is_rolemember(‘Germany-Manager’)=1 and [country] = ‘Germany’ and click Save:

Now use SQL Server Management Studio, rerun the previous query:

And voila! The table has been filtered and the user sees only German customers.

Dynamic Data Masking

Now let’s change the model a bit and assume the German Managers will see all customers, but the address column holding non-German addresses will be masked for them. Go back to table edit view and remove the table filter and click Save:

Now the user should be able to see all rows again. Select the table ‘customers’ -> Columns -> ‘address’ and click Edit:

Input the following expression into the “Data masking formula” field: case when is_rolemember(‘Germany-Manager’)=1 and [country] = ‘Germany’ then [address] else ‘*****’end and click Parse: