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.
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
houseis part of the role
housequeries the table, only one record pertaining to
houseis returned; the rest of the records are filtered as per the RLS policy. The sensitive column is also restricted, so users from the role
staffcan’t see this column. User
cuddyis part of the role
employeestable, 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
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:
- Define four RLS policies using the
- all_can_see – No restrictions to be imposed
- hide_confidential – Restricts records for non-confidential rows
- only_doctors_can_see – Restricts records such that only doctors can see data
- see_only_own_department – Restricts records to only see data for own department
- Create three roles for
- Now we define column-level access control for the roles and columns that are implementing the RLS policy:
MANAGERcan access all columns in the
Medicine_datatables, including the
confidentialcolumn that defines RLS policies:
STAFFrole can access all columns except the
- Attach RLS policies to the roles we created:
- Enable RLS security on objects:
- Create the users and grant them roles:
We can see RLS in action with a SELECT query:
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.