Friday, December 2, 2022
HomeBig DataCombine Amazon Redshift row-level safety with Amazon Redshift native IdP authentication

Combine Amazon Redshift row-level safety with Amazon Redshift native IdP authentication


Amazon Redshift is a completely managed, petabyte-scale information warehouse service within the cloud. You can begin with just some hundred gigabytes of knowledge and scale to a petabyte or extra. This allows you to use your information to amass new insights for your small business and prospects.

As enterprise prospects look to construct their information warehouse on Amazon Redshift, they’ve enterprise necessities to implement ample fine-grained entry controls to manipulate who can entry which rows of their delicate information primarily based on the person profiler. Moreover, many organizations need to entry Amazon Redshift utilizing their present id supplier (IdP) whereas sustaining these compliance and safety necessities round their information. With out correct built-in options to implement row-level and safe authentication, you could have to develop extra options akin to views, or attempt to combine third-party options round your information to implement safety.

With the introduction of row-level safety in Amazon Redshift, you may prohibit person entry on the row degree. Moreover, we have now launched a native IdP performance that can assist you implement authentication and authorization along with your alternative of enterprise intelligence (BI) instruments in a seamless approach.

Amazon Redshift row-level safety (RLS) offers granular entry management over your delicate information. It does this by utilizing RLS insurance policies to find out which rows to return within the question end result units.

On this put up, we stroll you thru an instance on how one can implement row-level safety in Amazon Redshift whereas utilizing present IdP credentials to simplify authentication and managing permissions. You should utilize this versatile answer to offer full management over information entry whereas sustaining authorization utilizing your present IdP.

Answer overview

For our use case, a company requires row-level safety to limit entry to gross sales efficiency information to particular states and their allotted salesperson. We’ve got the next enterprise guidelines and situations:

  • Alice, the salesperson for NY, ought to have entry to NY gross sales information solely
  • Bob, the salesperson for CA, ought to get entry to CA gross sales information solely
  • Charlie, the gross sales supervisor for the North America area, ought to have entry to gross sales information for all states
  • Jen, who belongs to HR division, shouldn’t have entry to any gross sales information

The next diagram illustrates the answer structure we implement to unravel this downside assertion utilizing Amazon Redshift row-level safety and Amazon Redshift native IdP authentication.

Solution Overview

The answer accommodates the next steps:

  1. Create RLS insurance policies to offer fine-grained entry management for row-level information on the Gross sales desk.
  2. Create Amazon Redshift roles for every of the completely different Azure AD teams and assign related permissions to the desk.

With native IdP, roles get created robotically primarily based on Azure teams. Nevertheless, as a greatest follow, we’re pre-creating the Amazon Redshift roles and assigning related permissions.

  1. Connect row-level safety insurance policies to the roles.
  2. Configure a JDBC or ODBC driver in your SQL consumer to make use of Azure AD federation and use Azure AD login credentials to sign up.
  3. Upon profitable authentication, Azure AD points an authentication token (OAuth token) again to the Amazon Redshift driver.
  4. The motive force forwards the authentication token to the Amazon Redshift cluster to provoke a brand new database session. Amazon Redshift verifies and validates the authentication token.
  5. Amazon Redshift calls the Azure Graph API to acquire the person’s group membership.
  6. Amazon Redshift maps the logged-in Azure AD person to the Amazon Redshift person and maps the Azure AD teams to Amazon Redshift roles.
  7. The Amazon Redshift roles are pre-mapped with the RLS insurance policies talked about in step 3. This enables the respective customers to question the fine-grained row-level entry information from the consumer.

Conditions

To implement this answer, you will need to have the next conditions:

Implement your Amazon Redshift native IdP

To arrange your Amazon Redshift native IdP setup, check with Combine Amazon Redshift native IdP federation with Microsoft Azure AD utilizing a SQL consumer. Observe the steps to arrange your Azure software and gather Azure AD info for the Amazon Redshift IdP.

