From 4ae54b5b608d6f784ec74e4e6aa34107f23e8629 Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 3 Mar 2010 16:38:52 +0000 Subject: [PATCH] patch from Jason Stephenson to allow the EVERYTHING permission in permission.usr_has_perm_at_nd git-svn-id: svn://svn.open-ils.org/ILS/trunk@15673 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/006.schema.permissions.sql | 6 +- ...ma.permission.usr_has_perm_at_nd-EVERYTHING.sql | 220 +++++++++++++++++++++ 3 files changed, 224 insertions(+), 4 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0175.schema.permission.usr_has_perm_at_nd-EVERYTHING.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 3bd17d6678..8a855338e3 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0174'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0175'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/006.schema.permissions.sql b/Open-ILS/src/sql/Pg/006.schema.permissions.sql index 66126b04a4..7e5515fcfc 100644 --- a/Open-ILS/src/sql/Pg/006.schema.permissions.sql +++ b/Open-ILS/src/sql/Pg/006.schema.permissions.sql @@ -376,11 +376,11 @@ BEGIN SELECT depth FROM permission.usr_perm_map upm WHERE upm.usr = user_id - AND upm.perm = n_perm + AND (upm.perm = n_perm OR upm.perm = -1) UNION SELECT gpm.depth FROM permission.grp_perm_map gpm - WHERE gpm.perm = n_perm + WHERE (gpm.perm = n_perm OR gpm.perm = -1) AND gpm.grp IN ( SELECT (permission.grp_ancestors( (SELECT profile FROM actor.usr WHERE id = user_id) @@ -389,7 +389,7 @@ BEGIN UNION SELECT p.depth FROM permission.grp_perm_map p - WHERE p.perm = n_perm + WHERE (p.perm = n_perm OR p.perm = -1) AND p.grp IN ( SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m diff --git a/Open-ILS/src/sql/Pg/upgrade/0175.schema.permission.usr_has_perm_at_nd-EVERYTHING.sql b/Open-ILS/src/sql/Pg/upgrade/0175.schema.permission.usr_has_perm_at_nd-EVERYTHING.sql new file mode 100644 index 0000000000..890dbb3747 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0175.schema.permission.usr_has_perm_at_nd-EVERYTHING.sql @@ -0,0 +1,220 @@ + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0175'); -- From patch by Jason Stephenson (applied by miker) + +CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd( + user_id IN INTEGER, + perm_code IN TEXT +) +RETURNS SETOF INTEGER AS $$ +-- +-- Return a set of all the org units for which a given user has a given +-- permission, granted directly (not through inheritance from a parent +-- org unit). +-- +-- The permissions apply to a minimum depth of the org unit hierarchy, +-- for the org unit(s) to which the user is assigned. (They also apply +-- to the subordinates of those org units, but we don't report the +-- subordinates here.) +-- +-- For purposes of this function, the permission.usr_work_ou_map table +-- defines which users belong to which org units. I.e. we ignore the +-- home_ou column of actor.usr. +-- +-- The result set may contain duplicates, which should be eliminated +-- by a DISTINCT clause. +-- +DECLARE + b_super BOOLEAN; + n_perm INTEGER; + n_min_depth INTEGER; + n_work_ou INTEGER; + n_curr_ou INTEGER; + n_depth INTEGER; + n_curr_depth INTEGER; +BEGIN + -- + -- Check for superuser + -- + SELECT INTO b_super + super_user + FROM + actor.usr + WHERE + id = user_id; + -- + IF NOT FOUND THEN + return; -- No user? No permissions. + ELSIF b_super THEN + -- + -- Super user has all permissions everywhere + -- + FOR n_work_ou IN + SELECT + id + FROM + actor.org_unit + WHERE + parent_ou IS NULL + LOOP + RETURN NEXT n_work_ou; + END LOOP; + RETURN; + END IF; + -- + -- Translate the permission name + -- to a numeric permission id + -- + SELECT INTO n_perm + id + FROM + permission.perm_list + WHERE + code = perm_code; + -- + IF NOT FOUND THEN + RETURN; -- No such permission + END IF; + -- + -- Find the highest-level org unit (i.e. the minimum depth) + -- to which the permission is applied for this user + -- + -- This query is modified from the one in permission.usr_perms(). + -- + SELECT INTO n_min_depth + min( depth ) + FROM ( + SELECT depth + FROM permission.usr_perm_map upm + WHERE upm.usr = user_id + AND (upm.perm = n_perm OR upm.perm = -1) + UNION + SELECT gpm.depth + FROM permission.grp_perm_map gpm + WHERE (gpm.perm = n_perm OR gpm.perm = -1) + AND gpm.grp IN ( + SELECT (permission.grp_ancestors( + (SELECT profile FROM actor.usr WHERE id = user_id) + )).id + ) + UNION + SELECT p.depth + FROM permission.grp_perm_map p + WHERE (p.perm = n_perm OR p.perm = -1) + AND p.grp IN ( + SELECT (permission.grp_ancestors(m.grp)).id + FROM permission.usr_grp_map m + WHERE m.usr = user_id + ) + ) AS x; + -- + IF NOT FOUND THEN + RETURN; -- No such permission for this user + END IF; + -- + -- Identify the org units to which the user is assigned. Note that + -- we pay no attention to the home_ou column in actor.usr. + -- + FOR n_work_ou IN + SELECT + work_ou + FROM + permission.usr_work_ou_map + WHERE + usr = user_id + LOOP -- For each org unit to which the user is assigned + -- + -- Determine the level of the org unit by a lookup in actor.org_unit_type. + -- We take it on faith that this depth agrees with the actual hierarchy + -- defined in actor.org_unit. + -- + SELECT INTO n_depth + type.depth + FROM + actor.org_unit_type type + INNER JOIN actor.org_unit ou + ON ( ou.ou_type = type.id ) + WHERE + ou.id = n_work_ou; + -- + IF NOT FOUND THEN + CONTINUE; -- Maybe raise exception? + END IF; + -- + -- Compare the depth of the work org unit to the + -- minimum depth, and branch accordingly + -- + IF n_depth = n_min_depth THEN + -- + -- The org unit is at the right depth, so return it. + -- + RETURN NEXT n_work_ou; + ELSIF n_depth > n_min_depth THEN + -- + -- Traverse the org unit tree toward the root, + -- until you reach the minimum depth determined above + -- + n_curr_depth := n_depth; + n_curr_ou := n_work_ou; + WHILE n_curr_depth > n_min_depth LOOP + SELECT INTO n_curr_ou + parent_ou + FROM + actor.org_unit + WHERE + id = n_curr_ou; + -- + IF FOUND THEN + n_curr_depth := n_curr_depth - 1; + ELSE + -- + -- This can happen only if the hierarchy defined in + -- actor.org_unit is corrupted, or out of sync with + -- the depths defined in actor.org_unit_type. + -- Maybe we should raise an exception here, instead + -- of silently ignoring the problem. + -- + n_curr_ou = NULL; + EXIT; + END IF; + END LOOP; + -- + IF n_curr_ou IS NOT NULL THEN + RETURN NEXT n_curr_ou; + END IF; + ELSE + -- + -- The permission applies only at a depth greater than the work org unit. + -- Use connectby() to find all dependent org units at the specified depth. + -- + FOR n_curr_ou IN + SELECT ou::INTEGER + FROM connectby( + 'actor.org_unit', -- table name + 'id', -- key column + 'parent_ou', -- recursive foreign key + n_work_ou::TEXT, -- id of starting point + (n_min_depth - n_depth) -- max depth to search, relative + ) -- to starting point + AS t( + ou text, -- dependent org unit + parent_ou text, -- (ignore) + level int -- depth relative to starting point + ) + WHERE + level = n_min_depth - n_depth + LOOP + RETURN NEXT n_curr_ou; + END LOOP; + END IF; + -- + END LOOP; + -- + RETURN; + -- +END; +$$ LANGUAGE 'plpgsql'; + +COMMIT; + -- 2.11.0