Arrange cross-account AWS Glue Knowledge Catalog entry utilizing AWS Lake Formation and AWS IAM Identification Middle with Amazon Redshift and Amazon QuickSight -

Most organizations handle their workforce id centrally in exterior id suppliers (IdPs) and are comprised of a number of enterprise models that produce their very own datasets and handle the lifecycle unfold throughout a number of AWS accounts. These enterprise models have various landscapes, the place a knowledge lake is managed by Amazon Easy Storage Service (Amazon S3) and analytics workloads are run on Amazon Redshift, a quick, scalable, and absolutely managed cloud information warehouse that lets you course of and run your complicated SQL analytics workloads on structured and semi-structured information.

Enterprise models that create information merchandise wish to share them with others, with out copying the information, to advertise evaluation to derive insights. Additionally, they need tighter management on consumer entry and the power to audit entry to their information merchandise. To deal with this, enterprises often catalog the datasets within the AWS Glue Knowledge Catalog for information discovery and use AWS Lake Formation for fine-grained entry management to stick to the compliance and working safety mannequin for his or her enterprise models. Given the various vary of companies, fine-grained information sharing, and personas concerned, these enterprises usually need a streamlined expertise for enterprise consumer identities when accessing their information utilizing AWS Analytics companies.

AWS IAM Identification Middle permits centralized administration of workforce consumer entry to AWS accounts and purposes utilizing an area id retailer or by connecting company directories utilizing IdPs. Amazon Redshift and AWS Lake Formation are built-in with the brand new trusted id propagation functionality in IAM Identification Middle, permitting you to make use of third-party IdPs comparable to Microsoft Entra ID (Azure AD), Okta, Ping, and OneLogin.

With trusted id propagation, Lake Formation permits information directors to straight present fine-grained entry to company customers and teams, and simplifies the traceability of end-to-end information entry throughout supported AWS companies. As a result of entry is managed primarily based on a consumer’s company id, end-users don’t want to make use of database native consumer credentials or assume an AWS Identification and Entry Administration (IAM) function to entry information. Moreover, this permits efficient consumer permissions primarily based on collective group membership and helps group hierarchy.

On this publish, we cowl easy methods to allow trusted id propagation with AWS IAM Identification Middle, Amazon Redshift, and AWS Lake Formation residing on separate AWS accounts and arrange cross-account sharing of an S3 information lake for enterprise identities utilizing AWS Lake Formation to allow analytics utilizing Amazon Redshift. Then we use Amazon QuickSight to construct insights utilizing Redshift tables as our information supply.

Resolution overview

This publish covers a use case the place a corporation centrally manages company customers inside their IdP and the place the customers belong to a number of enterprise models. Their purpose is to allow centralized consumer authentication via IAM Identification Middle within the administration account, whereas retaining the enterprise unit that analyzes information utilizing a Redshift cluster and the enterprise unit that produces information cataloged utilizing the Knowledge Catalog in separate member accounts. This enables them to keep up a single authentication mechanism via IAM Identification Middle inside a corporation whereas retaining entry management, useful resource, and value separation via the usage of separate AWS accounts per enterprise models and enabling cross-account information sharing utilizing Lake Formation.

For this answer, AWS Organizations is enabled within the central administration account and IAM Identification Middle is configured for managing workforce identities. The group has two member accounts: one account that manages the S3 information lake utilizing the Knowledge Catalog, and one other account that runs analytical workloads on Amazon Redshift and QuickSight, with all of the companies enabled with trusted id propagation. Amazon Redshift will entry cross-account AWS Glue sources utilizing IAM Identification Middle customers and teams arrange within the central administration account utilizing QuickSight in member account 1. In member account 2, permissions on the AWS Glue sources are managed utilizing Lake Formation and are shared with member account 1 utilizing Lake Formation information sharing.

The next diagram illustrates the answer structure.

The answer consists of the next:

