Combine Okta with Amazon Redshift Question Editor V2 utilizing AWS IAM Id Heart for seamless Single Signal-On

Big Data

Combine Okta with Amazon Redshift Question Editor V2 utilizing AWS IAM Id Heart for seamless Single Signal-On

lohitnath.453

November 30, 2023

Combine Okta with Amazon Redshift Question Editor V2 utilizing AWS IAM Id Heart for seamless Single Signal-On

[ad_1]

AWS IAM Id Heart (IdC) permits you to handle single sign-on (SSO) entry to all of your AWS accounts and functions from a single location. We’re happy to announce that Amazon Redshift now integrates with AWS IAM Id Heart, and helps trusted identification propagation, permitting you to make use of third-party Id Suppliers (IdP) akin to Microsoft Entra ID (Azure AD), Okta, Ping, and OneLogin. This integration simplifies the authentication and authorization course of for Amazon Redshift customers utilizing Question Editor V2 or Amazon Quicksight, making it simpler for them to securely entry your knowledge warehouse. Moreover, this integration positions Amazon Redshift as an IdC-managed software, enabling you to make use of database role-based entry management in your knowledge warehouse for enhanced safety.

AWS IAM Id Heart gives computerized consumer and group provisioning from Okta to itself by using the System for Cross-domain Id Administration (SCIM) 2.0 protocol. This integration permits for seamless synchronization of data between two companies, making certain correct and up-to-date data in AWS IAM Id Heart.

On this put up, we’ll define a complete information for establishing SSO to Amazon Redshift utilizing integration with IdC and Okta because the Id Supplier. This information reveals how one can SSO onto Amazon Redshift for Amazon Redshift Question Editor V2 (QEV2).

Answer overview

Utilizing IAM IdC with Amazon Redshift can profit your group within the following methods:

Customers can hook up with Amazon Redshift with out requiring an administrator to arrange AWS IAM roles with complicated permissions.
IAM IdC integration permits mapping of IdC teams with Amazon Redshift database roles. Directors can then assign totally different privileges to totally different roles and assigning these roles to totally different customers, giving organizations granular management for consumer entry.
IdC gives a central location on your customers in AWS. You may create customers and teams instantly in IdC or join your current customers and teams that you simply handle in a standards-based identification supplier like Okta, Ping Id, or Microsoft Entra ID (i.e., Azure Lively Listing [AD]).
IdC directs authentication to your chosen supply of reality for customers and teams, and it maintains a listing of customers and teams for entry by Amazon Redshift.
You may share one IdC occasion with a number of Amazon Redshift knowledge warehouses with a easy auto-discovery and join functionality. This makes it quick so as to add clusters with out the additional effort of configuring the IdC connection for every, and it ensures that each one clusters and workgroups have a constant view of customers, their attributes, and teams. Word: Your group’s IdC occasion have to be in the identical area because the Amazon Redshift knowledge warehouse you’re connecting to.
As a result of consumer identities are recognized and logged together with knowledge entry, it’s simpler so that you can meet compliance rules by means of auditing consumer entry in AWS CloudTrail authorizes entry to knowledge.

Amazon Redshift Question Editor V2 workflow:

Finish consumer initiates the move utilizing AWS entry portal URL (this URL could be accessible on IdC dashboard console). A browser pop-up triggers and takes you to the Okta Login web page the place you enter Okta credentials. After profitable authentication, you’ll be logged into the AWS Console as a federated consumer. Click on in your AWS Account and select the Amazon Redshift Question Editor V2 software. When you federate to Question Editor V2, choose the IdC authentication technique.
QEv2 invokes browser move the place you re-authenticate, this time with their AWS IdC credentials. Since Okta is the IdP, you enter Okta credentials, that are already cached in browser. At this step, federation move with IdC initiates and on the finish of this move, the Session token and Entry token is offered to the QEv2 console in browser as cookies.
Amazon Redshift retrieves your authorization particulars based mostly on session token retrieved and fetches consumer’s group membership.
Upon a profitable authentication, you’ll be redirected again to QEV2, however logged in as an IdC authenticated consumer.

