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:
Using Username to Pass in Attributes
The username property of any user is available within Calculated Fields through the USERNAME() function. Because the user cannot ever edit their own username, setting it programmatically to include additional attributes (a “client ID” or a “region ID” or whatever you need) is a secure way to pass through information to a security function.
While FULLNAME() function is also available, the “Display Name” / “Full Name” property can actually be edited by the end user in their own preferences menu, which is accessible through the Web Edit menu. So you have to be extra careful with what you enable or disable if you want to store the attributes in the Full Name property. If your Tableau Server is linked to AD or LDAP to get its users, you could use the Full Name property to store attributes, because an end user cannot change the values when Tableau uses AD or LDAP as its identity store.
This method is useful when you would be considering using a single “service account” or would only create users at the first time they log in. It has a few requirements:
- Using the Tableau REST API to create the users: At minimum, the Add User To Site method will be necessary to create a user with the appropriate name and attributes appended
- Trusted Authentication (instead of SAML or OpenID Connect): Because you will be creating unique username, you won’t be able to have that username in your IdP ahead of time, which is necessary for the SSO mechanisms aside from Trusted Authentication.
- Availability of the additional Authentication Parameters through the Web Application interface, in a way that can securely pass to the Tableau REST API
How it Works
Ahead of time, you’ll need to determine the set of attributes you want to pass in for security filtering, and an order. This will determine the pattern of the usernames you create. Ex:
- A user logs into the Web Application, which now has both the user’s system username (or whatever identifies them), and any other authentication attributes made available to the web application
- The Username and other Attributes are sent to a lightweight Web Service that can send a REST API command to the Tableau Server. The design of this is totally up to you, but it doesn’t need to be very complex, other than making sure it is secure.
- Alternatively, if everything is done Server-Side, the Web Application could send the REST API commands to Tableau Server directly, at login time.
- The Tableau REST API portion takes the attributes and constructs the final “Tableau Username” based on the established pattern. After building the “Tableau Username”, test to see if there is already an existing username that is Licensed (Tableau Servers can have any number of users in an “Unlicensed” state, which don’t take up any license quota) using the Get User on Site method with a filter:
- If “Tableau Username” already exists and is licensed, skip to the Trusted Auth step
- If “Tableau Username” already exists but is Unlicensed, send the Update User REST API command and update the siteRole property to one of the licensed options (Viewer, Explorer, Creator…)
- If “Tableau Username” does not exist, use the Add User To Site method to create the user with the “Tableau Username” and set them to a siteRole that is considered “licensed”
- Once the user has been added (or confirmed to be licensed), you should retrieve a Trusted Ticket for that new username to pass back to the browser to establish a Tableau Server Session. You only need to redeem a ticket once to establish a session (following the Session Sign In pattern).
In basic testing, there doesn’t seem to be a practical limit to the length of a username. I’ve created one with at least 126 characters with no issues, and Tableau usernames are stored in Unicode so any character is valid as far as I know.
In your Tableau datasource, you’ll need to create a Calculated Field for use as a Data Source Filter.
The crux of this calculation is the SPLIT() function, which takes SPLIT(string, delimiter, position) as arguments. DON’T FORGET: Tableau positions always start at 1, not 0. We can use SPLIT() to get to any slot within the attributes we sent.
The Tableau Calculated Field will look like:
IF [Client ID] = SPLIT(USERNAME(), '|', 2) AND [Location] = SPLIT(USERNAME(), '|', 3) THEN 1 ELSE 0 END
- This mechanism is obviously easier when you have Core-based (hardware) licensing vs. licensing individual users, but programmatic management and delicensing of old usernames could make it manageable
- You lose some amount of tracking if the attributes vary for a user overtime. Since the username itself changes, true “per-user” tracking is difficult, and centralized management of scheduled e-mails or alerts might be untenable
- The attributes passed in can each only be used as a single value in a filter. This is generic Tableau issue, however, that affects any calculations — there simply isn’t the concept of a Tableau Function that handles Multiple value responses. Only a Custom SQL clause can put a SQL “IN” operator in place.
- You can make complex Calculations using AND and OR logic however, as in the example above.
- Instead of having multiple seperate attributes, you could have some number of “slots”, like ‘username|client_id_1|client_id_2|client_id_3’. In that case, your calculation would chain together with “OR” instead of “AND”, all against the same field
Benefits vs. other methods
- Works with both Live Connections and Extracts
- No additional work required for any particular data system
- Username is definitely not changeable in any situation, unlike the level of protections needed by any other mechanism on this page
Securely Using Tableau Parameters to Filter
Obfuscating the Keys and Encoding the Values
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:
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:
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:
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.
Sending through a JWT or other type of token
A single Tableau Parameter can handle a very long string, so if you have a token with assertion values and feel you can send it through, you could build out decoder functions in your database. The article on passing a JWT through and using the results in a Custom SQL statement is here.
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:
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:
- Putting the Parameter value directly in the URL using a name value pair
- 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
- 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.
- 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
- 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.
- 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.