Within the centralized administration account, we create a permission set and create account assignments for Redshift_Member_Account. We combine customers and teams from the IdP with IAM Identification Middle.
Member account 1 (Redshift_Member_Account) is the place the Redshift cluster and software exist.
Member account 2 (Glue_Member_Account) is the place metadata is cataloged within the Knowledge Catalog and Lake Formation is enabled with IAM Identification Middle integration.
We assign permissions to 2 IAM Identification Middle teams to entry the Knowledge Catalog sources:
- awssso-sales – We apply column-level filtering for this group in order that customers belonging to this group will have the ability to choose two columns and skim all rows.
- awssso-finance – We apply row-level filtering utilizing information filters for this group in order that customers belonging to this group will have the ability to choose all columns and see rows after row-level filtering is utilized.
We apply totally different permissions for 3 IAM Identification Middle customers:
- Consumer Ethan, a part of awssso-sales – Ethan will have the ability to choose two columns and skim all rows.
- Consumer Frank, a part of awssso-finance – Frank will have the ability to choose all columns and see rows after row-level filtering is utilized.
- Consumer Brian, a part of awssso-sales and awssso-finance – Brian inherits permissions outlined for each teams.
We arrange QuickSight in the identical account the place Amazon Redshift exists, enabling authentication utilizing IAM Identification Middle.

Stipulations

You need to have the next stipulations alreday arrange:

Member account 2 configuration

Register to the Lake Formation console as the information lake administrator. To be taught extra about organising permissions for a knowledge lake administrator, see Create a knowledge lake administrator.

On this part, we stroll via the steps to arrange Lake Formation, allow Lake Formation permissions, and grant database and desk permissions to IAM Identification Middle teams.

Arrange Lake Formation

Full the steps on this part to arrange Lake Formation.

Create AWS Glue sources

You need to use an current AWS Glue database that has just a few tables. For this publish, we use a database known as customerdb and a desk known as evaluations whose information is saved within the S3 bucket lf-datalake-<account-id>-<area>.

Register the S3 bucket location

Full the next steps to register the S3 bucket location:

On the Lake Formation console, within the navigation pane, underneath Administration, select Knowledge lake places.
Select Register location.
For Amazon S3 location, enter the S3 bucket location that comprises desk information.
For IAM function, present a user-defined IAM function. For directions to create a user-defined IAM function, confer with Necessities for roles used to register places.
For Permission mode, choose Lake Formation.
Select Register location.

Set cross-account model

Full the next steps to set your cross-account model:

Register to the Lake Formation console as the information lake admin.
Within the navigation pane, underneath Administration, select Knowledge Catalog settings.
Below Cross-account model settings, preserve the newest model (Model 4) as the present cross-account model.
Select Save.

Add permissions required for cross-account entry

If the AWS Glue Knowledge Catalog useful resource coverage is already enabled within the account, then you’ll be able to both take away the coverage or add the next permissions to the coverage which might be required for cross-account grants. The supplied coverage permits AWS Useful resource Entry Supervisor (AWS RAM) to share a useful resource coverage whereas cross-account grants are made utilizing Lake Formation. For extra info, confer with Stipulations. Please skip to the next step in case your coverage is clean underneath Catalog Settings.

Register to the AWS Glue console as an IAM admin.
Within the navigation pane, underneath Knowledge Catalog, select Catalog settings.
Below Permissions, add the next coverage, and supply the account ID the place your AWS Glue sources exist:

{ "Model": "2012-10-17",
"Assertion": [
{
"Effect": "Allow",
"Principal": {
"Service": "ram.amazonaws.com"
},
"Action": "glue:ShareResource",
"Resource": [
"arn:aws:glue:us-east-1:<account-id>:table/*/*",
"arn:aws:glue:us-east-1:<account-id>:database/*",
"arn:aws:glue:us-east-1:<account-id>:catalog"
]
}
]
}

For extra info, see Granting cross-account entry.

Allow IAM Identification Middle integration for Lake Formation

To combine IAM Identification Middle together with your Lake Formation group occasion of IAM Identification Middle, confer with Connecting Lake Formation with IAM Identification Middle.

To allow cross-account sharing for IAM Identification Middle customers and teams, add the goal recipient accounts to your Lake Formation IAM Identification Middle integration underneath the AWS account and group IDs.

