Querying permissions in SQL is very difficult. It would be nice to have a canned report that shows you all of your patrons and the permissions they each have (well not all patrons - but patrons with permissions). Something like this: Patron Name (linked) | Permission Levels (listed) A filter to only show patrons with 'superlibrarian' or other specific permissions would be great too.
I still think this is a good idea :)
Okay, a partner of ours came up with this report - I'm not sure how to wrap it in Perl to make it a canned report - but maybe someone else can take this as a start. If you do please credit Christopher Brannon for his amazing report: SELECT surname AS 'Last Name',firstname AS 'First Name',cardnumber AS 'Card Number',userid AS 'UserID',branchcode AS 'Library',Super AS 'superlibrarian',A AS 'circulate',SubA AS 'circulate subs',B AS 'catalogue',C AS 'parameters',SubC AS 'parameters subs',D AS 'borrowers',E AS 'permissions',F AS 'reserveforothers',SubF AS 'reserveforothers subs',G AS 'borrow',I AS 'editcatalogue',SubI AS 'editcatalogue subs',J AS 'updatecharges',K AS 'acquisition',SubK AS 'acquisition subs',L AS 'management',M AS 'tools',SubM AS 'tools subs',N AS 'editauthorities',O AS 'serials',SubO AS 'serials subs',P AS 'reports',SubP AS 'reports subs',Q AS 'staffaccess' FROM (SELECT b.surname,b.firstname,b.cardnumber,b.userid,b.branchcode,b.categorycode,@Check:=b.flags AS 'CheckQ',if(@Check-131072>=0,@Q:="On",@Q:="Off") AS 'Q', if(@Check-131072>=0,@Check:=@Check-131072,@Check) AS 'CheckP',if(@Check-65536>=0,@P:="On",@P:="Off") AS 'P',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='16' AND borrowernumber=b.borrowernumber) As SubP, if(@Check-65536>=0,@Check:=@Check-65536,@Check) AS 'CheckO',if(@Check-32768>=0,@O:="On",@O:="Off") AS 'O',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='15' AND borrowernumber=b.borrowernumber) As SubO, if(@Check-32768>=0,@Check:=@Check-32768,@Check) AS 'CheckN',if(@Check-16384>=0,@N:="On",@N:="Off") AS 'N', if(@Check-16384>=0,@Check:=@Check-16384,@Check) AS 'CheckM',if(@Check-8192>=0,@M:="On",@M:="Off") AS 'M',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='13' AND borrowernumber=b.borrowernumber) As SubM, if(@Check-8192>=0,@Check:=@Check-8192,@Check) AS 'CheckL',if(@Check-4096>=0,@L:="On",@L:="Off") AS 'L', if(@Check-4096>=0,@Check:=@Check-4096,@Check) AS 'CheckK',if(@Check-2048>=0,@K:="On",@K:="Off") AS 'K',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='11' AND borrowernumber=b.borrowernumber) As SubK, if(@Check-2048>=0,@Check:=@Check-2048,@Check) AS 'CheckJ',if(@Check-1024>=0,@J:="On",@J:="Off") AS 'J', if(@Check-1024>=0,@Check:=@Check-1024,@Check) AS 'CheckI',if(@Check-512>=0,@I:="On",@I:="Off") AS 'I',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='9' AND borrowernumber=b.borrowernumber) As SubI, if(@Check-512>=0,@Check:=@Check-512,@Check) AS 'CheckH',if(@Check-256>=0,@H:="On",@H:="Off") AS 'H', if(@Check-256>=0,@Check:=@Check-256,@Check) AS 'CheckG',if(@Check-128>=0,@G:="On",@G:="Off") AS 'G', if(@Check-128>=0,@Check:=@Check-128,@Check) AS 'CheckF',if(@Check-64>=0,@F:="On",@F:="Off") AS 'F',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='6' AND borrowernumber=b.borrowernumber) As SubF, if(@Check-64>=0,@Check:=@Check-64,@Check) AS 'CheckE',if(@Check-32>=0,@E:="On",@E:="Off") AS 'E', if(@Check-32>=0,@Check:=@Check-32,@Check) AS 'CheckD',if(@Check-16>=0,@D:="On",@D:="Off") AS 'D', if(@Check-16>=0,@Check:=@Check-16,@Check) AS 'CheckC',if(@Check-8>=0,@C:="On",@C:="Off") AS 'C',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='3' AND borrowernumber=b.borrowernumber) As SubC, if(@Check-8>=0,@Check:=@Check-8,@Check) AS 'CheckB',if(@Check-4>=0,@B:="On",@B:="Off") AS 'B', if(@Check-4>=0,@Check:=@Check-4,@Check) AS 'CheckA',if(@Check-2>=0,@A:="On",@A:="Off") AS 'A',(SELECT group_concat(up.code) FROM user_permissions up WHERE up.module_bit='1' AND borrowernumber=b.borrowernumber) As SubA, if(@Check-2>=0,@Check:=@Check-2,@Check) AS 'CheckSuper',if(b.flags=1,"On","Off") AS "Super" FROM borrowers b LEFT JOIN categories USING (categorycode) WHERE b.branchcode=<<Accounts for|branches>> AND b.categorycode=<<Account type|categorycode>>) AS MainFlags ORDER BY surname, firstname ASC
Just throwing this in, Chris and I rewrote the report to make it easier to read: SELECT surname,firstname,cardnumber, categorycode, branchcode, IF(flags MOD 2,'Set','') AS SuperLib, IF(MOD(flags DIV 2,2),'All parameters',GROUP_CONCAT(IF(u_p.module_bit=1,p.code,'') SEPARATOR ' ' ) ) AS "CircPermissions", IF(MOD(flags DIV 4,2),'Set','') AS 'View staff interface', IF(MOD(flags DIV 8,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=3,p.code,'') SEPARATOR ' ' ) ) AS ManParams, IF(MOD(flags DIV 16,2),'Set','') AS 'Add/modify patrons', IF(MOD(flags DIV 32,2),'Set','') AS 'Modify permissions', IF(MOD(flags DIV 64,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=6,p.code,'') SEPARATOR ' ' ) ) AS ReservePermissions, IF(MOD(flags DIV 128,2),'Set','') AS BorrowBooks, IF(MOD(flags DIV 512,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=9,p.code,'') SEPARATOR ' ' ) ) AS EditCatalogue, IF(MOD(flags DIV 1024,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=10,p.code,'') SEPARATOR ' ' ) ) AS UpdateCharges, IF(MOD(flags DIV 2048,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=11,p.code,'') SEPARATOR ' ' ) ) AS Acquisition, IF(MOD(flags DIV 4096,2),'Set','') AS Management, IF(MOD(flags DIV 8192,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=13,p.code,'') SEPARATOR ' ' ) ) AS Tools, IF(MOD(flags DIV 16384,2),'Set','') AS EditAuthories, IF(MOD(flags DIV 32768,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=15,p.code,'') SEPARATOR ' ' ) ) AS Series, IF(MOD(flags DIV 65536,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=16,p.code,'') SEPARATOR ' ' ) ) AS Reports, IF(MOD(flags DIV 131072,2),'Set','') AS StaffAccess, IF(MOD(flags DIV 262144,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=18,p.code,'') SEPARATOR ' ' ) ) AS CourseReserves, IF(MOD(flags DIV 524288,2),"All parameters" ,GROUP_CONCAT(IF(u_p.module_bit=19,p.code,'') SEPARATOR ' ' ) ) AS Plugins FROM borrowers b LEFT JOIN user_permissions u_p ON b.borrowernumber=u_p.borrowernumber LEFT JOIN permissions p ON u_p.code=p.code WHERE flags>0 OR u_p.module_bit>0 GROUP BY b.borrowernumber ORDER BY categorycode,branchcode,surname,firstname ASC
I think this would be really nice, especially if it auto-updated with new permissions being added. If you have a lot of staff fluctuation you might want to check regularly that all old account have been removed or at least the permissions reset.
I was just about to write a new bug report about this. I'm looking for all the users in one instance who have a specific permission. Maybe this could be done in conjunction with the revamp of user permissions (bug 20813?), but we need an easier way to see who has which permission than an SQL report like this monster https://wiki.koha-community.org/wiki/SQL_Reports_Patrons#Accounts_with_permissions_.28v22.11.29 (kudos to Christopher for figuring all that out!) it's massive! A page in Administration would be great I think. - See all the users who have permissions - See all permissions of a user (could be a link to their "set permissions" page) - See all users who have a particular permission - Seeing this page could be limited by a permission or could be linked to the existing "permissions" permission
*** Bug 38708 has been marked as a duplicate of this bug. ***
I've got an old local customization (written in PHP) that renders a huge table that shows patron permissions. It uses the permission tables so that it dynamically updates itself and doesn't require manual updates each time a new permission is added. (Although that means it's not translation friendly.) It's quite an unwieldy table though, because it's so long. I think Caroline has lots of good ideas there, although this isn't something I'm super keen to work on. I've thought about re-developing my existing work as a Koha plugin, but it hasn't been that popular of a report locally I think, so it's not something I've been very motivated to do either. A small part of me wants to get rid of this local customization though, so I'm a little interested in getting a simple equivalent into upstream...