Bug 7003 - Canned report to show patrons and permissions
Summary: Canned report to show patrons and permissions
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: Main
Hardware: All All
: P5 - low new feature (vote)
Assignee: Galen Charlton
QA Contact: Bugs List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-10-11 15:34 UTC by Nicole C. Engard
Modified: 2024-04-23 21:33 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Nicole C. Engard 2011-10-11 15:34:57 UTC
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.
Comment 1 Nicole C. Engard 2013-05-02 21:50:49 UTC
I still think this is a good idea :)
Comment 2 Nicole C. Engard 2013-05-08 00:26:13 UTC
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
Comment 3 Nick Clemens 2014-12-13 18:22:41 UTC
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
Comment 4 Katrin Fischer 2019-04-28 22:38:14 UTC
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.
Comment 5 Caroline Cyr La Rose 2024-04-23 19:40:09 UTC
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