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


Amazon Redshift is a totally managed, petabyte-scale knowledge warehouse service within the cloud. You can begin with only a few hundred gigabytes of information and scale to a petabyte or extra. This lets you use your knowledge to accumulate new insights for your corporation and prospects.

As enterprise prospects look to construct their knowledge warehouse on Amazon Redshift, they’ve enterprise necessities to implement satisfactory fine-grained entry controls to control who can entry which rows of their delicate knowledge based mostly on the consumer profiler. Moreover, many organizations wish to entry Amazon Redshift utilizing their current id supplier (IdP) whereas sustaining these compliance and safety necessities round their knowledge. With out correct built-in options to implement row-level and safe authentication, you could have to develop further options equivalent to views, or attempt to combine third-party options round your knowledge to implement safety.

With the introduction of row-level safety in Amazon Redshift, you’ll be able to limit consumer entry on the row stage. Moreover, we have now launched a native IdP performance that will help you implement authentication and authorization together with your selection of enterprise intelligence (BI) instruments in a seamless approach.

Amazon Redshift row-level safety (RLS) supplies granular entry management over your delicate knowledge. It does this through the use of RLS insurance policies to find out which rows to return within the question consequence units.

On this publish, we stroll you thru an instance on how one can implement row-level safety in Amazon Redshift whereas utilizing current IdP credentials to simplify authentication and managing permissions. You should utilize this versatile resolution to supply full management over knowledge entry whereas sustaining authorization utilizing your current IdP.

Resolution overview

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

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

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

The answer comprises the next steps:

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

With native IdP, roles get created routinely based mostly on Azure teams. Nevertheless, as a finest 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 check in.
  3. Upon profitable authentication, Azure AD points an authentication token (OAuth token) again to the Amazon Redshift driver.
  4. The driving 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 consumer’s group membership.
  6. Amazon Redshift maps the logged-in Azure AD consumer to the Amazon Redshift consumer 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 knowledge from the consumer.

Conditions

To implement this resolution, you have to have the next stipulations:

Implement your Amazon Redshift native IdP

To arrange your Amazon Redshift native IdP setup, seek advice from Combine Amazon Redshift native IdP federation with Microsoft Azure AD utilizing a SQL consumer. Observe the steps to arrange your Azure utility and accumulate Azure AD info for the Amazon Redshift IdP.

For this publish, 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 acceptable 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 sort 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, seek advice from Acquire Azure AD Info 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, identified solely to the IdP and the registered utility.
  • viewers – The applying ID that’s assigned to the appliance in Azure. For this publish, 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 utility. 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 comprises 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 based mostly on the teams that we created on the Azure AD portal and assign related permissions to them. This simplifies administration by assigning totally different permissions to totally different roles and assigning them to totally different customers.

The position identify within the Amazon Redshift cluster appears like <namespace>:<azure_ad_group_name>, the place the namespace is the one we offered 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 position on the suitable tables. For this publish, 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 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 data 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 consumer logs in utilizing their Amazon Redshift native IdP, they will get fine-grained entry to the data.

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 position, we haven’t created or hooked up any RLS coverage as a result of we don’t need any consumer from the HR group to get entry to gross sales data.

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 resolution, use Microsoft Energy BI Desktop- Model: 2.102.683.0 64-bit and above.

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

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 publish, we enter dev).
  3. Select OK.

Native IdP- PowerBI Desktop-connection

  1. Select Microsoft Account.

Native IdP- PowerBI Desktop-Login

  1. Select Sign up.

RLS-Native IdP- PowerBI Desktop-Login

  1. Enter your Microsoft Account credentials within the authorization dialog. For this instance, we check in with consumer Alice.
  1. Select Subsequent.

RLS-Native IdP- PowerBI Desktop-Login

As soon as linked, you will notice the message “You might be at the moment signed in.”

  1. Select Join.

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

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

Equally, we are able to attempt signing in as consumer Bob and see solely CA state info.

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

Charlie belongs to the supervisor position 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 be able to entry the desk, however isn’t capable of view any gross sales data as a result of no RLS coverage has been hooked up to the HR position.

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

If we haven’t granted SELECT on the gross sales desk to the position aad:hr, which Jen belongs to, then she will be able to’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 take a look at 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 recordsdata from the downloaded AWS JDBC driver pack .zip file. (Keep in mind to unzip the file.)

Ensure 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 help 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 utility. For instance, api://991abc78-78ab-4ad8-a123-zf123ab03612p/jdbc_login.
    5. client_id – Enter the client_id worth from the OAuth utility. For instance, 991abc78-78ab-4ad8-a123-zf123ab03612p.
    6. idp_tenant – Enter the tenant ID worth from the OAuth utility. 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 check in together with your Azure AD credentials.

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

Equally, we are able to re-authenticate and check in as consumer 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 data as a result of no RLS coverage has been hooked up to the HR position.

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

Abstract

On this publish, 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, seek advice from:


In regards to the authors

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 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 powerful group in question processing, SQL language, knowledge safety, and efficiency. Kiran is keen about delivering merchandise that seamlessly combine with prospects’ enterprise functions with the fitting 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 trade chief in analytics, utility platforms, and database applied sciences, and has greater than 25 years of expertise within the IT world. Debu has revealed quite a few articles on analytics, enterprise Java, and databases, and has offered at a number of conferences equivalent to AWS re:Invent, Oracle Open World, and Java One. He’s lead writer of the EJB 3 in Motion (Manning Publications 2007, 2014) and Middleware Administration (Packt).