[Bug 41045] New: Suggestions manage permissions added to patrons who previously had no permissions in that category
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Bug ID: 41045 Summary: Suggestions manage permissions added to patrons who previously had no permissions in that category Change sponsored?: --- Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Patrons Assignee: koha-bugs@lists.koha-community.org Reporter: emmi.takkinen@koha-suomi.fi QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com, kyle@bywatersolutions.com 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)
-- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |33363 CC| |jonathan.druart@gmail.com Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33363 [Bug 33363] More specific permissions for purchase suggestions -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Emmi Takkinen <emmi.takkinen@koha-suomi.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|enhancement |normal -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Group|Koha security | Component|Koha |Architecture, internals, | |and plumbing Product|Koha security |Koha QA Contact| |testopia@bugs.koha-communit | |y.org Assignee|chris@bigballofwax.co.nz |koha-bugs@lists.koha-commun | |ity.org --- Comment #1 from David Cook <dcook@prosentient.com.au> --- I'm just about to finish for the day, so I get you to just rephrase this one? What's "full catalogue permissions"? -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 --- Comment #2 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 --- Comment #3 from David Cook <dcook@prosentient.com.au> --- 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". -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 --- Comment #4 from David Cook <dcook@prosentient.com.au> --- 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. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 --- Comment #5 from David Cook <dcook@prosentient.com.au> --- (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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 --- Comment #6 from David Cook <dcook@prosentient.com.au> --- Created attachment 188182 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=188182&action=edit 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. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |In Discussion --- Comment #7 from David Cook <dcook@prosentient.com.au> --- 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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 --- Comment #8 from David Cook <dcook@prosentient.com.au> --- 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"... -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Component|Architecture, internals, |Patrons |and plumbing | -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 --- Comment #9 from David Cook <dcook@prosentient.com.au> --- (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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|normal |critical --- Comment #10 from Fridolin Somers <fridolin.somers@biblibre.com> --- Ouch we just found this. Upgrading important to Critical since it adds permissions :O -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 --- Comment #11 from Fridolin Somers <fridolin.somers@biblibre.com> --- (In reply to David Cook from comment #9)
(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.
You can add sort by code to this SQL so that result is easier to read : select borrowernumber,group_concat(code order by 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%' -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Eric Phetteplace <ephetteplace@cca.edu> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |ephetteplace@cca.edu --- Comment #12 from Eric Phetteplace <ephetteplace@cca.edu> --- Note for folks trying to use this SQL in a report; it contains the SQL keywords create and delete which are not allowed, so you can to work around them with CONCAT: select borrowernumber,group_concat(code order by code) as perms from user_permissions where module_bit = 12 group by borrowernumber having perms like CONCAT('%suggestions_cr','eate%') and perms like CONCAT('%suggestions_de','lete%') and perms not like '%suggestions_manage%' -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 --- Comment #13 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Eric Phetteplace from comment #12)
Note for folks trying to use this SQL in a report; it contains the SQL keywords create and delete which are not allowed, so you can to work around them with CONCAT:
select borrowernumber,group_concat(code order by code) as perms from user_permissions where module_bit = 12 group by borrowernumber having perms like CONCAT('%suggestions_cr','eate%') and perms like CONCAT('%suggestions_de','lete%') and perms not like '%suggestions_manage%'
Haha, nice :) -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|In Discussion |Needs Signoff --- Comment #14 from David Cook <dcook@prosentient.com.au> --- Trying to recall why I put this to "In Discussion". Going to move it to Needs Signoff I guess. People try it out? Let me know if you need a test plan for it. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Emmi Takkinen <emmi.takkinen@koha-suomi.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #188182|0 |1 is obsolete| | --- Comment #15 from Emmi Takkinen <emmi.takkinen@koha-suomi.fi> --- Created attachment 195691 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195691&action=edit 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. Signed-off-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi> -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Emmi Takkinen <emmi.takkinen@koha-suomi.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off --- Comment #16 from Emmi Takkinen <emmi.takkinen@koha-suomi.fi> --- Tested, works as described. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |emily.lamancusa@montgomeryc | |ountymd.gov Assignee|koha-bugs@lists.koha-commun |dcook@prosentient.com.au |ity.org | QA Contact|testopia@bugs.koha-communit |emily.lamancusa@montgomeryc |y.org |ountymd.gov -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195691|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 --- Comment #17 from Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> --- Created attachment 195701 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195701&action=edit 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. Signed-off-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi> Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Trivial patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |26.05.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 --- Comment #18 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Thanks for all the hard work! Pushed to main for the next 26.05.00 release as RM Assistant -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |rel_25_05_candidate, | |rel_25_11_candidate --- Comment #19 from David Cook <dcook@prosentient.com.au> --- We should've backported this one I reckon -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Jacob O'Mara <jacob.omara@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to main |Pushed to stable Version(s)|26.05.00 |26.05.00,25.11.05 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 --- Comment #20 from Jacob O'Mara <jacob.omara@openfifth.co.uk> --- Thanks all, pushed to 25.11.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |wainuiwitikapark@catalyst.n | |et.nz Version(s)|26.05.00,25.11.05 |26.05.00,25.11.05,25.05.12 released in| | Status|Pushed to stable |Pushed to oldstable --- Comment #21 from Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> --- Backported to 25.05 for 25.05.12 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to oldstable |Pushed to oldoldstable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to oldoldstable |Pushed to oldoldoldstable --- Comment #22 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to 24.11.x for v24.11.17 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|26.05.00,25.11.05,25.05.12 |26.05.00,25.11.05,25.05.12, released in| |24.11.17 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41045 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to oldoldoldstable |Needs documenting -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org