Entitlements and Row-Level Security – Definition & Use Cases - Satori

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:

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.

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 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	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 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 write 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).