https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37052 --- Comment #2 from gclg <nglhelpdesk@cityofglasgowcollege.ac.uk> --- If access to the permission Execute SQL reports (execute_reports) is unticked then all the options mentioned would be made unavailable. What I am proposing is the ability to limit the ability to run certain reports by used ID. -- 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=37052 Andrew Fuerste-Henry <andrewfh@dubcolib.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |andrewfh@dubcolib.org -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37052 --- Comment #4 from David Cook <dcook@prosentient.com.au> --- (In reply to gclg from comment #2)

What I am proposing is the ability to limit the ability to run certain reports by used ID.

I think that I like this idea. For backwards compatibility, I think all existing reports would have to be allowed for any User ID. However, for new Koha installs, it would be interesting to default to only allow the creator of the report to run it (or a superlibrarian). And then the creator/owner or the superlibrarian could add more users to run it. So if the only Reports permission the user has is "Execute SQL reports (execute_reports)" and they are only allowed to run certain reports... that would dramatically improve the current situation. -- One thing to keep in mind... this could get unwieldy/difficult to manage. Some libraries have hundreds of reports, and you might have 10 staff who should have the same/similar access to many of those. (This is where RBAC would come in handy. You could assign a bunch of reports to a particular group. We could potentially do this with patron category, but I don't think most libraries have granular library staff categories.) -- 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=37052 davedaghita <ddaghita@mckinneytexas.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |ddaghita@mckinneytexas.org -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37052 --- Comment #5 from gclg <nglhelpdesk@cityofglasgowcollege.ac.uk> --- We are one of the Libraries with 100s of reports so would share the pain. I reckon the limitation would need to be made on ID as the default minimal access option [bar none] is to apply the option to run a report when assigning permissions at the point of account creation. Account created [account ID] - permission to run reports [granted] - account ID required to run/execute the report. I imagine this will tick a few boxes in terms of system access and data security too. It may well be another option in the tools permissions would be needed for accounts below Super Librarian.... "Assign report permissions (execute_reports)" -- 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=37052 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|normal |enhancement -- 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=37052 Brendan Lawlor <blawlor@clamsnet.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |blawlor@clamsnet.org -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37052 --- Comment #8 from Christopher Brannon <cbrannon@cdalibrary.org> --- This is the approach we took, but since it is not baked in, can be bypassed. https://wiki.koha-community.org/wiki/JQuery_Library#Securing_Special_Reports -- 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=37052 --- Comment #10 from David Cook <dcook@prosentient.com.au> --- (In reply to Christopher Brannon from comment #7)

What if the report has a text box you could list permitted borrower numbers/card numbers in? If the box is blank, everyone can run it. If it is populated, only those mention in the box would be able to see/run it. And obviously, regardless of who is listed, the author and superlibrarians always have full access to it.

To me, the barrier to getting this in Koha isn't technological complexity. It is quite easy to do. Rather, it's getting a few people agreeing on the same design, and that's where things fall apart. For instance, your idea of a text box that could list borrowernumbers/cardnumbers in it implies a column added to the saved_sql table which would store multiple values. However, generally speaking, that's considered a sin in the database design world[1]. Instead, you'd typically create another database table that "maps" borrower DB entries to saved_sql DB entries. But then Koha developers are going to ask "but there's many things we could secure in this way... it doesn't make sense to make a "saved_sql_permissions" or "saved_sql_borrowers" table. Some people might also prefer to use patron category instead of borrowernumber/cardnumber for granting access to a report, which then adds another layer of complexity, which changes the database design. But then if we start thinking about a more generic permission/allowed user model, the challenge to create a design that applies equally well to all parts of Koha becomes overwhelming. And in the process "the perfect becomes the enemy of the good", and everyone ignores it as a task which is just too hard. -- [1] Of course, these days, JSON data types have become a thing in relational databases, which makes it very easy to store and query multi-value data in single database columns. We could add a column to "saved_sql" like "access_rules" and have it contain a JSON object which contains a list of borrowers, a list of category codes, or whatever we want really. Support for JSON in SQL databases isn't vendor agnostic, so you do get more locked-in for the type of SQL database you're using, but it's very power and very flexible. That said, I suppose an argument could be made that the permissions should live in the "borrowers" or "categories" tables, but... that starts leading us back to the perfect being the enemy of the good. -- Hopefully the above shows some of the difficulties that are associated with fixing this particular problem. -- 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=37052 --- Comment #12 from David Cook <dcook@prosentient.com.au> --- So I think that this is a duplicate of bug 30198 and that bug is older and has more CCs so I think I'll mark this one as a duplicate of that one. I am going to take the title of this report though and copy it over to there though as it's the more accurate title. -- 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=37052 --- Comment #14 from David Cook <dcook@prosentient.com.au> --- I'm starting to think that "module-based" permission policies might not be a bad idea. Here's a thought on columns for a "perm_policies_reports" table for the Reports module: policy_id (autonumber), report_id (nullable), report_group (nullable), borrowernumber (nullable), categorycode (nullable), action, effect (ie allow/deny). The nullable columns allow for great flexibility but we'd probably want to be careful with our coding. For instance, something like the following could be used to allow executing reports to everyone: 1, null, null, null, null, 'execute_reports', 'allow' Although we'd have to decide whether this refers to just the staff interface or all interfaces. Maybe we need another column for "interface". Alternatively, I suppose we could also do: policy_id (autonumber), policy_json (JSON/LONGTEXT) But it means we'd need to embrace vendor-specific JSON functions for querying, and I do think MariaDB and MySQL have some differences in JSON support, so maybe that's not practical after all. -- 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=37052 --- Comment #16 from David Cook <dcook@prosentient.com.au> --- (In reply to David Cook from comment #15)

As I was falling asleep last night, I remember that someone somewhere suggested we treat reports like lists. We could have private and public reports, and then private reports could be shared with other users (and perhaps categories).

We already have a pattern for that, so I suppose we could imitate it.

After reviewing bug 40396 I take that back. Permissions for lists are a mess. -- 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=37052 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=40546 -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.