Bug 33363 separated suggestions_manage permission in three permissions for creating, updating, and deleting suggetions. In bugs test plan and in its atomicupdate file it is stated that these permissions should have been added only for those users who previously had suggestions_manage permissions. However there is a typo on SQL insert statement and new permissions have been added to all users with full catalogue permissions: >INSERT IGNORE INTO user_permissions (borrowernumber, module_bit, code) SELECT borrowernumber, 12, 'suggestions_create' FROM borrowers WHERE flags & (1 << 2)
I'm just about to finish for the day, so I get you to just rephrase this one? What's "full catalogue permissions"?
(In reply to David Cook from comment #1) > I'm just about to finish for the day, so I get you to just rephrase this > one? What's "full catalogue permissions"? Usually referring to the module level checkbox instead of sub-permissions.
Now that it's the start of the day, I understand much better. Looking at userflags, bit 2 is "catalogue" (hence "full catalogue permissions"). It looks like that WHERE should've been "WHERE flags & (1 << 12)", since bit 12 is "suggestions".
This is an interesting problem. We should fix this atomic update in "main", 24.11, 25.05, and 25.11 so that newly upgraded sites don't suffer this same problem. But for sites that are already upgraded... I don't know that we can do any automated remediation, because we can't know which users we updated automatically and which were given these permissions after the fact. We might just have to add a warning in the release notes telling people to review the permissions of all their users because of this mistake that was made. -- Locally, I created a permission audit tool many years ago within Koha. It doesn't have the best UI but it does provide an overview of all permissions for all patrons. Something like that would be useful in this situation... otherwise perhaps an SQL report we could provide in the release notes so that people can double-check their users.
(In reply to David Cook from comment #4) > But for sites that are already upgraded... I don't know that we can do any > automated remediation, because we can't know which users we updated > automatically and which were given these permissions after the fact. That being said... if a user has "suggestions_create" and "suggestions_delete" and not "suggestions_manage", that's pretty suspicious.
Created attachment 188182 [details] [review] Bug 41045: Fix BZ 33363 database update This patch fixes the BZ 33363 database update, so that it selects uses with the "suggestions" permission rather than the "catalogue" permission.
I've attached the patch that fixes the problem for future upgrades, but I'll see what people think regarding the need for any additional patches for trying to address past problematic upgrades.
Note I'm going to immediately backport this patch locally myself... and I'm planning on writing some queries to find any users with that have only "suggestions_create" and "suggestions_delete" but not "suggestions_manage"...
(In reply to David Cook from comment #8) > Note I'm going to immediately backport this patch locally myself... and I'm > planning on writing some queries to find any users with that have only > "suggestions_create" and "suggestions_delete" but not "suggestions_manage"... There's probably a better way to do this, but this is the query I used in the end: select borrowernumber,group_concat(code) as perms from user_permissions where module_bit = 12 group by borrowernumber having perms like '%suggestions_create%' and perms like '%suggestions_delete%' and perms not like '%suggestions_manage%' ; It's possible that some users legitimately had that set of permissions, but it seemed unlikely to me, so I wrote a Perl script to blitz them.