Bring your Data Securely to the Cloud by Implementing Column Level security, Row Level Security & Dynamic Data Masking with Azure Synapse Analytics

Azure Synapse Analytics from Microsoft is a limitless analytics service that brings together data integration, enterprise data warehousing, and big data analytics. SNP helps its customers migrate their legacy data warehouse solutions to Azure Synapse Analytics to gain the benefits of an end-to-end analytics platform that provides high availability, security, speed, scalability, cost savings, and industry-leading performance for enterprise data warehousing workloads.

A common business scenario:

As organizations scale, data grows exponentially. And with the workforce working remotely, data protection is one of the primary concerns of organizations around the world today. There are several high-level security best practices that every enterprise should adopt, to protect their data from unauthorized access. Here are our recommendations to help you prevent unauthorized data access.

The solution:

With Azure Synapse Analytics, SNP provides its customers enhanced security with column level security, row-level security & dynamic data masking.

Below is an example of a sample table data which is required to implement the column level security, row-level security & dynamic data masking for your data.

 

Revenue table:

 

Codes:

Step:1 Create users

create user [CEO] without login;

create user [US Analyst] without login;

create user [WS Analyst] without login;

 

Column Level Security

A column-level security feature in Azure Synapse simplifies the design and coding of security in applications. It ensures column-level security by restricting column access to protect sensitive data.

In this scenario, we will be working with two users. The first one is the CEO, who needs access to all company data. The second one is an Analyst based in the United States, who does not have access to the confidential Revenue column in the Revenue table.

Follow this lab, one step at a time to see how Column-level security removes access to the revenue column to US Analyst.

 

Step:2 Verify the existence of the “CEO” and “US Analyst” users in the Data Warehouse.

SELECT Name as [User1] FROM sys.sysusers WHERE name = N'CEO';

SELECT Name as [User2] FROM sys.sysusers WHERE name = N'US Analyst';

 

Step:3 Now let us enforce column-level security for the US Analyst.

The revenue table in the warehouse has information like Analyst, CampaignName, Region, State, City, RevenueTarget, and Revenue. The Revenue generated from every campaign is classified and should be hidden from US Analysts.

REVOKE SELECT ON dbo.Revenue FROM [US Analyst];

GRANT SELECT ON dbo.Revenue([Analyst], [CampaignName], [Region], [State], [City], [RevenueTarget]) TO [US Analyst];

The security feature has been enforced,  where the following query with the current user as 'US Analyst', this will result in an error. Since the US Analyst does not have access to the Revenue column the following query will succeed since we are not including the Revenue column in the query.

 

Row Level Security

Row-level Security (RLS) in Azure Synapse enables us to use group membership to control access to rows in a table. Azure Synapse applies the access restriction every time data access is attempted from any user.

In this scenario, the revenue table has two Analysts, US Analysts & WS Analysts. Each analyst has jurisdiction across a specific Region. US Analyst on the South East Region. An Analyst only sees the data for their own data from their own region. In the Revenue table, there is an Analyst column that we can use to filter data to a specific Analyst value.

SELECT DISTINCT Analyst, Region FROM dbo.Revenue order by Analyst ;

Review any existing security predicates in the database

SELECT * FROM sys.security_predicates

 

Step:1

Create a new Schema to hold the security predicate, then define the predicate function. It returns 1 (or True) when a row should be returned in the parent query.

CREATE SCHEMA Security

GO

CREATE FUNCTION Security.fn_securitypredicate(@Analyst AS sysname) 

    RETURNS TABLE 

WITH SCHEMABINDING 

AS 

    RETURN SELECT 1 AS fn_securitypredicate_result

    WHERE @Analyst = USER_NAME() OR USER_NAME() = 'CEO'

GO

 

Step:2

Now we define a security policy that adds the filter predicate to the Sale table. This will filter rows based on their login name.

CREATE SECURITY POLICY SalesFilter 

ADD FILTER PREDICATE Security.fn_securitypredicate(Analyst)

ON dbo.Revenue

WITH (STATE = ON);

Allow SELECT permissions to the Sale Table.

GRANT SELECT ON dbo.Revenue TO CEO, [US Analyst], [WS Analyst];

 

Step:3

Let us now test the filtering predicate, by selecting data from the Sale table as 'US Analyst' user.

As we can see, the query has returned rows here. Login name is US Analyst and Row-level Security is working.

 

 

Dynamic Data Masking

Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. DDM can be configured on designated database fields to hide sensitive data in the result sets of queries. With DDM the data in the database is not changed. Dynamic data masking is easy to use with existing applications since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.

In this scenario, we have identified some sensitive information in the customer table. The customer would like us to obfuscate the Credit Card and Email columns of the Customer table to Data Analysts.

Let us take the below customer table:

Confirmed no masking enabled as of now,

 

Let us make masking for Credit card & email information,

Step:1

Now let us mask the 'CreditCard' and 'Email' Column of the 'Customer' table.

ALTER TABLE dbo.Customer 

ALTER COLUMN [CreditCard] ADD MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');

GO

ALTER TABLE dbo.Customer

ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

GO

 

Now, the results show masking enabled for data:

Execute query as User ‘US Analyst’, now the data of both columns is masked,

Unmask data:

 

Conclusion:

From the above samples, SNP has shown how column level security, row level security & dynamic data masking can be implemented in different business scenarios. Contact SNP Technologies for more information.