Establish Row Level Security (RLS)

Row Level Security (RLS) is a data protection feature that allows you to control access to rows in a database table based on the characteristics of the user executing a query. This means that users can only access data that is permitted based on their assigned security policies. RLS is particularly useful in multi-tenant environments where you need to enforce security that segregates user data.

Benefits

• Enhanced Data Security: Ensures that users can only access data for which they have permissions, significantly reducing the risk of accidental or malicious data exposure.
• Customizable Security Policies: Ensures that users can only access data for which they have permissions, significantly reducing the risk of accidental or malicious data exposure.
• Customizable Security Policies: Allows for the creation of complex and finely tuned security policies that can be tailored to specific user requirements and conditions.
• Improved Compliance Posture: Helps meet data privacy standards and compliance requirements by ensuring that data access is appropriately restricted.

In this article, we'll cover how to implement RLS within MemberJunction and provide a few working examples.

RLS Filters

In supporting RLS, MemberJunction maintains a feature known as RLS Filters that can be created and linked to one or more entities. RLS Filters are, in effect, SQL where clauses that can be used to control access to data for one or more entities.

To deploy an RLS Filter, a system administrator must first create a record in the RowLevelSecurity table. As these filters can be created independent of a specific entity, it's important to consider ways to define clauses and statements that allow for re-use when applied across different entities.

After creating the RLS filter, it must then be bound to a specific combination of Role and Entity via the EntityPermission table which controls data access. For each entity on which RLS is desired, specify the ReadRLSFilterID field.

Below are a few scenarios in which RLS may be applied:

Example 1
LastName LIKE 'A%' OR LastName LIKE 'B%' OR LastName LIKE 'C%'

This simple example covers the scenario in which specific fields are used for determining the RLS restriction.

Example 2
ID IN ( Select AccountID FROM vwAccountCompanyIntegrations aci INNER JOIN vwEmployees e ON aci.CompanyID = e.CompanyID INNER JOIN vwUsers u ON e.ID=u.EmployeeID WHERE u.Email='{{UserEmail}}' )

This more advanced example uses templating where a sub-query is used to filter records in one entity based on information from another within the sub-query. Here, we're only allowing the current user to see Account records that are linked to their Company. The {{UserEmail}} template is a simple search and replace on the Email field from the User entity.

The assumption then is whatever follows the keyword User within the double curly braces will be the field name from the Users entity. This example demonstrates that you can filter on fields within a current entity and sub-queries as well as contextualize queries using markup syntax like {{UserEmail}}.