System predefined parameters that can be used in queries

[Slind14]

Now that parameter merging happens in the backend, it allows for two new options:

1. Support for Redash predefined parameters:

The system will inject a set of values you can use in your query, like:

• system:user_id
• system:user_email

These parameters won't render input fields in the UI, and will get the value based on the current user.

2. Define parameter values in the group, user object, or API key:

An admin can "attach" parameter values to a group, user or API key that will be used when evaluating a query. This is a bit trickier, because for users who don't have such values assigned -- we need to render the UI, but otherwise we will use the preassigned parameter.

[arikfr]

Thanks, @Slind14 . I updated your issue to better describe the functionality. Please revise if I missed anything.

Thanks!

[arikfr]

@rauchy

[Slind14]

@arikfr sorry we no longer use redash. For this instance we custom tailored a solution and for another use case we had an update breaking the setup. (some micro services updated and others didn't..)

[androsj]

This would come in really handy.

Some use cases we recently thought of at work: 1) Current user can see his assigned work, and continue checking on his progress. 2) Current user's work can be validated.

And probably many other possibilities that could provide a more personalized experience, & add value to Redash as a productivity tool.

[arikfr]

Relevant discussion: https://discuss.redash.io/t/row-level-access-permission-or-builtin-variable-related-to-account-id/1431/5

[jdbranham]

I would like to customize the returned dataset based on the current user.
If not a system parameter, perhaps pass the current authentication context can be passed to a json datasource.

Currently I have to manage reports in 2 systems, Redash and a custom platform.

Is this issue being considered?

[flogeh]

Is this issue on any roadmap? Would be very helpful. We use redash in a multi tenant environment, where we want to create one query/dashboard for all tenants, but the data has to be filtered on the users group. You maybe can a have a look on Cognos Analytics (for my opinion best bi tool for multi tenant environment), they use query paraemters, that can come from users attributes (like UserName, ID, UserGroups, etc.)

[Slind14]

Is this issue on any roadmap? Would be very helpful. We use redash in a multi tenant environment, where we want to create one query/dashboard for all tenants, but the data has to be filtered on the users group. You maybe can a have a look on Cognos Analytics (for my opinion best bi tool for multi tenant environment), they use query paraemters, that can come from users attributes (like UserName, ID, UserGroups, etc.)

Redash feature development was abandoned with the acquisition. We switch to metabase.

[carlos-alonso]

Is this issue on any roadmap? Would be very helpful. We use redash in a multi tenant environment, where we want to create one query/dashboard for all tenants, but the data has to be filtered on the users group. You maybe can a have a look on Cognos Analytics (for my opinion best bi tool for multi tenant environment), they use query paraemters, that can come from users attributes (like UserName, ID, UserGroups, etc.)

After some tweaking I found a way (probably not the best) to achieve this in Redash

The idea is to use username or its groups to filter data, so each user sees only the data marked for him. The same query and datasource could serve for all users. Filtering data could be done easily with simple conditions ( i.e select * from data where ….. and allowed_groups in (redash_user_groups) )

I tried some different options at client or backend, and finally the backend one seems best.

For example, this could be a simple query to start trying to check values at execution time. (The values we want to be substituted should have some fixed specific names, not easy to be used some other way)

Select
'__REDASH_USER'        as redash_connected_user, 
'__REDASH_USER_GROUPS' as redash_user_groups, 
from dual

The aim is to substitute __REDASH_USER with the actual user who retrieves data and __REDASH_USER_GROUPS with his redash groups at execution time.

After some tests the easier way to do it is at backend, specifically at app/redash/handlers/query_results.py Adding some lines at run_query

    (...)
    query_text = data_source.query_runner.apply_auto_limit(
        query.text, should_apply_auto_limit
    )

    # RLS  Substitute username or groups.  Turn cache or previous results off with max_age=0

    if '__REDASH_USER' in query_text or '__REDASH_USER_GROUPS' in query_text:
        if hasattr(current_user, 'email'):
            query_text = query_text.replace('__REDASH_USER_GROUPS',','.join(str(v) for v in current_user.group_ids))
            query_text = query_text.replace('__REDASH_USER',current_user.email)
        else:
            query_text = query_text.replace('__REDASH_USER_GROUPS',' -1 ')
            query_text = query_text.replace('__REDASH_USER','guest@example.com')
        max_age=0

      # END RLS

    if query.missing_params:
   (...)

That way, at query execution time, __REDASH_USER would be substituted with user’s mail and __REDASH_USER_GROUPS would be substituted with a list of comma separated values of the redash groups ids the user belongs to.

Then, the result is

REDASH_CONNECTED_USER  REDASH_USER_GROUPS
somebody@mydomain.com           1,2

So we can use these values in any query we want to filter data depending on users or groups (preferably)

One potential problem could be cached or previous results, but seems that with max_age=0 no results are stored or cached (at least on my tests, probably needs deeper testing)

It is important to note that public or using the api_key way of retrieving data have no username associated, so this substitution just return 'guest@example.com' and -1 as group.

(This approach is somehow similar to https://github.com/getredash/redash/pull/3723 )