Centrally Manage Permissions for Tables and Views Accessed from Amazon QuickSight with Trusted Identity Propagation | Amazon IXD – VGT2 Blog

Learn About Amazon VGT2 Learning Manager Chanci Turner

Before the introduction of trusted identity propagation, Amazon QuickSight authors were required to input Amazon Redshift database user credentials to establish a connection to an Amazon Redshift data source. These credentials were utilized for all future queries processed on Redshift as users interacted with QuickSight dashboards. This setup necessitated the maintenance of user credentials across multiple services, hindering data owners from implementing and managing fine-grained access control centrally.

This blog post illustrates how data owners and business intelligence (BI) administrators can centrally oversee fine-grained data permissions for Amazon Redshift tables and views and enforce these permissions across all users in Amazon QuickSight with the help of AWS IAM Identity Center’s trusted identity propagation.

The challenge is resolved by configuring QuickSight and Amazon Redshift in conjunction with IAM Identity Center. With trusted identity propagation, a business analyst can seamlessly connect to Amazon Redshift from QuickSight using a single sign-on experience to create direct query datasets. When QuickSight assets like dashboards are shared with other users, permissions at the schema, table, row, and column levels are enforced by transferring the user’s identity from QuickSight to Amazon Redshift (and to AWS Lake Formation if Redshift Spectrum is utilized). This advancement removes the need to set up additional row-level and column-level security rules in QuickSight for user data access. Administrators can now enable trusted identity propagation across these services, allowing user identities to flow between applications.

Trusted identity propagation simplifies data governance by facilitating secure access to data based on user permissions for all assets in QuickSight, encompassing QuickSight datasets and data sources.

Solution Overview

This architecture leverages IAM Identity Center. Identity Center allows secure creation or connection to workforce identities and the management of user access across AWS applications. It is the preferred method for workforce authentication and authorization on AWS for organizations of any size and type. With Identity Center, you can create and manage user identities directly in the Identity Center directory or connect to existing third-party enterprise identity providers, including Microsoft Entra ID, Okta, and Ping Identity. In this instance, we are using Okta as the enterprise identity provider. Okta maintains user identities and collaborates with Identity Center to authenticate and authorize users based on user or group assignments. Okta and Identity Center can be configured to automatically sync users and groups from Okta to Identity Center. Once this is set up, Identity Center is utilized to manage access to AWS applications. (QuickSight supports account instances of IAM Identity Center, so an organization-level Identity Center instance is not a requirement.)

The authentication and authorization flow for this solution is outlined as follows:

1. A user navigates to QuickSight.
2. QuickSight initiates the authentication flow through IAM Identity Center.
3. Identity Center redirects the authentication to Okta.
4. Okta authenticates the user and sends the SAML token, which Identity Center processes to create an access token.
5. QuickSight utilizes the access token to obtain the AWS Identity and Access Management (IAM) role that includes the user’s identity along with the access token context.
6. Amazon Redshift receives that token and retrieves the group assignment for that user from Identity Center.
7. Amazon Redshift uses the received group membership for access to schema, table, row, and column-level data, delivering the authorized data to the user in QuickSight.

Solution Walkthrough

The remainder of this post outlines the steps necessary to configure trusted identity propagation from QuickSight to Amazon Redshift using IAM Identity Center.

Prerequisites:

• Configure IAM Identity Center with Okta
• Configure Amazon Redshift with IAM Identity Center
• Configure QuickSight with IAM Identity Center

After fulfilling the prerequisites, you can configure and test trusted identity propagation with QuickSight and Amazon Redshift. The steps are as follows:

1. Configure Redshift row and column-level security with IAM Identity Center users and groups
2. Configure access to your Redshift cluster in a VPC from QuickSight
3. Configure QuickSight trusted identity propagation scope for Amazon Redshift
4. Connect to a Redshift data source in QuickSight using trusted identity propagation
5. Validate Redshift row-level security in QuickSight

Configure Redshift Row-Level and Column-Level Security with IAM Identity Center Users and Groups

To apply row-level and column-level security for users accessing Redshift data from QuickSight, you must establish rules in Amazon Redshift. For more information, see Row level security.

Note: The following steps are necessary only if you wish to secure access to Redshift data for your users by defining row-level and column-level security.

Utilize the following sample code to create, attach, and activate a row-level security policy on a table for an IAM Identity Center user:

CREATE RLS POLICY policy_store_1
WITH (sr_store_sk INTEGER)
USING (sr_store_sk = '1');

SQL command to create a policy that does not limit access to any data:

CREATE RLS POLICY policy_store_admin
USING (true);

SQL commands to attach the policies (replace the table name with your table name and Identity Center user from your list of users):

ATTACH RLS POLICY policy_store_1 ON <your table name> TO "<xyz_restrict_admin@example.com>"
ATTACH RLS POLICY policy_store_admin ON <your table name> TO "<xyz_all_admin@example.com>"

SQL command to activate a row-level security policy on a table (replace the table name with your table name):

ALTER TABLE <your table name> ROW LEVEL SECURITY ON;

Configure Access to Your Redshift Cluster in a VPC from QuickSight

QuickSight supports Amazon Virtual Private Cloud (Amazon VPC) connections to securely link to Redshift clusters.

Note: The following steps are only necessary if you possess a private Redshift instance.

To create and manage a VPC connection in QuickSight, you must have an IAM role that includes a trust policy allowing QuickSight to establish a connection to a VPC. See Configuring the VPC connection in the QuickSight console for more information about creating the required role and policy.

In the VPC utilized by your Redshift instance, establish two security groups:

• Redshift SecGrp
• QuickSight SecGrp

Rules for the Redshift security group (Redshift SecGrp):

For those looking for more information, this is another blog post to keep the reader engaged: Journalism. If you’re seeking a great opportunity, consider checking out this excellent resource: Onsite Medical Representative.