For this put up, we have now created the next 4 teams in Azure AD:

  • sales_ny
  • sales_ca
  • sales_manager
  • hr_group

Then we created the next 4 customers in Azure AD:

  • Alice – The salesperson in NY state
  • Bob – The salesperson in CA state
  • Charlie – The supervisor for the North America area
  • Jen – A member of the HR group

Add the respective customers to their applicable group:

  • Alicesales_ny
  • Bobsales_ca
  • Charliesales_manager
  • JenHR

Subsequent, we have to register the IdP in Amazon Redshift utilizing the next command:

CREATE IDENTITY PROVIDER rls_idp TYPE
azure NAMESPACE 'aad'
PARAMETERS '{
"issuer":"https://sts.home windows.internet/87f4aa26-78b7-410e-bf29-57b39929ef9a/",
"viewers":["https://analysis.windows.net/powerbi/connector/AmazonRedshift",
"api://991abc78-78ab-4ad8-a123-zf123ab03612p"],
"client_id":"123ab555-a321-666d-7890-11a123a44890",
"client_secret":"KiG7Q~FEDnE.VsWS1IIl7LV1R2BtA4qVv2ixB" }'
;

Within the previous assertion, the kind azure signifies that the supplier particularly facilitates communication with Microsoft Azure AD. We use the next parameters to gather Azure AD info (for extra info, check with Gather Azure AD Data in Combine Amazon Redshift native IdP federation with Microsoft Azure AD utilizing a SQL consumer).

  • issuer – The issuer ID to belief when a token is acquired. The distinctive identifier for the tenant_id is appended to the issuer.
  • client_id – The distinctive public identifier of the appliance registered with the IdP. This may be known as the appliance ID.
  • client_secret – A secret identifier, or password, recognized solely to the IdP and the registered software.
  • viewers – The appliance ID that’s assigned to the appliance in Azure. For this put up, we join with Amazon Redshift utilizing Energy BI Desktop and SQL Workbench/J. The viewers worth is hardcoded for Energy BI desktop, for instance https://evaluation.home windows.internet/powerbi/connector/AmazonRedshift . The second viewers worth is for the SQL consumer, which you get from the appliance ID URI within the OAuth software. For instance, api://991abc78-78ab-4ad8-a123-zf123ab03612p.

Use the next command to view the registered IdP on Amazon Redshift:

DESC IDENTITY PROVIDER rls_idp;

Native IdP - data

Use the next command to view all of the IdPs registered:

choose * from svv_identity_providers;

The next Gross sales desk accommodates details about every salesperson, the respective state they cowl, and their whole gross sales quantity:

CREATE TABLE SALES (sales_person VARCHAR(30), state CHAR(2), "total_sales" INT);
INSERT INTO SALES VALUES ('Alice', 'NY', 5000);
INSERT INTO SALES VALUES ('Bob', 'CA', 6000);
INSERT INTO SALES VALUES ('Sally', 'IL', 7000);

Sales data

Now we create 4 roles within the Amazon Redshift cluster primarily based on the teams that we created on the Azure AD portal and assign related permissions to them. This simplifies administration by assigning completely different permissions to completely different roles and assigning them to completely different customers.

The function identify within the Amazon Redshift cluster appears like <namespace>:<azure_ad_group_name>, the place the namespace is the one we supplied within the IdP creation command (aad) and the group identify is the Azure AD group. See the next code:

CREATE ROLE "aad:sales_ny";
CREATE ROLE "aad:sales_ca";
CREATE ROLE "aad:sales_manager";
CREATE ROLE "aad:hr";

Now we grant permission to the Amazon Redshift function on the suitable tables. For this put up, we assign SELECT permission on the Gross sales desk for all 4 roles:

GRANT SELECT ON TABLE SALES TO ROLE "aad:sales_ny";
GRANT SELECT ON TABLE SALES TO ROLE "aad:sales_ca";
GRANT SELECT ON TABLE SALES TO ROLE "aad:sales_manager";
GRANT SELECT ON TABLE SALES TO ROLE "aad:hr";

