Personalized Data for In-DB Connectors

Applying row level security for in db connectors (Pre 6.5)

for 6.5 onwards check this  articles 

http://spotfiretipsntricks.blogspot.com/2014/12/personalized-in-db-connectors-for.html

In  6.5 this is part of out of the box functionality to pass in username, group and domain 

By default InDB Connectors can ask for username password from the user and that cna be used to apply row level security. However certain times, you would want to keep using the service account to query the database, but still apply row level security while using IN-DB queries

Though InDB connectors directly do not support passing in  %CURRENT_USER%  parameter, we can still apply row level security using a simple personalized information link and loading the in-db connector as a ondemand table.

This article will walk thru the steps needed to do so.

Lets assume each spotfire user has access to some states data and we have  a lookup table which maps the user id to state he has access to, it may look something like this

ID	State
User1	New York
User1	Maine
User2	New Jersey
User2	Connecticut

Using this table and a personalized information link we can get back list of states that a user has access to. You can read about personalized Information Links here http://stn.spotfire.com/spotfire_client_help/id/id_personalized_information_links.htm

Now with 6.0 it is possible, to load in-db datatables on-demand, so now you could do a in-db connector to you table

For this example we will work sql indb connection

Type in your credentials and select the username/pwd and database.

Select table of interest and columns of interest as desired

Establish relations if needed. Hit Ok.

Now Load on Demand and hit settings.

In the settings dialog that you see select the state column and hit the define “Input Button”

Select values from column for “input for selected parameter”

In the settings  select datatable that was original personalized information link lookuptable in the screen,

Select the state column                         .

The use limit by Filtered rows and select the scheme.

Remove the Marked rows checkbox.

Hit OK.

Ensure that you select Load Automatically on the screen after you are done mapping

Now this in-db connector will always load data that belongs to you !!!!