How to set logged in User's username as a filter condition in Grafana

Hi There,

I request help for setting up logged in User’s username as a filter condition Grafana Dashboard.

In my case there are different Member users in the system. And when a Member user logged in,
he should be able to view his data only. For this I want to get the user name in my panels and
add this user name as a filter condition. I am using SQL server Database.

For eg.

There are Member1 and Member2, when Member1 logged into the system he should be 
able to view the Dashboard with his data only.
I have one column in my database table with user name data (eg. member1@gmail.com).
Here ‘member1@gmail.com’ is the user name of Member1 to log into the Dashboard.

How can I get the user name(member1@gmail.com) and apply the same as a filter condition in my query.

Suggesting work around also would be great help.

Thanks in advance,
Sony.

2 Likes

Hi sonyvl!

Trying to enforce data access by adding conditions to a query at runtime isn’t going to give you the security they’re probably looking for since the queries are assembled in the browser. If you want to enforce data access restrictions that needs to be done in the data source.

In open source Grafana you’d need to use a separate orgs or a completely separate instance for each user with its own datasource defined that uses a dedicated database user account with access to just the data the user should be able to see (if the user should only be able to see certain rows then that would be done by creating a view that includes only those rows and not allowing the user access to the underlying table directly). In enterprise you can do the grafana part of restricting users to specific datasources via datasource permissions, rather than needing to use a separate org or instance. Unfortunately, this kind of access control is problematic in grafana today.

1 Like

Hi @ivanahuckova,

Thank you for your response. That is really informative for us.

Yes, we were looking to integrate a single data source and restrict user access based on email ID/username.

As per your explanation we understand that it is not possible in Grafana community version.

You did mention that enterprise allows to specify data source permissions. Is this possible only in the case of multiple data sources or is it feasible even if we’re using a single data source in Grafana enterprise ?

Could you please shed more light on the extent to which we can control data source permissions with Enterprise Grafana ?

Thank you

In enterprise you can control which users can access each datasource. If you want to control access to the data within a given database, you would need to define a login for the database server that has access only to the data you want, then create a datasource in grafana that uses that restricted account to access the database.

I tried to do the same thing for a Individual Metric page for a call center. Unless I hid the address bar, they would be able to copy the URL out to view the metric and change the ID to another user.

Another way to do it, is if you have a Concatenated name so they need an ID number as well.

image

Hi @brianhaskinstele

That sounds interesting. Can you please explain how you achieved to filter the data source for a specific user in the second option you mentioned (concatenated name).

Any sample code snippet would be really helpful!!

Thank you.

Hi, I’m facing the same need as @ sonyvl, This topic was open in October 2019 and I’d like to know if there is any new alternative since then.
Regards,

the same here, I’m looking for this solution
BR

Since v7.1, active user information is available via a set template variables - see Global variables | Grafana Labs. These can be used in datasource queries as appropriate.

Note that when it comes to access control, @ivanahuckova’s comment still stands. I.e. simply adding a clause like WHERE user='${__user.login}' to your query isn’t going to prevent a particular user from issuing a modified query - so as to obtain unfiltered data (as long as the datasource itself allows such access).

1 Like

I came across this topic while trying to address this exact issue. I have a single postgres database with information from bunch of different users. Each user should not be allowed to access information from other users. After reading all this, here is the strategy I have come up with specifically for postgres.

  1. create a “token” column in my databases user table, which has some sort of long random string.
  2. Create a new schema in the database. Fill it with a bunch of functions which are basically wrappers to predefined SELECT statements, which take the token as an input parameter. Calls to these functions will lookup the appropriate user id and only return data for this user. Create these functions with the “security definer” setting, which will execute them as the owner of the function. (optional: create a user with read-only access to all tables to be the owner of the functions. This will give a layer of protection to your db)
  3. Create a new database user that exclusively has permissions to execute functions in the new schema. This user can still see all table names in the database, but cannot select from them directly. (there may be a way to hide db structure from this user but I haven’t found it yet)
  4. In grafana, create a datasource using this new user.
  5. Create a dashboard with a text box variable called token.
  6. Somehow send the token to each user. When they load up the dashboard, they can paste the token into the token text box at the top (or include it in a URL to them as var-token=XXXXXX)
  7. In the queries in the panels of your dashboard, call the postgres function, including the token variable in the functions required parameters.

Unless I have missed something, this will restrict users to only access data their tokens have access to. The issue is that that the user logged into Grafana and the value of the token variable are not interrelated. The password is basically in plain text in the URL, and if any user unknowingly copies that URL or gives it to another user, then they have now exposed access to all their private data. If there is a variation of this method that does not involve variables, I would love to hear it.

The closest variation I can think of relates to the $__user.id and $__user.email variables. These global variables could potentially be passed into the functions instead of the token, however they are too easy to guess by a bad actor. The user id is a simple number, and the email and login are guessable. What we really need is a random uuid for each user. If there was a uuid, that would not be able to be brute forced, and would take the place of the token, but wouldn’t be saved in plain text. Users could still see it in client side web developer tools, but there would be very little risk in accidently exposing it.

1 Like

It goes without saying the uid could also be guessed or brut forced or found in dev tools

Whatever you pass to the whatever endpoint should also implement some securr way of handling things.

We also use this global user variable to sql server but only the grafana svc account user under whose identity the grafana servicr is running has read only access to the data via stored procedures. Execute permission on these stored procedures is given only to this svc account.

Grafana by itself cannot enforce it all. Nothing ever can. Even if they claim that it does, it behooves you to tread very carefully. You,on your side ,whatever resources you are exposing to grafana, should be well very secured.