Federate to Amazon Redshift Question Editor v2 with Microsoft Entra ID

Amazon Redshift is a quick, petabyte-scale, cloud information warehouse that tens of hundreds of consumers depend on to energy their analytics workloads. With its massively parallel processing (MPP) structure and columnar information storage, Amazon Redshift delivers excessive price-performance for advanced analytical queries in opposition to massive datasets.

To work together with and analyze information saved in Amazon Redshift, AWS gives the Amazon Redshift Question Editor V2, a web-based instrument that lets you discover, analyze, and share information utilizing SQL. The Question Editor V2 affords a user-friendly interface for connecting to your Redshift clusters, executing queries, and visualizing outcomes.

As organizations more and more undertake cloud-based options and centralized identification administration, the necessity for seamless and safe entry to information warehouses like Amazon Redshift turns into essential. Many purchasers have already applied identification suppliers (IdPs) like Microsoft Entra ID (previously Azure Energetic Listing) for single sign-on (SSO) entry throughout their purposes and providers. For extra details about utilizing Microsoft Entra ID for federation to Amazon Redshift with SQL shoppers, see Federate Amazon Redshift entry with Microsoft Azure AD single sign-on. This put up focuses on establishing federation for accessing the Redshift Question Editor.

Via this federated setup, customers can connect with the Redshift Question Editor utilizing their present Microsoft Entra ID credentials, permitting you to regulate permissions for database objects based mostly on enterprise teams outlined in your Energetic Listing. This strategy gives a seamless person expertise whereas centralizing the governance of authentication and permissions for end-users, eliminating the necessity to handle separate credentials for information warehousing. Moreover, you may limit entry to particular datasets based mostly on the person’s enterprise group, so customers solely have entry to the information they’re approved to view and handle.

Within the following sections, we discover the method of federating into AWS utilizing Microsoft Entra ID and AWS Identification and Entry Administration (IAM), and learn how to limit entry to datasets based mostly on permissions linked to AD teams. Though the mixing with AWS IAM Identification Heart is the beneficial strategy, this put up focuses on setups the place IAM Identification Heart won’t be relevant as a result of compliance constraints, resembling organizations requiring FedRAMP Average compliance, which IAM Identification Heart doesn’t but meet. We cowl the conditions, information you thru the setup course of, and reveal learn how to seamlessly connect with the Redshift Question Editor whereas ensuring information entry permissions are precisely enforced based mostly in your Microsoft Entra ID teams.

Resolution overview

The next diagram illustrates the authentication circulate of Microsoft Entra ID with a Redshift cluster utilizing federated IAM roles.

The configuration of federation between Microsoft Entra ID and IAM to allow seamless entry to Amazon Redshift via a SQL consumer such because the Redshift Question Editor V2 includes the next primary elements:

