Using Pass-Through Functions (RAWSQL) for Row-Level Security

In the classic text on the subject of Tableau Row Level Security, How to set up your Database for Row Level Security in Tableau, this author discussed the “WHERE method” of doing security look-ups, but advised that since the only practical method for achieving it was Initial SQL, that the “JOIN method” was best practice.

However, it has come to my attention that one of the most overlooked features that has been in Tableau for a long long time can be used to achieve the WHERE method, as well as run any arbitrary function or stored procedure that might be useful in establishing security context. What is this functionality, you are asking yourself (hopefully not out loud but I won’t judge too much): Pass-Through Functions i.e. the RAWSQL commands.

Framework for Row Level Security

  1. Use the RAWSQL_BOOL function, combined with USERNAME(), to create a statement which returns values that will filter on a column (or multiple columns)
  2. Place that Calculated Field as a Data Source Filter set to True
  3. Publish the Data Source and build workbooks off of it
RAWSQL_BOOL('[Team Code] = (SELECT [Team Code] FROM [Security Context] WHERE [Username] = %1)', 
USERNAME() )

How It Works

The standard RAWSQL commands are found in the “Pass Through” section of the Calculated Fields menu (Don’t use RAWSQL_AGG for this particular technique (although it might be useful for other things)). You want the calculation to be passed through on each row to generate row level security.

As “pass-through” commands, the RAWSQL functions take any SQL code you can dream up and insert it directly into the SQL query that Tableau constructs. They also have a syntax for inserting in values, similar to how the R and Python integrations work. You do need to specify the type of return value the SQL statement will have. In most cases, RAWSQL_BOOL will be appropriate, since we just want to filter out rows.

When you make something a filter in Tableau, you are placing that statement into the WHERE clause. And when you make something a data source filter, it will be inserted into the main statement’s WHERE clause, inside any other calls. It also can’t be tampered with by an end user once the data source has been published).

The one caveat is that your SQL must be based on what is really in the database, rather than using the “pretty names” / aliases that Tableau is showing. So you will need to know a little bit about the database to use it correctly.

Possible Use Cases

Direct SQL Statement

The simplest way to use the technique would be to include a short SELECT statement which pulls the values you need based on the USERNAME() being passed through. One thing to remember about the “WHERE method” is that you are not trying to assign the USERNAME to every row that is applicable, the way that the “FROM method” works. Instead, the database is looking up the appropriate values for the username from some other table, and only returning the values to filter the “column”.

For example, if your security is done more at a team or branch level, you may have a table which says which team or branch that user belongs to. Doing


SELECT sp.team
WHERE user = 'some user'

will return back the team or teams that the user belongs to. Then you want your eventual WHERE clause in Tableau to look like


FROM table t
WHERE t.team IN (
SELECT sp.team
FROM security_principles sp
WHERE user = 'some user'
)

Stored Procedures / Functions

One of the other main use cases for the Pass-Through functions is to execute SQL functions that don’t exist in standard SQL (whether something specific to a particular RDBMS you are using or something user-defined). From that perspective, you don’t need to be writing a direct SQL statement; you could instead call a Stored Procedure or Function that resolves the security context, taking username as the input. As long as the SP/function returns back a boolean True or False, it would work exactly the same way.

Obviously each RDBMS has its own mechanism for setting up Stored Procedures / Functions, so its hard to write out a general example.

Tableau Parameters

This also could solve the dilemma of securely passing in security context through Tableau Parameters (the security issues with this are discussed here). Rather than passing in USERNAME(), you could pass in the Tableau Parameter value. To be secure, this would require that the parameter is correctly encoded from the sending web application,  and that there is an SP/function in the database that can decode it and returns back the correct security context. As long as the database had a way of interpreting these (and they were not reusable or possibly tampered with), it could prove to be a sufficiently secure method of sending user context through Parameters.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s