Kusto Lightning Fact 7: Row Level Security (RLS)

It's been a while but I'm back with lightning facts!

So Row Level Security or RLS is a key feature of Microsoft data platform design which enables authorised users to see rows which only they are allowed to see.

To illustrate this with ADX I've fallen back onto a credit card example which you can try out for yourself.

To start with let's create a credit card table.

# create the credit card table
.create table CreditCards (Name:string, CreditCard:string, PostCode:string, Email:string)

Once the table is created we'll need to ingest the table. There are a number of ways of doing this but well focus on the UI method. In the next few posts we'll look at lightingest and update functions but for now create a file using generatedata.com which looks like this:

Download the 100 rows that it allows you to use and then ingest the data directly into ADX. Follow the docs to do directly through the web ui.

https://docs.microsoft.com/en-us/azure/data-explorer/ingest-data-one-click

Once you've completed that you should have referenced the table that we created but also created a mapping between the csv schema and the table. Double check this through the following command.

# Ingest the data through the UI
# Check the mapping
.show table CreditCards ingestion csv mappings

Let's take a look at the credit card data to make sure it's there. Run the following:

# Project out the credit card data
CreditCards | limit 10

Now that we've done that we'll need to create a function which will need a little explaining.

# Create the RLS function - show a few permutations of users and groups
.create-or-alter function CreateRLSForCreditCards() {
	let CanSeeAll = current_principal_is_member_of('aaduser={place your email here}');
	let AllData = CreditCards | where CanSeeAll = true;
	let PartialData = CreditCards  
	                  | extend CreditCard = strcat("***-", substring(CreditCard, strlen(CreditCard) - 4, strlen(CreditCard)))
	                  | project Name, Email, PostCode, CreditCard
	                  | where CanSeeAll != true;
	union AllData, PartialData
} 

So the function above CreateRLSForCreditCards uses a function called current_principal_is_member_of which can take either an aaduser= or an aad group parameter defined by aadgroup= . The result is that CanSeeAll is evaluated and if true AllData is unfiltered otherwise it returns zero rows if false. PartialData will then be used if false through the union of the two. Read https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/current-principal-ismemberoffunction?pivots=azuredataexplorer to understand the return values of the current_principal_is_member_of function which also includes a null return value.

Next we need to attach an RLS policy like so:

# Alter the table and set RLS
.alter table CreditCards policy row_level_security enable "CreateRLSForCreditCards()"

We can now check the data in the table to make sure that the RLS is applied correctly.

# re-project with the user added to the aad group
CreditCards
# double check that there is a policy bound to the table
.show table CreditCards policy row_level_security 

If everything has worked as expected we should see the following output.

RLS is a great way to secure your queryable output and is akin to both row filtering through row level security and Dynamic Data Masking in SQL Server. It's a really good way to start bringing enterprise security features into your Kusto.

Happy trails!