Customers begin by authenticating with their Microsoft Entra ID credentials by accessing the enterprise software’s person entry URL.
Upon profitable authentication, the {custom} claims supplier triggers the {custom} authentication extension’s token issuance begin occasion listener.
The {custom} authentication extension calls an Azure operate (your REST API endpoint) with details about the occasion, person profile, session information, and different context.
The Azure operate makes a name to the Microsoft Graph API to retrieve the authenticated person’s group membership info.
The Microsoft Graph API responds with the person’s group membership particulars.
The Azure operate takes the group info and transforms it right into a colon-separated checklist, resembling group1:group2:group3, and passes this colon-separated group info again to the {custom} authentication extension as a response payload.
The {custom} authentication extension processes the response and augments the token with the person’s group info as SAML claims (principal tags). The token, now enriched with the group membership, is returned to the enterprise software.
The enterprise software in Azure AD generates a SAML assertion with principal tags. It sends an HTTP POST to the person’s browser containing an HTML kind. This manner contains the SAML assertion and specifies the AWS sign-in SAML endpoint (https://signin.aws.amazon.com/saml) because the vacation spot the place the SAML assertion needs to be submitted.
The browser routinely submits this SAML assertion, sending an HTTP POST to the AWS SAML endpoint. This endpoint validates and processes the SAML assertion. If a number of IAM roles can be found, the person selects one. The AWS SAML endpoint then makes use of AWS Safety Token Service (AWS STS) to generate momentary credentials for that particular function, creates a console sign-in URL, and redirects the person to the AWS Administration Console. From there, the person can entry the Redshift Question Editor V2. To be taught extra about this course of, discuss with Enabling SAML 2.0 federated customers to entry the AWS Administration Console.
Inside Redshift Question Editor V2, the person selects the choice to authenticate utilizing their IAM identification. This triggers the Redshift Question Editor V2 to name the GetClusterCredentialsWithIAM API, which checks the principal tags to find out the person’s database roles. If that is the person’s first login, the API routinely creates a database person and assigns the required database roles.
The GetClusterCredentialsWithIAM API points a short lived person title and password to the person. Utilizing these credentials, the person logs in to the Redshift database. This login authorizes the person based mostly on the Redshift database roles assigned earlier and permits them to run queries on the datasets.

Stipulations

On the Microsoft Entra ID aspect, you want the next conditions to arrange this resolution:

A Microsoft Entra ID tenant – Required to arrange and configure the Microsoft Entra ID service for managing and securing entry to AWS assets via federation.
An Azure Subscription – Wanted to entry and use Azure providers like Azure Capabilities.

Customers needs to be members of particular Azure AD teams based mostly on their entry wants:

Person A – Member of the "redshift_sales" group for entry to gross sales datasets in Amazon Redshift, and the "AWS-_dev-bdt-team" group for entry to AWS providers within the growth surroundings. is the AWS account the place you’ve gotten your Redshift cluster.
Person B – Member of the "redshift_product" group for entry to product datasets in Amazon Redshift, and the "AWS-_dev-bdt-team" group for entry to AWS providers.
Person C – Member of each "redshift_sales" and "redshift_product" teams for entry to each datasets, and the "AWS-_dev-bdt-team" group for entry to AWS providers.

The "AWS-_dev-bdt-team" group in Azure AD is configured to permit customers to imagine an IAM function in AWS, offering the required permissions to entry the AWS account. For a multi-account setup, create a number of teams for various environments or accounts and add customers based mostly on their entry wants. For instance, "AWS-_prd-bdt-team" could possibly be used for entry to the manufacturing surroundings, the place displays the account quantity for the manufacturing account.

On the Amazon Redshift aspect, you want the next assets:

Redshift cluster – A Redshift cluster needs to be obtainable within the AWS account specified by within the AWS-_dev-bdt-team group. If not, observe the directions to create a pattern Redshift cluster.
Redshift database roles – Create database roles in Amazon Redshift that correspond to Microsoft Entra ID teams:
- redshift_sales – For customers with entry to gross sales datasets.
- redshift_product – For customers with entry to product datasets.
Redshift schemas – You want a Redshift schema named gross sales with the desk sales_table, which could be accessed by customers of the group redshift_sales. You additionally want a Redshift schema named product with the desk product_table, which could be accessed by customers of the group redshift_product within the dev database. You need to use the next SQL statements in your Redshift cluster to create the teams and tables, inserting information into the created tables and granting entry to the suitable teams:

-- Create Redshift Roles
CREATE ROLE redshift_sales;
CREATE ROLE redshift_product;
-- Create gross sales schema and sales_table
CREATE SCHEMA gross sales;
CREATE TABLE gross sales.sales_table (
    id INT PRIMARY KEY,
    merchandise VARCHAR(255),
    amount INT,
    value DECIMAL(10,2)
);

-- Create product schema and product_table
CREATE SCHEMA product;
CREATE TABLE product.product_table (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    class VARCHAR(255),
    value DECIMAL(10,2)
);
-- Insert information into sales_table
INSERT INTO gross sales.sales_table (id, merchandise, amount, value) VALUES
(1, 'Laptop computer', 10, 999.99),
(2, 'Smartphone', 20, 499.99),
(3, 'Headphones', 15, 199.99),
(4, 'Keyboard', 12, 89.99),
(5, 'Mouse', 30, 29.99);
-- Insert information into product_table
INSERT INTO product.product_table (id, title, class, value) VALUES
(1, 'Laptop computer', 'Electronics', 999.99),
(2, 'Smartphone', 'Electronics', 499.99),
(3, 'Blender', 'Dwelling Home equipment', 199.99),
(4, 'Mixer', 'Dwelling Home equipment', 89.99),
(5, 'Desk Lamp', 'Furnishings', 29.99);
-- Grant utilization on schema and choose on all tables within the schema to redshift_sales
GRANT USAGE ON SCHEMA gross sales TO ROLE redshift_sales;
GRANT SELECT ON ALL TABLES IN SCHEMA gross sales TO ROLE redshift_sales;
-- Grant utilization on schema and choose on all tables within the schema to redshift_product
GRANT USAGE ON SCHEMA product TO ROLE redshift_product;
GRANT SELECT ON ALL TABLES IN SCHEMA product TO ROLE redshift_product;

Setup Azure Capabilities and {custom} authentication extensions

Full the steps on this part to arrange Azure Operate and {custom} authentication extensions.

Create a brand new operate app

Full the next steps to create a brand new operate app:

Open your net browser and navigate to the Azure Portal (portal.azure.com).
Log in together with your Azure account credentials.
Select Create a useful resource.
Select Create below Operate App.

Choose the Consumption internet hosting plan after which select Choose.

On the Fundamentals tab, for Subscription, present the subscription you need to use. For this instance, we use our default subscription, Azure subscription 1.
Select or create a brand new useful resource group to prepare your Azure assets. We title our useful resource group rg-redshift-federated-sso.

Beneath Occasion Particulars, enter a globally distinctive title. For this put up, we use the title fn-entra-id-transformer.
For Runtime stack, select as Python.
For Model, select 3.11.
For Area, select East Us.
For Working System, choose Linux.
Select Evaluation + create to overview the app configuration.

Select Create to create the Azure Capabilities app.

Select Go to useful resource within the notification message or deployment output window to navigate on to your newly created app.

Create a operate

Subsequent, we create a HTTP set off operate within the newly created operate app, referred to as fn-entra-id-transformer.

Within the operate app, select Overview, then select Create operate within the Capabilities part.
Within the Create operate pane, present the next info:
1. For Choose a template, select v2 Programming Mannequin.
2. For Programming Mannequin, select the HTTP set off template.
3. select Subsequent.
Within the Template particulars part, present the next info:
1. For Job kind, select Create new app.
2. For Present a operate title, enter CustomAuthenticationFunction.
3. Go away the Authorization degree unchanged, which is about to Operate by default.
4. Select Create.
After the operate is created, select Get operate URL and replica the worth for default (Operate key).
Retailer the copied URL securely; you’ll want to make use of this URL later when establishing a {custom} authentication extension later within the part.

We are going to come again to this operate later to replace the code to retrieve group info.

Create a {custom} authentication extension

Subsequent, we create a {custom} authentication extension. Full the next steps:

Navigate to Microsoft Entra ID, Enterprise purposes, Customized authentication extensions.
Select Create a {custom} extension.
Within the Fundamentals part, present the next info:
1. Go away Occasion kind as TokenIssuanceStart (which is the default possibility).
2. Choose it and select Subsequent.
Within the Endpoint Configuration part, present the next info:
1. For Title, enter Retrieve_user_group_information.
2. For Goal URL, enter the operate URL you saved earlier.
3. Go away Timeout in milliseconds and Most Retries because the default values.
4. Select Subsequent.
Within the Api Authentication part, present the next info:
1. Choose Create new app registration for App registration kind.
2. For Title, enter Retrieve_user_group_information.
3. Select Subsequent.
Within the Claims part, present the next info:
1. For Declare title, enter dbGroupsqueryeditor and dbGroupssqltools.
2. Select Subsequent.
Within the Evaluation part, overview the configuration particulars, and if the whole lot seems to be appropriate, select Create.After the creation is accomplished, you may be redirected to the overview web page of the newly created {custom} authentication extension.On the overview web page, within the API Authentication part, you will notice a message indicating that admin consent is required.
Select Grant admin consent to grant the required permissions.

After the admin consent is granted efficiently, the API Authentication part will present the standing as Configured.

Now you may proceed to create the enterprise software.

Arrange the Azure enterprise software

Full the steps on this part to configure the Azure enterprise software.

Create a brand new Azure enterprise software

Full the next steps to create an Azure Enterprise software:

Navigate to Microsoft Entra ID, Enterprise purposes, New software.
Beneath Cloud platforms, select Amazon Internet Providers (AWS).
For Title, enter AWS Single-Account Entry.
Select Create.

When the create course of is full, you may be redirected to the newly created enterprise software.

Configure SSO

Full the next steps to configure SSO to your software:

On the enterprise software web page, select Get began below Arrange single signal on.
Select SAML.
Within the Primary SAML Configuration part, select Edit.
1. For Identifier (Entity ID) and Reply URL, enter https://signin.aws.amazon.com/saml.
2. Select Save.
Within the Attributes & Claims part, select Edit.
1. Within the Superior settings part, select Configure subsequent to the {custom} claims supplier setting.
2. For Customized claims supplier, select Retrieve_user_group_information.
3. Select Save.

Configure a bunch declare

We use the group declare to remodel the Azure AD group assignments into corresponding IAM roles. By making use of an everyday expression sample, the group names are mapped to applicable Amazon Useful resource Names (ARNs) for IAM roles and SAML suppliers. Full the next steps to configure the group declare:

On the Attributes & Claims web page, delete declare title https://aws.amazon.com/SAML/Attributes/Function.
Select Add a bunch declare.
Choose Teams assigned to the applying for the related teams.
For Supply attribute, select Cloud-only group show names.
Beneath Superior choices, choose Filter teams and supply the next info:
1. For Attribute to match, select Show title.
2. For Match with, select Prefix.
3. For String, enter AWS-.
Choose Customise the title of group declare and supply the next info:
1. For Title, select Function.
2. For Namespace, enter https://aws.amazon.com/SAML/Attributes.
3. Choose Apply regex exchange to teams declare content material.
4. For Regex sample, enter AWS-(?'accountid'[d]{12})_(?'env'[a-z]+)-(?'app'[a-z]+)-(?'function'[a-z]+).
5. For Regex substitute sample, enter arn:aws:iam::{accountid}:saml-provider/AzureADDemo,arn:aws:iam::{accountid}:function/{env}-{app}-{function}
Select Save.

Add new claims

Full the next steps so as to add new claims:

On the Attributes & Claims web page, select Add new declare.
Add claims with the next values:
1. Select Add a brand new declare, title the brand new declare https://aws.amazon.com/SAML/Attributes/PrincipalTag:RedshiftDbRoles, choose Attribute for Supply, enter customclaimsprovider.dbGroupsqueryeditor for Supply attribute, and select Save.
2. Select Add a brand new declare, title the brand new declare https://aws.amazon.com/SAML/Attributes/PrincipalTag:RedshiftDbUser, choose Attribute for Supply, enter person.userprincipalname for Supply attribute, and select Save.
3. Select Add a brand new declare, title the brand new declare https://redshift.amazon.com/SAML/Attributes/AutoCreate, choose Attribute for Supply, enter true for Supply attribute, and select Save.

The values of PrincipalTag:RedshiftDbUser and PrincipalTag:RedshiftDbGroups have to be lowercase; start with a letter; include solely alphanumeric characters, underscore (_), plus signal (+), dot (.), at (@), or hyphen (-); and be lower than 128 characters.

Whenever you full including all of the claims, your Attributes & Claims web page ought to appear to be the next screenshot.

Save the federation metadata XML file

You utilize the federation metadata file to configure the IAM IdP in a later step. Full the next steps to obtain the file:

Navigate again to your SAML-based sign-in web page.
Within the Single sign-on part, below SAML Certificates, select Obtain for Federation Metadata XML.
Save this file domestically.

The title of the file is usually the identical as the applying title; for instance, AWS Single-Account Entry.xml.

Create a brand new consumer secret

Full the next steps to create a brand new consumer secret:

Return to the Azure listing overview and navigate to App registrations.
Select the applying AWS Single-Account Entry.
If you happen to don’t see your software within the checklist, select the All purposes tab and register it if it’s not registered.
Report the values for Utility (consumer) ID and Listing (tenant) ID.
Beneath Certificates & secrets and techniques, select New consumer secret.
Within the Add a consumer secret pane, present the next info:
1. For Description, enter AWSRedshiftFederationsecret.
2. For Expires, select choose the Microsoft’s beneficial worth of 180 days.
3. Select Add.
Copy the key worth and retailer it securely.

The key expires after 180 days. Be certain that there’s a course of in place to replace with a brand new secret earlier than the present secret expires in your surroundings.

Add permissions

Full the next steps so as to add permissions:

Navigate to API permissions for software AWS Single-Account Entry.
Select Add a permission and supply the next info:
1. For Choose an API, select Microsoft Graph.
2. Choose Delegated permissions for the kind of permission your software requires.
3. In Choose permissions, select Person after which Person.Learn.
4. Select Utility permissions for the kind of permission your software requires.
5. In Choose permissions, select Listing after which Listing.Learn.All.
Select Add permissions.
This permits the Redshift enterprise software to grant admin consent to learn the person profile and group information related to the person and carry out the login utilizing SSO.
Beneath Configured permissions, select Grant admin consent for added permissions.
Within the affirmation pane, select Sure to grant consent for the requested permissions for all accounts to the enterprise software.
Navigate to your enterprise purposes and choose AWS Single-Account Entry and select Customers and teams.
Select Add person/group.
Beneath Customers and teams choose the teams redshift_product, redshift_sales, and AWS-_dev-bdt-team, that are created as a part of the conditions, and select Choose.
On the Add Assignment web page, select Assign.

Replace Azure Operate code

Full the next steps to replace the Azure Operate code:

Return to Dwelling and navigate to fn-entra-id-transformer below Operate App.
Select CustomAuthenticationFunction below Capabilities.
On the Code + Check web page, exchange the pattern code with the next code, which retrieves the person’s group membership, and select Save.

On this code, exchange the values of clientId, clientSecret, and tenantId with the values recorded beforehand. Additionally, in enterprise environments, use secret administration service to retailer these secrets and techniques and use necessities file to put in required packages resembling requests.

import azure.features as func
import logging
import json
import sys
import subprocess

def set up(package deal):
    allowed_pattern = r'^[a-zA-Z0-9-_.]+$'
    if not re.match(allowed_pattern, package deal):
        increase ValueError("Invalid package deal title")

    subprocess.check_call([sys.executable, "-m", "pip", "install", package], shell=False)

# Make sure the requests package deal is put in
strive:
    import requests
besides ImportError:
    set up("requests")
    import requests

app = func.FunctionApp(http_auth_level=func.AuthLevel.FUNCTION)

@app.route(route="custom-extension")
def custom_extension(req: func.HttpRequest) -> func.HttpResponse:
    logging.information("Azure AD Customized Extension operate triggered")

    strive:
        request_body = req.get_body().decode('utf-8')
        information = json.hundreds(request_body)
        user_id = information['data']['authenticationContext']['user']['id']

        # Fetch entry token for Microsoft Graph API
        access_token = get_access_token()
        if not access_token:
            error_response = {"error": "Did not acquire entry token for Graph API"}
            return func.HttpResponse(physique=json.dumps(error_response), status_code=200, headers={"Content material-Sort": "software/json"})

        # Fetch person teams
        user_groups = fetch_user_groups(user_id, access_token)
        if user_groups is None:
            error_response = {"error": "Did not fetch person teams"}
            return func.HttpResponse(physique=json.dumps(error_response), status_code=200, headers={"Content material-Sort": "software/json"})

        # Format teams as : seperated values as wanted by redshift question editor
        groups_colon_separated = ":".be part of(user_groups)

        # Assemble response as per the required JSON construction
        response_content = {
            "information": {
                "@odata.kind": "microsoft.graph.onTokenIssuanceStartResponseData",
                "actions": [
                    {
                        "@odata.type": "microsoft.graph.tokenIssuanceStart.provideClaimsForToken",
                        "claims": {
                            "dbGroupsqueryeditor": groups_colon_separated,
                            "dbGroupssqltools": user_groups
                        }
                    }
                ]
            }
        }

        return func.HttpResponse(physique=json.dumps(response_content), status_code=200, headers={"Content material-Sort": "software/json"})

    besides Exception as e:
        logging.error(f"Error in operate execution: {str(e)}")
        error_response = {"error": str(e)}
        return func.HttpResponse(physique=json.dumps(error_response), status_code=200, headers={"Content material-Sort": "software/json"})

def get_access_token():
    # Hardcoded credentials for demonstration; exchange with safe storage earlier than manufacturing
    client_id = 'client_id'
    client_secret="client_secret" 
    tenant_id = 'tenant_id'
    token_url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"

    physique = {
        "client_id": client_id,
        "scope": "https://graph.microsoft.com/.default",
        "client_secret": client_secret,
        "grant_type": "client_credentials"
    }

    strive:
        response = requests.put up(token_url, information=physique, timeout=10)
        response.raise_for_status()
        return response.json()['access_token']
    besides requests.RequestException as e:
        logging.error(f"Did not retrieve entry token: {str(e)}")
        return None

def fetch_user_groups(user_id, access_token):
    graph_url = f"https://graph.microsoft.com/v1.0/customers/{user_id}/memberOf?$choose=displayName"

    headers = {
        "Authorization": f"Bearer {access_token}"
    }

    strive:
        response = requests.get(graph_url, headers=headers, timeout=10)
        response.raise_for_status()
        return [group['displayName'] for group in response.json().get('worth', []) if group["@odata.type"] == "#microsoft.graph.group"]
    besides requests.RequestException as e:
        logging.error(f"Did not fetch person teams: {str(e)}")
        return None

Now you may create an IAM IdP and function.

In IAM, an IdP represents a trusted exterior authentication service like Microsoft Entra ID that helps SAML 2.0, permitting AWS to acknowledge person identities authenticated by that service. It’s essential to call this IdP AzureADDemo to match the beforehand configured SAML claims for function creation.

Create your IAM SAML IdP

Full the next steps to create your IAM SAML IdP:

On the IAM console, select Identification suppliers within the navigation pane.
Select Add supplier.
For Supplier kind, choose SAML.
For Supplier title, enter a descriptive title, resembling AzureADDemo.
Add the SAML metadata doc, which you downloaded as Federation Metadata.xml and saved as AWS Single-Account Entry.xml.
Select Add supplier.

Create an IAM function

Subsequent, you create an IAM function for SAML-based federation, which will likely be used to grant entry to the Redshift Question Editor and Redshift cluster. Full the next steps:

On the IAM console, select Roles within the navigation pane.
Select Create function.
For Trusted identification kind, choose SAML 2.0 federation.
For SAML 2.0-based supplier, select AzureADDemo.
For Entry to be allowed, choose Enable programmatic and AWS Administration Console entry.
Select Subsequent.
Add the permissions AmazonRedshiftQueryEditorV2ReadSharing and ReadOnlyAccess, and select Subsequent.
For Function title, enter a descriptive title, resembling dev-bdt-team.
Select Create function.

Replace belief coverage

On the IAM console, select Roles within the navigation pane, and seek for and select the function dev-bdt-team.
Within the Trusted entities part, select Edit belief coverage.
Add the motion sts:TagSession by eradicating the Motion line and including the next code:"Motion": [ "sts:AssumeRoleWithSAML", "sts:TagSession" ],
Select Replace coverage.

Create an IAM coverage

Within the following steps, you create an IAM coverage to permit the dev-bdt-team function to acquire momentary credentials for connecting to Amazon Redshift utilizing IAM:

On the IAM console, select Insurance policies within the navigation pane.
Select Create coverage.

On the JSON tab, enter the next coverage doc, changing placeholders with applicable values:

{
    "Model": "2012-10-17",
    "Assertion": [
                    {
                        "Sid": "VisualEditor0",
                        "Effect": "Allow",
                        "Action": "redshift:GetClusterCredentialsWithIAM",
                        "Resource": "arn:aws:redshift:::dbname::/*"
                    }
                ]
}

Evaluation the coverage particulars and supply a descriptive title to your coverage, resembling redshiftAccessPolicy.
Evaluation the coverage abstract and resolve any warnings or errors.
Select Create coverage to finalize the coverage creation course of.
On the Roles web page, seek for and open dev-bdt-team function.
On the Add permissions menu, select Connect insurance policies.
Connect redshiftAccessPolicy to the function.

Your permissions below the function dev-bdt-team ought to appear to be the next screenshot.

Check the SSO setup

Now you can take a look at the SSO setup. Full the next steps:

On the Azure Portal, to your AWS Single-Account Entry software, select Single sign-on.
Select Check this software.
Select Sign up as present person.

If the setup is appropriate, you’re redirected to the AWS Administration Console (which is likely to be in a brand new tab for some browsers).

Check with Redshift Question Editor

Full the next steps:

After the connection is established, it is best to have the ability to see your dev database and schemas below it, as proven within the following screenshot.

As a result of person A is simply a part of group redshift_sales, they’ll have the ability to see solely the gross sales schema.

Run a SQL assertion to get information from sales_table.

As a result of person A has entry to the desk, you may see output like the next screenshot.

Person C is ready to see each the product and gross sales schemas as a result of they’re a part of each the redshift_product and redshift_sales teams.

Run a SQL assertion to get information from each sales_table and product_table.

Person C has entry to each tables, as you may see within the following screenshot.

Clear up

To keep away from incurring future fees, delete the assets you created, together with the Redshift cluster, IAM function, IAM coverage, Microsoft Entra ID software, and Azure Capabilities app.

Conclusion

On this put up, we demonstrated learn how to use Microsoft Entra ID to federate into your AWS account and use the Redshift Question Editor V2 to connect with a Redshift cluster and entry the schemas based mostly on the AD teams related to the person.

In regards to the writer

Koushik Konjeti is a Senior Options Architect at Amazon Internet Providers. He has a ardour for aligning architectural steerage with buyer objectives, guaranteeing options are tailor-made to their distinctive necessities. Exterior of labor, he enjoys enjoying cricket and tennis.