search

IBM / db2forzosdeveloperextension-about

IBM Db2 for z/OS Developer Extension for VS Code
https://IBM.github.io/db2forzosdeveloperextension-about/
Other
12 stars 7 forks source link

Users should not be able to create tuning connection profiles #109

Open Marty850 opened 1 year ago

Marty850 commented 1 year ago

As a Db2 administrator I want to provide the Db2 tuning services to our developers as easys as possible. Thats why we use one tuning connection profile with a technical userid for all the developers. The problem is that every user can change or delete this profile and create other profiles.

In my point of view it would be better to have a parameter where I can secure the administration of tuning connection profiles so that only administrators are able to create, delete and modify tuning connection profiles.

katsoohoo commented 1 year ago

Hi @Marty850,

SQL Tuning Services has user privilege management features that may be able to help with your scenario.

Firstly, there are 2 kinds of SQL Tuning Services users: user and administrator. Regular users have execute permission on IBMTMS.CANVIEW UDF while administrators have execute permission on IBMTMS.CANVIEW and IBMTMS.CANADMINISTER UDFs. For more details, see https://www.ibm.com/docs/en/db2-for-zos/13?topic=services-setting-up-required-user-ids-permissions

For your scenario, the DBAs would be a SQL Tuning Services administrator and developers would be SQL Tuning Services users. The DBA would create a tuning connection profile with the intention for developers to use. At this point, developers (being SQL Tuning Services users) won't be able to access the tuning connection profile yet. To give developer's access to the tuning connection profile, the DBA can invoke the POST tuningservice/v1/userprofileprivileges API to give user access to this tuning connection profile. Users with user access to the tuning connection profile can use the profile for tuning actions, but cannot delete or modify the profile.

One caveat is that SQL Tuning Services users can still create their own tuning connection profiles. If this is something you'd still like to restrict, we'll need to look into more granularities for user privileges.

Marty850 commented 1 year ago

Hi @katsoohoo,

thanks for the explanation. I checked the permissions and you are right! They are users and cannot change or delete the tuning profile I created. But they create new tuning profiles with wrong parameters if they don't read my manual ;-)

For me it would be great if I could restrict the creation of tuning connection profiles by users.

Marty850 commented 1 year ago

I also noticed that there are privileges for connection profiles that do not exist anymore. It seems like permissions are not deleted when a connection profile is deleted.

How can I get rid of this stale permissions?

katsoohoo commented 1 year ago

Hi @Marty850,

I will bring the enhancement about restricting creation of tuning connection profiles by users back to the team.

For the stale permissions, I wasn't able to re-create the issue on my side. Would you mind opening another issue with the detailed steps and SQL Tuning Services PTF version you are using?

zhaoxq commented 1 year ago

@Marty850
For creating profile permissions, I'm not sure if this is a common requirement. For issue #2, what are the permissions specifically? Can you pls elaborate? Thanks.

b-tsao commented 2 months ago

Hi @Marty850

Developer extension 2.1.6 with QWT PTF level UI97373 now allows users to disable tuning profile.