Restrict User Access to Only Their Own Charts and Dashboards

[aprendizlaura]

Currently, I am duplicating the Gamma role and granting some additional permissions so that users assigned to this new role can use SQL Lab, execute queries, save created datasets, and create charts and dashboards.

The issue arises when I grant the database access on [database_name] permission. While this permission fulfills all my intended objectives, it also allows users to view charts they did not create, which I would like to prevent.

My goal is for users assigned to this custom role to:

1. Access and execute queries in SQL Lab on a specific database.
2. View and manage only the charts they have created, without access to charts or dashboards created by other users.
3. See only the dashboards they have created or, alternatively, dashboards explicitly associated with their role.

I would like to know if this access restriction can be achieved by modifying the permissions granted to the role, or if there is another approach to address this issue. I’m currently stuck, so any guidance or suggestions would be greatly appreciated. Thank you so much!

[dosubot[bot]]

I found a similar discussion that might be relevant to your issue: Give users database access but restrict the dashboards, charts and datasets they see ^[1].

To achieve your goal of restricting user access so they can only view and manage the charts and dashboards they have created, while still allowing them to execute queries in SQL Lab on a specific database, you can follow these steps:

1. Assign the Gamma Role: Start by assigning users the Gamma role, which allows them to see only the slices and dashboards they have access to, including those they have created themselves.

2. Create a Custom Role for SQL Lab Access: Create a custom role that grants access to the specific database you want the users to query in SQL Lab. This role should include the necessary permissions for SQL Lab access.

3. Combine Roles: Assign both the Gamma role and the custom SQL Lab role to the users. This combination will ensure that they can only view and manage their own charts and dashboards while having the ability to execute queries in SQL Lab on the specified database ^[2].

Additionally, you can explicitly associate dashboards with a specific role using the DashboardRoles table, which allows you to link dashboards to roles directly. This setup can help ensure that users only see dashboards associated with their roles ^[3].

To continue talking to Dosu, mention @dosu.

[aprendizlaura]

@dosu I have no issues with the dashboards; my problem lies in granting access to a specific database. Once access is granted, the user with that associated role gains access to all charts created from that database. I want my user to be able to see only the charts they have created themselves.

[dosubot[bot]]

To ensure that users can only see the charts they have created themselves, even after granting them access to a specific database, you can implement a custom filter. The ChartCreatedByMeFilter class is designed to restrict chart visibility to only those created by the user. This filter checks if the created_by_fk or changed_by_fk fields of the chart match the current user's ID, effectively limiting visibility to charts created or modified by the user ^[1].

Here's a brief overview of how you can use this filter:

1. Implement the Filter: Integrate the ChartCreatedByMeFilter into your role-based access control setup. This will ensure that users only see charts they have created.

2. Assign Roles: Continue using the Gamma role for basic access and create a custom role for database access. Ensure that the custom role includes the necessary permissions for SQL Lab and database access.

3. Combine Roles and Filters: Assign both the Gamma role and the custom role to users, and apply the ChartCreatedByMeFilter to restrict chart visibility.

This approach will help you achieve the desired restriction on chart visibility while allowing users to access and query the specific database.

To continue talking to Dosu, mention @dosu.