Managing row-level security with Tableau & Write-Back
Did you know that you can use Write-back to manage user access to data in a simple, visual way using a Tableau dashboard? This process will allow the application administrator to manage user access to data by specific regions. So, when a user logs in to Tableau to analyse their dashboards, they will only be able to see filtered data for the regions and states that they have been granted access to.
Let me explain what row-level security is, and how Write-back can help you with the process.
What is row-level security?
When you share workbooks with others by publishing them to Tableau Server or Tableau Online, by default, all those users who have access to the workbooks can see all the data shown in the views. You can change this behaviour by applying a type of filter that allows you to specify which data “rows” any given person signed into the server can see in the views. This is often referred to as row-level security (RLS).
For example, you can ensure that sales representatives can only access those data rows that are pertinent to their region and state. This access restriction logic is placed in the database tier rather than away from the data in another application tier.
To manage user access restrictions, we need to apply rules dynamically for every person who has access. RLS allows these security rules to be applied at the data level to filter what each user can access.
Why row-level security with Write-Back?
Typically, to implement RLS security, the standard approach is to create a new table in the database to manage which users can access which rows. This new table is then joined with the dataset for analytical purposes, and a filter is created in Tableau. A classic example is defining which sales reps can access which regions and states:
This approach, while it works perfectly, brings a few limitations with it. Using a database to store these new users’ tables will require administrators either to have the technical skills to be able to manage user permissions directly in the database, or it will create a dependency on external IT teams to manage this for them. This is where Tableau Write-back can help. User access can be managed directly in a Tableau dashboard, using Write-back to insert, edit and delete user accesses visually, while writing information to the database table in the background.
How to implement it?
Let’s use the Superstore example to see how this works.
- Admin Dashboard
First, you need to create a dashboard that can only be accessed by the administrator, to manage user access with the Write-back extension.
As an example, we created a dashboard showing a map visualisation with the state and region to associate users with the correct data views. Then we added the Write-back extension to start adding users.
Step 1. Write-Back Setup
- Open the dashboard where you want to add the write-back
- Insert the extension
- Log in with your credentials
- Select Source Name
- Select Data Fields
- Select Date source
- Write the File Name
- Submit Form
These steps were used to log in and create the database associated with the users who will have access to the data and dashboards.
Step 2. Insert new users on Write-Back
- Select the states and regions to associate with a particular user
- Insert user name
- Existing users for the selected states and regions are visible in the right-hand sidebar
Here all users are added according to region and state.
In the example in this blog, the Write-back table is in a MySQL database and at this point we already can see that the Write-back table is already
filled with data from the new “user1” by region and state, such as was defined in the previous step.
- End Users Dashboard
Now that we have a dashboard to manage user access, we can start adding Row Level Security to the dashboard that will be presented to the end-users.
In the data source, it is necessary to filter the rows of data accessible for the user by state and region as shown in Step 3.
Step 3. Filter data source by User
- Add Write-back data source
- Add user table to the existing superstore data source
- Make an inner join with the data fields (Region and State)
- Filter IsActive table to 1
- Filter Name to the username → Name = Username()
- The data is filtered and the view is adapted to the correct user.
In conclusion…In conclusion, following this step-by-step approach it’s possible to use to Write-back to visually manage user access at the Row Level.
For this, we will have to create a separate administration dashboard to assign which users can access which rows and only then add the row-level security to the main dataset that will be accessible to business users.
Besides having two separate dashboards and datasets (one for admin and one for business), there is a limitation to be aware – Let’s say, for example, that user “123” is added to the state of Kansas and then submitted. If I wanted to associate this user 123 with Kansas and Oklahama (selecting Kansas, as it already existed for user 123), when submitting the form in Write-back, the state of Kansas would be active twice. Then, when we make the internal link, the information will duplicate the data that the user sees for Kansas, giving incorrect results on the dashboards.