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] IN (SELECT [Team Code] FROM [Security Context] WHERE [Username] = %1)', 
USERNAME() )

Note: Remember to use IN rather than = if there is a possibility of more than one value.

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. Also note that you do not include the “WHERE” keyword in the statement you make. Assume whatever you write will be wrapped in parentheses directly after a WHERE (or AND) that Tableau will insert automatically.

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). This means that you aren’t restricted to using basic SQL clauses; you could instead tap into custom defined logic. For example, you could instead call a Stored Procedure or Function that resolves the security context, taking username as the input.

Obviously each RDBMS has its own mechanism for setting up Stored Procedures / Functions, so its hard to write out a general example, but here is an illustration in Microsoft SQL Server. In SQL Server, Stored Procedures must be run using the EXEC command, which cannot be inserted into a WHERE clause directly. A Function, on the other hand, can be placed inline in a SQL Statement, particularly if it is a Table-Valued Function.

Put simply, if the function returns a Table with a Single Column of values, it can be used by the IN operator of a WHERE clause, like the following:

WHERE [field] IN ( SELECT * FROM security_calculation("myname") )

The goal then is to set up the security_calculation function to do any of the processing necessary to go from the username to a single column of the values needed to filter that particular field. Since the parameter is programmatic, it can do almost any of the logic available in Stored Procedures (but my knowledge is limited as to what is really different).

This would be accomplished in Tableau using RAWSQL_BOOL like:

RAWSQL_BOOL('[field] IN ( SELECT * FROM security_calculation(%1)', USERNAME() )

Switching a Stored Procedure to a Table or View + a Function

One very valid use case for this technique is to separate the security context logic out of existing Stored Procedures from the data query, so that Tableau can connect directly to a View or Table rather than Stored Procedure. The way that Tableau (Desktop and Server) handle Stored Procedures is by running the Stored Procedure using the EXEC command, then storing the result set to a TEMP table within SQL Server. Unless the parameters are changed, Tableau will be querying from the TEMP table from that point forward.

Unfortunately, TEMP table creation is not always something that a given SQL Server instance has been optimized for. The speed also varies greatly depending on how large the result set is. In addition, because a TEMP table is only accessible within a given connection, but Tableau can establish multiple ODBC connections to send simultaneous queries, you may end up with many different TEMP tables of the same data, thus exacerbating the performance issues.

Thus, if you can move the security or context logic into a function that can be Applied to a Table or View, Tableau will start to apply the queries directly, bypassing the TEMP table storage and receiving the results directly.

Tableau Parameters

The same function technique 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.

RAWSQL_BOOL("[field1] IN ( SELECT * FROM decode_token(%1, 'field1')", [Token_Parameter]

In this situation, the imagined decode_token function takes in both a token and a specifier as to which attribute value(s) it will return, which might vary depending on which column the security would need to be applied for. In this way, you could send through multiple attributes through a single token (like a JWT) but have them decoded and applied to different columns.

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