Guide: Row Level Security

Entitlements and Row-Level Security – Definition & Use Cases

Row-level security may be a crucial part of your optimal data security strategy as a component of an entitlements process. In this article, we will explore row-level security, entitlements, and some of their common use-cases in modern data stacks:

  1. What is Row-Level Security?
  2. What are Entitlements in Data Security?
  3. What is Entitlement Creep and How Organizations Can Manage It?
  4. Common Use Cases of Entitlements & Row-Level Security
  5. Conclusion

What Is Row-Level Security?

With the help of row-level security, you can limit data that different users and groups are accessing which can be done in several ways depending on the platform you are using:

  • By implementing policies in BI tools
  • By implementing views and functions in the data store platform
  • By implementing a security layer between the data store and data consumers 

When limitations are in place, the user accessing the data from a table or a view receives an accordingly limited result.

This is part of our complete Row-Level Security guide.

What Are Entitlements in Data Security?

Entitlements describe the resources a user is allowed to access, and the specific details differ in various business contexts. Entitlements are configured by applying access limitations to the available information. 

Frequently, entitlements are applied as filters to different information, for example, as row-level security. A few sources of entitlements include: 

  • Entitlement configuration stored within data stores, such as data warehouses
  • Entitlement configuration stored within metadata management solutions, data catalogs, and data governance solutions
  • Business domain services 
  • Central entitlement services

What Is Entitlement Creep and How Can Organizations Manage It?

An employee’s role, team, and responsibilities can shift drastically throughout employment. When these changes are handled manually, it is quite possible to neglect to remove a few or all accesses from a past role. This negligence results in entitlement creep, where employees amass authorizations in the long run. Accordingly, when an employee leaves an organization, they may still have more access than IT knows about, putting overlooked or stranded files and documents in security risks. 

To avoid these risks, it is therefore essential to continuously verify that data entitlements are accurate and up-to-date. If possible, it is best practice to set access controls so that authorization is either limited based on time or revoked automatically after specific data is no longer used.

Common Use Cases of Entitlements & Row-Level Security

Use Case #1: Regional Separation Due to Privacy Regulations

An example of a he model dataset for this use case is a retail store which operates a chain of retail outlets in six different countries (South Korea, Japan, China, Vietnam, Philippines, and Singapore). 

The following business requirements and conditions summarize the organization’s row-level security: 

  • Country-level supervisors are only allowed to access sales information for their respective country. 
  • Sub-regional-level managers are only allowed to see sales information for countries that fall under their sub-region. 
  • The business head of the APAC region has no restrictions and can see information for business tasks in every country. 

Below are some key data tables that we will consider for this example:

Table 1: ORDERS

ORDER_ID
ORDER_DATE
PURCHASING_COUNTRY_CODE
SALES_REVENUE
1
01/01/2021
CN
409
2
01/02/2021
KR
583
3
01/03/2021
JP
741
4
01/04/2021
VN
214
5
01/05/2021
PH
865
6
01/06/2021
SG
150
7
01/07/2021
CN
150
8
01/08/2021
KR
224
9
01/09/2021
JP
427
10
01/10/2021
VN
466
11
01/11/2021
PH
460
12
01/12/2021
SG
682

Table 2: ACCESS_CONTROL (used for entitlements validation)

RESTRICTION_LEVEL
FIRST_NAME
LAST_NAME
USERNAME
DATA_RESTRICTION
Region
Mendelsohn
Chan
MCHAN
Sub Region
Daphne
Chan
DCHAN
Southeast Asia
Sub Region
Lilian
Huang
LHUANG
East Asia
Country
John
Zhang
JZHANG
CN
Country
Jacob
Kim
JKIM
KR
Country
Bob
Sato
BSATO
JP
Country
Sue
Tran
STRAN
VN
Country
Mary
Santos
MSANTOS
PH
Country
Tom
Chua
TCHUA
SG

Table 3: GEO_LOCATIONS

COUNTRY_CODE
COUNTRY_NAME
SUB_REGION
REGION
CN
China
East Asia
APAC
KR
South Korea
East Asia
APAC
JP
Japan
East Asia
APAC
VN
Vietnam
Southeast Asia
APAC
PH
Philippines
Southeast Asia
APAC
SG
Singapore
Southeast Asia
APAC

Table 4: ORDERS_AGGREGATED Summary Table

CREATE OR REPLACE TABLE ORDERS_AGGREGATED 
AS SELECT *
FROM "DEMO_TS_PRESENTATION". "SALES". "ORDERS" t1 
LEFT JOIN "DEMO_TS_PRESENTATION". "SALES". "GEO_LOCATIONS" t2 
ON t1.PURCHASING_COUNTRY_CODE = t2.COUNTRY_CODE;

