Achieve fine-grained data security with row-level access control in Amazon Redshift

Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud. With Amazon Redshift, you can analyze all your data to derive holistic insights about your business and your customers. One of the challenges with security is that enterprises want to provide fine-grained access control at the row level for sensitive data. You can do this by creating views or using different databases and schemas for different users. However, this approach isn’t scalable and becomes complex to maintain over time. Customers have asked us to simplify the process of securing their data by providing the ability to control granular access.

Row-level security (RLS) in Amazon Redshift is built on the foundation of role-based access control (RBAC). RLS allows you to control which users or roles can access specific records of data within tables, based on security policies that are defined at the database object level. This new RLS capability in Amazon Redshift enables you to dynamically filter existing rows of data in a table. This is in addition to column-level access control, where you can grant users permissions to a subset of columns. Now you can combine column-level access control with RLS policies to further restrict access to particular rows of visible columns.

In this post, we explore the row-level security features of Amazon Redshift and how you can use roles to simplify managing privileges required to your end-users.

Customer feedback

TrustLogix is a Norwest Venture Partners backed cloud security startup in the Data Security Governance space. TrustLogix delivers powerful monitoring, observability, audit, and fine-grained data entitlement capabilities that empower Amazon Redshift clients to implement data-centric security for their digital transformation initiatives.

“We’re excited about this new and deeper level of integration with Amazon Redshift. Our joint customers in security-forward and highly regulated sectors including financial services, healthcare, and pharmaceutical need to have incredibly fine-grained control over which users are allowed to access what data, and under which specific contexts. The new row-level security capabilities will allow our customers to precisely dictate data access controls based on their business entitlements while abstracting them away from the technical complexities. The new Amazon Redshift RLS capability will enable our joint customers to model policies at the business level, deploy and enforce them via a security-as-code model, ensuring secure and consistent access to their sensitive data.”

-Ganesh Kirti, founder and CEO of TrustLogix.

Overview of row-level security in Amazon Redshift

Row-level security allows you to restrict some records to certain users or roles, depending on the content of those records. With RLS, you can define policies to enforce fine-grained row-level access control. When creating RLS policies, you can specify expressions that control whether Amazon Redshift returns any existing rows in a table in a query. With RLS policies limiting access, you don’t have to add or externalize additional conditions in your queries. You can attach multiple policies to a table, and a single policy can be attached to multiple tables, making this implementation relationship many-to-many. Once attached, the RLS policy is applied on a relation and a set of users or roles, to run SELECT, UPDATE, and DELETE operations. All attached RLS policies have to evaluate together to true for a record to be returned by query. The RBAC built-in role, security admin, is responsible for managing the policies.

The following diagram illustrates the workflow.

With RLS, you can do the following:

• Restrict row access based on roles – The security admin creates and defines if a role can access specific records of data within a table based on an RLS policy.
• Combine multiple policies per user or role – Multiple policies can be defined per user or role, and all policies are applied with AND syntax.
• Enhance granular access control – RLS is built on role-based access control and can work alongside column-level access control.
• No access if no policy applied – All data access is blocked when there is no applicable policy on an RLS-protected table.
• Enable row-level and column-level security on the table – In the following example, the user house is part of the role staff. When house queries the table, only one record pertaining to house is returned; the rest of the records are filtered as per the RLS policy. The sensitive column is also restricted, so users from the role staff can’t see this column. User cuddy is part of the role manager. When cuddy queries the employees table, all records and columns are returned.

Row-level security relevant use cases

With row-level security, many use cases for fine-grained access controls become possible. The following are just some of the many application use cases:

• A global company with data analysts across different countries or regions can enforce restriction of data access to analysts based on geo location due to data compliance requirements.
• A sales department can create a policy that allows them to restrict the access to sales performance information specific to a particular salesperson or region.
• A payroll department can create an RLS policy to restrict access to look at an individual’s payroll, but managers need payroll information on their direct reports. Managers don’t need to know the details of payroll information for other departments.
• A hospital can create an RLS policy that allows doctors and nurses to view data rows for their patients only.
• A bank can create a policy to restrict access to financial data rows based on an employee’s business division or role in the company.
• A multi-tenant application can create a policy to enforce a logical separation of each tenant’s data rows from every other tenant’s rows.

In the following example use cases, we illustrate enforcing an RLS policy on a fictitious healthcare setup. We demonstrate RLS on the medicine_data table and patients table, based on a policy established for managers, doctors, and departments. We also cover using a custom session variable context to set an RLS policy for the multi-tenant table customer.

To download the script and set up the tables, choose rls_createtable.sql.

Example 1: Read and write access

To grant read and write access, complete the following steps:

1. Define four RLS policies using the secadmin role:
   1. all_can_see – No restrictions to be imposed
   2. hide_confidential – Restricts records for non-confidential rows
   3. only_doctors_can_see – Restricts records such that only doctors can see data
   4. see_only_own_department – Restricts records to only see data for own department

      CREATE RLS POLICY all_can_see
      USING ( true );
      
      CREATE RLS POLICY hide_confidential
      WITH ( confidential BOOLEAN )
      USING ( confidential = false )
      ;
      
      Note: Employee table is used as lookup in this policy
      
      CREATE RLS POLICY only_doctors_can_see
      USING (
          true = (
                  SELECT employee_is_doctor
                  FROM employees
                  WHERE employee_username = current_user
                  )
          )
      ;
      
      GRANT SELECT ON employees
      TO RLS POLICY only_doctors_can_see;
      
      CREATE RLS POLICY see_only_own_department
      WITH ( patient_dept_id INTEGER )
      USING (
          patient_dept_id IN (
                              SELECT department_id
                              FROM employees_departments
                              WHERE employee_username = current_user
                              )
          )
      ;
      
      GRANT SELECT ON employees_departments 
      TO RLS POLICY see_only_own_department;

