Row Level Security

In today’s data-driven world, protecting sensitive information is of utmost importance. Databases hold a lot of data. And it’s important to make sure only the right people can access certain parts of it. This is where Row Level Security (RLS) comes into play.

Row Level Security is a powerful feature that allows database administrators to control access to individual rows. The control depends on user roles, permissions, or other criteria. This article will cover the basics of Row Level Security. We’ll look at how this works in various databases and use examples to show its usage.

What is Row Level Security?

Row Level Security limits access to certain rows in a database based on conditions you set. It gives precise control over who can see or change data, ensuring users only interact with authorized rows. This is particularly important in scenarios where a table contains sensitive information, such as personal data, financial records, or confidential business details.

Row Level Security focuses on controlling access at the level of individual rows, rather than the whole table. You don’t control access to the whole table. RLS allows you to specify which rows a user can view based on factors like their role or department.

Implementing RLS

Different database systems offer various mechanisms to implement Row Level Security. Let’s see how Row Level Security works in PostgreSQL and SQL Server.

PostgreSQL

PostgreSQL provides a feature called Row Level Security. It allows you to define policies to control access to individual rows. To enable RLS for a table, you need to execute the following SQL statement:

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Once Row Level Security is on, you can make rules that decide when a user can see certain rows. For example, let’s say we have a table called employees with columns id, name, department, and salary. We want to ensure that employees can only view their own salary information. Here’s how we can create a policy to achieve this:

CREATE POLICY employee_salary_policy ON employees
FOR SELECT
TO PUBLIC
USING (current_user = name);

In this example, we’re making a policy called employee_salary_policy for the employees table. It allows users to select rows only if the current_user matches the value in the name column. This ensures that employees can only view their own salary information.

SQL Server

SQL Server implements Row Level Security through the use of security predicates. Security predicates are T-SQL expressions that define the conditions under which a user can access specific rows. To set up Row Level Security in SQL Server, you make a security policy and link it to a table.

Here’s an example of creating a security policy for the employees table:

CREATE SECURITY POLICY employee_policy
ADD FILTER PREDICATE dbo.fn_securitypredicate(department) ON dbo.employees
WITH (STATE = ON);

In this example, we’re making a security policy called employee_policy. We’re using a function called fn_securitypredicate to add a filter rule. This function checks the department column and decides if the user can access the row, giving either a true or false answer.

The fn_securitypredicate function can be as follows:

CREATE FUNCTION fn_securitypredicate(@department varchar(100))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @department = USER_NAME() OR USER_NAME() = 'admin';

This function lets users see rows only if their username matches the department column or if they’re an ‘admin’.

Detailed Example for PostgreSQL

Test table

Let’s consider an example to understand how Row Level Security works in practice. Suppose we have a table called orders with the following structure:

CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
product VARCHAR(100),
quantity INT,
price DECIMAL(10, 2)
);

Security Policy Setup

We want to ensure that customers can only view their own orders. Here’s how we can implement Row Level Security in PostgreSQL:

1. Enable Row Level Security for the orders table:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

2. Create a policy that restricts access based on the customer_id column:

CREATE POLICY customer_orders_policy ON orders
FOR SELECT
TO PUBLIC
USING (customer_id = current_user_id());

In this example, the current_user_id() function returns the ID of the currently logged-in customer.

Access

Now, when a customer queries the orders table, they will only see the rows where the customer_id matches their own ID. For example:

SELECT * FROM orders;

Result for Customer 1:

id | customer_id | product  | quantity | price
---+-------------+----------+----------+-------
1  | 1           | Product A| 10       | 100.00
3  | 1           | Product C| 5        | 250.00

Result for Customer 2:

id | customer_id | product  | quantity | price
---+-------------+----------+----------+-------
2  | 2           | Product B| 20       | 200.00
4  | 2           | Product D| 15       | 300.00

As you can see, each customer can only view their own orders, ensuring data privacy and security.

Beyond the example

In addition to PostgreSQL and SQL Server, several other database systems offer RLS features. Here are a few notable examples:

Oracle Database

Oracle Database supports RLS through the Virtual Private Database (VPD) feature.
VPD allows you to create security policies that enforce row-level access control based on user-defined conditions.
Policies are defined using PL/SQL functions and are transparently applied to SQL queries accessing the protected tables.

MySQL

MySQL provides RLS through the use of views and the DEFINER clause.
You can create views that include specific row-level conditions and grant access to those views instead of the underlying tables.
The DEFINER clause allows you to specify the security context under which the view is executed, ensuring that users can only access the rows they are authorized to see.

IBM Db2

IBM Db2 implements Row Level Security through the use of Row and Column Access Control (RCAC).
RCAC allows you to define row permissions and column masks to control access to specific rows and columns within a table.
Row permissions are defined using SQL expressions that determine the conditions under which a user can access a row.
Column masks are used to control the visibility and modification of specific columns based on user-defined rules.

SAP HANA

SAP HANA supports RLS through the use of analytic privileges and row-level access control.
Analytic privileges allow you to define access control policies based on user roles and other attributes.
Row-level access control enables you to restrict access to specific rows within a table based on user-defined conditions.
These security features can be implemented using SQL statements and security policy definitions.

Amazon Redshift

Amazon Redshift, a cloud-based data warehousing service, provides RLS through the use of row-level access control policies.
You can define policies that restrict access to specific rows based on user roles, data classifications, or other criteria.
Policies are created using SQL statements and are applied transparently to queries accessing the protected tables.

These are just a few examples of database systems that support RLS. Each database system may have its own specific implementation and syntax for configuring and managing RLS policies.

Conclusion

Row Level Security is a feature that allows database administrators to control access to individual rows within a table. It helps ensure users can only access data they’re permitted to view. RLS protects sensitive information from unauthorized access.

This article covers the fundamentals of RLS. It also compares how you can implement RLS in PostgreSQL and SQL Server. We give examples to show how it limits access based on user roles or other factors.

Implementing Row Level Security is crucial for maintaining data privacy and complying with various regulations. It adds an extra layer of security to your database, giving you fine-grained control over data access.

DataSunrise offers top-notch security tools for databases, including features for security, audit rules, masking, and compliance. Our Row Level Security feature stands out, letting you set detailed access controls at the row level.

If you want to know more about DataSunrise’s security solutions, contact our team for an online demo. We’ll gladly demonstrate our robust security tools and discuss how they safeguard your data.

Remember, data security is crucial nowadays. With Row Level Security and expertise from companies like DataSunrise, you can protect your database’s confidentiality and integrity while granting access as needed.