Securely Passing Parameters into a Tableau Viz at Load Time

The standard answer for enforcing user-based data entitlements in Tableau is to use Row Level Security, where the user is authenticated in Tableau Server and then tied into an “entitlements view” in the database so that the user only ever sees data they have access rights to.

However, we are very often asked about passing parameters in to the viz to filter down information directly at load time, often driven by an application that Tableau vizes are embedded in. This post is about a few methods of implementing this behavior, and the security implications of each of them.

Basics of Security

Everything must be HTTPS

I’ll start by saying, to do any of this securely, you need EVERY resource you are working with to be using the HTTPS protocol (latest TLS version). If anything is not HTTPS, you could be passing important information in the clear.

Using URL Parameters to set a Filter directly is NOT SECURE

You can use the URL Parameter syntax to directly set the values for a Filter on any field, but this is completely insecure. Why? Because the following two methods will clear any filter and reveal all of the rows of data. Unless you have the JS API turned off, there is no way to prevent this.


Sheet.clearFilterAsync(fieldName);
Sheet.applyFilterAsync(fieldName, "", tableau.FilterUpdateType.ALL);

Tableau Parameters are the (potentially) secure way to make an adjustable Data Source Filter

The only way to prevent a user from resetting a filter value is by making it a Data Source Filter.  Thankfully, you can use a Calculated Field for the Data Source Filter. If you use Tableau Parameters in the Calculated Field, the Parameter value(s) can be set to change what is filtered, and you will have a Data Source Filter that cannot be altered by the JS API (or the end user).

However, there are quite a few considerations to make this a truly secure method for setting filter values:

Securely Using Tableau Parameters to Filter

Obfuscating the Keys and Encoding the Values

Regardless of which way you set the Tableau Parameter value, it is always possible for an end user to modify a Tableau Parameter via the URL or JavaScript.

How can we prevent them from setting arbitrary values? You can’t — but you can make sure that it is nearly impossible for someone to ever come up with another value that will actually show them any data that they are not entitled to.

For the following example, we will be trying to pass the following three attributes over the URL:

  • Username (one possible value)
  • Category (multiple possible values)
  • Sub-Category (multiple possible values)

The “naive”, insecure method of setting values

A basic example setting of parameter values in the URL would look like this:

MyWorkbook/MyView?username=bhowell&category=Furniture&sub-category=Tables

This is not secure in any way. Why? Since both the parameter name and the values are in the clear, the user can easily figure out what the pattern is. Username is clearly first initial + last name, and the categories and sub-categories start with uppercase English names. It wouldn’t be too hard to come up with a set of possible other values and try them out.

Let’s walk through what it would take to make it close to near impossible for a user to try and determine other potential values that would reveal their data.

Obfuscating the URL Parameter Names

The first step to using a Tableau Parameter in a secure manner is to make sure to make it as non-obvious as possible what is being filtered on. Let’s take our Username parameter example from above. If we only encrypt the values (which we should), but pass a URL like:

MyWorkbook/MyView?username=9zU74zyqlewiI3z

It may be very difficult for them to break the encryption on the username, but it’s really obvious what you would be looking for. They may know their username, and if they get a list of other usernames from the URLs somehow, that gives more room to eventually to work to crack whatever encryption or hashing mechanism you are using. A much better version of this looks like this:

MyWorkbook/MyView?p1=9zU74zyqlewiI3z

In this case, the end user would have to actually have access to the workbook itself to have any idea what the parameter represents.

This is relatively easy to do with a Tableau Parameter, which you can give any name. I do suggest they be short names, because there is an overall limit to the length of URLs, and the values we will be long.

Encrypting or Hashing the Values OR Using Non-sequential IDs

Note: I’ll be referring to the “encoded” value here, which in most sentences simply means the resulting value that is not the “real” value. The actual process may or may not be described as “encoding” — it could be encryption, or creating unique IDs.

Because the end user will see the values and they will be filtering directly on a column in the data, the values both passed to Tableau and in the database both need to be sent in a way that meets the following requirement:

  • If you knew both the encoded and the real value, you would never be able to generate the encoded value for any other real value.

Given in an example, if I knew the encrypted/encoded/ID value of “Furniture” and “Office Supplies”, there should be no way that I can determine what the value for “Technology” would be.

