Integrate Amazon Redshift row-level security with Amazon Redshift native IdP authentication

{"value":"[Amazon Redshift](http://aws.amazon.com/redshift) is a fully managed, petabyte-scale data warehouse service in the cloud. You can start with just a few hundred gigabytes of data and scale to a petabyte or more. This enables you to use your data to acquire new insights for your business and customers.\n\nAs enterprise customers look to build their data warehouse on [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail), they have business requirements to enforce adequate fine-grained access controls to govern who can access which rows of their sensitive data based on the user profiler. Furthermore, many organizations want to access [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) using their existing identity provider (IdP) while maintaining these compliance and security requirements around their data. Without proper integrated features to enforce row-level and secure authentication, you may have to develop additional solutions such as views, or try to integrate third-party solutions around your data to enforce security.\n\nWith the introduction of [row-level security in Amazon Redshift](https://aws.amazon.com/about-aws/whats-new/2022/07/amazon-redshift-row-level-security/), you can restrict user access at the row level. Additionally, we have introduced a [native IdP functionality](https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-access-control-native-idp.html) to help you implement authentication and authorization with your choice of business intelligence (BI) tools in a seamless way.\n\n[Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) row-level security (RLS) provides granular access control over your sensitive data. It does this by using RLS policies to determine which rows to return in the query result sets.\n\nIn this post, we walk you through an example on how you can implement row-level security in [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) while using existing IdP credentials to simplify authentication and managing permissions. You can use this flexible solution to provide complete control over data access while maintaining authorization using your existing IdP.\n\n\n#### **Solution overview**\n\n\nFor our use case, an organization requires row-level security to restrict access to sales performance data to specific states and their allocated salesperson. We have the following business rules and conditions:\n\n- Alice, the salesperson for NY, should have access to NY sales data only\n- Bob, the salesperson for CA, should get access to CA sales data only\n- Charlie, the sales manager for the North America region, should have access to sales data for all states\n- Jen, who belongs to HR department, shouldn’t have access to any sales data\n\nThe following diagram illustrates the solution architecture we implement to solve this problem statement using [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) row-level security and [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) native IdP authentication.\n\n\n\nThe solution contains the following steps:\n\n1. Create RLS policies to provide fine-grained access control for row-level data on the Sales table.\n2. Create [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) roles for each of the different Azure AD groups and assign relevant permissions to the table.\n\nWith native IdP, roles get created automatically based on Azure groups. However, as a best practice, we’re pre-creating the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) roles and assigning relevant permissions.\n\n3. Attach row-level security policies to the roles.\n4. Configure a JDBC or ODBC driver in your SQL client to use Azure AD federation and use Azure AD login credentials to sign in.\n5. Upon successful authentication, Azure AD issues an authentication token (OAuth token) back to the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) driver.\n6. The driver forwards the authentication token to the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) cluster to initiate a new database session. [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) verifies and validates the authentication token.\n7. [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) calls the Azure Graph API to obtain the user’s group membership.\n8. [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) maps the logged-in Azure AD user to the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) user and maps the Azure AD groups to [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) roles.\n9. The [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) roles are pre-mapped with the RLS policies mentioned in step 3. This allows the respective users to query the fine-grained row-level access data from the client.\n\n\n#### **Prerequisites**\n\n\nTo implement this solution, you must have the following prerequisites:\n\n- An Amazon Web Services account. If you don’t have one, you can [sign up](https://portal.aws.amazon.com/billing/signup#/start) for one.\n- An [Amazon Redshift Serverless](https://aws.amazon.com/redshift/redshift-serverless/) or [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) provisioned cluster. For setup instructions, refer to [Setting up Amazon Redshift Serverless for the first time](https://docs.aws.amazon.com/redshift/latest/mgmt/serverless-console-getting-started.html) or [Create a sample Amazon Redshift cluster](https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-launch-sample-cluster.html).\n- A [Microsoft Azure account](https://portal.azure.com/) that has an active subscription. You need to be an admin role to set up the application on Azure AD.\n- [Power BI Desktop version 2.102.683.0 64-bit](https://www.microsoft.com/en-us/download/details.aspx?id=58494) or higher version downloaded and installed.\n- The latest [Amazon Redshift JDBC SDK driver-dependent libraries](https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-download-driver.html) downloaded and the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) JDBC JAR .zip folder unzipped. Note that all prior [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) driver versions before JDBC version 2.1.0.4 don’t support native IdP.\n- Any SQL client. For this post, we use SQL Workbench/J.\n\n\n#### **Implement your [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) native IdP**\n\n\nTo set up your [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) native IdP setup, refer to [Integrate Amazon Redshift native IdP federation with Microsoft Azure AD using a SQL client](https://aws.amazon.com/blogs/big-data/integrate-amazon-redshift-native-idp-federation-with-microsoft-azure-ad-using-a-sql-client/). Follow the steps to set up your Azure application and collect Azure AD information for the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) IdP.\n\nFor this post, we have created the following four groups in Azure AD:\n\n- ```sales_ny```\n- ```sales_ca```\n- ```sales_manager```\n- ```hr_group```\n\nThen we created the following four users in Azure AD:\n\n- **Alice** – The salesperson in NY state\n- **Bob** – The salesperson in CA state\n- **Charlie** – The manager for the North America region\n- **Jen** – A member of the HR group\n\nAdd the respective users to their appropriate group:\n\n- **Alice** – ```sales_ny```\n- **Bob**– ```sales_ca```\n- **Charlie**– ```sales_manager```\n- **Jen** – ```HR```\n\nNext, we need to register the IdP in [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) using the following command:\n\n```\\nCREATE IDENTITY PROVIDER rls_idp TYPE\\nazure NAMESPACE 'aad'\\nPARAMETERS '{\\n\\"issuer\\":\\"https://sts.windows.net/87f4aa26-78b7-410e-bf29-57b39929ef9a/\\",\\n\\"audience\\":[\\"https://analysis.windows.net/powerbi/connector/AmazonRedshift\\",\\n\\"api://991abc78-78ab-4ad8-a123-zf123ab03612p\\"],\\n\\"client_id\\":\\"123ab555-a321-666d-7890-11a123a44890\\",\\n\\"client_secret\\":\\"KiG7Q~FEDnE.VsWS1IIl7LV1R2BtA4qVv2ixB\\" }'\\n;\\n```\n\nIn the preceding statement, the type azure indicates that the provider specifically facilitates communication with Microsoft Azure AD. We use the following parameters to collect Azure AD information (for more information, refer to Collect Azure AD Information in [Integrate Amazon Redshift native IdP federation with Microsoft Azure AD using a SQL client](https://aws.amazon.com/blogs/big-data/integrate-amazon-redshift-native-idp-federation-with-microsoft-azure-ad-using-a-sql-client/)).\n\n- **issuer** – The issuer ID to trust when a token is received. The unique identifier for the ```tenant_id``` is appended to the issuer.\n- **client_id** – The unique public identifier of the application registered with the IdP. This can be referred to as the application ID.\n- **client_secret** – A secret identifier, or password, known only to the IdP and the registered application.\n- **audience** – The application ID that is assigned to the application in Azure. For this post, we connect with [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) using Power BI Desktop and SQL Workbench/J. The audience value is hardcoded for Power BI desktop, for example ```https://analysis.windows.net/powerbi/connector/AmazonRedshift``` . The second audience value is for the SQL client, which you get from the application ID URI in the OAuth application. For example, ```api://991abc78-78ab-4ad8-a123-zf123ab03612p```.\n\nUse the following command to view the registered IdP on [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail):\n\n```\\nDESC IDENTITY PROVIDER rls_idp;\\n```\n\n\n\nUse the following command to view all the IdPs registered:\n\n```\\nselect * from svv_identity_providers;\\n```\nThe following Sales table contains information about each salesperson, the respective state they cover, and their total sales amount:\n\n```\\nCREATE TABLE SALES (sales_person VARCHAR(30), state CHAR(2), \\"total_sales\\" INT);\\nINSERT INTO SALES VALUES ('Alice', 'NY', 5000);\\nINSERT INTO SALES VALUES ('Bob', 'CA', 6000);\\nINSERT INTO SALES VALUES ('Sally', 'IL', 7000);\\n```\n\n\n\nNow we create four roles in the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) cluster based on the groups that we created on the Azure AD portal and assign relevant permissions to them. This simplifies administration by assigning different permissions to different roles and assigning them to different users.\n\nThe role name in the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) cluster looks like ```<namespace>:<azure_ad_group_name>```, where the namespace is the one we provided in the IdP creation command (```aad```) and the group name is the Azure AD group. See the following code:\n\n```\\nCREATE ROLE \\"aad:sales_ny\\";\\nCREATE ROLE \\"aad:sales_ca\\";\\nCREATE ROLE \\"aad:sales_manager\\";\\nCREATE ROLE \\"aad:hr\\";\\n```\n\nNow we grant permission to the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) role on the appropriate tables. For this post, we assign SELECT permission on the Sales table for all four roles:\n\n```\\nGRANT SELECT ON TABLE SALES TO ROLE \\"aad:sales_ny\\";\\nGRANT SELECT ON TABLE SALES TO ROLE \\"aad:sales_ca\\";\\nGRANT SELECT ON TABLE SALES TO ROLE \\"aad:sales_manager\\";\\nGRANT SELECT ON TABLE SALES TO ROLE \\"aad:hr\\";\\n```\n\nUse the following command to view all the roles in the cluster:\n\n```\\nselect * from svv_roles;\\n```\n\n\n\n\n\n#### **Create a row-level security policy**\n\n\nLet’s enforce an RLS policy on the Sales table to restrict access to sales performance information for a salesperson specific to a particular state. We create the following policy:\n\n```\\nCREATE RLS POLICY policy_sales_ny\\nWITH (state char(2))\\nUSING (state = 'NY');\\nCREATE RLS POLICY policy_sales_ca\\nWITH (state char(2))\\nUSING (state = 'CA');\\n```\n\nThe sales manager is also required to view sales across the North American region. For this, we create the following policy:\n\n```\\nCREATE RLS POLICY policy_sales_all\\nUSING (true);\\n```\n\nThe ```policy_sales_all``` policy allows the sales manager to view all the information in the sales table.\n\n\n#### **Attach the row-level security policy to roles**\n\n\nNow we have to attach the row-level security policies to their respective [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) roles so that when the user logs in using their [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) native IdP, they can get fine-grained access to the records.\n\n```\\nATTACH RLS POLICY policy_sales_ny ON public.sales TO ROLE \\"aad:sales_ny\\";\\nATTACH RLS POLICY policy_sales_ca ON public.sales TO ROLE \\"aad:sales_ca\\";\\nATTACH RLS POLICY policy_sales_all ON public.sales TO ROLE \\"aad:sales_manager\\";\\n```\n\nFor the HR role, we haven’t created or attached any RLS policy because we don’t want any user from the HR group to get access to sales records.\n\n\n#### **Enable row-level security on the table**\n\n\nNow let’s enable row-level security on the respective tables. In this demo, we enable the RLS policy on the Sales table using the following command:\n\n```\\nALTER TABLE public.sales ROW LEVEL SECURITY ON;\\n```\n\nUse the following command to view the RLS policies:\n\n```\\nSELECT * FROM svv_rls_attached_policy;\\n```\n\n\n\n\n#### **Test row-level security using Power BI Desktop**\n\n\nIn this example, we use Microsoft Power BI Desktop to connect with [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) using a native IdP. For this solution, use [Microsoft Power BI Desktop- Version: 2.102.683.0 64-bit](https://www.microsoft.com/en-us/download/details.aspx?id=58494) and above.\n\n1. In your Microsoft Power BI Desktop, choose **Get data**.\n\n\n\n2. Search for the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) connector, choose it, and choose Connect.\n\n\n\n3. For **Server**, enter your [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) cluster’s endpoint. For example: ```test-cluster.ct4abcufthff.us-east-1.redshift.amazonaws.com```.\n4. For **Database**, enter your database name (for this post, we enter ```dev```).\n5. Choose **OK**.\n\n\n\n6. Choose **Microsoft Account**.\n\n\n\n7. Choose **Sign in**.\n\n\n\n8. Enter your Microsoft Account credentials in the authorization dialog. For this example, we sign in with user Alice.\n9. Choose **Next**.\n\n\n\nOnce connected, you will see the message “You are currently signed in.”\n\n10. Choose **Connect**.\n\n\n\nAs shown in the following screenshot, Azure AD user Alice is able to authenticate using an [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) native IdP, and the RLS policies were applied automatically, allowing Alice to access sales performance information for only NY state.\n\n\n\nSimilarly, we can try signing in as user Bob and see only CA state information.\n\n\n\nCharlie belongs to the manager role where the view all policy has been applied, so when he signs in, he is able to view all the rows in the sales table.\n\n\n\nFinally, when Jen signs in, she can access the table, but isn’t able to view any sales records because no RLS policy has been attached to the HR role.\n\n\n\nIf we haven’t granted SELECT on the sales table to the role aad:hr, which Jen belongs to, then she can’t access the sales table.\n\n\n\n\n#### **Test row-level security using SQL Workbench/J**\n\n\nNow we test row-level security with an [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) native IdP using SQL Workbench/J.\n\n1. Create a new connection in SQL Workbench/J and choose [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) as the driver.\n2. Choose **Manage drivers** and add all the files from the downloaded Amazon Web Services JDBC driver pack .zip file. (Remember to unzip the file.)\n\nMake sure to use the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) driver 2.1.0.4 onwards, because all previous [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) driver versions don’t support the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) native IDP feature.\n\n\n\n3. For URL, enter ```jdbc:redshift://<cluster endpoint>:<port>:<databasename>```. For example: ```jdbc:redshift://test-cluster.ab6yejheyhgf.us-east-1.redshift.amazonaws.com:5439/dev```.\n\n\n\n4. On the Driver properties tab, add the following properties:\na. **plugin_name** – ```com.amazon.redshift.plugin.BrowserAzureOAuth2CredentialsProvider```\nb. **listen_port** – 7890\nc. **idp_response_timeout** – 50\nd. **scope** – Enter the scope value from the OAuth application. For example, ```api://991abc78-78ab-4ad8-a123-zf123ab03612p/jdbc_login```.\ne. **client_id** – Enter the client_id value from the OAuth application. For example, ```991abc78-78ab-4ad8-a123-zf123ab03612p```.\nf. **idp_tenant** – Enter the tenant ID value from the OAuth application. For example, ```87f4aa26-78b7-410e-bf29-57b39929ef9a```.\n\n\n\n5. Choose **OK** from SQL Workbench/J.\n\nYou’re redirected to the browser to sign in with your Azure AD credentials.\n\nAs shown in the following screenshot, Azure AD user Alice is able to authenticate using an [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) native IdP and view only sales performance information for NY state.\n\n\n\nSimilarly, we can re-authenticate and sign in as user Bob, who is able to view sales information specific to CA state.\n\n\n\nWhen Charlie signs in, he is able to view all the rows from every state.\n\n\n\nFinally, when Jen signs in, she is able to access the table, but can’t view any sales records because no RLS policy has been attached to the HR role.\n\n\n\nIf we haven’t granted SELECT on the sales table to the role aad:hr, which Jen belongs to, then Jen can’t access the sales table.\n\n\n\n\n#### **Summary**\n\n\nIn this post, we covered how you can achieve a secure end-to-end experience using [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) native IdP authentication, which simplifies administration and row-level security to enable fine-grained row-level access in [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail).\n\nFor more information about [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) row-level security and native IdP federation, refer to:\n\n- [Row-level security](https://docs.aws.amazon.com/redshift/latest/dg/t_rls.html)\n- [Achieve fine-grained data security with row-level access control in Amazon Redshift](https://aws.amazon.com/blogs/big-data/achieve-fine-grained-data-security-with-row-level-access-control-in-amazon-redshift/)\n- [Native identity provider (IdP) federation for Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-access-control-native-idp.html)\n\n\n#### **About the authors**\n\n\n\n\n**Maneesh Sharma** is a Senior Database Engineer at Amazon Web Services with more than a decade of experience designing and implementing large-scale data warehouse and analytics solutions. He collaborates with various [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) Partners and customers to drive better integration.\n\n\n\n**Harshida Patel** is a Specialist Sr. Solutions Architect, Analytics, with Amazon Web Services.","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. You can start with just a few hundred gigabytes of data and scale to a petabyte or more. This enables you to use your data to acquire new insights for your business and customers.</p>\\n<p>As enterprise customers look to build their data warehouse on Amazon Redshift, they have business requirements to enforce adequate fine-grained access controls to govern who can access which rows of their sensitive data based on the user profiler. Furthermore, many organizations want to access Amazon Redshift using their existing identity provider (IdP) while maintaining these compliance and security requirements around their data. Without proper integrated features to enforce row-level and secure authentication, you may have to develop additional solutions such as views, or try to integrate third-party solutions around your data to enforce security.</p>\n<p>With the introduction of <a href=\\"https://aws.amazon.com/about-aws/whats-new/2022/07/amazon-redshift-row-level-security/\\" target=\\"_blank\\">row-level security in Amazon Redshift</a>, you can restrict user access at the row level. Additionally, we have introduced a <a href=\\"https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-access-control-native-idp.html\\" target=\\"_blank\\">native IdP functionality</a> to help you implement authentication and authorization with your choice of business intelligence (BI) tools in a seamless way.</p>\\n<p>Amazon Redshift row-level security (RLS) provides granular access control over your sensitive data. It does this by using RLS policies to determine which rows to return in the query result sets.</p>\n<p>In this post, we walk you through an example on how you can implement row-level security in Amazon Redshift while using existing IdP credentials to simplify authentication and managing permissions. You can use this flexible solution to provide complete control over data access while maintaining authorization using your existing IdP.</p>\n<h4><a id=\\"Solution_overview_11\\"></a><strong>Solution overview</strong></h4>\\n<p>For our use case, an organization requires row-level security to restrict access to sales performance data to specific states and their allocated salesperson. We have the following business rules and conditions:</p>\n<ul>\\n<li>Alice, the salesperson for NY, should have access to NY sales data only</li>\n<li>Bob, the salesperson for CA, should get access to CA sales data only</li>\n<li>Charlie, the sales manager for the North America region, should have access to sales data for all states</li>\n<li>Jen, who belongs to HR department, shouldn’t have access to any sales data</li>\n</ul>\\n<p>The following diagram illustrates the solution architecture we implement to solve this problem statement using Amazon Redshift row-level security and Amazon Redshift native IdP authentication.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/c036948da4e649808ed0e5c86d0f1581_image.png\\" alt=\\"image.png\\" /></p>\n<p>The solution contains the following steps:</p>\n<ol>\\n<li>Create RLS policies to provide fine-grained access control for row-level data on the Sales table.</li>\n<li>Create Amazon Redshift roles for each of the different Azure AD groups and assign relevant permissions to the table.</li>\n</ol>\\n<p>With native IdP, roles get created automatically based on Azure groups. However, as a best practice, we’re pre-creating the Amazon Redshift roles and assigning relevant permissions.</p>\n<ol start=\\"3\\">\\n<li>Attach row-level security policies to the roles.</li>\n<li>Configure a JDBC or ODBC driver in your SQL client to use Azure AD federation and use Azure AD login credentials to sign in.</li>\n<li>Upon successful authentication, Azure AD issues an authentication token (OAuth token) back to the Amazon Redshift driver.</li>\n<li>The driver forwards the authentication token to the Amazon Redshift cluster to initiate a new database session. Amazon Redshift verifies and validates the authentication token.</li>\n<li>Amazon Redshift calls the Azure Graph API to obtain the user’s group membership.</li>\n<li>Amazon Redshift maps the logged-in Azure AD user to the Amazon Redshift user and maps the Azure AD groups to Amazon Redshift roles.</li>\n<li>The Amazon Redshift roles are pre-mapped with the RLS policies mentioned in step 3. This allows the respective users to query the fine-grained row-level access data from the client.</li>\n</ol>\\n<h4><a id=\\"Prerequisites_41\\"></a><strong>Prerequisites</strong></h4>\\n<p>To implement this solution, you must have the following prerequisites:</p>\n<ul>\\n<li>An Amazon Web Services account. If you don’t have one, you can <a href=\\"https://portal.aws.amazon.com/billing/signup#/start\\" target=\\"_blank\\">sign up</a> for one.</li>\\n<li>An <a href=\\"https://aws.amazon.com/redshift/redshift-serverless/\\" target=\\"_blank\\">Amazon Redshift Serverless</a> or [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) provisioned cluster. For setup instructions, refer to <a href=\\"https://docs.aws.amazon.com/redshift/latest/mgmt/serverless-console-getting-started.html\\" target=\\"_blank\\">Setting up Amazon Redshift Serverless for the first time</a> or <a href=\\"https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-launch-sample-cluster.html\\" target=\\"_blank\\">Create a sample Amazon Redshift cluster</a>.</li>\\n<li>A <a href=\\"https://portal.azure.com/\\" target=\\"_blank\\">Microsoft Azure account</a> that has an active subscription. You need to be an admin role to set up the application on Azure AD.</li>\\n<li><a href=\\"https://www.microsoft.com/en-us/download/details.aspx?id=58494\\" target=\\"_blank\\">Power BI Desktop version 2.102.683.0 64-bit</a> or higher version downloaded and installed.</li>\\n<li>The latest <a href=\\"https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-download-driver.html\\" target=\\"_blank\\">Amazon Redshift JDBC SDK driver-dependent libraries</a> downloaded and the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) JDBC JAR .zip folder unzipped. Note that all prior [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) driver versions before JDBC version 2.1.0.4 don’t support native IdP.</li>\\n<li>Any SQL client. For this post, we use SQL Workbench/J.</li>\n</ul>\\n<h4><a id=\\"Implement_your_Amazon_Redshift_native_IdP_54\\"></a><strong>Implement your Amazon Redshift native IdP</strong></h4>\\n<p>To set up your Amazon Redshift native IdP setup, refer to <a href=\\"https://aws.amazon.com/blogs/big-data/integrate-amazon-redshift-native-idp-federation-with-microsoft-azure-ad-using-a-sql-client/\\" target=\\"_blank\\">Integrate Amazon Redshift native IdP federation with Microsoft Azure AD using a SQL client</a>. Follow the steps to set up your Azure application and collect Azure AD information for the [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) IdP.</p>\\n<p>For this post, we have created the following four groups in Azure AD:</p>\n<ul>\\n<li><code>sales_ny</code></li>\\n<li><code>sales_ca</code></li>\\n<li><code>sales_manager</code></li>\\n<li><code>hr_group</code></li>\\n</ul>\n<p>Then we created the following four users in Azure AD:</p>\n<ul>\\n<li><strong>Alice</strong> – The salesperson in NY state</li>\\n<li><strong>Bob</strong> – The salesperson in CA state</li>\\n<li><strong>Charlie</strong> – The manager for the North America region</li>\\n<li><strong>Jen</strong> – A member of the HR group</li>\\n</ul>\n<p>Add the respective users to their appropriate group:</p>\n<ul>\\n<li><strong>Alice</strong> – <code>sales_ny</code></li>\\n<li><strong>Bob</strong>– <code>sales_ca</code></li>\\n<li><strong>Charlie</strong>– <code>sales_manager</code></li>\\n<li><strong>Jen</strong> – <code>HR</code></li>\\n</ul>\n<p>Next, we need to register the IdP in Amazon Redshift using the following command:</p>\n<pre><code class=\\"lang-\\">CREATE IDENTITY PROVIDER rls_idp TYPE\\nazure NAMESPACE 'aad'\\nPARAMETERS '{\\n&quot;issuer&quot;:&quot;https://sts.windows.net/87f4aa26-78b7-410e-bf29-57b39929ef9a/&quot;,\\n&quot;audience&quot;:[&quot;https://analysis.windows.net/powerbi/connector/AmazonRedshift&quot;,\\n&quot;api://991abc78-78ab-4ad8-a123-zf123ab03612p&quot;],\\n&quot;client_id&quot;:&quot;123ab555-a321-666d-7890-11a123a44890&quot;,\\n&quot;client_secret&quot;:&quot;KiG7Q~FEDnE.VsWS1IIl7LV1R2BtA4qVv2ixB&quot; }'\\n;\\n</code></pre>\\n<p>In the preceding statement, the type azure indicates that the provider specifically facilitates communication with Microsoft Azure AD. We use the following parameters to collect Azure AD information (for more information, refer to Collect Azure AD Information in <a href=\\"https://aws.amazon.com/blogs/big-data/integrate-amazon-redshift-native-idp-federation-with-microsoft-azure-ad-using-a-sql-client/\\" target=\\"_blank\\">Integrate Amazon Redshift native IdP federation with Microsoft Azure AD using a SQL client</a>).</p>\\n<ul>\\n<li><strong>issuer</strong> – The issuer ID to trust when a token is received. The unique identifier for the <code>tenant_id</code> is appended to the issuer.</li>\\n<li><strong>client_id</strong> – The unique public identifier of the application registered with the IdP. This can be referred to as the application ID.</li>\\n<li><strong>client_secret</strong> – A secret identifier, or password, known only to the IdP and the registered application.</li>\\n<li><strong>audience</strong> – The application ID that is assigned to the application in Azure. For this post, we connect with [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) using Power BI Desktop and SQL Workbench/J. The audience value is hardcoded for Power BI desktop, for example <code>https://analysis.windows.net/powerbi/connector/AmazonRedshift</code> . The second audience value is for the SQL client, which you get from the application ID URI in the OAuth application. For example, <code>api://991abc78-78ab-4ad8-a123-zf123ab03612p</code>.</li>\\n</ul>\n<p>Use the following command to view the registered IdP on Amazon Redshift:</p>\n<pre><code class=\\"lang-\\">DESC IDENTITY PROVIDER rls_idp;\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/f215953e3e9f4af69c42f63d3769712d_image.png\\" alt=\\"image.png\\" /></p>\n<p>Use the following command to view all the IdPs registered:</p>\n<pre><code class=\\"lang-\\">select * from svv_identity_providers;\\n</code></pre>\\n<p>The following Sales table contains information about each salesperson, the respective state they cover, and their total sales amount:</p>\n<pre><code class=\\"lang-\\">CREATE TABLE SALES (sales_person VARCHAR(30), state CHAR(2), &quot;total_sales&quot; INT);\\nINSERT INTO SALES VALUES ('Alice', 'NY', 5000);\\nINSERT INTO SALES VALUES ('Bob', 'CA', 6000);\\nINSERT INTO SALES VALUES ('Sally', 'IL', 7000);\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/c01e6b96200c47c4913afc2df91d9f1c_image.png\\" alt=\\"image.png\\" /></p>\n<p>Now we create four roles in the Amazon Redshift cluster based on the groups that we created on the Azure AD portal and assign relevant permissions to them. This simplifies administration by assigning different permissions to different roles and assigning them to different users.</p>\n<p>The role name in the Amazon Redshift cluster looks like <code>&lt;namespace&gt;:&lt;azure_ad_group_name&gt;</code>, where the namespace is the one we provided in the IdP creation command (<code>aad</code>) and the group name is the Azure AD group. See the following code:</p>\\n<pre><code class=\\"lang-\\">CREATE ROLE &quot;aad:sales_ny&quot;;\\nCREATE ROLE &quot;aad:sales_ca&quot;;\\nCREATE ROLE &quot;aad:sales_manager&quot;;\\nCREATE ROLE &quot;aad:hr&quot;;\\n</code></pre>\\n<p>Now we grant permission to the Amazon Redshift role on the appropriate tables. For this post, we assign SELECT permission on the Sales table for all four roles:</p>\n<pre><code class=\\"lang-\\">GRANT SELECT ON TABLE SALES TO ROLE &quot;aad:sales_ny&quot;;\\nGRANT SELECT ON TABLE SALES TO ROLE &quot;aad:sales_ca&quot;;\\nGRANT SELECT ON TABLE SALES TO ROLE &quot;aad:sales_manager&quot;;\\nGRANT SELECT ON TABLE SALES TO ROLE &quot;aad:hr&quot;;\\n</code></pre>\\n<p>Use the following command to view all the roles in the cluster:</p>\n<pre><code class=\\"lang-\\">select * from svv_roles;\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/a3a71e68427b44468bcbfa3e1eeb4704_image.png\\" alt=\\"image.png\\" /></p>\n<h4><a id=\\"Create_a_rowlevel_security_policy_155\\"></a><strong>Create a row-level security policy</strong></h4>\\n<p>Let’s enforce an RLS policy on the Sales table to restrict access to sales performance information for a salesperson specific to a particular state. We create the following policy:</p>\n<pre><code class=\\"lang-\\">CREATE RLS POLICY policy_sales_ny\\nWITH (state char(2))\\nUSING (state = 'NY');\\nCREATE RLS POLICY policy_sales_ca\\nWITH (state char(2))\\nUSING (state = 'CA');\\n</code></pre>\\n<p>The sales manager is also required to view sales across the North American region. For this, we create the following policy:</p>\n<pre><code class=\\"lang-\\">CREATE RLS POLICY policy_sales_all\\nUSING (true);\\n</code></pre>\\n<p>The <code>policy_sales_all</code> policy allows the sales manager to view all the information in the sales table.</p>\\n<h4><a id=\\"Attach_the_rowlevel_security_policy_to_roles_179\\"></a><strong>Attach the row-level security policy to roles</strong></h4>\\n<p>Now we have to attach the row-level security policies to their respective Amazon Redshift roles so that when the user logs in using their Amazon Redshift native IdP, they can get fine-grained access to the records.</p>\n<pre><code class=\\"lang-\\">ATTACH RLS POLICY policy_sales_ny ON public.sales TO ROLE &quot;aad:sales_ny&quot;;\\nATTACH RLS POLICY policy_sales_ca ON public.sales TO ROLE &quot;aad:sales_ca&quot;;\\nATTACH RLS POLICY policy_sales_all ON public.sales TO ROLE &quot;aad:sales_manager&quot;;\\n</code></pre>\\n<p>For the HR role, we haven’t created or attached any RLS policy because we don’t want any user from the HR group to get access to sales records.</p>\n<h4><a id=\\"Enable_rowlevel_security_on_the_table_193\\"></a><strong>Enable row-level security on the table</strong></h4>\\n<p>Now let’s enable row-level security on the respective tables. In this demo, we enable the RLS policy on the Sales table using the following command:</p>\n<pre><code class=\\"lang-\\">ALTER TABLE public.sales ROW LEVEL SECURITY ON;\\n</code></pre>\\n<p>Use the following command to view the RLS policies:</p>\n<pre><code class=\\"lang-\\">SELECT * FROM svv_rls_attached_policy;\\n</code></pre>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/a46fb02d0b9e45a884fdc49b6b0da00d_image.png\\" alt=\\"image.png\\" /></p>\n<h4><a id=\\"Test_rowlevel_security_using_Power_BI_Desktop_211\\"></a><strong>Test row-level security using Power BI Desktop</strong></h4>\\n<p>In this example, we use Microsoft Power BI Desktop to connect with Amazon Redshift using a native IdP. For this solution, use <a href=\\"https://www.microsoft.com/en-us/download/details.aspx?id=58494\\" target=\\"_blank\\">Microsoft Power BI Desktop- Version: 2.102.683.0 64-bit</a> and above.</p>\\n<ol>\\n<li>In your Microsoft Power BI Desktop, choose <strong>Get data</strong>.</li>\\n</ol>\n<p><img src=\\"https://dev-media.amazoncloud.cn/447d916e2c1942c2b4947a5877d28cee_image.png\\" alt=\\"image.png\\" /></p>\n<ol start=\\"2\\">\\n<li>Search for the Amazon Redshift connector, choose it, and choose Connect.</li>\n</ol>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/d04b2e55555e42cdb02f298168c7a085_image.png\\" alt=\\"image.png\\" /></p>\n<ol start=\\"3\\">\\n<li>For <strong>Server</strong>, enter your [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) cluster’s endpoint. For example: <code>test-cluster.ct4abcufthff.us-east-1.redshift.amazonaws.com</code>.</li>\\n<li>For <strong>Database</strong>, enter your database name (for this post, we enter <code>dev</code>).</li>\\n<li>Choose <strong>OK</strong>.</li>\\n</ol>\n<p><img src=\\"https://dev-media.amazoncloud.cn/d8277bc5320e474aa535631c3b6741f2_image.png\\" alt=\\"image.png\\" /></p>\n<ol start=\\"6\\">\\n<li>Choose <strong>Microsoft Account</strong>.</li>\\n</ol>\n<p><img src=\\"https://dev-media.amazoncloud.cn/916d207606534a64b1a61aa08368cabf_image.png\\" alt=\\"image.png\\" /></p>\n<ol start=\\"7\\">\\n<li>Choose <strong>Sign in</strong>.</li>\\n</ol>\n<p><img src=\\"https://dev-media.amazoncloud.cn/97a94f458b2a4ffa8f9fd835bbc828f4_image.png\\" alt=\\"image.png\\" /></p>\n<ol start=\\"8\\">\\n<li>Enter your Microsoft Account credentials in the authorization dialog. For this example, we sign in with user Alice.</li>\n<li>Choose <strong>Next</strong>.</li>\\n</ol>\n<p><img src=\\"https://dev-media.amazoncloud.cn/99dc5d20e12845acbc860cca876336fa_image.png\\" alt=\\"image.png\\" /></p>\n<p>Once connected, you will see the message “You are currently signed in.”</p>\n<ol start=\\"10\\">\\n<li>Choose <strong>Connect</strong>.</li>\\n</ol>\n<p><img src=\\"https://dev-media.amazoncloud.cn/e598a450992648ac91213b3cd1ddf9d1_image.png\\" alt=\\"image.png\\" /></p>\n<p>As shown in the following screenshot, Azure AD user Alice is able to authenticate using an Amazon Redshift native IdP, and the RLS policies were applied automatically, allowing Alice to access sales performance information for only NY state.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/60ee5cfd22ed4fd2937ecf3654ae3347_image.png\\" alt=\\"image.png\\" /></p>\n<p>Similarly, we can try signing in as user Bob and see only CA state information.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/cbe300a2a6aa4481835ebd8902a04a23_image.png\\" alt=\\"image.png\\" /></p>\n<p>Charlie belongs to the manager role where the view all policy has been applied, so when he signs in, he is able to view all the rows in the sales table.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/9cc3c2244c4845e698f75767bd48983d_image.png\\" alt=\\"image.png\\" /></p>\n<p>Finally, when Jen signs in, she can access the table, but isn’t able to view any sales records because no RLS policy has been attached to the HR role.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/82f44d8acb4a40289154a16a7ef547cc_image.png\\" alt=\\"image.png\\" /></p>\n<p>If we haven’t granted SELECT on the sales table to the role aad:hr, which Jen belongs to, then she can’t access the sales table.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/34bb69912851411eaa6f5fddb8e1b1c3_image.png\\" alt=\\"image.png\\" /></p>\n<h4><a id=\\"Test_rowlevel_security_using_SQL_WorkbenchJ_270\\"></a><strong>Test row-level security using SQL Workbench/J</strong></h4>\\n<p>Now we test row-level security with an Amazon Redshift native IdP using SQL Workbench/J.</p>\n<ol>\\n<li>Create a new connection in SQL Workbench/J and choose Amazon Redshift as the driver.</li>\n<li>Choose <strong>Manage drivers</strong> and add all the files from the downloaded Amazon Web Services JDBC driver pack .zip file. (Remember to unzip the file.)</li>\\n</ol>\n<p>Make sure to use the Amazon Redshift driver 2.1.0.4 onwards, because all previous Amazon Redshift driver versions don’t support the Amazon Redshift native IDP feature.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/1ec974ba50a842f5a54d254b84f9de62_image.png\\" alt=\\"image.png\\" /></p>\n<ol start=\\"3\\">\\n<li>For URL, enter <code>jdbc:redshift://&lt;cluster endpoint&gt;:&lt;port&gt;:&lt;databasename&gt;</code>. For example: <code>jdbc:redshift://test-cluster.ab6yejheyhgf.us-east-1.redshift.amazonaws.com:5439/dev</code>.</li>\\n</ol>\n<p><img src=\\"https://dev-media.amazoncloud.cn/00841b64a94b4816ad72d6ee5bc6d2b5_image.png\\" alt=\\"image.png\\" /></p>\n<ol start=\\"4\\">\\n<li>On the Driver properties tab, add the following properties:<br />\\na. <strong>plugin_name</strong> – <code>com.amazon.redshift.plugin.BrowserAzureOAuth2CredentialsProvider</code><br />\\nb. <strong>listen_port</strong> – 7890<br />\\nc. <strong>idp_response_timeout</strong> – 50<br />\\nd. <strong>scope</strong> – Enter the scope value from the OAuth application. For example, <code>api://991abc78-78ab-4ad8-a123-zf123ab03612p/jdbc_login</code>.<br />\\ne. <strong>client_id</strong> – Enter the client_id value from the OAuth application. For example, <code>991abc78-78ab-4ad8-a123-zf123ab03612p</code>.<br />\\nf. <strong>idp_tenant</strong> – Enter the tenant ID value from the OAuth application. For example, <code>87f4aa26-78b7-410e-bf29-57b39929ef9a</code>.</li>\\n</ol>\n<p><img src=\\"https://dev-media.amazoncloud.cn/ddd056f6bd6a4473b51a966996238017_image.png\\" alt=\\"image.png\\" /></p>\n<ol start=\\"5\\">\\n<li>Choose <strong>OK</strong> from SQL Workbench/J.</li>\\n</ol>\n<p>You’re redirected to the browser to sign in with your Azure AD credentials.</p>\n<p>As shown in the following screenshot, Azure AD user Alice is able to authenticate using an Amazon Redshift native IdP and view only sales performance information for NY state.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/c0f5ab61eeba42768ef9b88103d61caf_image.png\\" alt=\\"image.png\\" /></p>\n<p>Similarly, we can re-authenticate and sign in as user Bob, who is able to view sales information specific to CA state.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/3bcde102643e4477bc3f95807c4b678e_image.png\\" alt=\\"image.png\\" /></p>\n<p>When Charlie signs in, he is able to view all the rows from every state.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/2cffbe7cb37c41b492beaa2b501ffbb5_image.png\\" alt=\\"image.png\\" /></p>\n<p>Finally, when Jen signs in, she is able to access the table, but can’t view any sales records because no RLS policy has been attached to the HR role.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/7c1d3c70b5be40bc9209517f6755c45d_image.png\\" alt=\\"image.png\\" /></p>\n<p>If we haven’t granted SELECT on the sales table to the role aad:hr, which Jen belongs to, then Jen can’t access the sales table.</p>\n<p><img src=\\"https://dev-media.amazoncloud.cn/1726d9d623594644ae48829548c87e5b_image.png\\" alt=\\"image.png\\" /></p>\n<h4><a id=\\"Summary_321\\"></a><strong>Summary</strong></h4>\\n<p>In this post, we covered how you can achieve a secure end-to-end experience using Amazon Redshift native IdP authentication, which simplifies administration and row-level security to enable fine-grained row-level access in Amazon Redshift.</p>\n<p>For more information about Amazon Redshift row-level security and native IdP federation, refer to:</p>\n<ul>\\n<li><a href=\\"https://docs.aws.amazon.com/redshift/latest/dg/t_rls.html\\" target=\\"_blank\\">Row-level security</a></li>\\n<li><a href=\\"https://aws.amazon.com/blogs/big-data/achieve-fine-grained-data-security-with-row-level-access-control-in-amazon-redshift/\\" target=\\"_blank\\">Achieve fine-grained data security with row-level access control in Amazon Redshift</a></li>\\n<li><a href=\\"https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-access-control-native-idp.html\\" target=\\"_blank\\">Native identity provider (IdP) federation for Amazon Redshift</a></li>\\n</ul>\n<h4><a id=\\"About_the_authors_333\\"></a><strong>About the authors</strong></h4>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/868fb3b213d247c3a4826586c770f374_image.png\\" alt=\\"image.png\\" /></p>\n<p><strong>Maneesh Sharma</strong> is a Senior Database Engineer at Amazon Web Services with more than a decade of experience designing and implementing large-scale data warehouse and analytics solutions. He collaborates with various [Amazon Redshift](https://aws.amazon.com/cn/redshift/?trk=cndc-detail) Partners and customers to drive better integration.</p>\\n<p><img src=\\"https://dev-media.amazoncloud.cn/aad2f4d7b9e947889e78c9aa9e3d7318_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"}