Restriction of Operator Role Permissions in Superset

[vladimirCampusGovIl]

What:
We need to modify the role permissions for all users assigned the "Operator" role in Superset.

Why:
Our platform, which is based on Open edX, grants global staff permissions to certain team members. This allows them to make changes and view course data via the LMS/CMS admin interfaces. However, when these users, designated as "Operators", access Superset, they currently have the capability to execute MySQL queries on our ClickHouse databases. Specifically, we want to prevent them from executing 'ALTER' and 'INSERT' queries. We are using an older version of the Aspects project deployed by the RG team, and it's possible that this issue has been addressed in more recent releases.

How:
We attempted to manage this through the roles configuration within the Superset UI but found discrepancies. For example, the role labeled as "Instructor" unexpectedly has extensive permissions, despite such extensive access being unnecessary for instructors.

Supporting Images:

[bmtcril]

Hi Vladimir, I'm looking into the permissions now though @Ian2012 may have more context on why the permissions are set the way they are. However my first thought is that ClickHouse shouldn't be using a user that has alter/insert permissions to connect to MySQL, so maybe an easy short-term solution is to give ClickHouse a user that only has select permissions on the tables it needs access to?

[Ian2012]

Which Aspects version are you running and which Open edX version?

The operator role shouldn't be empty and the instructor role has limited permissions for read-only to dashboards and charts. You can try to rerun Aspects init jobs to synchronize the role permissions, if that doesn't work try to upgrade to the latest version of Aspects.

[Ian2012]

@vladimirCampusGovIl Another thing is that you shouldn't modify the role via the UI. There is an extension to create custom roles and assign them via Python code, however you cannot modify base roles.

We are thinking on refactoring of that functionality so that you can assign and create the roles more easily, however, this is part of a major refactor to move all Python code from Aspects Jinja templates to a Python plugin which can be extended easily with other Python plugins and customizable via settings.

[bmtcril]

I did just confirm that instructors can't access sqllab in the current version of Aspects. I believe those permissions were required for some reason, though I can't remember what, but they do not grant overall access to run SQL.

[vladimirCampusGovIl]

Hi Vladimir, I'm looking into the permissions now though @Ian2012 may have more context on why the permissions are set the way they are. However my first thought is that ClickHouse shouldn't be using a user that has alter/insert permissions to connect to MySQL, so maybe an easy short-term solution is to give ClickHouse a user that only has select permissions on the tables it needs access to?

Yes, it is an option in case we need one of our operators only to make selects on the SQLLab.

Which Aspects version are you running and which Open edX version?

We use the old version of Aspects (6-month-old version or above). In case these permissions are different in the latest version of Aspects our DevOps team will need to check the approach you wrote or just get the latest version changes.

I did just confirm that instructors can't access sqllab in the current version of Aspects. I believe those permissions were required for some reason, though I can't remember what, but they do not grant overall access to run SQL.

Ok, in our version the SQLLab tab does not appear. Our need is more about how we can remove this tab for the Operators and leave it only for SA of edX (or Admin of Superset) - I wanted to try to make it using the UI changes. Now I understand that it can be done for now only using the "create custom roles and assign them via Python code" as @Ian2012 wrote above.