What would let me meet these requirements?

  • Good encoding/encryption practices: You’ll need to use a mechanism for hashing or encrypting that has a shared salt or a shared key of enough length so that even if an end user had a reasonably large list of real values and their encoded values, they would also have to figure out the salt or key to be able to generate new “encoded” values. Why?:
    • It is reasonable to assume that the end user can at minimum figure out which of the “real values” they see match up to the “encoded values”, since they can remove parameters in the URL and through a process of elimination, figure out which encoded values match up to the real values.
    • This means you can’t just use a standard hashing algorithm or just encode using something like base64. It would be trivial to figure out what mechanism you are using, because they will have actual values to test out and put through the standard set of encoding or hashing algorithms.
  • Length of encoded value: The biggest risk to any of these security attacks is a “brute force” attack, where a computer starts running through every possible value until it hits a match. Every additional character exponentially increases the time it would take to brute force attack an encoded message. We recommend 20 characters at minimum, but ask your security team what they recommend.
  • If using IDs that are assigned at a database level: You must use non-sequential values for the IDs. In a traditional database, you might assign numeric ideas, which move up sequentially with each new element added. Computers are really good at going in numerical order, so this is a weakness. If you are generating unique IDs as an encoding method, there should be no sequence to the IDs.
    • A good example of this is a Salesforce.com ID — the API version of this ID is 18 alphanumeric characters long, and there is no relationship between the name of the object and the ID, nor are the IDs for objects of the same type generated from the same sequential pool.

Creating a Calculated Field that will Filter on Multiple Parameter Values

The one limitation of a Tableau Parameter is that it is a single value only. However, you may want to pass a set of values that the user has access to. This can be accomplished using the following pattern in a Tableau Calculated Field:

CASE [Encoded Database Field]
WHEN [Parameter 1] THEN 1
WHEN [Parameter 2] THEN 1
WHEN [Parameter 3] THEN 1 
...
ELSE 0
END

This gives you N number of Parameter slots that are an inclusive filter, the equivalent of WHERE [Encoded Database Field] IN (Parameter 1, Parameter 2…).

The first example has the values written out so you understand the structure. The actual calculation, taking the need for obfuscation into account, will look more like:

CASE [z1]
WHEN [p1] THEN 1
WHEN [p2] THEN 1
WHEN [p3] THEN 1 
...
ELSE 0
END

You should set a Data Source Filter on this Calculated Field, filtered only to the value 1. This will only show rows of data whose values are in the set of valid Parameters. Anything else will result in 0 and be filtered out.

If my values are encoded in a near-unbreakable way, my URL will look like:

MyWorkbook/MyView?p1=d1WdJKoQyHSBKo2gGbxENB48rMgGbpnr0VTntvQLCQw&p2=9aWoG9uv-wxUB_awd6MabhxHMYH4Sh8fOYVBgAPKU5k

With IDs of this length and obfuscated parameter names (the end user would have to do a much more difficult process of elimination to determine how these IDs connect with the data they see), we now have a process for setting a filter using parameters that would be almost impossible to tamper with.

Creating a Composite Key for Filtering on Multiple Columns

As you may have noticed, this method doesn’t allow for passing an unlimited number of values — you have to set up a new Tableau Parameter to give you a new “slot” in the calculation. Additionally, as mentioned above, there is a hard total limit to the amount of characters that can be in a URL, something around 2000 characters.

What if we want to filter on several columns in the data? One method would be to create multiple Calculated Fields, each taking in a different set of Tableau Parameter values.

Another technique would be to create a single Column in the data that has a unique ID for each combination of the values in those columns. Here’s an example

Category | Sub-Category | CategorySub-Category | Encoded CategorySub-Category
Furniture | Tables | FurnitureTables | d1WdJKoQyHSBKo2gGbxENB48rMgGbpnr0VTntvQLCQw
Furniture | Chairs | FurnitureChairs | 9aWoG9uv-wxUB_awd6MabhxHMYH4Sh8fOYVBgAPKU5k

The third column is the “Composite Key” while the fourth column is the “encoded” version of the composite key. In this case, the composite key is the actual values combined together — but that’s just to illustrate the concept. In reality, there is no requirement that the Composite Key value have any actual relationship to the combination of attributes it represents. The fourth column can just as easily be the composite key, or it could be non-sequential IDs.

Showing “All” or Representing Hierarchy

As shown above, you are filtering at the Lowest Level of Granularity in your security entitlements. For people low down in the organizational chain, this pattern along with a fixed number of Tableau Parameters should be able to represent that maximum number of granular entitlements someone has.

What about people who are higher in an organization, who can see large sets of data?

One technique would be to use Tableau Group membership to handle a switch on which field is being filtered on by the calculated field.

IF ISMEMBEROF('Big Boss') THEN 1
ELSEIF ISMEMBEROF('Category Managers') THEN 
    CASE [f1]
    WHEN [z1] THEN 1
    WHEN [z2] THEN 1
    WHEN [z3] THEN 1
    ELSE 0
    END
ELSEIF ISMEMBEROF('Sub-Category Managers') THEN
    CASE [f2]
    WHEN [z1] THEN 1
    WHEN [z2] THEN 1
    WHEN [z3] THEN 1
    ELSE 0
    END
