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

{"value":"[Amazon Redshift](http://aws.amazon.com/redshift) is a fully managed, petabyte-scale data warehouse service in the cloud. With [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail), 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.\n\nRow-level security (RLS) in [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) is built on the foundation of role-based access control ([RBAC](https://aws.amazon.com/blogs/big-data/simplify-management-of-database-privileges-in-amazon-redshift-using-role-based-access-control/)). 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](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) enables you to dynamically filter existing rows of data in a table. This is in addition to [column-level access control](https://aws.amazon.com/blogs/big-data/achieve-finer-grained-data-security-with-column-level-access-control-in-amazon-redshift/), 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.\n\nIn this post, we explore the row-level security features of [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) and how you can use roles to simplify managing privileges required to your end-users.\n\n\n#### **Customer feedback**\n\n\n[TrustLogix](http://www.trustlogix.io/) 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](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) clients to implement data-centric security for their digital transformation initiatives.\n\n*“We’re excited about this new and deeper level of integration with [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail). 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](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 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.”*\n\n*-Ganesh Kirti, founder and CEO of TrustLogix.*\n\n\n#### **Overview of row-level security in [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail)**\n\n\nRow-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](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) 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](https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html), is responsible for managing the policies.\n\nThe following diagram illustrates the workflow.\n\n\n\nWith RLS, you can do the following:\n\n- **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.\n- **Combine multiple policies per user or role** – Multiple policies can be defined per user or role, and all policies are applied with AND syntax.\n- **Enhance granular access control** – RLS is built on role-based access control and can work alongside column-level access control.\n- **No access if no policy applied** – All data access is blocked when there is no applicable policy on an RLS-protected table.\n- **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.\n\n\n\n\n#### **Row-level security relevant use cases**\n\n\n- 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.\n- 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.\n- 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.\n- A hospital can create an RLS policy that allows doctors and nurses to view data rows for their patients only.\n- 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.\n- 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.\n\nIn 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```.\n\nTo download the script and set up the tables, choose [rls_createtable.sql](https://redshift-blogs.s3.amazonaws.com/amazon-redshift-row-level-security/RLS_CreateTables.sql).\n\n\n#### **Example 1: Read and write access**\n\n\nTo grant read and write access, complete the following steps:\n\n1. Define four RLS policies using the secadmin role:\na. all_can_see – No restrictions to be imposed\nb. hide_confidential – Restricts records for non-confidential rows\nc. only_doctors_can_see – Restricts records such that only doctors can see data\nd. see_only_own_department – Restricts records to only see data for own department\n\n```\\nCREATE RLS POLICY all_can_see\\nUSING ( true );\\n\\nCREATE RLS POLICY hide_confidential\\nWITH ( confidential BOOLEAN )\\nUSING ( confidential = false )\\n;\\n\\nNote: Employee table is used as lookup in this policy\\n\\nCREATE RLS POLICY only_doctors_can_see\\nUSING (\\n true = (\\n SELECT employee_is_doctor\\n FROM employees\\n WHERE employee_username = current_user\\n )\\n )\\n;\\n\\nGRANT SELECT ON employees\\nTO RLS POLICY only_doctors_can_see;\\n\\nCREATE RLS POLICY see_only_own_department\\nWITH ( patient_dept_id INTEGER )\\nUSING (\\n patient_dept_id IN (\\n SELECT department_id\\n FROM employees_departments\\n WHERE employee_username = current_user\\n )\\n )\\n;\\n\\nGRANT SELECT ON employees_departments \\nTO RLS POLICY see_only_own_department;\\n```\n\n2. Create three roles for ```STAFF```, ```MANAGER```, and ```EXTERNAL```:\n\n```\\nCREATE ROLE staff;\\nCREATE ROLE manager;\\nCREATE ROLE external;\\n```\n\n3. Now we define column-level access control for the roles and columns that are implementing the RLS policy:\na. The ```MANAGER``` can access all columns in the ```Patients``` and ```Medicine_data``` tables, including the ```confidential``` column that defines RLS policies:\n\n```\\n--- manager can see full table patients and medicine data\\nGRANT SELECT ON employees, employees_departments, patients, medicine_data TO ROLE manager, ROLE external;\\n```\n\nb. The ```STAFF``` role can access all columns except the ```confidential``` column:\n\n```\\n--- staff can see limited columns from medicine data\\nGRANT SELECT (medicine_name, medicine_price) ON medicine_data \\nTO ROLE staff;\\n\\n--- staff can see, update and delete limited columns from patients\\nGRANT SELECT (patient_dept_id, patient_name, patient_birthday, patient_medicine, diagnosis) ON patients TO ROLE staff;\\nGRANT UPDATE (patient_dept_id, patient_name, patient_birthday, patient_medicine, diagnosis) ON patients TO ROLE staff;\\nGRANT DELETE ON patients TO ROLE staff;\\n```\n\n4. Attach RLS policies to the roles we created:\n\n```\\n--- manager can see all medicine data\\nATTACH RLS POLICY all_can_see\\nON medicine_data\\nTO ROLE manager;\\n\\n--- manager can see all patient data\\nATTACH RLS POLICY all_can_see\\nON patients\\nTO ROLE manager;\\n\\n--- staff cannot see confidential medicine data\\nATTACH RLS POLICY hide_confidential\\nON medicine_data\\nTO ROLE staff;\\n\\n--- staff cannot see confidential patient data\\nATTACH RLS POLICY hide_confidential\\nON patients\\nTO ROLE staff;\\n\\n--- only doctors can see patient data\\nATTACH RLS POLICY only_doctors_can_see \\nON patients\\nTO PUBLIC;\\n\\n--- regular staff (doctors) can see data for patients in their department only\\nATTACH RLS POLICY see_only_own_department \\nON patients\\nTO ROLE staff;\\n\\n```\n\n5. Enable RLS security on objects:\n\n```\\nALTER TABLE medicine_data ROW LEVEL SECURITY on;\\nALTER TABLE patients ROW LEVEL SECURITY on;\\n```\n\n6. Create the users and grant them roles:\n\n```\\nCREATE USER house PASSWORD DISABLE;\\nCREATE USER cuddy PASSWORD DISABLE;\\nCREATE USER external PASSWORD DISABLE;\\n\\nGRANT ROLE staff TO house;\\nGRANT ROLE manager TO cuddy;\\nGRANT ROLE external TO external;\\n```\n\nWe can see RLS in action with a SELECT query:\n\n```\\n--- As Cuddy, who is a doctor and a manager\\nSET SESSION AUTHORIZATION 'cuddy';\\n\\nSELECT * FROM medicine_data;\\n--- policies applied: all_can_see\\n```\n\n\n\n```\\nSELECT * FROM patients;\\n--- policies applied: all_can_see, only_doctors_can_see\\n```\n\n\n\nAs 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.\n\n```\\n--- As House, who is a doctor but not a manager - he is staff in department id 1\\n\\nSET SESSION AUTHORIZATION 'house';\\n\\nSELECT * FROM medicine_data;\\n--- column level access control applied \\n```\n\n\n\n```\\nSELECT current_user, medicine_name, medicine_price FROM medicine_data;\\n--- CLS + RLS policy = hide_confidential\\n```\n\n\n\n```\\nSELECT * FROM patients;\\n--- column level access control applied\\n```\n\n\n\n```\\nSELECT current_user, patient_dept_id, patient_name, patient_birthday, patient_medicine, diagnosis FROM patients;\\n--- CLS + RLS policies = hide_confidential, only_doctors_can_see, see_only_own_department\\n```\n\n\n\n```\\n--- As External, who has no permission granted\\nSET SESSION AUTHORIZATION 'external';\\n\\nSELECT * FROM medicine_data;\\n--- RLS policy applied: none - so no access\\n\\n```\n\n\n\n```\\nSELECT * FROM patients;\\n--- policies applied: none - so no access\\n```\n\n\n\nWith the UPDATE command, only the user house should be able to update patients records, as per the RLS for department 1:\n\n```\\nSET SESSION AUTHORIZATION 'house';\\nUPDATE patients\\nSET diagnosis = 'house updated diagnosis';\\n```\n\n\n\n```\\nselect current_user,\\npatient_dept_id,patient_name,patient_birthday,patient_medicine,diagnosis\\nfrom patients;\\n```\n\nThe user house should only be able to query department 1 non-confidential records.\n\n\n\nTo test DELETE, as the user ```house```, let’s delete records from patient table. Only two non-confidential records from ```patient_dept_id``` should be deleted as per the RLS policy:\n\n```\\nSET SESSION AUTHORIZATION 'house';\\ndelete from patients;\\n```\n\n\n\nBecause both the records that ```house``` has access to are deleted from ```patients```, selecting from the table will return no records.\n\n\n\nWhen we switch to the user ```cuddy```, who is ```manager``` and ```doctor```, we have access to confidential records and can see three records:\n\n```\\nSET SESSION AUTHORIZATION 'cuddy';\\nSELECT current_user, * from patients;\\n```\n\n\n\nAs a security admin, you can detach a policy from a table, user, or role. In this example, we detach the policy ```hide_confidential``` from the table ```patients``` from role ``staff```:\\n\\n```\\nDETACH RLS POLICY hide_confidential ON patients FROM ROLE staff;\\n```\\n\\nWhen the user ```house``` queries the ```patients``` table, they should now have access to confidential records:\\n\\n```\\nSET SESSION AUTHORIZATION 'house';\\n\\nSELECT current_user,patient_dept_id,patient_name,patient_birthday,patient_medicine,diagnosis from patients;\\n```\\n\\n\\n\\nUsing the security admin role, you can drop the policy ```hide_confidential```:\\n\\n```\\nDROP RLS POLICY IF EXISTS hide_confidential;\\n```\\n\\nBecause the ```hide_confidential```RLS policy is still attached to the ```medicine_data``` table, you get the dependency error.\\n\\n\\n\\nTo remove this policy from all the tables, users, and roles, you can use the cascade option:\\n\\n```\\nDROP RLS POLICY IF EXISTS hide_confidential cascade;\\n```\\n\\nWhen user house queries the ```medicine_data``` table, no records are returned, because the ```medicine_data``` table has RLS on and no RLS policy is attached to the role ```staff``` for this table.\\n\\n\\n```\\nSET SESSION AUTHORIZATION 'house';\\nSELECT * from MEDICINE_DATA;\\n```\\n\\n\\n\\nLet’s turn off row-level security on the table ```medicine_data``` using the security admin role:\\n\\n```\\nALTER TABLE MEDICINE_DATA ROW LEVEL SECURITY OFF;\\nSET SESSION AUTHORIZATION 'house';\\n\\nSELECT * FROM MEDICINE_DATA;\\n```\\n\\n\\n\\n\\n#### **Example 2: Session context variables**\\n\\n\\nSome of the applications require you to use connection pooling, and you can use application-based user authentication instead of using separate database users for each user. The session context variables feature in Amazon Redshift enables you to pass the application user ID to the database for applying role-base security.\\n\\nAmazon Redshift now allows you to set a customized session context variable using ```set_config```. Using the session context variable allows you to provide such granular access using RLS.\\n\\nIn this example, we illustrate the use case when you have the common table ```customer```, where you’re getting data from several customers. The table has a column with ```c_customer_id``` to distinguish data for respective customers.\\n\\n1. Create the ```external``` user and grant the ```external``` role:\\n\\n```\\nCREATE USER external_user PASSWORD 'Testemp1';\\ngrant role EXTERNAL to external_user;\\n```\\n\\n2. Grant SELECT on the ```customer``` table to role ```external```:\\n\\n```\\ngrant usage on schema report to role EXTERNAL;\\nGRANT select ON TABLE report.customer TO ROLE EXTERNAL;\\n```\\n\\n3. Turn on row-level security for the ```report.customer``` table:\\n\\n```\\nALTER TABLE report.customer row level security on;\\n```\\n\\n4. Create a row-level security policy using the session context variable ```app.customer_id``` to enforce the policy to filter records for ```c_customer_id```:\\n\\n```\\nCREATE RLS POLICY see_only_own_customer_rows\\nWITH ( c_customer_id char(16) )\\nUSING ( c_customer_id = current_setting('app.customer_id', FALSE));\\nATTACH RLS POLICY see_only_own_customer_rows ON report.customer TO ROLE EXTERNAL;\\n```\\n\\nNow we can observe RLS in action. When you query the ```customer``` table with session context set to customer ID ```AAAAAAAAJNGEGCBA```, the row-level policy was enforced only to return one ```customer``` row that matched the session variable value:\\n\\n```\\nSET SESSION AUTHORIZATION 'external_user';\\n\\nselect set_config('app.customer_id', 'AAAAAAAAJNGEGCBA', FALSE);\\nselect * from report.customer limit 10;\\n\\n```\\n\\n\\n\\n\\n#### **Auditing and monitoring RLS policies**\\n\\n\\nAmazon Redshift has added several new system views to be able to monitor the row-level policies. The following table lists the system views, users, and roles that have access, and the function of the views.\\n\\n\\n\\n\\n\\n#### **Conclusion**\\n\\n\\nIn this post, we demonstrated how you can simplify the management of row-level security for fine-grained access control of your sensitive data building on the foundation of role-based access control. For more information about RLS best practices, refer to [Amazon Redshift security overview](https://docs.aws.amazon.com/redshift/latest/dg/c_security-overview.html). Try out RLS for your future Amazon Redshift implementations, and feel free to leave a comment about your use cases and experience.\\n\\n[Amazon Redshift Spectrum](https://docs.aws.amazon.com/redshift/latest/dg/c-getting-started-using-spectrum.html) supports row-level, column-level, and cell-level access control for data stored in [Amazon Simple Storage Service](https://docs.aws.amazon.com/redshift/latest/dg/c-getting-started-using-spectrum.html) (Amazon S3) and managed by [Amazon Web Services Lake Formation](https://aws.amazon.com/lake-formation/). In a future post, we will show how you can implement row-level security for Redshift Spectrum tables using Lake Formation.\\n\\n\\n##### **About the authors**\\n\\n\\n\\n\\n**Harshida Patel** is a Specialist Sr. Solutions Architect, Analytics, with Amazon Web Services.\\n\\n\\n\\n**Milind Oke** is a Senior Specialist Solutions Architect based out of New York. He has been building data warehouse solutions for over two decades and specializes in Amazon Redshift.\\n\\n\\n\\n**Abhilash Nagilla** is a Specialist Solutions Architect, Analytics, with Amazon Web Services.\\n\\n\\n\\n**Yanzhu Ji** is a Product Manager on the Amazon Redshift team. She worked on the Amazon Redshift team as a Software Engineer before becoming a Product Manager. She has rich experience of how the customer-facing Amazon Redshift features are built from planning to launching, and always treats customers’ requirements as first priority. In her personal life, Yanzhu likes painting, photography, and playing tennis.\\n\\n\\n\\n**Kiran Chinta** is a Software Development Manager at Amazon Redshift. He leads a strong team in query processing, SQL language, data security, and performance. Kiran is passionate about delivering products that seamlessly integrate with customers’ business applications with the right ease of use and performance. In his spare time, he enjoys reading and playing tennis.\\n\\n\\n\\n**Debu Panda** is a Senior Manager, Product Management, with Amazon Web Services. He is an industry leader in analytics, application platforms, and database technologies, and has more than 25 years of experience in the IT world. Debu has published numerous articles on analytics, enterprise Java, and databases, and has presented at multiple conferences such as Amazon Web Services re:Invent, Oracle Open World, and Java One. He is lead author of the EJB 3 in Action (Manning Publications 2007, 2014) and Middleware Management (Packt).","render":"<p><a href=\\"http://aws.amazon.com/redshift\\" target=\\"_blank\\">Amazon Redshift</a> 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.</p>\\n<p>Row-level security (RLS) in Amazon Redshift is built on the foundation of role-based access control (<a href=\\"https://aws.amazon.com/blogs/big-data/simplify-management-of-database-privileges-in-amazon-redshift-using-role-based-access-control/\\" target=\\"_blank\\">RBAC</a>). 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 <a href=\\"https://aws.amazon.com/blogs/big-data/achieve-finer-grained-data-security-with-column-level-access-control-in-amazon-redshift/\\" target=\\"_blank\\">column-level access control</a>, 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.</p>\\n<p>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.</p>\\n<h4><a id=\\"Customer_feedback_7\\"></a><strong>Customer feedback</strong></h4>\\n<p><a href=\\"http://www.trustlogix.io/\\" target=\\"_blank\\">TrustLogix</a> 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.</p>\\n<p><em>“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.”</em></p>\\n<p><em>-Ganesh Kirti, founder and CEO of TrustLogix.</em></p>\\n<h4><a id=\\"Overview_of_rowlevel_security_in_Amazon_Redshift_17\\"></a><strong>Overview of row-level security in Amazon Redshift</strong></h4>\\n<p>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, <a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html\\" target=\\"_blank\\">security admin</a>, is responsible for managing the policies.</p>\\n<p>The following diagram illustrates the workflow.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/8f0161a543f8481586fee59dd3af1123_image.png\\" alt=\\"image.png\\" /></p>\\n<p>With RLS, you can do the following:</p>\\n<ul>\\n<li><strong>Restrict row access based on roles</strong> – The security admin creates and defines if a role can access specific records of data within a table based on an RLS policy.</li>\\n<li><strong>Combine multiple policies per user or role</strong> – Multiple policies can be defined per user or role, and all policies are applied with AND syntax.</li>\\n<li><strong>Enhance granular access control</strong> – RLS is built on role-based access control and can work alongside column-level access control.</li>\\n<li><strong>No access if no policy applied</strong> – All data access is blocked when there is no applicable policy on an RLS-protected table.</li>\\n<li><strong>Enable row-level and column-level</strong> security on the table – In the following example, the user <code>house</code> is part of the role <code>staff</code>. When <code>house</code> queries the table, only one record pertaining to <code>house</code> 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 <code>staff</code> can’t see this column. User <code>cuddy</code> is part of the role <code>manager</code>. When <code>cuddy</code> queries the <code>employees</code> table, all records and columns are returned.</li>\\n</ul>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/0519fd3973b14411977b8c51c4c9bde6_image.png\\" alt=\\"image.png\\" /></p>\\n<h4><a id=\\"Rowlevel_security_relevant_use_cases_37\\"></a><strong>Row-level security relevant use cases</strong></h4>\\n<ul>\\n<li>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.</li>\\n<li>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.</li>\\n<li>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.</li>\\n<li>A hospital can create an RLS policy that allows doctors and nurses to view data rows for their patients only.</li>\\n<li>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.</li>\\n<li>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.</li>\\n</ul>\\n<p>In the following example use cases, we illustrate enforcing an RLS policy on a fictitious healthcare setup. We demonstrate RLS on the <code>medicine_data</code> table and <code>patients</code> 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 <code>customer</code>.</p>\\n<p>To download the script and set up the tables, choose <a href=\\"https://redshift-blogs.s3.amazonaws.com/amazon-redshift-row-level-security/RLS_CreateTables.sql\\" target=\\"_blank\\">rls_createtable.sql</a>.</p>\\n<h4><a id=\\"Example_1_Read_and_write_access_52\\"></a><strong>Example 1: Read and write access</strong></h4>\\n<p>To grant read and write access, complete the following steps:</p>\\n<ol>\\n<li>Define four RLS policies using the secadmin role:<br />\\na. all_can_see – No restrictions to be imposed<br />\\nb. hide_confidential – Restricts records for non-confidential rows<br />\\nc. only_doctors_can_see – Restricts records such that only doctors can see data<br />\\nd. see_only_own_department – Restricts records to only see data for own department</li>\\n</ol>\\n<pre><code class=\\"lang-\\">CREATE RLS POLICY all_can_see\\nUSING ( true );\\n\\nCREATE RLS POLICY hide_confidential\\nWITH ( confidential BOOLEAN )\\nUSING ( confidential = false )\\n;\\n\\nNote: Employee table is used as lookup in this policy\\n\\nCREATE RLS POLICY only_doctors_can_see\\nUSING (\\n true = (\\n SELECT employee_is_doctor\\n FROM employees\\n WHERE employee_username = current_user\\n )\\n )\\n;\\n\\nGRANT SELECT ON employees\\nTO RLS POLICY only_doctors_can_see;\\n\\nCREATE RLS POLICY see_only_own_department\\nWITH ( patient_dept_id INTEGER )\\nUSING (\\n patient_dept_id IN (\\n SELECT department_id\\n FROM employees_departments\\n WHERE employee_username = current_user\\n )\\n )\\n;\\n\\nGRANT SELECT ON employees_departments \\nTO RLS POLICY see_only_own_department;\\n</code></pre>\\n<ol start=\\"2\\">\\n<li>Create three roles for <code>STAFF</code>, <code>MANAGER</code>, and <code>EXTERNAL</code>:</li>\\n</ol>\\n<pre><code class=\\"lang-\\">CREATE ROLE staff;\\nCREATE ROLE manager;\\nCREATE ROLE external;\\n</code></pre>\\n<ol start=\\"3\\">\\n<li>Now we define column-level access control for the roles and columns that are implementing the RLS policy:<br />\\na. The <code>MANAGER</code> can access all columns in the <code>Patients</code> and <code>Medicine_data</code> tables, including the <code>confidential</code> column that defines RLS policies:</li>\\n</ol>\\n<pre><code class=\\"lang-\\">--- manager can see full table patients and medicine data\\nGRANT SELECT ON employees, employees_departments, patients, medicine_data TO ROLE manager, ROLE external;\\n</code></pre>\\n<p>b. The <code>STAFF</code> role can access all columns except the <code>confidential</code> column:</p>\\n<pre><code class=\\"lang-\\">--- staff can see limited columns from medicine data\\nGRANT SELECT (medicine_name, medicine_price) ON medicine_data \\nTO ROLE staff;\\n\\n--- staff can see, update and delete limited columns from patients\\nGRANT SELECT (patient_dept_id, patient_name, patient_birthday, patient_medicine, diagnosis) ON patients TO ROLE staff;\\nGRANT UPDATE (patient_dept_id, patient_name, patient_birthday, patient_medicine, diagnosis) ON patients TO ROLE staff;\\nGRANT DELETE ON patients TO ROLE staff;\\n</code></pre>\\n<ol start=\\"4\\">\\n<li>Attach RLS policies to the roles we created:</li>\\n</ol>\\n<pre><code class=\\"lang-\\">--- manager can see all medicine data\\nATTACH RLS POLICY all_can_see\\nON medicine_data\\nTO ROLE manager;\\n\\n--- manager can see all patient data\\nATTACH RLS POLICY all_can_see\\nON patients\\nTO ROLE manager;\\n\\n--- staff cannot see confidential medicine data\\nATTACH RLS POLICY hide_confidential\\nON medicine_data\\nTO ROLE staff;\\n\\n--- staff cannot see confidential patient data\\nATTACH RLS POLICY hide_confidential\\nON patients\\nTO ROLE staff;\\n\\n--- only doctors can see patient data\\nATTACH RLS POLICY only_doctors_can_see \\nON patients\\nTO PUBLIC;\\n\\n--- regular staff (doctors) can see data for patients in their department only\\nATTACH RLS POLICY see_only_own_department \\nON patients\\nTO ROLE staff;\\n\\n</code></pre>\\n<ol start=\\"5\\">\\n<li>Enable RLS security on objects:</li>\\n</ol>\\n<pre><code class=\\"lang-\\">ALTER TABLE medicine_data ROW LEVEL SECURITY on;\\nALTER TABLE patients ROW LEVEL SECURITY on;\\n</code></pre>\\n<ol start=\\"6\\">\\n<li>Create the users and grant them roles:</li>\\n</ol>\\n<pre><code class=\\"lang-\\">CREATE USER house PASSWORD DISABLE;\\nCREATE USER cuddy PASSWORD DISABLE;\\nCREATE USER external PASSWORD DISABLE;\\n\\nGRANT ROLE staff TO house;\\nGRANT ROLE manager TO cuddy;\\nGRANT ROLE external TO external;\\n</code></pre>\\n<p>We can see RLS in action with a SELECT query:</p>\\n<pre><code class=\\"lang-\\">--- As Cuddy, who is a doctor and a manager\\nSET SESSION AUTHORIZATION 'cuddy';\\n\\nSELECT * FROM medicine_data;\\n--- policies applied: all_can_see\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/06e5658d27c84abf86e3a3376f56f55d_image.png\\" alt=\\"image.png\\" /></p>\\n<pre><code class=\\"lang-\\">SELECT * FROM patients;\\n--- policies applied: all_can_see, only_doctors_can_see\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/dccfa046e62447acb1873dd662cd7c6e_image.png\\" alt=\\"image.png\\" /></p>\\n<p>As a super user and <code>secadmin</code>, you can query the <code>svv_rls_applied_policy</code> to audit and monitor the policies applied. We discuss system views for auditing and monitoring more later in this post.</p>\\n<pre><code class=\\"lang-\\">--- As House, who is a doctor but not a manager - he is staff in department id 1\\n\\nSET SESSION AUTHORIZATION 'house';\\n\\nSELECT * FROM medicine_data;\\n--- column level access control applied \\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/62f8c1b96f11461188c160ab970f5e94_image.png\\" alt=\\"image.png\\" /></p>\\n<pre><code class=\\"lang-\\">SELECT current_user, medicine_name, medicine_price FROM medicine_data;\\n--- CLS + RLS policy = hide_confidential\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/f8d40e01d45c460a98d02ed8cb1856b2_image.png\\" alt=\\"image.png\\" /></p>\\n<pre><code class=\\"lang-\\">SELECT * FROM patients;\\n--- column level access control applied\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/329c3784c4ab40b48ec91318eb80ed42_image.png\\" alt=\\"image.png\\" /></p>\\n<pre><code class=\\"lang-\\">SELECT current_user, patient_dept_id, patient_name, patient_birthday, patient_medicine, diagnosis FROM patients;\\n--- CLS + RLS policies = hide_confidential, only_doctors_can_see, see_only_own_department\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/6e8cf8f3e4694a04964c5c5ea84d4124_image.png\\" alt=\\"image.png\\" /></p>\\n<pre><code class=\\"lang-\\">--- As External, who has no permission granted\\nSET SESSION AUTHORIZATION 'external';\\n\\nSELECT * FROM medicine_data;\\n--- RLS policy applied: none - so no access\\n\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/a833b0dcb656478386ba7e0aaffc6180_image.png\\" alt=\\"image.png\\" /></p>\\n<pre><code class=\\"lang-\\">SELECT * FROM patients;\\n--- policies applied: none - so no access\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/079410237ab643c78073b37e67b01375_image.png\\" alt=\\"image.png\\" /></p>\\n<p>With the UPDATE command, only the user house should be able to update patients records, as per the RLS for department 1:</p>\\n<pre><code class=\\"lang-\\">SET SESSION AUTHORIZATION 'house';\\nUPDATE patients\\nSET diagnosis = 'house updated diagnosis';\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/15009a68a7104c20b31fc1de65419c21_image.png\\" alt=\\"image.png\\" /></p>\\n<pre><code class=\\"lang-\\">select current_user,\\npatient_dept_id,patient_name,patient_birthday,patient_medicine,diagnosis\\nfrom patients;\\n</code></pre>\\n<p>The user house should only be able to query department 1 non-confidential records.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/41997dfb78fc43bcb23b32138997a0aa_image.png\\" alt=\\"image.png\\" /></p>\\n<p>To test DELETE, as the user <code>house</code>, let’s delete records from patient table. Only two non-confidential records from <code>patient_dept_id</code> should be deleted as per the RLS policy:</p>\\n<pre><code class=\\"lang-\\">SET SESSION AUTHORIZATION 'house';\\ndelete from patients;\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/2e13d50068144978bbde2c46c649d9e8_image.png\\" alt=\\"image.png\\" /></p>\\n<p>Because both the records that <code>house</code> has access to are deleted from <code>patients</code>, selecting from the table will return no records.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/1e30154c57db4d16b5b4716c5b15dd03_image.png\\" alt=\\"image.png\\" /></p>\\n<p>When we switch to the user <code>cuddy</code>, who is <code>manager</code> and <code>doctor</code>, we have access to confidential records and can see three records:</p>\\n<pre><code class=\\"lang-\\">SET SESSION AUTHORIZATION 'cuddy';\\nSELECT current_user, * from patients;\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/6eb2f09f0b1c4358a76fffc41bfd2221_image.png\\" alt=\\"image.png\\" /></p>\\n<p>As a security admin, you can detach a policy from a table, user, or role. In this example, we detach the policy <code>hide_confidential</code> from the table <code>patients</code> from role ``staff```:</p>\\n<pre><code class=\\"lang-\\">DETACH RLS POLICY hide_confidential ON patients FROM ROLE staff;\\n</code></pre>\\n<p>When the user <code>house</code> queries the <code>patients</code> table, they should now have access to confidential records:</p>\\n<pre><code class=\\"lang-\\">SET SESSION AUTHORIZATION 'house';\\n\\nSELECT current_user,patient_dept_id,patient_name,patient_birthday,patient_medicine,diagnosis from patients;\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/3242e108fe994caf8928b34aab5cc050_image.png\\" alt=\\"image.png\\" /></p>\n<p>Using the security admin role, you can drop the policy <code>hide_confidential</code>:</p>\\n<pre><code class=\\"lang-\\">DROP RLS POLICY IF EXISTS hide_confidential;\\n</code></pre>\\n<p>Because the <code>hide_confidential</code>RLS policy is still attached to the <code>medicine_data</code> table, you get the dependency error.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/3eae36ab55ad4d77aa4ddd94c2fe72a6_image.png\\" alt=\\"image.png\\" /></p>\n<p>To remove this policy from all the tables, users, and roles, you can use the cascade option:</p>\n<pre><code class=\\"lang-\\">DROP RLS POLICY IF EXISTS hide_confidential cascade;\\n</code></pre>\\n<p>When user house queries the <code>medicine_data</code> table, no records are returned, because the <code>medicine_data</code> table has RLS on and no RLS policy is attached to the role <code>staff</code> for this table.</p>\\n<pre><code class=\\"lang-\\">SET SESSION AUTHORIZATION 'house';\\nSELECT * from MEDICINE_DATA;\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/749126b4f4aa45d6b7302407866f50cc_image.png\\" alt=\\"image.png\\" /></p>\n<p>Let’s turn off row-level security on the table <code>medicine_data</code> using the security admin role:</p>\\n<pre><code class=\\"lang-\\">ALTER TABLE MEDICINE_DATA ROW LEVEL SECURITY OFF;\\nSET SESSION AUTHORIZATION 'house';\\n\\nSELECT * FROM MEDICINE_DATA;\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/04c3e1790e1d4901a7206fbb28d4841a_image.png\\" alt=\\"image.png\\" /></p>\n<h4><a id=\\"Example_2_Session_context_variables_352\\"></a><strong>Example 2: Session context variables</strong></h4>\\n<p>Some of the applications require you to use connection pooling, and you can use application-based user authentication instead of using separate database users for each user. The session context variables feature in Amazon Redshift enables you to pass the application user ID to the database for applying role-base security.</p>\n<p>Amazon Redshift now allows you to set a customized session context variable using <code>set_config</code>. Using the session context variable allows you to provide such granular access using RLS.</p>\\n<p>In this example, we illustrate the use case when you have the common table <code>customer</code>, where you’re getting data from several customers. The table has a column with <code>c_customer_id</code> to distinguish data for respective customers.</p>\\n<ol>\\n<li>Create the <code>external</code> user and grant the <code>external</code> role:</li>\\n</ol>\n<pre><code class=\\"lang-\\">CREATE USER external_user PASSWORD 'Testemp1';\\ngrant role EXTERNAL to external_user;\\n</code></pre>\\n<ol start=\\"2\\">\\n<li>Grant SELECT on the <code>customer</code> table to role <code>external</code>:</li>\\n</ol>\n<pre><code class=\\"lang-\\">grant usage on schema report to role EXTERNAL;\\nGRANT select ON TABLE report.customer TO ROLE EXTERNAL;\\n</code></pre>\\n<ol start=\\"3\\">\\n<li>Turn on row-level security for the <code>report.customer</code> table:</li>\\n</ol>\n<pre><code class=\\"lang-\\">ALTER TABLE report.customer row level security on;\\n</code></pre>\\n<ol start=\\"4\\">\\n<li>Create a row-level security policy using the session context variable <code>app.customer_id</code> to enforce the policy to filter records for <code>c_customer_id</code>:</li>\\n</ol>\n<pre><code class=\\"lang-\\">CREATE RLS POLICY see_only_own_customer_rows\\nWITH ( c_customer_id char(16) )\\nUSING ( c_customer_id = current_setting('app.customer_id', FALSE));\\nATTACH RLS POLICY see_only_own_customer_rows ON report.customer TO ROLE EXTERNAL;\\n</code></pre>\\n<p>Now we can observe RLS in action. When you query the <code>customer</code> table with session context set to customer ID <code>AAAAAAAAJNGEGCBA</code>, the row-level policy was enforced only to return one <code>customer</code> row that matched the session variable value:</p>\\n<pre><code class=\\"lang-\\">SET SESSION AUTHORIZATION 'external_user';\\n\\nselect set_config('app.customer_id', 'AAAAAAAAJNGEGCBA', FALSE);\\nselect * from report.customer limit 10;\\n\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/f7181c95e49040b4a04d8e868b7f3836_image.png\\" alt=\\"image.png\\" /></p>\n<h4><a id=\\"Auditing_and_monitoring_RLS_policies_403\\"></a><strong>Auditing and monitoring RLS policies</strong></h4>\\n<p>Amazon Redshift has added several new system views to be able to monitor the row-level policies. The following table lists the system views, users, and roles that have access, and the function of the views.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/b9e057146c5a43de878167fb19fb3cb2_image.png\\" alt=\\"image.png\\" /></p>\n<h4><a id=\\"Conclusion_412\\"></a><strong>Conclusion</strong></h4>\\n<p>In this post, we demonstrated how you can simplify the management of row-level security for fine-grained access control of your sensitive data building on the foundation of role-based access control. For more information about RLS best practices, refer to <a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/c_security-overview.html\\" target=\\"_blank\\">Amazon Redshift security overview</a>. Try out RLS for your future [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) implementations, and feel free to leave a comment about your use cases and experience.</p>\\n<p><a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/c-getting-started-using-spectrum.html\\" target=\\"_blank\\">Amazon Redshift Spectrum</a> supports row-level, column-level, and cell-level access control for data stored in <a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/c-getting-started-using-spectrum.html\\" target=\\"_blank\\">Amazon Simple Storage Service</a> ([Amazon S3](https://aws.amazon.com/cn/s3/?trk=cndc-detail)) and managed by <a href=\\"https://aws.amazon.com/lake-formation/\\" target=\\"_blank\\">Amazon Web Services Lake Formation</a>. In a future post, we will show how you can implement row-level security for Redshift Spectrum tables using Lake Formation.</p>\\n<h5><a id=\\"About_the_authors_420\\"></a><strong>About the authors</strong></h5>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/5ea4bbd20663419f8e5a0fee2158b160_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Harshida Patel</strong> is a Specialist Sr. Solutions Architect, Analytics, with Amazon Web Services.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/7777bb92132d46efba94a7126991069c_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Milind Oke</strong> is a Senior Specialist Solutions Architect based out of New York. He has been building data warehouse solutions for over two decades and specializes in [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail).</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/6d1d815965b04307a8986629aacea835_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Abhilash Nagilla</strong> is a Specialist Solutions Architect, Analytics, with Amazon Web Services.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/35ce103c839641e6b5a14f0d978f72aa_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Yanzhu Ji</strong> is a Product Manager on the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) team. She worked on the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) team as a Software Engineer before becoming a Product Manager. She has rich experience of how the customer-facing [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) features are built from planning to launching, and always treats customers’ requirements as first priority. In her personal life, Yanzhu likes painting, photography, and playing tennis.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/c8101e76ea5f40da9ccfe530dc6f0e54_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Kiran Chinta</strong> is a Software Development Manager at [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail). He leads a strong team in query processing, SQL language, data security, and performance. Kiran is passionate about delivering products that seamlessly integrate with customers’ business applications with the right ease of use and performance. In his spare time, he enjoys reading and playing tennis.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/0c5bf98d4b0b4976a6d172989a7a610a_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Debu Panda</strong> is a Senior Manager, Product Management, with Amazon Web Services. He is an industry leader in analytics, application platforms, and database technologies, and has more than 25 years of experience in the IT world. Debu has published numerous articles on analytics, enterprise Java, and databases, and has presented at multiple conferences such as Amazon Web Services re:Invent, Oracle Open World, and Java One. He is lead author of the EJB 3 in Action (Manning Publications 2007, 2014) and Middleware Management (Packt).</p>\n"}