This answer covers following steps:

Combine Okta with AWS IdC to sync consumer and teams.
Organising IdC integration with Amazon Redshift
Assign Customers or Teams from IdC to Amazon Redshift Utility.
Allow IdC integration for a brand new Amazon Redshift provisioned or Amazon Redshift Serverless endpoint.
Affiliate an IdC software with an current provisioned or serverless knowledge warehouse.
Configure Amazon Redshift role-based entry.
Create a permission set.
Assign permission set to AWS accounts.
Federate to Redshift Question Editor V2 utilizing IdC.
Troubleshooting

Stipulations

Walkthrough

Combine Okta with AWS IdC to sync consumer and teams

Allow group and consumer provisioning from Okta with AWS IdC by following this documentation right here.

Should you see points whereas syncing customers and teams, then consult with this part these issues for utilizing computerized provisioning.

Organising IAM IdC integration with Amazon Redshift

Amazon Redshift cluster administrator or Amazon Redshift Serverless administrator should carry out steps to configure Redshift as an IdC-enabled software. This allows Amazon Redshift to find and hook up with the IdC robotically to obtain sign-in and consumer listing companies.

After this, when the Amazon Redshift administrator creates a cluster or workgroup, they’ll allow the brand new knowledge warehouse to make use of IdC and its identity-management capabilities. The purpose of enabling Amazon Redshift as an IdC-managed software is so you’ll be able to management consumer and group permissions from inside the IdC, or from a supply third-party identification supplier that’s built-in with it.

When your database customers sign up to an Amazon Redshift database, for instance an analyst or a knowledge scientist, it checks their teams in IdC and these are mapped to roles in Amazon Redshift. On this method, a gaggle can map to an Amazon Redshift database position that enables learn entry to a set of tables.

The next steps present make Amazon Redshift an AWS-managed software with IdC:

Choose IAM Id Heart connection from Amazon Redshift console menu.
Select Create software
The IAM Id Heart connection opens. Select Subsequent.
In IAM Id Heart integration setup part, for:
1. IAM Id Heart show identify – Enter a singular identify for Amazon Redshift’s IdC-managed software.
2. Managed software identify – You may enter the managed Amazon Redshift software identify or use the assigned worth as it’s.
In Reference to third-party identification suppliers part, for:
1. Id Supplier Namespace – Specify the distinctive namespace on your group. That is usually an abbreviated model of your group’s identify. It’s added as a prefix on your IdC-managed customers and roles within the Amazon Redshift database.
In IAM position for IAM Id Heart entry – Choose an IAM position to make use of. You may create a brand new IAM position in the event you don’t have an current one. The particular coverage permissions required are the next:

- sso:DescribeApplication – Required to create an identification supplier (IdP) entry within the catalog.
- sso:DescribeInstance – Used to manually create IdP federated roles or customers.
- redshift:DescribeQev2IdcApplications – Used to detect functionality for IDC authentication from Redshift Question Editor V2.

The next screenshot is from the IAM position:

We gained’t allow Trusted identification propagation as a result of we aren’t integrating with AWS Lake Formation on this put up.
Select Subsequent.
In Configure shopper connections that use third-party IdPs part, select Sure if you wish to join Amazon Redshift with a third-party software. In any other case, select No. For this put up we selected No as a result of we’ll be integrating solely with Amazon Redshift Question Editor V2.
Select Subsequent.
Within the Evaluation and create software part, evaluate all the small print you could have entered earlier than and select Create software.

After the Amazon Redshift administrator finishes the steps and saves the configuration, the IdC properties seem within the Redshift Console. Finishing these duties makes Redshift an IdC-enabled software.

After you choose the managed software identify, the properties within the console contains the mixing standing. It says Success when it’s accomplished. This standing signifies if IdC configuration is accomplished.

Assigning customers or teams from IdC to Amazon Redshift software

On this step, Customers or teams synced to your IdC listing can be found to assign to your software the place the Amazon Redshift administrator can determine which customers or teams from IDC should be included as a part of Amazon Redshift software.