2. Create three roles for STAFF, MANAGER, and EXTERNAL:

   CREATE ROLE staff;
   CREATE ROLE manager;
   CREATE ROLE external;

3. Now we define column-level access control for the roles and columns that are implementing the RLS policy:
   1. The MANAGER can access all columns in the Patients and Medicine_data tables, including the confidential column that defines RLS policies:

      --- manager can see full table patients and medicine data
      GRANT SELECT ON employees, employees_departments, patients, medicine_data TO ROLE manager, ROLE external;

   2. The STAFF role can access all columns except the confidential column:

      --- staff can see limited columns from medicine data
      GRANT SELECT (medicine_name, medicine_price) ON medicine_data 
      TO ROLE staff;
      
      --- staff can see, update and delete limited columns from patients
      GRANT SELECT (patient_dept_id, patient_name, patient_birthday, patient_medicine, diagnosis) ON patients TO ROLE staff;
      GRANT UPDATE (patient_dept_id, patient_name, patient_birthday, patient_medicine, diagnosis) ON patients TO ROLE staff;
      GRANT DELETE ON patients TO ROLE staff;

4. Attach RLS policies to the roles we created:

   --- manager can see all medicine data
   ATTACH RLS POLICY all_can_see
   ON medicine_data
   TO ROLE manager;
   
   --- manager can see all patient data
   ATTACH RLS POLICY all_can_see
   ON patients
   TO ROLE manager;
   
   --- staff cannot see confidential medicine data
   ATTACH RLS POLICY hide_confidential
   ON medicine_data
   TO ROLE staff;
   
   --- staff cannot see confidential patient data
   ATTACH RLS POLICY hide_confidential
   ON patients
   TO ROLE staff;
   
   --- only doctors can see patient data
   ATTACH RLS POLICY only_doctors_can_see 
   ON patients
   TO PUBLIC;
   
   --- regular staff (doctors) can see data for patients in their department only
   ATTACH RLS POLICY see_only_own_department 
   ON patients
   TO ROLE staff;

5. Enable RLS security on objects:

   ALTER TABLE medicine_data ROW LEVEL SECURITY on;
   ALTER TABLE patients ROW LEVEL SECURITY on;

6. Create the users and grant them roles:

   CREATE USER house PASSWORD DISABLE;
   CREATE USER cuddy PASSWORD DISABLE;
   CREATE USER external PASSWORD DISABLE;
   
   GRANT ROLE staff TO house;
   GRANT ROLE manager TO cuddy;
   GRANT ROLE external TO external;

We can see RLS in action with a SELECT query:

--- As Cuddy, who is a doctor and a manager
SET SESSION AUTHORIZATION 'cuddy';

SELECT * FROM medicine_data;
--- policies applied: all_can_see

SELECT * FROM patients;
--- policies applied: all_can_see, only_doctors_can_see

As a super user and secadmin, you can query the svv_rls_applied_policy to audit and monitor the policies applied. We discuss system views for auditing and monitoring more later in this post.

--- As House, who is a doctor but not a manager - he is staff in department id 1

SET SESSION AUTHORIZATION 'house';

SELECT * FROM medicine_data;
--- column level access control applied 

SELECT current_user, medicine_name, medicine_price FROM medicine_data;
--- CLS + RLS policy = hide_confidential

SELECT * FROM patients;
--- column level access control applied

SELECT current_user, patient_dept_id, patient_name, patient_birthday, patient_medicine, diagnosis FROM patients;
--- CLS + RLS policies = hide_confidential, only_doctors_can_see, see_only_own_department

--- As External, who has no permission granted
SET SESSION AUTHORIZATION 'external';

SELECT * FROM medicine_data;
--- RLS policy applied: none - so no access

SELECT * FROM patients;
--- policies applied: none - so no access

SET SESSION AUTHORIZATION 'house';
UPDATE patients
SET diagnosis = 'house updated diagnosis';

select current_user,
patient_dept_id,patient_name,patient_birthday,patient_medicine,diagnosis
from patients;

SET SESSION AUTHORIZATION 'house';
delete  from patients;

SET SESSION AUTHORIZATION 'cuddy';
SELECT current_user, * from patients;

DETACH RLS POLICY hide_confidential ON patients FROM ROLE staff;

SET SESSION AUTHORIZATION 'house';

SELECT current_user,patient_dept_id,patient_name,patient_birthday,patient_medicine,diagnosis from patients;

DROP RLS POLICY IF EXISTS hide_confidential;

DROP RLS POLICY IF EXISTS hide_confidential cascade;

SET SESSION AUTHORIZATION 'house';
SELECT * from MEDICINE_DATA;

ALTER TABLE MEDICINE_DATA ROW LEVEL SECURITY OFF;
SET SESSION AUTHORIZATION 'house';

SELECT * FROM MEDICINE_DATA;

SET SESSION AUTHORIZATION 'external_user';

select set_config('app.customer_id', 'AAAAAAAAJNGEGCBA', FALSE);
select * from report.customer limit 10;