ELSE 0
END

However, this would require adding the Tableau users to the correct groups to allow this to work, so it might not work in all situations. If we wanted to solve this without knowing who the user is on the Tableau repository side, we could pass another Parameter that represents the level of hierarchy

IF [z21] == ‘QvvLsrPSUqVgaYEKsZNH’ THEN 1
ELSEIF [z21] == ‘pgveiTGEUBTNBlTItWmN’  THEN 
    CASE [f1]
    WHEN [z1] THEN 1
    WHEN [z2] THEN 1
    WHEN [z3] THEN 1
    ELSE 0
    END
ELSEIF [z21] == ‘swYrTwJePagkLBmRcLdY’ THEN
    CASE [f2]
    WHEN [z1] THEN 1
    WHEN [z2] THEN 1
    WHEN [z3] THEN 1
    ELSE 0
    END
ELSE 0
END

Since the “Security Level” parameter values are arbitrary and random, they only need to be long enough that it would be impossible to brute force attack them. You could even combine this technique to give a special “all in this field” value at some levels of the hierarchy, like the following:

IF [z21] == ‘QvvLsrPSUqVgaYEKsZNH’ THEN 1
ELSEIF [z21] == ‘pgveiTGEUBTNBlTItWmN’  THEN 
    IF [z1] == ‘d1WdJKoQyHSBKo2gGbxENB48rMgGbpnr0VTntvQLCQw’ THEN 1
	ELSEIF
    CASE [f1]
    WHEN [z1] THEN 1
    WHEN [z2] THEN 1
    WHEN [z3] THEN 1
    ELSE 0
    END
ELSE 0
END
ELSEIF [z21] == ‘swYrTwJePagkLBmRcLdY’ THEN
    CASE [f2]
    WHEN [z1] THEN 1
    WHEN [z2] THEN 1
    WHEN [z3] THEN 1
    ELSE 0
    END
ELSE 0
END

These techniques handle the two most common access scopes — either a narrowly restricted level of granular access or an entitlement to all within a category (or overall).

The toughest situation is one with a large number of granularly restricted data points. For example, a user with a large set of arbitrarily assigned individual elements (for example, 1000 unique account IDs). This is a difficult use case to satisfy while passing in parameters, but it’s also a difficult use case even if you COULD pass in that many values. Even directly in a database, there are only so many values you would want to stick in a WHERE [Field] IN () clause, which is what a Filter in Tableau is going to generate in most cases. The preferred solution at the database level is to use a JOIN against another table to reduce down the data set — which is exactly how Tableau’s standard row level security mechanism works.

Ways to Set the Tableau Parameter Values Programmatically

There are three methods of setting a Tableau Parameter on a Tableau Server viz:

  1. Putting the Parameter value directly in the URL using a name value pair
  2. Setting the Parameter value using the JS API options object in the constructor method. This actually does the equivalent of #1 and puts the values in the URL
  3. Use changeParameterValueAsync() method of the JS API.

Each has its own benefits and downsides.

The “GET” Methods

Methods 1 and 2 both work prior to the viz loading, which means they are more efficient and direct. However, they both use the GET method in the URL, which requires thinking about about potential “leaks” of the information.

I will admit to being misinformed until researching this issue. I thought that GET parameters passed in the URL were insecure even using HTTPS. This is not the case — the full URL, as well as the POST data, is completely secure in transmission using the HTTPS protocol.

So where are the risks? Because the values are in the URL, at the endpoints of the transmission they can be written down in the clear.

  1. If you have the parameters on a Viz that is loaded directly (i.e. viewed through the Tableau Server UI rather than embedded into another page), the URL will be written down in the user’s browser history
  2. Any external content that is loaded by the viz will receive the full URL as a “Referrer” header. The only place this would typically happen is the Tableau mapping server, maps.tableausoftware.com. But if you embed some other web content into a Dashboard, it could definitely happen. That should all be HTTPS content as well though.
  3. The Tableau Server logs themselves will hold the parameters. But, your Tableau Server should be securely behind the firewall with access only granted to administrators. If someone has direct access to the Tableau Server machine, there is a lot more danger than just the URLs being in the logs.

The “POST” Method

The changeParameterValueAsync() method is more programmatic, but can’t be used until after the viz has loaded at least once. It uses the POST method which is less “leaky” than GET. POST data shouldn’t be logged down, nor does it appear in any referrer links.

The downside is the delay required to load a viz in a “blank state” then send the update. The best practice here would be to have a “cover div” to keep the viz from being shown until the changeParameterValueAsync() method is finished, then take away the “cover div” in the callback function.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s