For instance, when you have whole 20 teams out of your IdC and also you don’t need all of the teams to incorporate as a part of Amazon Redshift software, then you could have choices to decide on which IdC teams to incorporate as a part of Amazon Redshift-enabled IdC software. Later, you’ll be able to create two Redshift database roles as a part of IDC integration in Amazon Redshift.

The next steps assign teams to Amazon Redshift-enabled IdC software:

On IAM Id Heart properties within the Amazon Redshift Console, choose Assign underneath Teams tab.
If that is the primary time you’re assigning teams, then you definately’ll see a notification. Choose Get began.
Enter which teams you wish to synchronize within the software. On this instance, we selected the teams wssso-sales and awssso-finance.
Select Completed.

Enabling IdC integration for a brand new Amazon Redshift provisioned cluster or Amazon Redshift Serverless

After finishing steps underneath part (Organising IAM Id Heart integration with Amazon Redshift) — Amazon Redshift database administrator must configure new Redshift assets to work in alignment with IdC to make sign-in and knowledge entry simpler. That is carried out as a part of the steps to create a provisioned cluster or a Serverless workgroup. Anybody with permissions to create Amazon Redshift assets can carry out these IdC integration duties. If you create a provisioned cluster, you begin by selecting Create Cluster within the Amazon Redshift Console.

Select Allow for the cluster (advisable) within the part for IAM Id Heart connection within the create-cluster steps.
From the drop down, select the redshift software which you created in above steps.

Word that when a brand new knowledge warehouse is created, the IAM position specified for IdC integration is robotically connected to the provisioned cluster or Serverless Namespace. After you end getting into the required cluster metadata and create the useful resource, you’ll be able to test the standing for IdC integration within the properties.

Associating an IdC software with an current provisioned cluster or Serverless endpoint

If in case you have an current provisioned cluster or serverless workgroup that you simply wish to allow for IdC integration, then you are able to do that by working a SQL command. You run the next command to allow integration. It’s required {that a} database administrator run the question.

CREATE IDENTITY PROVIDER "<idc_application_name>" TYPE AWSIDC
NAMESPACE '<idc_namespace_name>'
APPLICATION_ARN '<idc_application_arn>'
IAM_ROLE '<IAM position for IAM Id Heart entry>';

Instance:

CREATE IDENTITY PROVIDER "redshift-idc-app" TYPE AWSIDC
NAMESPACE 'awsidc'
APPLICATION_ARN 'arn:aws:sso::123456789012:software/ssoins-12345f67fe123d4/apl-a0b0a12dc123b1a4'
IAM_ROLE 'arn:aws:iam::123456789012:position/EspressoRedshiftRole';

To change the IdP, use the next command (this new set of parameter values utterly replaces the present values):

ALTER IDENTITY PROVIDER "<idp_name>"
NAMESPACE '<idc_namespace_name>'|
IAM_ROLE default | 'arn:aws:iam::<AWS account-id-1>:position/<role-name>';

Few of the examples are:

ALTER IDENTITY PROVIDER "redshift-idc-app"
NAMESPACE 'awsidctest';

ALTER IDENTITY PROVIDER "redshift-idc-app"
IAM_ROLE 'arn:aws:iam::123456789012:position/administratoraccess';

Word: Should you replace the idc-namespace worth, then all the brand new cluster created afterwards will likely be utilizing the up to date namespace.

For current clusters or serverless workgroups, you’ll want to replace the namespace manually on every Amazon Redshift cluster utilizing the earlier command. Additionally, all of the database roles related to identification supplier will likely be up to date with new namespace worth.

You may disable or allow the identification supplier utilizing the next command:

ALTER IDENTITY PROVIDER <provider_name> disable|allow;

Instance:

ALTER IDENTITY PROVIDER <provider_name> disable;

You may drop an current identification supplier. The next instance reveals how CASCADE deletes customers and roles connected to the identification supplier.

DROP IDENTITY PROVIDER <provider_name> [ CASCADE ]

Configure Amazon Redshift role-based entry

On this step, we pre-create the database roles in Amazon Redshift based mostly on the teams that you simply synced in IdC. Be sure the position identify matches with the IdC Group identify.