Creating the Secure View (syntax may vary by data platform)

CREATE OR REPLACE SECURE VIEW
"DEMO_T5 PRESENTATION". "SALES". "VW_FACT_ORDERS_AGGREGATED" 
COPY GRANTS
AS
SELECT *
FROM "DEMO_T5_PRESENTATION". "SALES". "ORDERS_AGGREGATED" t1 INNER JOIN "DEMO_TO_CONTROL","DATA_SECURITY". "ACCESS_CONTROL" t2 ON CURRENT_USER = t2.USERNAME WHERE
t2.RESTRICTION_LEVEL = 'Region'
OR (
-- Sub-Region and Country Restriction -- 
CASE t2.RESTRICTION_LEVEL
WHEN 'Sub Region' THEN t1.SUB_REGION
WHEN 'Country' THEN T1.COUNTRY_CODE END = t2.DATA_RESTRICTION
END = t2.DATA_RESTRICTION
)

Testing the Solution:

Case #1:

User: Daphne Chan

Title: Southeast Asia Sub-Region Lead

ORDER_ID
ORDER_DATE
SALES_REVENUE
SUB_REGION
COUNTRY_NAME
COUNTRY_CODE
REGION
DATA_RESTRICTION
USERNAME
4
2021-01-04
214
Southeast Asia
Vietnam
VN
APAC
Southeast Asia
DCHAN
5
2021-01-05
865
Southeast Asia
Philippines
PH
APAC
Southeast Asia
DCHAN
6
2021-01-06
150
Southeast Asia
Singapore
SG
APAC
Southeast Asia
DCHAN
10
2021-01-10
466
Southeast Asia
Vietnam
VN
APAC
Southeast Asia
DCHAN
11
2021-01-11
460
Southeast Asia
Philippines
PH
APAC
Southeast Asia
DCHAN
12
2021-01-12
682
Southeast Asia
Singapore
SG
APAC
Southeast Asia
DCHAN
16
2021-01-16
791
Southeast Asia
Vietnam
VN
APAC
Southeast Asia
DCHAN
17
2021-01-17
247
Southeast Asia
Philippines
PH
APAC
Southeast Asia
DCHAN

Case #2:

User: Mendelsohn Chan

Title: APAC Sales Director

ORDER_ID
ORDER_DATE
SALES_REVENUE
SUB_REGION
COUNTRY_NAME
COUNTRY_CODE
REGION
DATA_RESTRICTION
USERNAME
1
2021-01-01
409
East Asia
China
CN
APAC
MCHAN
2
2021-01-02
583
East Asia
South Korea
KR
APAC
MCHAN
3
2021-01-03
741
East Asia
Japan
JP
APAC
MCHAN
4
2021-01-04
214
Southeast Asia
Vietnam
VN
APAC
MCHAN
5
2021-01-05
865
Southeast Asia
Philippines
PH
APAC
MCHAN
6
2021-01-06
150
Southeast Asia
Singapore
SG
APAC
MCHAN
7
2021-01-07
150
East Asia
China
CN
APAC
MCHAN
8
2021-01-08
224
East Asia
South Korea
KR
APAC
MCHAN
9
2021-01-09
427
East Asia
Japan
JP
APAC
MCHAN

Use Case #2: Entitlement Data Integrity in Financial Firms

Financial organizations create and utilize a colossal amount of information in their everyday work processes. A few teams carry out research activities and decide on pricing for customers. Other teams share exclusive information with traders and risk managers, and they may need to publish press releases, newsletters, and internal updates to organizations worldwide. However, because employee roles are so diverse, not every user is qualified to see all of the organization’s data. To avoid mishaps, companies utilize specific entitlements for different teams, limit access based on customer ownership, and set other similar policies to avoid the over-access problem. Here are some entitlement rules that can be defined in this scenario:

  • All analysts can only access the data of their clients.
  • Team leaders can only access data of their clients or of their analysts.
  • Regional managers can access data of the teams in their regions.
  • Only a specific group can access any PII or payment information.

Such entitlement restrictions can be easily implemented with tools like row-level security and dynamic masking that Satori provides.

Conclusion

The above use cases and explanations address only a few of the many potential entitlements you can define. The initial step to improving your entitlements and security levels is to be proactive in ensuring your resources are authorized by proper permissions.

Entitlements and Row-Level Security with Satori

One of the main challenges in setting entitlements in general, and specifically with row-level security, is setting them at scale and in a simple manner. They often require data engineers to writie code (such as views and functions), and changing management also tends to be a difficult and slow process.

With Satori, configuring row-level security policies can be done across all of your data platforms in a simplified way. These policies can be applied without the need for data engineering resources and can be done directly by business users (such as data owners and data stewards).

Click here to learn more about Satori or schedule a demo.

Satori logo2 white