Set row-level permissions and filter data based on the viewer

There are different cases where row level permissions and filtering can come in handy. For example, when you need a dashboard to return different results based on who’s viewing it, or when certain parameters need to be blocked from different users. For these different scenarios you need a solution involving some form of row level permission or filtering.

There are different needs and different levels of difficulty in implementing this. It is important to first identify your needs so you can adopt the best solution.

Embedding dashboards

If you’re looking to share dashboards with external users, embedding is a great option to restrict data using filtering. When embedding a dashboard, you can add environment variables to pass values to the dashboard based on the viewer and filter your queries accordingly. All the Dashboard Controls are encrypted with the JWT tokens to ensure security and with the proper filters you won’t have any worries that users will be able to see someone else’s information.

In the example below, you can see that the dashboard has a user variable in the payload which will accept a user value that is passed when this dashboard is requested based on which user is viewing the dashboard:

The logged in user email is passed to the environment variable “user” and filters the dashboard accordingly so that the loaded dashboard only contains the results pertaining to that user.

Internal dashboard viewing

If you’re looking to share dashboards internally within Chartio, you can utilize our Controls to help filter your data. Let’s say you want to make a dashboard with all of the opportunities, past sales, and past quota attainment of a sales representative.

You can do this by making adding a Dashboard Control that filters with some parameter like sales_rep. For example:

But, each sales rep could then adjust the Control on the page to see the other reps’ data. You can instead switch it to a Hidden Variable, but users will still be able to adjust in the value in the URL.

Filtering by CURRENT_USER

To solve this, remove the sales_rep_id variable and instead filter by the {CURRENT_USER} variable. The {CURRENT_USER} variable is a native Chartio variable that passes in the current user’s email address as a value to your query. You then just join the rep’s email from the Rep table to anything using the sales_rep.

The Sales Rep filter has been removed from the dashboard and you can use {CURRENT_USER} to display who’s viewing the dashboard using a text element.

Using the {CURRENT_USER} variable, depending on who’s viewing the dashboard, the user’s email will be passed in when the query runs.

Building an access lookup table in your database

In the above example, the database already has a mapping from the user’s email to their sales_rep. If the mapping isn’t already set up for your use case, you’ll want to make a table in your database that looks something like this:

Now you can easily map your queries to use the {CURRENT_USER} value in your queries for the dashboard, to control which rows of other tables can be seen.

This can also be made a bit easier to manage if you have (or make) some kind of grouping/teams in your database.

And then make your access map group based via access_level

Query / Explore / Edit Access

If you want different users to be able to create their own charts and dashboards from your dataset in Chartio, but require different access into rows or tables of data when the queries are performed then you’ll need database level permissions setup.

You can do this by either creating siloed copies of the data, or by creating different database users with different access permissions. Both approaches work similarly with Chartio - so the choice is really up to you.

Whether it’s a different user or a different schema, you will need to connect them each as separate data sources in Chartio.

Database user permissions

Chartio requires you to grant a read only user for its database connection. It is easiest to grant access to all of the tables, but if you would like Chartio to have access to only certain tables, you can grant a user with read access to specific tables only.

More information:

• MySQL Table Level Permissions
• Postgres Table Level Permissions
• Oracle Privileges, Roles, and Security Policies