Using Initial SQL to Pass Usernames to Stored Procedures or Views in SQL Server

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:

  1. Passing the Tableau Username to a Stored Procedure (previously only possible using the JavaScript API )
  2. 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
)
)
Advertisements

4 comments

  1. This blog has been really helpfull. But after adding the initial SQL, i can’t find how to modify it. Any idea how ?

    Thank’s

    Like

    1. Hi Patrick,
      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.

      Like

  2. 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?

    Like

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