View-only users cannot execute queries with parameters

[AntoineAugusti]

Issue Summary

Not an issue maybe, but at least a problematic behaviour for us.

Steps to Reproduce

1. Create a query with query parameters
2. Set the datasource for this query to a datasource where another user has only the view-only permission
3. With this other user, try to inject parameters and then click the refresh button
4. See this error message
   

I expect it to be okay (or at least that can be allowed) to execute queries with query parameters for view-only users.

Technical details:

• Redash Version: 0.11.0+b1959
• Browser/OS: Chrome / OS X
• How did you install Redash: AMI

[arikfr]

I expect it to be okay (or at least that can be allowed) to execute queries with query parameters for view-only users.

Current parameters implementations requires ability to run any query, therefore it requires full access to the data source. While the UI seems to allow running a specific query with parameters, the API allows you to submit any query. Even if we change the API to take query + parameters, it's still open to SQL injections.

We need to refactor our parameters support in order to allow read only users to be able to use them.

[AntoineAugusti]

I use the following permissions for a group that needs to execute some queries: {list_dashboards,view_query,execute_query,view_query,view_source}

[arikfr]

That's what I recommend, but note that I would use this only for internal users you trust won't actively try to avoid your restrictions. As the API allows to practically run any query.

[wichert]

Why was this ticket closed? As far as I can see this problem still remains.

[vietvudanh]

I do not understand why the hell does this not available? There are some queries we need to provide params!
And why did it closed?
@AntoineAugusti

[adityaU]

Can we please reopen this as this is not fixed in 2.0.0 version?

[C-Wood]

Yes, this needs to be reopened.

[RichardLitt]

@arikfr Can you include a bit more description of how you and @AntoineAugusti used the permissions you did to make this work? Otherwise, this issue may need to remain open until a fix is implemented.

[jgoux]

I needed to organize my groups into 3 categories :

• admin : have all permissions
• default : have no permission
• readonly : have readonly permissions and can execute queries with parameters

This is what I did in database in order to achieve my organisation, all the values concern the table groups and the column permissions

• admin : {admin,super_admin,create_dashboard,create_query,edit_dashboard,edit_query,view_query,view_source,execute_query,list_users,schedule_query,list_dashboards,list_alerts,list_data_sources}
• default : {}
• readonly : {list_dashboards,view_query,execute_query}

In order to be able to run parametrized queries as a member of the readonly group, you need to define the datasources for the group as Full Access or it won't work!

Hope this helps 👍

[tonyjiangh]

We are coming accross the permission problem as well.

I'm trying to understand the situation here.
Regarding the execute with parameter problem. The issue now is

• View only permission can't execute query (let alone with parameter)
  • If granted Full Access the query might somewhat be vulnerable to injection. So we might need to tackle the problem here.
• No UI to set group-wise access permissions

So I think the options here are:
Execute permission

• introduce method to sanitize injection
• Grand View Only permission with the execute query right
  • Or create a new permission other than 'View Only' and 'Full Access' to do that

BTW, our team do have some other problems

• View-only users can't change visualization setting, we are expecting the permission to
  • apply change to visualization to do some exploratory analysis
  • but not persist the settings to db so that the change only happends locally
• It seems that being able to list_datasource = permission to change it.
  • We are expecting a permission which could see the list and test connection of datasource, but not able to create to change.

[goodwill]

Permission to run query vs permission to change query should be a very separate matter, and given the life of this project is not that short, I am actually very surprise how this kind of issue can still exist

[RichardLitt]

@goodwill Thanks for expressing your surprise.

The open source version of Redash is a community project and comes with no warranties or guarantees of support. We'll fix this issue when we can or when it becomes relevant to our customers, but other issues have a higher priority right now. The fastest way for this to be fixed would most likely be for you or someone else to suggest a pull request, which would really be great. Let us know if it's something you would like help with, and we will review the PR and try and help you land it.

But until that happens, your comment comes off as a bit caustic, as it implies that we're failing in some way. I might be reading too much into this - I just want to make sure that you're aware why this might take a while. Please be patient with us. Thanks.

[goodwill]

I submitted a PR. Sorry for being negative on the comments, just it’s kinda surprise as it renders the view only permission on data source pretty much useless. See PR here:

#2539

[RichardLitt]

@goodwill Thank you! That's awesome. Sorry if I was over-reading things!

[arikfr]

We've started a project to redo permissions in Redash, which will also address this use case. To follow along you can watch #3284.