Use the next command to view all of the roles within the cluster:

roles information

Create a row-level safety coverage

Let’s implement an RLS coverage on the Gross sales desk to limit entry to gross sales efficiency info for a salesman particular to a specific state. We create the next coverage:

CREATE RLS POLICY policy_sales_ny
WITH (state char(2))
USING (state="NY");
CREATE RLS POLICY policy_sales_ca
WITH (state char(2))
USING (state="CA");

The gross sales supervisor can also be required to view gross sales throughout the North American area. For this, we create the next coverage:

CREATE RLS POLICY policy_sales_all
USING (true);

The policy_sales_all coverage permits the gross sales supervisor to view all the knowledge within the gross sales desk.

Connect the row-level safety coverage to roles

Now we have now to connect the row-level safety insurance policies to their respective Amazon Redshift roles in order that when the person logs in utilizing their Amazon Redshift native IdP, they will get fine-grained entry to the information.

ATTACH RLS POLICY policy_sales_ny ON public.gross sales TO ROLE "aad:sales_ny";
ATTACH RLS POLICY policy_sales_ca ON public.gross sales TO ROLE "aad:sales_ca";
ATTACH RLS POLICY policy_sales_all ON public.gross sales TO ROLE "aad:sales_manager";

For the HR function, we haven’t created or connected any RLS coverage as a result of we don’t need any person from the HR group to get entry to gross sales information.

Allow row-level safety on the desk

Now let’s allow row-level safety on the respective tables. On this demo, we allow the RLS coverage on the Gross sales desk utilizing the next command:

ALTER TABLE public.gross sales ROW LEVEL SECURITY ON;

Use the next command to view the RLS insurance policies:

SELECT * FROM svv_rls_attached_policy;

RLS policy - Data

Check row-level safety utilizing Energy BI Desktop

On this instance, we use Microsoft Energy BI Desktop to attach with Amazon Redshift utilizing a local IdP. For this answer, use Microsoft Energy BI Desktop- Model: 2.102.683.0 64-bit and above.

  1. In your Microsoft Energy BI Desktop, select Get information.

Native IdP- PowerBI Desktop-Login

  1. Seek for the Amazon Redshift connector, select it, and select Join.

Native IdP- PowerBI Desktop-Login

  1. For Server, enter your Amazon Redshift cluster’s endpoint. For instance: test-cluster.ct4abcufthff.us-east-1.redshift.amazonaws.com.
  2. For Database, enter your database identify (for this put up, we enter dev).
  3. Select OK.

Native IdP- PowerBI Desktop-connection

  1. Select Microsoft Account.

Native IdP- PowerBI Desktop-Login

  1. Select Register.

RLS-Native IdP- PowerBI Desktop-Login

  1. Enter your Microsoft Account credentials within the authorization dialog. For this instance, we sign up with person Alice.
  1. Select Subsequent.

RLS-Native IdP- PowerBI Desktop-Login

As soon as linked, you will notice the message “You’re at present signed in.”

  1. Select Join.

As proven within the following screenshot, Azure AD person Alice is ready to authenticate utilizing an Amazon Redshift native IdP, and the RLS insurance policies have been utilized robotically, permitting Alice to entry gross sales efficiency info for less than NY state.

RLS-Native IdP- PowerBI Desktop-Authorized to view respective data

Equally, we will strive signing in as person Bob and see solely CA state info.

RLS-Native IdP- PowerBI Desktop-UnAuthorized to view respective data

Charlie belongs to the supervisor function the place the view all coverage has been utilized, so when he indicators in, he is ready to view all of the rows within the gross sales desk.

RLS-Native IdP- PowerBI Desktop-Authorized to view data

Lastly, when Jen indicators in, she will entry the desk, however isn’t capable of view any gross sales information as a result of no RLS coverage has been connected to the HR function.

