Tableau 9.3 brings a wonderful new feature called Initial SQL with Parameters which allows you to declare a specific set of SQL commands to be run before any queries within a particular Tableau Server user’s session. One of the initial use cases is Oracle VPD. It can also be used in Microsoft SQL Server with a single technique to solve two issues:
- How to use the WHERE Method of row level security (see the Row Level Security post for full explanation), when the usernames you need for data security are not users in the database. If the usernames are users in the database on SQL Server, you can use Impersonate User in SQL Server as per the section in this post titled Recommendation: Use the JOIN Method unless the WHERE Method is “easy”. That is to say, this technique is only necessary when the usernames and entitlements exist only in the database tables, and not as part of the database security model.
We still need some setup in our database, specifically a table that lets us match SQL Server connection sessions to a particular username. Each SQL Server session has something called an SPID, which can be accessed through the variable @@SPID . You’ll want to create a table for your Tableau based SPID sessions; something like [Tableau Sessions]. You only need two columns: [Username] nvarchar(n) and [Current SPID] int
Your initial SQL should be the following:
DECLARE @query as nvarchar(300) = 'SELECT [Current SPID] FROM [Tableau Sessions] WHERE [Current SPID] = @@SPID' EXECUTE sp_executesql @query IF @@rowcount = 0 BEGIN INSERT INTO [Tableau Sessions] VALUES ([TableauServerUser], @@SPID) END ELSE BEGIN UPDATE [Tableau Sessions] SET username = [TableauServerUser] WHERE [Current SPID] = @@SPID END
[TableauServerUser] is a variable that Tableau automatically substitutes with the logged in server’s username. The other possible tokens are listed in the blog post I linked to at the beginning.
Basically, you use this table to give you a mapping of SPID -> Username.
Then in any SP or View you create, you can use this as a lookup to drive your security calculations in a WHERE clause:
WHERE (dbo.table.[Security_Key] IN (SELECT username FROM dbo.[Tableau Sessions] WHERE ([Current SPID] = @@SPID)) )
or more likely, you’ll use the [Tableau Sessions] table to filter your [Security Table], which then returns the value that will filter the data table:
WHERE dbo.table.[Security_Key] IN ( SELECT [s].[Security_Key] FROM [Security] [s] WHERE [s].[Username] = ( SELECT [Username] FROM [USERNAME_SPID] WHERE [SPID] = @@SPID ) )
This blog has been really helpfull. But after adding the initial SQL, i can’t find how to modify it. Any idea how ?
I’ve complained about this as well, because it is super hidden and I don’t think many people will find it naturally. You have to go back to the data connection screen, go to the Data menu at the top, go to the data source name at the bottom of the window, and then in the > menu, there will be an option for “Initial SQL…”. That’s the only place to edit it right now.
Thanks for this blog. Great stuff!
Just a quick note to initial sql statement:
DECLARE @query as nvarchar(300) = ‘SELECT [Current SPID] FROM tableau_sessions WHERE …..
The table name ‘tableau_sessions’ should be changed to [Tableau Sessions], right?
Thank you so much, great catch. Should be updated now!
Wow! This works great! Thank you so much for your post. It’s a shame that Tableau doesn’t have these simple instructions on their site.