You’re probably aware that Tableau Parameters are not an inherently secure way to filter down data. A lot of people are using JWTs to pass around tokens with security entitlements, so if you are good with that, did you know you can pass one right into a Tableau Parameter?

I’ve built out an example where the JWT comes through then a function fires off a web service request, as part of the work for using a Web Service / REST API as a live data source, but there’s no reason you couldn’t build a smaller function for processing the JWT and then use just that in Custom SQL in Tableau when building your data source.

Although the following example uses PostgreSQL (and PL/Python), you could theoretically implement this in any language with access to a language that can process JWT. In MS SQL Server, a CLR Stored Procedure can access C# functionality, and the Systems.IdentityModel.Tokens.Jwt namespace appears to have all the necessary functionality to implement a very similar workflow.

Below I’ll work through a workflow using PL/Python on PostgreSQL — again, the same concept could be implemented on any RDBMS with functions.

Tableau’s Custom SQL capabilities let you define your own “inner query” which will be used as the basis for any other query that Tableau writes.

The feature that is useful here is that you can use Tableau Parameter’s as variables within a Custom SQL query.

As far as I can determine, there’s not really any effective limit to the length of a String parameter in Tableau — the only limits would probably be in a database. The long and short of this (ugh) is that you can easily out a JWT with quite a few attributes encoded within into a single Tableau string parameter.

 

In this example, we just want to build a generic function that takes in a JWT, verifies it, then returns back the Value of whatever Attribute Key we pass in. The following will be written in PL/Python (plpython3u in particular), which you would need to enable on your PostgreSQL server. If you just want to prove out the concept initially, you can use the same Docker image from the Web Service / REST API as a live data post to get a fresh PostgreSQL machine set up for PL/Python.

CREATE OR REPLACE FUNCTION
jwt_decode(jwt_token text, key text)
RETURNS text
AS $$

for arg in [jwt_token, key]:
    if arg == "" or arg is None:
        return None

import datetime
import jwt

# We'll parse the JWT here
# Assume a JWT payload that looks like this (Yours will look like whatever it looks like)
# jwt_payload = {'series_name': str,
#              'iat': iat, # timestamp
#               'start_date': str,
#                'end_date' : str,
#               'username' : str
#               }

# Your jwt_secret must be shared between the JWT creator and this JWT decoder. You also need to use the same algorithm
jwt_secret = 'between-you-and-me-keep-this-a-secret'
decoded_object = jwt.decode(jwt_token, jwt_secret, algorithm='HS256')

# Check the timestamp for freshness
# if iat <= ... some time math to reject old sessions, to stop any replay attacks
#    return None
if key in decoded_object:
    # Casting to string here, for safety sake. You may need to do more detection in case you have different types to process
    return str(decode_object)
else:
    return None
    # or throw an Exception for Warning or Error

$$ LANGUAGE plpython3u;

You’ll notice this is basically just doing some protection against invalid arguments (you might instead want to throw warnings or errors instead of returning None/NULL, which could cause unintended results in your eventual Custom SQL query). Otherwise, it’s simply decoding the JWT and then returning back the desired value from the key that is passed in.

Here’s what it would look like to use this in Tableau when making a Data Source:

Alternative to the JWT Decoder Function: Creating a Table-Value Function

Functions / Stored Procedures that return a Table (or table-like set of rows) can actually be used in the FROM clause. So you could build a function that wraps all of this together (the JWT parsing and the subsequent query), and you would end up using it similar to

but just passing in the JWT once (and then within the function, insert the values into the WHERE clause where needed).

This is less flexible than simply providing a generic JWT decoding function and letting people use it against any number of tables.

Alternative way to connect to Stored Procedures in MS SQL Server

When you connect to a Stored Procedure in Tableau through the standard workflow, Tableau will always take the result set and store in a temporary table on the server, often leading to slow performance. You may not have a lot of temp storage space, or it might be on a very slow drive, or already be maxed out. There is also a chance that since Tableau makes many different ODBC connections (up to 16), it may be repeatedly running and storing the same SP result set over and over for the same user.

If you transform the Stored Proc instead to work as a Table-Value Function, you can access it via the Custom SQL dialog and just pass the values in as Tableau Parameters. This will force the Stored Proc to rerun every time Tableau issues a different query (which could happen frequently depending on the analysis happening). But depending on your environment, it might perform better for you. It’s impossible to determine without just trying it on your system. But this at least gives you an alternative to try.