Register to the Lake Formation console as a knowledge lake admin.
Within the navigation pane, underneath Administration, select IAM Identification Middle integration.
Below AWS account and group IDs, select Add.
Enter your goal accounts.
Select Add.

Allow Lake Formation permissions for databases

For Knowledge Catalog databases that include tables that you simply may share, you’ll be able to cease new tables from having the default grant of Tremendous to IAMAllowedPrincipals. Full the next steps:

Register to the Lake Formation console as a knowledge lake admin.
Within the navigation pane, underneath Knowledge Catalog, select Databases.
Choose the database customerdb.
Select Actions, then select Edit.
Below Default permissions for newly created tables, deselect Use solely IAM entry management for brand spanking new tables on this database.
Select Save.

For Knowledge Catalog databases, take away IAMAllowedPrincipals.

Below Knowledge Catalog within the navigation pane, select Databases.
Choose the database customerdb.
Select Actions, then select View.
Choose IAMAllowedPrincipals and select Revoke.

Repeat the identical steps for tables underneath the customerdb database.

Grant database permissions to IAM Identification Middle teams

Full the next steps to grant database permissions to your IAM Identification Middle teams:

On the Lake Formation console, underneath Knowledge Catalog, select Databases.
Choose the database customerdb.
Select Actions, then select Grant.
Choose IAM Identification Middle.
Select Add and choose Get Began.
Seek for and choose your IAM Identification Middle group names and select Assign.

Choose Named Knowledge Catalog sources.
Below Databases, select customerdb.
Below Database permissions, choose Describe for Database permissions.
Select Grant.

Grant desk permissions to IAM Identification Middle teams

Within the following part, we are going to grant totally different permissions to our two IAM Identification Middle teams.

Column filter

We first add permissions to the group awssso-sales. This group may have entry to the customerdb database and have the ability to choose solely two columns and skim all rows.

On the Lake Formation console, underneath Knowledge Catalog within the navigation pane, select Databases.
Choose the database customerdb.
Select Actions, then select Grant.
Choose IAM Identification Middle.
Select Add and choose Get Began.
Seek for and choose awssso-sales and select Assign.

Choose Named Knowledge Catalog sources.
Below Databases, select customerdb.
Below Tables, select evaluations.
Below Desk permissions, choose Choose for Desk permissions.
Choose Column-based entry.
Choose Embrace columns and select product_title and star_rating.
Select Grant.

Row filter

Subsequent, we grant permissions to awssso-finance. This group may have entry to customerdb and have the ability to choose all columns and apply filters on rows.

We have to first create a knowledge filter by performing the next steps:

On the Lake Formation console, select Knowledge filters underneath Knowledge Catalog.
Select Create information filter.
For Knowledge filter title, present a reputation.
For Goal database, select customerdb.
For Goal desk, select evaluations.
For Column-level entry, choose Entry to all columns.
For Row-level entry, select Filter rows and apply your filter. On this instance, we’re filtering evaluations with star_rating as 5.
Select Create information filter.

Below Knowledge Catalog within the navigation pane, select Databases.
Choose the database customerdb.
Select Actions, then select Grant.
Choose IAM Identification Middle.
Select Add and choose Get Began.
Seek for and choose awssso-finance and select Assign.
Choose Named Knowledge Catalog sources.
Below Databases, select customerdb.
Below Tables, select evaluations.
Below Knowledge Filters, select the High_Rating
Below Knowledge Filter permissions, choose Choose.
Select Grant.

Member account 1 configuration

On this part, we stroll via the steps so as to add Amazon Redshift Spectrum desk entry in member account 1, the place the Redshift cluster and software exist.

Settle for Invite from RAM

You need to have acquired a Useful resource Entry Supervisor (RAM) invite from member account 2 whenever you added member account 1 underneath IAM Identification Middle integration in Lake Formation on the member account 1.

Navigate to Useful resource Entry Supervisor(RAM) from admin console.
Below Shared with me, click on on useful resource shares.
Choose the useful resource title and click on on Settle for useful resource share.

Please just remember to have adopted this complete weblog to determine the Redshift Integration with IAM Identification Middle earlier than following the subsequent steps.

Arrange Redshift Spectrum desk entry for the IAM Identification Middle group

Full the next steps to arrange Redshift Spectrum desk entry:

Register to the Amazon Redshift console utilizing the admin function.
Navigate to Question Editor v2.
Select the choices menu (three dots) subsequent to the cluster and select Create connection.
Join because the admin consumer and run the next instructions to make the shared useful resource hyperlink information within the S3 information lake accessible to the gross sales group (use the account ID the place the Knowledge Catalog exists):

create exterior schema if not exists <schema_name> from DATA CATALOG database '<glue_catalog_name>' catalog_id '<accountid>';
grant utilization on schema <schema_name> to function "<role_name>";

For instance:

create exterior schema if not exists cross_account_glue_schema from DATA CATALOG database 'customerdb' catalog_id '932880906720';
grant utilization on schema cross_account_glue_schema to function "awsidc:awssso-sales";
grant utilization on schema cross_account_glue_schema to function "awsidc:awssso-finance";

Validate Redshift Spectrum entry as an IAM Identification Middle consumer

Full the next steps to validate entry:

On the Amazon Redshift console, navigate to Question Editor v2.
Select the choices menu (three dots) subsequent to the cluster and select Create connection.
Choose IAM Identification Middle.
Enter your Okta consumer title and password within the browser pop-up.

While you’re related as a federated consumer, run the next SQL instructions to question the cross_account_glue_schema information lake desk.

choose * from "dev"."cross_account_glue_schema"."evaluations";

The next screenshot exhibits that consumer Ethan, who’s a part of the awssso-sales group, has entry to 2 columns and all rows from the Knowledge Catalog.

The next screenshot exhibits that consumer Frank, who’s a part of the awssso-finance group, has entry to all columns for data which have star_rating as 5.

The next screenshot exhibits that consumer Brian, who’s a part of awssso-sales and awssso-finance, has entry to all columns for data which have star_rating as 5 and entry to solely two columns (different columns are returned NULL) for data with star_rating aside from 5.

Subscribe to QuickSight with IAM Identification Middle

On this publish, we arrange QuickSight in the identical account the place the Redshift cluster exists. You need to use the identical or a distinct member account for QuickSight setup. To subscribe to QuickSight, full the next steps:

Register to your AWS account and open the QuickSight console.
Select Join QuickSight.

Enter a notification e mail tackle for the QuickSight account proprietor or group. This e mail tackle will obtain service and utilization notifications.
Choose the id choice that you simply wish to subscribe with. For this publish, we choose Use AWS IAM Identification Middle.
Enter a QuickSight account title.
Select Configure.

Subsequent, assign teams in IAM Identification Middle to roles in QuickSight (admin, creator, and reader.) This step permits your customers to entry the QuickSight software. On this publish, we select awssso-sales and awssso-finance for Admin group.
Specify an IAM function to regulate QuickSight entry to your AWS sources. On this publish, we choose Use QuickSight managed function (default).
For this publish, we deselect Add Paginated Reviews.
Assessment the alternatives that you simply made, then select End.

Allow trusted id propagation in QuickSight

Trusted id propagation authenticates the end-user in Amazon Redshift once they entry QuickSight belongings that use a trusted id propagation enabled information supply. When an creator creates a knowledge supply with trusted id propagation, the id of the information supply shoppers in QuickSight is propagated and logged in AWS CloudTrail. This enables database directors to centrally handle information safety in Amazon Redshift and robotically apply information safety guidelines to information shoppers in QuickSight.

To configure QuickSight to connect with Amazon Redshift information sources with trusted id propagation, configure Amazon Redshift OAuth scopes to your QuickSight account:

aws quicksight update-identity-propagation-config --aws-account-id "AWSACCOUNTID" --service "REDSHIFT" --authorized-targets "IAM Identification Middle managed software ARN"

For instance:

aws quicksight update-identity-propagation-config --aws-account-id "1234123123" --service "REDSHIFT" --authorized-targets "arn:aws:sso::XXXXXXXXXXXX:software/ssoins-XXXXXXXXXXXX/apl-XXXXXXXXXXXX"

After you will have added the scope, the next command lists all OAuth scopes which might be at present on a QuickSight account:

aws quicksight list-identity-propagation-configs --aws-account-id "AWSACCOUNTID"

The next code is the instance with output:

aws quicksight list-identity-propagation-configs --aws-account-id "1234123123"
{
"Standing": 200,
"Providers": [
{
"Service": "REDSHIFT",
"AuthorizedTargets": [
"arn:aws:sso::1004123000:application/ssoins-1234f1234bb1f123/apl-12a1234e2e391234"
]
}
],
"RequestId": "116ec1b0-1533-4ed2-b5a6-d7577e073b35"
}

For extra info, confer with Authorizing connections from Amazon QuickSight to Amazon Redshift clusters.

For QuickSight to connect with a Redshift occasion, you should add an acceptable IP tackle vary within the Redshift safety group for the particular AWS Area. For extra info, see AWS Areas, web sites, IP tackle ranges, and endpoints.

Check your IAM Identification Middle and Amazon Redshift integration with QuickSight

Now you’re prepared to connect with Amazon Redshift utilizing QuickSight.

Within the administration account, open the IAM Identification Middle console and replica the AWS entry portal URL from the dashboard.
Signal out from the administration account and enter the AWS entry portal URL in a brand new browser window.
Within the pop-up window, enter your IdP credentials.

On the Purposes tab, choose the QuickSight app.
After you federate to QuickSight, select Datasets.
Choose New Dataset after which select Redshift (Auto Found).
Enter your information supply particulars. Ensure to pick out Single sign-on for Authentication methodology.
Select Create information supply.

Congratulations! You’re signed in utilizing IAM Identification Middle integration with Amazon Redshift and are able to discover and analyze your information utilizing QuickSight.

The next screenshot from QuickSight exhibits that consumer Ethan, who’s a part of the awssso-sales group, has entry to 2 columns and all rows from the Knowledge Catalog.

The next screenshot from QuickSight exhibits that consumer Frank, who’s a part of the awssso-finance group, has entry to all columns for data which have star_rating as 5.

The next screenshot from QuickSight exhibits that consumer Brian, who’s a part of awssso-sales and awssso-finance, has entry to all columns for data which have star_rating as 5 and entry to solely two columns (different columns are returned NULL) for data with star_rating aside from 5.

Clear up

Full the next steps to scrub up your sources:

Delete the information from the S3 bucket.
Delete the Knowledge Catalog objects that you simply created as a part of this publish.
Delete the Lake Formation sources and QuickSight account.
If you happen to created new Redshift cluster for testing this answer, delete the cluster.

Conclusion

On this publish, we established cross-account entry to allow centralized consumer authentication via IAM Identification Middle within the administration account, whereas retaining the Amazon Redshift and AWS Glue sources remoted by enterprise unit in separate member accounts. We used Question Editor V2 for querying the information from Amazon Redshift. Then we confirmed easy methods to construct user-facing dashboards by integrating with QuickSight. Consult with Combine Tableau and Okta with Amazon Redshift utilizing AWS IAM Identification Middle to find out about integrating Tableau and Okta with Amazon Redshift utilizing IAM Identification Middle.

Be taught extra about IAM Identification Middle with Amazon Redshift, QuickSight, and Lake Formation. Depart your questions and suggestions within the feedback part.

In regards to the Authors

Srividya Parthasarathy is a Senior Large Knowledge Architect on the AWS Lake Formation crew. She enjoys constructing information mesh options and sharing them with the neighborhood.

Maneesh Sharma is a Senior Database Engineer at AWS with greater than a decade of expertise designing and implementing large-scale information warehouse and analytics options. He collaborates with numerous Amazon Redshift Companions and clients to drive higher integration.

Poulomi Dasgupta is a Senior Analytics Options Architect with AWS. She is obsessed with serving to clients construct cloud-based analytics options to unravel their enterprise issues. Outdoors of labor, she likes travelling and spending time together with her household.

Arrange cross-account AWS Glue Knowledge Catalog entry utilizing AWS Lake Formation and AWS IAM Identification Middle with Amazon Redshift and Amazon QuickSight