Amazon Redshift roles simplify managing privileges required on your end-users. On this put up, we create two database roles, gross sales and finance, and grant them entry to question tables with gross sales and finance knowledge, respectively. You may obtain this pattern SQL Pocket book and import into Redshift Question Editor v2 to run all cells within the pocket book used on this instance. Alternatively, you’ll be able to copy and enter the SQL into your SQL shopper.

Under is the syntax to create position in Amazon Redshift:

create position "<IDC_Namespace>:<IdP groupname>;

For instance:

create position "awsidc:awssso-sales";
create position "awsidc:awssso-finance";

Create the gross sales and finance database schema:

create schema sales_schema;
create schema finance_schema;

Creating the tables:

CREATE TABLE IF NOT EXISTS finance_schema.income
(
account INTEGER   ENCODE az64
,buyer VARCHAR(20)   ENCODE lzo
,salesamt NUMERIC(18,0)   ENCODE az64
)
DISTSTYLE AUTO
;

insert into finance_schema.income values (10001, 'ABC Firm', 12000);
insert into finance_schema.income values (10002, 'Tech Logistics', 175400);
insert into finance_schema.income values (10003, 'XYZ Trade', 24355);
insert into finance_schema.income values (10004, 'The tax consultants', 186577);

CREATE TABLE IF NOT EXISTS sales_schema.store_sales
(
ID INTEGER   ENCODE az64,
Product varchar(20),
Sales_Amount INTEGER   ENCODE az64
)
DISTSTYLE AUTO
;

Insert into sales_schema.store_sales values (1,'product1',1000);
Insert into sales_schema.store_sales values (2,'product2',2000);
Insert into sales_schema.store_sales values (3,'product3',3000);
Insert into sales_schema.store_sales values (4,'product4',4000);

Under is the syntax to grant permission to the Amazon Redshift Serverless position:

GRANT  ALL [ PRIVILEGES ]  ON  ALL TABLES IN SCHEMA schema_name [, ...]  TO position <IdP groupname>;

Grant related permission to the position as per your requirement. In following instance, we grant full permission to position gross sales on sales_schema and solely choose permission on finance_schema to position finance.

For instance:

grant utilization on schema sales_schema to position "awsidc:awssso-sales";
grant choose on all tables in schema sales_schema to position "awsidc:awssso-sales";

grant utilization on schema finance_schema to position "awsidc:awssso-finance";
grant choose on all tables in schema finance_schema to position "awsidc:awssso-finance";

Create a permission set

A permission set is a template that you simply create and preserve that defines a group of a number of IAM insurance policies. Permission units simplify the task of AWS account entry for customers and teams in your group. We’ll create a permission set to permit federated consumer to entry Question Editor V

The next steps to create permission set:

Open the IAM Id Heart Console.
Within the navigation pane, underneath Multi-Account permissions, select Permission units.
Select Create permission set.
Select Customized permission set after which select Subsequent.
Below AWS managed insurance policies, select AmazonRedshiftQueryEditorV2ReadSharing.
Below Buyer managed insurance policies, present the coverage identify which you created in step 4 underneath part – Organising IAM Id Heart integration with Amazon Redshift.
Select Subsequent.
Enter permission set identify. For instance, Amazon Redshift-Question-Editor-V2.
Below Relay state – elective – set default relay state to the Question Editor V2 URL, utilizing the format : https://<area>.console.aws.amazon.com/sqlworkbench/dwelling.
For this put up, we use: https://us-east-1.console.aws.amazon.com/sqlworkbench/dwelling.
Select Subsequent.
On the Evaluation and create display screen, select Create. The console shows the next message: The permission set Redshift-Question-Editor-V2 was efficiently created.

Assign permission set to AWS accounts

