Case Study
Implementing Row Level Security & SSO Authentication for a AWS Redshift- Tableau application for Corsair
Overview
Corsair Components decided to make use of Tableau as the Visualization medium and Redshift as the back-end DataMart reporting solution. Tableau server log in is integrated with Corsair’s Active Directory. At the back end, there is Redshift, that has its own log in authentication. When a report link is opened in Tableau it requires a log in to Tableau Server first. Then another authentication page is shown to users to enter the RedShift database credentials to view the reports. This defeats the purpose of Single Sign on.

Also, the Tableau User Id information must be passed onto RedShift seamlessly so that the Security mechanism can be implemented in RedShift using the user information passed from Tableau.

The Challenge

When a report link is opened in Tableau it requires a log in to Tableau Server first. Then another authentication page is shown to users to enter the RedShift database credentials to view the reports. This meant that users must undergo two levels of authentication mechanism which made the user experience bad.

We can’t make use of the “save” password feature in Tableau as well, because anyway for the first-time user still needs to enter the RedShift credentials. This defeats the purpose of Single Sign on.

Also, there were no out of the box feature to pass the AD information from Tableau onto RedShift so that the user id information can be used to implement Row level security in RedShift

This also meant that the same user Id of Tableau must be maintained in RedShift as well, to implement user based / Row level security. This made the process cumbersome and hard to maintain the user related information in two places.

Our Solution

Agilisium helped our client to achieve the Single sign on Authentication from Tableau while connecting to Redshift and display the reports and make use of the Tableau AD user information in RedShift to implement security for reports.

The RedShift Credentials are embedded along with Tableau CDS and hence there is no need for user to reenter RedShift DB credentials.

Now to pass the AD credentials from Tableau to Redshift, there were no out of the box solution provided by Tableau, we made use one of the feature of Tableau to do this. Tableau has a feature called “Initial SQL”.

Using this Initial SQL, we write an insert query by passing the “currently logged in User Id” information onto a table created in Red Shift. When Tableau makes the first connection to RedShift, this Initial SQL gets executed first and it passes on the Tableau User id information onto RedShift Table (Tableau Session Table – refer diagram below)

Once the data is available in RedShift, the user id information can be used to implement security for data.

Results and Benefits
  • The solution is highly reliable and easy to maintain and manage
  • This way we could achieve client requirement that the security needs to be maintained at RedShift level only
  • When the reporting medium is changed, we can still make use of the security mechanism in RedShift, with only the user log on information to be passed from the reporting medium onto redshift