RLS-Native IdP- PowerBI Desktop-UnAuthorized to view data

If we haven’t granted SELECT on the gross sales desk to the function aad:hr, which Jen belongs to, then she will’t entry the gross sales desk.

RLS-Native IdP- PowerBI Desktop-UnAuthorized to access table

Check row-level safety utilizing SQL Workbench/J

Now we check row-level safety with an Amazon Redshift native IdP utilizing SQL Workbench/J.

  1. Create a brand new connection in SQL Workbench/J and select Amazon Redshift as the motive force.
  2. Select Handle drivers and add all of the information from the downloaded AWS JDBC driver pack .zip file. (Bear in mind to unzip the file.)

Be certain to make use of the Amazon Redshift driver 2.1.0.4 onwards, as a result of all earlier Amazon Redshift driver variations don’t assist the Amazon Redshift native IDP characteristic.

Native IdP- Workbench/J drivers

  1. For URL, enter jdbc:redshift://<cluster endpoint>:<port>:<databasename>. For instance: jdbc:redshift://test-cluster.ab6yejheyhgf.us-east-1.redshift.amazonaws.com:5439/dev.

Native IdP- Workbench/J Connection

  1. On the Driver properties tab, add the next properties:
    1. plugin_namecom.amazon.redshift.plugin.BrowserAzureOAuth2CredentialsProvider
    2. listen_port – 7890
    3. idp_response_timeout – 50
    4. scope – Enter the scope worth from the OAuth software. For instance, api://991abc78-78ab-4ad8-a123-zf123ab03612p/jdbc_login.
    5. client_id – Enter the client_id worth from the OAuth software. For instance, 991abc78-78ab-4ad8-a123-zf123ab03612p.
    6. idp_tenant – Enter the tenant ID worth from the OAuth software. For instance, 87f4aa26-78b7-410e-bf29-57b39929ef9a.

Native IdP- Workbench/J Parameters

  1. Select OK from SQL Workbench/J.

You’re redirected to the browser to sign up along with your Azure AD credentials.

As proven within the following screenshot, Azure AD person Alice is ready to authenticate utilizing an Amazon Redshift native IdP and look at solely gross sales efficiency info for NY state.

Equally, we will re-authenticate and sign up as person Bob, who is ready to view gross sales info particular to CA state.

When Charlie indicators in, he is ready to view all of the rows from each state.

Lastly, when Jen indicators in, she is ready to entry the desk, however can’t view any gross sales information as a result of no RLS coverage has been connected to the HR function.

If we haven’t granted SELECT on the gross sales desk to the function aad:hr, which Jen belongs to, then Jen can’t entry the gross sales desk.

Abstract

On this put up, we coated how one can obtain a safe end-to-end expertise utilizing Amazon Redshift native IdP authentication, which simplifies administration and row-level safety to allow fine-grained row-level entry in Amazon Redshift.

For extra details about Amazon Redshift row-level safety and native IdP federation, check with:


Concerning the authors

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 varied Amazon Redshift Companions and prospects to drive higher integration.

Harshida Patel is a Specialist Sr. Options Architect, Analytics, with AWS.

Kiran Chinta is a Software program Improvement Supervisor at Amazon Redshift. He leads a robust group in question processing, SQL language, information safety, and efficiency. Kiran is obsessed with delivering merchandise that seamlessly combine with prospects’ enterprise functions with the proper ease of use and efficiency. In his spare time, he enjoys studying and taking part in tennis.

Debu-PandaDebu Panda is a Senior Supervisor, Product Administration, with AWS. He’s an business chief in analytics, software platforms, and database applied sciences, and has greater than 25 years of expertise within the IT world. Debu has printed quite a few articles on analytics, enterprise Java, and databases, and has offered at a number of conferences akin to AWS re:Invent, Oracle Open World, and Java One. He’s lead creator of the EJB 3 in Motion (Manning Publications 2007, 2014) and Middleware Administration (Packt).

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments