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

AWS IAM Id Middle (IdC) lets you handle single sign-on (SSO) entry to all of your AWS accounts and purposes from a single location. We’re happy to announce that Amazon Redshift now integrates with AWS IAM Id Middle, and helps trusted identification propagation, permitting you to make use of third-party Id Suppliers (IdP) equivalent 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 information warehouse. Moreover, this integration positions Amazon Redshift as an IdC-managed utility, enabling you to make use of database role-based entry management in your information warehouse for enhanced safety.

AWS IAM Id Middle provides automated 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 knowledge between two companies, guaranteeing correct and up-to-date data in AWS IAM Id Middle.

On this submit, we’ll define a complete information for organising 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 connect with Amazon Redshift with out requiring an administrator to arrange AWS IAM roles with advanced permissions.
• IAM IdC integration permits mapping of IdC teams with Amazon Redshift database roles. Directors can then assign completely different privileges to completely different roles and assigning these roles to completely different customers, giving organizations granular management for consumer entry.
• IdC gives a central location in your customers in AWS. You possibly can create customers and teams straight 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 Energetic 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 possibly can share one IdC occasion with a number of Amazon Redshift information 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 information warehouse you’re connecting to.
• As a result of consumer identities are identified and logged together with information entry, it’s simpler so that you can meet compliance rules via auditing consumer entry in AWS CloudTrail authorizes entry to information.
  

Amazon Redshift Question Editor V2 workflow:

1. Finish consumer initiates the move utilizing AWS entry portal URL (this URL could be out there 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 utility. When you federate to Question Editor V2, choose the IdC authentication methodology.
2. 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 obtainable to the QEv2 console in browser as cookies.
3. Amazon Redshift retrieves your authorization particulars based mostly on session token retrieved and fetches consumer’s group membership.
4. Upon a profitable authentication, you’ll be redirected again to QEV2, however logged in as an IdC authenticated consumer.

This resolution covers following steps:

1. Combine Okta with AWS IdC to sync consumer and teams.
2. Establishing IdC integration with Amazon Redshift
3. Assign Customers or Teams from IdC to Amazon Redshift Utility.
4. Allow IdC integration for a brand new Amazon Redshift provisioned or Amazon Redshift Serverless endpoint.
5. Affiliate an IdC utility with an current provisioned or serverless information warehouse.
6. Configure Amazon Redshift role-based entry.
7. Create a permission set.
8. Assign permission set to AWS accounts.
9. Federate to Redshift Question Editor V2 utilizing IdC.
10. Troubleshooting

Conditions

It’s best to have the next stipulations:

• An AWS account. If you happen to don’t have one, you possibly can join for one.
• A Redshift cluster. For setup directions, see Create a pattern Amazon Redshift cluster.
• Alternatively, you can use a Redshift Serverless endpoint. For setup directions, see Getting began with Amazon Redshift Serverless.
• An Okta account that has an lively subscription. You want an administrator function to arrange the applying on Okta. If you happen to’re new to Okta, then you possibly can join a free trial or join a developer account.

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.

If you happen to see points whereas syncing customers and teams, then discuss with this part these issues for utilizing automated provisioning.

Establishing 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 utility. This permits Amazon Redshift to find and connect with the IdC mechanically 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 information warehouse to make use of IdC and its identity-management capabilities. The purpose of enabling Amazon Redshift as an IdC-managed utility is so you possibly can management consumer and group permissions from throughout 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 function that enables learn entry to a set of tables.

The next steps present how you can make Amazon Redshift an AWS-managed utility with IdC:

1. Choose IAM Id Middle connection from Amazon Redshift console menu.
   
2. Select Create utility
   
   
3. The IAM Id Middle connection opens. Select Subsequent.
4. In IAM Id Middle integration setup part, for:
   1. IAM Id Middle show title – Enter a novel title for Amazon Redshift’s IdC-managed utility.
   2. Managed utility title – You possibly can enter the managed Amazon Redshift utility title or use the assigned worth as it’s.
5. In Reference to third-party identification suppliers part, for:
   1. Id Supplier Namespace – Specify the distinctive namespace in your group. That is sometimes an abbreviated model of your group’s title. It’s added as a prefix in your IdC-managed customers and roles within the Amazon Redshift database.
6. In IAM function for IAM Id Middle entry – Choose an IAM function to make use of. You possibly can create a brand new IAM function if you happen to 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 function:

7. We received’t allow Trusted identification propagation as a result of we’re not integrating with AWS Lake Formation on this submit.
8. Select Subsequent.
   
9. In Configure shopper connections that use third-party IdPs part, select Sure if you wish to join Amazon Redshift with a third-party utility. In any other case, select No. For this submit we selected No as a result of we’ll be integrating solely with Amazon Redshift Question Editor V2.
10. Select Subsequent.
    
11. Within the Assessment and create utility part, overview all the main points you could have entered earlier than and select Create utility.
    

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 utility.

After you choose the managed utility title, the properties within the console consists of 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 utility

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

For instance, if in case 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 utility, then you could have choices to decide on which IdC teams to incorporate as a part of Amazon Redshift-enabled IdC utility. Later, you possibly can create two Redshift database roles as a part of IDC integration in Amazon Redshift.

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

1. On IAM Id Middle properties within the Amazon Redshift Console, choose Assign below Teams tab.
2. If that is the primary time you’re assigning teams, then you definately’ll see a notification. Choose Get began.
3. Enter which teams you wish to synchronize within the utility. On this instance, we selected the teams wssso-sales and awssso-finance.
4. Select Carried out.
   

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

After finishing steps below part (Establishing IAM Id Middle integration with Amazon Redshift) — Amazon Redshift database administrator must configure new Redshift assets to work in alignment with IdC to make sign-in and information 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. Whenever you create a provisioned cluster, you begin by selecting Create Cluster within the Amazon Redshift Console.

1. Select Allow for the cluster (really helpful) within the part for IAM Id Middle connection within the create-cluster steps.
2. From the drop down, select the redshift utility which you created in above steps.

Word that when a brand new information warehouse is created, the IAM function specified for IdC integration is mechanically connected to the provisioned cluster or Serverless Namespace. After you end coming into the required cluster metadata and create the useful resource, you possibly can verify the standing for IdC integration within the properties.

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

You probably 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 operating 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 function for IAM Id Middle entry>';

Instance:

CREATE IDENTITY PROVIDER "redshift-idc-app" TYPE AWSIDC
NAMESPACE 'awsidc'
APPLICATION_ARN 'arn:aws:sso::123456789012:utility/ssoins-12345f67fe123d4/apl-a0b0a12dc123b1a4'
IAM_ROLE 'arn:aws:iam::123456789012:function/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>:function/<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:function/administratoraccess';

Word: If you happen to replace the idc-namespace worth, then all the brand new cluster created afterwards can be utilizing the up to date namespace.

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

You possibly can 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 possibly can 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 certain that the function title matches with the IdC Group title.

Amazon Redshift roles simplify managing privileges required in your end-users. On this submit, we create two database roles, gross sales and finance, and grant them entry to question tables with gross sales and finance information, respectively. You possibly can 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 possibly can copy and enter the SQL into your SQL shopper.

Beneath is the syntax to create function in Amazon Redshift:

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

For instance:

create function "awsidc:awssso-sales";
create function "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 specialists', 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);

Beneath is the syntax to grant permission to the Amazon Redshift Serverless function:

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

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

For instance:

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

grant utilization on schema finance_schema to function "awsidc:awssso-finance";
grant choose on all tables in schema finance_schema to function "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:

1. Open the IAM Id Middle Console.
2. Within the navigation pane, below Multi-Account permissions, select Permission units.
3. Select Create permission set.
   
4. Select Customized permission set after which select Subsequent.
5. Below AWS managed insurance policies, select AmazonRedshiftQueryEditorV2ReadSharing.
6. Below Buyer managed insurance policies, present the coverage title which you created in step 4 below part – Establishing IAM Id Middle integration with Amazon Redshift.
7. Select Subsequent.
8. Enter permission set title. For instance, Amazon Redshift-Question-Editor-V2.
9. Below Relay state – non-obligatory – set default relay state to the Question Editor V2 URL, utilizing the format : https://<area>.console.aws.amazon.com/sqlworkbench/house.
   For this submit, we use: https://us-east-1.console.aws.amazon.com/sqlworkbench/house.
10. Select Subsequent.
11. On the Assessment 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

1. Open the IAM Id Middle Console.
2. Within the navigation pane, below Multi-account permissions, select AWS accounts.
3. On the AWS accounts web page, choose a number of AWS accounts that you simply wish to assign single sign-on entry to.
4. Select Assign customers or teams.
5. 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.
6. 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.
7. On the Assessment and submit assignments to AWS-account-name web page, for Assessment 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 connect with Amazon Redshift Question Editor V2 and federated login utilizing IdC authentication:

1. Open the IAM Id Middle Console.
2. Go to dashboard and choose the AWS entry portal URL.
3. A browser pop-up triggers and takes you to the Okta Login web page the place you enter your Okta credentials.
4. After profitable authentication, you’ll be logged into the AWS console as a federated consumer.
5. Choose your AWS Account and select the Amazon Redshift Question Editor V2 utility.
6. When you federate to Question Editor V2, select your Redshift occasion (i.e., right-click) and select Create connection.
7. To authenticate utilizing IdC, select the authentication methodology IAM Id Middle.
8. 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. Person Ethan accesses the sales_schema tables. If Person Ethan tries to entry the tables in finance_schema, then the consumer will get a permission denied error.

Troubleshooting

• If you happen to 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 task with a fallacious namespace. Please verify present namespace utilizing the command choose * from identity_providers;

• If you happen to get beneath error:

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

Which means an IAM function doesn’t have enough privileges to entry to the IdC. Your IAM function ought to include 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:utility/ssoins-722324fe82a3f0b8/*"
        }
    ]
}

• If you happen to get beneath error:

  FATAL: Invalid scope. Person's credentials will not be licensed to connect with Redshift. [SQL State=28000]

Please ensure that the consumer and group are added to the Amazon Redshift IdC utility.

Clear up

Full the next steps to scrub up your assets:

1. Delete the Okta Functions which you could have created to combine with IdC.
2. Delete IAM Id Middle configuration.
3. Delete the Redshift utility and the Redshift provisioned cluster which you could have created for testing.
4. Delete the IAM function which you could have created for IdC and Redshift integration.

Conclusion

On this submit, 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 lets you use role-based entry management with Amazon Redshift. We encourage you to check out this integration.

To be taught 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 trade chief in analytics, utility 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 information warehouse and analytics options. He collaborates with numerous Amazon Redshift Companions and prospects 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 practically 20 years of expertise spanning numerous domains together with filesystems, storage virtualization and community safety. At AWS, he focuses on enhancing the Redshift information safety.

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