As relational databases became the standard for storing tabular data, the need to put constraints on how different users may access the data began to emerge.
Row-level security is a term that exists for years and was usually accomplished by applying a filter, returning rows from a table based on a certain filter (which usually involves the user’s identity). In some databases, such as SQL Server 2016 and above, or Snowflake, it is established as a capability within the database itself.
In this article, you will learn:
What Is Row-Level Security?
Row-Level Security, or RLS, is a security mechanism that limits the records in a SQL Server table based on the current user’s authorization context. This type of security means that the data from the tables are presented based on the user’s identity and the records to which the user has access. This option provides certain users access to their data solely, with no authority to view the data of other users.
Instead of handling limits at the application level, Row-Level Security restricts users at the database level. The database applies this access control to the table whenever a query runs, regardless of which application tier gets used. This feature enables a more advanced security system to handle the limits without external circumstances.
Platform Specific Examples of Row-Level Security
Row-level security can be applied at the database level by native capabilities. As an example, you can refer to our guides for Snowflake row-level security, MS SQL row-level security, or Postgres row-level security.
Even in databases that do not have native support, you may craft your own RLS by using abstraction layers (such as views and functions). For example, you can refer to our guide for Amazon Redshift row-level security.
It is important to note that with Satori, you can apply row-level security on any data platform, as access control policies are decoupled from the native data store’s capabilities.
Advantages of Row-Level Security
Row Level Security enables programmatic control over whether or not a user can access or edit a row of data. Switching these constraints to the database level has several significant benefits:
- RLS applies to database access outside of applications, such as when a user uses SQL Server Management Studio or BI tools to access the database.
- RLS access coding is centralized in one place. In most circumstances, applications coded to expect access limitations should only require minor adjustments in reaction to future access rule changes.
- Compared to a centralized solution with significantly less program code, the likelihood of security gaps in access rules due to defects in several complicated end-user apps is higher.
Disadvantages of Row-Level Security
Row-level security is a development of user-level security. The visibility of distinct database rows or records is determined by the level of access granted to specific users or user groups. It does, however, have a few drawbacks:
- Trying to effectively manage row-level security for all of your users and user groups as the size of your database will become an increasingly time-consuming and error-prone activity.
- Organizations should ideally employ several data security approaches, giving them a “Plan B,” “Plan C,” and so on. This feature provides a plan to fall back on if the original safeguards get breached. Regrettably, row-level security does not offer this level of protection. Therefore, row-level security should be part of a layered approach.
- Row-level security is especially troubling since it may have only one point of failure: the data warehouse administrator’s account, which has access to all of the database’s critical data. There would be no recourse if an attacker gained access to the administrator’s account — the attacker could see and extract the entire data warehouse before you could take any steps to prevent or stop it.
Examples of Row-Level Security
To give a better understanding of when row-level security can be a good solution, let’s discuss some use-cases of what challenges it can solve.
Example 1: Data Localization
In the first example, ACME Industries, a global company, has data analysts from different regions. To meet compliance requirements, the requirement is to enforce limitations on data access, so that analysts from different regions can only access details of customers from their own region.
For this to happen, regardless of the data store technology, what’s needed is a row-level security policy, that adds filtering according to a mapping between the users’ identity groups (or other attributes), and the data in the tables. For example, the policy can add a filter of “WHERE region IN (‘eu’, ‘uk’)” when an analyst is allowed to view customer details from Europe & UK.
Example 2: Event Types
In the second example, data for different system events is kept in a table, with a semi-structured report containing the technical details of the event. In certain cases, the technical details may contain sensitive operational data. A row-level security policy can be placed to add filtering on the event_type column, according to the user’s identity.
Implementation Example: Postgres
The following implementation example is taken from our PostgreSQL RLS guide:
Allowing managers to edit their own rows
Here is an example showing how to create a policy that ensures only members of the “manager” role can access only the rows for a specific account. The code in this and the following example was shared in the PostgreSQL documentation.
The USING clause implicitly adds a WITH CHECK clause, ensuring that members of the manager role cannot perform SELECT, DELETE, or UPDATE operations on rows that belong to other managers, and cannot INSERT new rows belonging to another manager.
Alternatively, instead of providing a specific user role, you can use PUBLIC, which has the same effect, but applies the restrictions to all users of the database.
Row-Level Security vs. Column-Level Security
Data access is restricted at the row level with Row-Level Security, whereas data access gets restricted at the column level with Column-Level Security. For example, because Boston is a value (row) in the City Dimension, only salespeople in Boston can view sales in this city. This is row-level security.
Only members of the Marketing Team, on the other hand, have access to the discount value (one of the columns). Discount is a column; hence this is an example of Column-Level Security. To learn more about column-level security, read our guide to dynamic data masking.
Summary
Row-Level security may appear to be a difficult and intimidating task, but it is necessary.
RLS is an important part of a company’s data security strategy. You may ensure that each user has proper data visibility by adopting RLS. Without RLS, end users may have access to data they should not, such as payroll or sales data, generating unforeseen problems for your company.
Companies must keep up with changes within their own company and efficiently comply with new standards and regulations, given the current needs in information privacy and security.
With Satori, you can apply row-level security on any data platform, as access control policies are decoupled from the native data store’s capabilities.
