Arrange cross-account AWS Glue Information Catalog entry utilizing AWS Lake Formation and AWS IAM Id Heart with Amazon Redshift and Amazon QuickSight

[ad_1]

Most organizations handle their workforce id centrally in exterior id suppliers (IdPs) and are comprised of a number of enterprise items that produce their very own datasets and handle the lifecycle unfold throughout a number of AWS accounts. These enterprise items 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 items 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 flexibility to audit entry to their information merchandise. To handle this, enterprises normally catalog the datasets within the AWS Glue Information 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 items. Given the various vary of providers, fine-grained information sharing, and personas concerned, these enterprises usually desire a streamlined expertise for enterprise consumer identities when accessing their information utilizing AWS Analytics providers.

AWS IAM Id Heart permits centralized administration of workforce consumer entry to AWS accounts and functions 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 Id Heart, permitting you to make use of third-party IdPs equivalent 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 providers. 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 Id and Entry Administration (IAM) position to entry information. Moreover, this allows efficient consumer permissions primarily based on collective group membership and helps group hierarchy.

On this put up, we cowl how one can allow trusted id propagation with AWS IAM Id Heart, 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 put up 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 items. Their objective is to allow centralized consumer authentication by IAM Id Heart within the administration account, whereas preserving the enterprise unit that analyzes information utilizing a Redshift cluster and the enterprise unit that produces information cataloged utilizing the Information Catalog in separate member accounts. This enables them to keep up a single authentication mechanism by IAM Id Heart inside a corporation whereas retaining entry management, useful resource, and value separation by the usage of separate AWS accounts per enterprise items and enabling cross-account information sharing utilizing Lake Formation.

For this answer, AWS Organizations is enabled within the central administration account and IAM Id Heart is configured for managing workforce identities. The group has two member accounts: one account that manages the S3 information lake utilizing the Information Catalog, and one other account that runs analytical workloads on Amazon Redshift and QuickSight, with all of the providers enabled with trusted id propagation. Amazon Redshift will entry cross-account AWS Glue sources utilizing IAM Id Heart 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 Id Heart.
Member account 1 (Redshift_Member_Account) is the place the Redshift cluster and utility exist.
Member account 2 (Glue_Member_Account) is the place metadata is cataloged within the Information Catalog and Lake Formation is enabled with IAM Id Heart integration.
We assign permissions to 2 IAM Id Heart teams to entry the Information Catalog sources:
- awssso-sales – We apply column-level filtering for this group in order that customers belonging to this group will be capable of choose two columns and browse all rows.
- awssso-finance – We apply row-level filtering utilizing information filters for this group in order that customers belonging to this group will be capable of choose all columns and see rows after row-level filtering is utilized.
We apply completely different permissions for 3 IAM Id Heart customers:
- Person Ethan, a part of awssso-sales – Ethan will be capable of choose two columns and browse all rows.
- Person Frank, a part of awssso-finance – Frank will be capable of choose all columns and see rows after row-level filtering is utilized.
- Person 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 Id Heart.

Stipulations

You must have the next stipulations alreday arrange:

Member account 2 configuration

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

On this part, we stroll by the steps to arrange Lake Formation, allow Lake Formation permissions, and grant database and desk permissions to IAM Id Heart teams.

Arrange Lake Formation

Full the steps on this part to arrange Lake Formation.

Create AWS Glue sources

You should use an current AWS Glue database that has just a few tables. For this put up, we use a database known as customerdb and a desk known as opinions 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, beneath Administration, select Information lake areas.
Select Register location.
For Amazon S3 location, enter the S3 bucket location that comprises desk information.
For IAM position, present a user-defined IAM position. For directions to create a user-defined IAM position, check with Necessities for roles used to register areas.
For Permission mode, choose Lake Formation.
Select Register location.

Set cross-account model

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

Check in to the Lake Formation console as the information lake admin.
Within the navigation pane, beneath Administration, select Information Catalog settings.
Beneath Cross-account model settings, hold the most recent model (Model 4) as the present cross-account model.
Select Save.

Add permissions required for cross-account entry

If the AWS Glue Information Catalog useful resource coverage is already enabled within the account, then you possibly can both take away the coverage or add the next permissions to the coverage which can be required for cross-account grants. The offered 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, check with Stipulations. Please skip to the next step in case your coverage is clean beneath Catalog Settings.

Check in to the AWS Glue console as an IAM admin.
Within the navigation pane, beneath Information Catalog, select Catalog settings.
Beneath 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 Id Heart integration for Lake Formation

To combine IAM Id Heart together with your Lake Formation group occasion of IAM Id Heart, check with Connecting Lake Formation with IAM Id Heart.

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

Check in to the Lake Formation console as a knowledge lake admin.
Within the navigation pane, beneath Administration, select IAM Id Heart integration.
Beneath AWS account and group IDs, select Add.
Enter your goal accounts.
Select Add.

Allow Lake Formation permissions for databases

For Information Catalog databases that include tables that you just may share, you possibly can cease new tables from having the default grant of Tremendous to IAMAllowedPrincipals. Full the next steps:

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

For Information Catalog databases, take away IAMAllowedPrincipals.

Beneath Information 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 beneath the customerdb database.

Grant database permissions to IAM Id Heart teams

Full the next steps to grant database permissions to your IAM Id Heart teams:

On the Lake Formation console, beneath Information Catalog, select Databases.
Choose the database customerdb.
Select Actions, then select Grant.
Choose IAM Id Heart.
Select Add and choose Get Began.
Seek for and choose your IAM Id Heart group names and select Assign.

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

Grant desk permissions to IAM Id Heart teams

Within the following part, we’ll grant completely different permissions to our two IAM Id Heart teams.

Column filter

We first add permissions to the group awssso-sales. This group can have entry to the customerdb database and be capable of choose solely two columns and browse all rows.

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

Choose Named Information Catalog sources.
Beneath Databases, select customerdb.
Beneath Tables, select opinions.
Beneath 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 can have entry to customerdb and be capable of 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 Information filters beneath Information Catalog.
Select Create information filter.
For Information filter identify, present a reputation.
For Goal database, select customerdb.
For Goal desk, select opinions.
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 opinions with star_rating as 5.
Select Create information filter.

Beneath Information Catalog within the navigation pane, select Databases.
Choose the database customerdb.
Select Actions, then select Grant.
Choose IAM Id Heart.
Select Add and choose Get Began.
Seek for and choose awssso-finance and select Assign.
Choose Named Information Catalog sources.
Beneath Databases, select customerdb.
Beneath Tables, select opinions.
Beneath Information Filters, select the High_Rating
Beneath Information Filter permissions, choose Choose.
Select Grant.

Member account 1 configuration

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

Settle for Invite from RAM

You must have acquired a Useful resource Entry Supervisor (RAM) invite from member account 2 while you added member account 1 beneath IAM Id Heart integration in Lake Formation on the member account 1.

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

Please just remember to have adopted this complete weblog to ascertain the Redshift Integration with IAM Id Heart earlier than following the following steps.

Arrange Redshift Spectrum desk entry for the IAM Id Heart group

Full the next steps to arrange Redshift Spectrum desk entry:

Check in to the Amazon Redshift console utilizing the admin position.
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 Information 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 position "<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 position "awsidc:awssso-sales";
grant utilization on schema cross_account_glue_schema to position "awsidc:awssso-finance";

Validate Redshift Spectrum entry as an IAM Id Heart 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 Id Heart.
Enter your Okta consumer identify and password within the browser pop-up.

While you’re linked 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"."opinions";

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

The next screenshot reveals 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 reveals 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 apart from 5.

Subscribe to QuickSight with IAM Id Heart

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

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

Enter a notification e mail handle for the QuickSight account proprietor or group. This e mail handle will obtain service and utilization notifications.
Choose the id choice that you just need to subscribe with. For this put up, we choose Use AWS IAM Id Heart.
Enter a QuickSight account identify.
Select Configure.

Subsequent, assign teams in IAM Id Heart to roles in QuickSight (admin, writer, and reader.) This step permits your customers to entry the QuickSight utility. On this put up, we select awssso-sales and awssso-finance for Admin group.
Specify an IAM position to regulate QuickSight entry to your AWS sources. On this put up, we choose Use QuickSight managed position (default).
For this put up, we deselect Add Paginated Reviews.
Assessment the alternatives that you just 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 writer 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 mechanically 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 Id Heart managed utility ARN"

For instance:

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

After you may have added the scope, the next command lists all OAuth scopes which can 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, check with Authorizing connections from Amazon QuickSight to Amazon Redshift clusters.

For QuickSight to connect with a Redshift occasion, it’s essential to add an applicable IP handle vary within the Redshift safety group for the precise AWS Area. For extra info, see AWS Areas, web sites, IP handle ranges, and endpoints.

Take a look at your IAM Id Heart and Amazon Redshift integration with QuickSight

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

Within the administration account, open the IAM Id Heart 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. Be sure that to pick out Single sign-on for Authentication methodology.
Select Create information supply.

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

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

The next screenshot from QuickSight reveals 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 reveals 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 apart from 5.

Clear up

Full the next steps to scrub up your sources:

Delete the information from the S3 bucket.
Delete the Information Catalog objects that you just created as a part of this put up.
Delete the Lake Formation sources and QuickSight account.
In the event you created new Redshift cluster for testing this answer, delete the cluster.

Conclusion

On this put up, we established cross-account entry to allow centralized consumer authentication by IAM Id Heart within the administration account, whereas preserving 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 how one can construct user-facing dashboards by integrating with QuickSight. Discuss with Combine Tableau and Okta with Amazon Redshift utilizing AWS IAM Id Heart to find out about integrating Tableau and Okta with Amazon Redshift utilizing IAM Id Heart.

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

Concerning the Authors

Srividya Parthasarathy is a Senior Huge Information Architect on the AWS Lake Formation group. She enjoys constructing information mesh options and sharing them with the group.

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 prospects to drive higher integration.

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

[ad_2]