Open the IAM Id Heart Console.
Within the navigation pane, underneath Multi-account permissions, select AWS accounts.
On the AWS accounts web page, choose a number of AWS accounts that you simply wish to assign single sign-on entry to.
Select Assign customers or teams.
On the Assign customers and teams to AWS-account-name, select the teams that you simply wish to create the permission set for. Then, select Subsequent.
On the Assign permission units to AWS-account-name, select the permission set you created within the part – Create a permission set. Then, select Subsequent.
On the Evaluation and submit assignments to AWS-account-name web page, for Evaluation and submit, select Submit. The console shows the next message: We reprovisioned your AWS account efficiently and utilized the up to date permission set to the account.

Federate to Amazon Redshift utilizing Question Editor V2 utilizing IdC

Now you’re prepared to hook up with Amazon Redshift Question Editor V2 and federated login utilizing IdC authentication:

Open the IAM Id Heart Console.
Go to dashboard and choose the AWS entry portal URL.
A browser pop-up triggers and takes you to the Okta Login web page the place you enter your Okta credentials.
After profitable authentication, you’ll be logged into the AWS console as a federated consumer.
Choose your AWS Account and select the Amazon Redshift Question Editor V2 software.
When you federate to Question Editor V2, select your Redshift occasion (i.e., right-click) and select Create connection.
To authenticate utilizing IdC, select the authentication technique IAM Id Heart.
It’ll present a pop-up and since your Okta credentials is already cached, it makes use of the identical credentials and connects to Amazon Redshift Question Editor V2 utilizing IdC authentication.

The next demonstration reveals a federated consumer (Ethan) used the AWS entry portal URL to entry Amazon Redshift utilizing IdC authentication. Consumer Ethan accesses the sales_schema tables. If Consumer Ethan tries to entry the tables in finance_schema, then the consumer will get a permission denied error.

Troubleshooting

Should you get the next error:

ERROR: registered identification supplier doesn't exist for "<idc-namespace:redshift_database_role_name"

Which means you are attempting to create a job with a improper namespace. Please test present namespace utilizing the command choose * from identity_providers;

Should you get under error:

ERROR: (arn:aws:iam::123456789012:position/<iam_role_name>) Inadequate privileges to entry AWS IdC. [ErrorId: 1-1234cf25-1f23ea1234c447fb12aa123e]

Which means an IAM position doesn’t have ample privileges to entry to the IdC. Your IAM position ought to comprise a coverage with following permissions:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "redshift:DescribeQev2IdcApplications",
            "Resource": "*"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "sso:DescribeApplication",
                "sso:DescribeInstance"
            ],
            "Useful resource": "arn:aws:sso::726034267621:software/ssoins-722324fe82a3f0b8/*"
        }
    ]
}

Should you get under error:

FATAL: Invalid scope. Consumer's credentials are usually not approved to hook up with Redshift. [SQL State=28000]

Please make it possible for the consumer and group are added to the Amazon Redshift IdC software.

Clear up

Full the next steps to scrub up your assets:

Delete the Okta Purposes which you could have created to combine with IdC.
Delete IAM Id Heart configuration.
Delete the Redshift software and the Redshift provisioned cluster which you could have created for testing.
Delete the IAM position which you could have created for IdC and Redshift integration.

Conclusion

On this put up, we confirmed you an in depth walkthrough of how one can combine Okta with the IdC and Amazon Redshift Question Editor model 2 to simplify your SSO setup. This integration permits you to use role-based entry management with Amazon Redshift. We encourage you to check out this integration.

To study extra about IdC with Amazon Redshift, go to the documentation.

Concerning the Authors

Debu Panda is a Senior Supervisor, Product Administration at AWS. He’s an business chief in analytics, software platform, and database applied sciences, and has greater than 25 years of expertise within the IT world.

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

Harshida Patel is a Principal Options Architect, Analytics with AWS.

Praveen Kumar Ramakrishnan is a Senior Software program Engineer at AWS. He has almost 20 years of expertise spanning numerous domains together with filesystems, storage virtualization and community safety. At AWS, he focuses on enhancing the Redshift knowledge safety.

Karthik Ramanathan is a Sr. Software program Engineer with AWS Redshift and is predicated in San Francisco. He brings near 20 years of growth expertise throughout the networking, knowledge storage and IoT verticals previous to Redshift. When not at work, he’s additionally a author and likes to be within the water.

[ad_2]