From 2790b6e8a1d375134ff654d098eeccafea2f27f7 Mon Sep 17 00:00:00 2001 From: Michele Morgan Date: Thu, 29 Sep 2016 16:35:20 -0400 Subject: [PATCH] LP#1480432: choose broadest depth if staff has same perm multiple times Fixes a staff user permission depth issue that can exist when multiple permission groups are assigned. In particular, this patch clarifies that if a given permission is assigned to a staff member multiple times, e.g., via mutiple profiles or by individual permission mapping, whatever permission depth is the broadest will apply. To test ------- [1] Run the t/lp1480432_test_func.permissions.usr_perms_depth_sort.pg pgTAP test. [2] Set up a staff user that has the same permission at multiple depths, and verify that its scope of applicability applies at the broadest depth. For example, if you give SET_CIRC_CLAIMS_RETURNED at system and consortial depth, verify that the staff user can mark any loan as claims returned regardless of system. Signed-off-by: Michele Morgan Signed-off-by: Cesar Velez Signed-off-by: Galen Charlton --- .../src/sql/Pg/006.schema.permissions.sql | 2 +- .../XXXX.function.permission.user_perms.sql | 27 +++++++++++++++++++ 2 files changed, 28 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.permission.user_perms.sql diff --git a/Open-ILS/src/sql/Pg/006.schema.permissions.sql b/Open-ILS/src/sql/Pg/006.schema.permissions.sql index 30f5ce86ac..df154fbf6c 100644 --- a/Open-ILS/src/sql/Pg/006.schema.permissions.sql +++ b/Open-ILS/src/sql/Pg/006.schema.permissions.sql @@ -177,7 +177,7 @@ CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission FROM permission.grp_perm_map p WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1)) ) AS x - ORDER BY 2, 3, 1 DESC, 5 DESC ; + ORDER BY 2, 3, 4 ASC, 5 DESC ; $$ LANGUAGE SQL STABLE ROWS 10; CREATE TABLE permission.usr_work_ou_map ( diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.permission.user_perms.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.permission.user_perms.sql new file mode 100644 index 0000000000..10af7ba973 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.permission.user_perms.sql @@ -0,0 +1,27 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('0991', :eg_version); + +CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$ + SELECT DISTINCT ON (usr,perm) * + FROM ( + (SELECT * FROM permission.usr_perm_map WHERE usr = $1) + UNION ALL + (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable + FROM permission.grp_perm_map p + WHERE p.grp IN ( + SELECT (permission.grp_ancestors( + (SELECT profile FROM actor.usr WHERE id = $1) + )).id + ) + ) + UNION ALL + (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable + FROM permission.grp_perm_map p + WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1)) + ) AS x + ORDER BY 2, 3, 4 ASC, 5 DESC ; +$$ LANGUAGE SQL STABLE ROWS 10; + +COMMIT; + -- 2.43.2