Open-ILS/src/sql/Pg/upgrade/0982.function.perm_functions_without_connectby.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0982', :eg_version);
   4
   5 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
   6         user_id    IN INTEGER,
   7         perm_code  IN TEXT
   8 )
   9 RETURNS SETOF INTEGER AS $$
  10 --
  11 -- Return a set of all the org units for which a given user has a given
  12 -- permission, granted directly (not through inheritance from a parent
  13 -- org unit).
  14 --
  15 -- The permissions apply to a minimum depth of the org unit hierarchy,
  16 -- for the org unit(s) to which the user is assigned.  (They also apply
  17 -- to the subordinates of those org units, but we don't report the
  18 -- subordinates here.)
  19 --
  20 -- For purposes of this function, the permission.usr_work_ou_map table
  21 -- defines which users belong to which org units.  I.e. we ignore the
  22 -- home_ou column of actor.usr.
  23 --
  24 -- The result set may contain duplicates, which should be eliminated
  25 -- by a DISTINCT clause.
  26 --
  27 DECLARE
  28         b_super       BOOLEAN;
  29         n_perm        INTEGER;
  30         n_min_depth   INTEGER;
  31         n_work_ou     INTEGER;
  32         n_curr_ou     INTEGER;
  33         n_depth       INTEGER;
  34         n_curr_depth  INTEGER;
  35 BEGIN
  36         --
  37         -- Check for superuser
  38         --
  39         SELECT INTO b_super
  40                 super_user
  41         FROM
  42                 actor.usr
  43         WHERE
  44                 id = user_id;
  45         --
  46         IF NOT FOUND THEN
  47                 return;                         -- No user?  No permissions.
  48         ELSIF b_super THEN
  49                 --
  50                 -- Super user has all permissions everywhere
  51                 --
  52                 FOR n_work_ou IN
  53                         SELECT
  54                                 id
  55                         FROM
  56                                 actor.org_unit
  57                         WHERE
  58                                 parent_ou IS NULL
  59                 LOOP
  60                         RETURN NEXT n_work_ou;
  61                 END LOOP;
  62                 RETURN;
  63         END IF;
  64         --
  65         -- Translate the permission name
  66         -- to a numeric permission id
  67         --
  68         SELECT INTO n_perm
  69                 id
  70         FROM
  71                 permission.perm_list
  72         WHERE
  73                 code = perm_code;
  74         --
  75         IF NOT FOUND THEN
  76                 RETURN;               -- No such permission
  77         END IF;
  78         --
  79         -- Find the highest-level org unit (i.e. the minimum depth)
  80         -- to which the permission is applied for this user
  81         --
  82         -- This query is modified from the one in permission.usr_perms().
  83         --
  84         SELECT INTO n_min_depth
  85                 min( depth )
  86         FROM    (
  87                 SELECT depth
  88                   FROM permission.usr_perm_map upm
  89                  WHERE upm.usr = user_id
  90                    AND (upm.perm = n_perm OR upm.perm = -1)
  91                                 UNION
  92                 SELECT  gpm.depth
  93                   FROM  permission.grp_perm_map gpm
  94                   WHERE (gpm.perm = n_perm OR gpm.perm = -1)
  95                 AND gpm.grp IN (
  96                            SELECT       (permission.grp_ancestors(
  97                                         (SELECT profile FROM actor.usr WHERE id = user_id)
  98                                 )).id
  99                         )
 100                                 UNION
 101                 SELECT  p.depth
 102                   FROM  permission.grp_perm_map p
 103                   WHERE (p.perm = n_perm OR p.perm = -1)
 104                     AND p.grp IN (
 105                                 SELECT (permission.grp_ancestors(m.grp)).id
 106                                 FROM   permission.usr_grp_map m
 107                                 WHERE  m.usr = user_id
 108                         )
 109         ) AS x;
 110         --
 111         IF NOT FOUND THEN
 112                 RETURN;                -- No such permission for this user
 113         END IF;
 114         --
 115         -- Identify the org units to which the user is assigned.  Note that
 116         -- we pay no attention to the home_ou column in actor.usr.
 117         --
 118         FOR n_work_ou IN
 119                 SELECT
 120                         work_ou
 121                 FROM
 122                         permission.usr_work_ou_map
 123                 WHERE
 124                         usr = user_id
 125         LOOP            -- For each org unit to which the user is assigned
 126                 --
 127                 -- Determine the level of the org unit by a lookup in actor.org_unit_type.
 128                 -- We take it on faith that this depth agrees with the actual hierarchy
 129                 -- defined in actor.org_unit.
 130                 --
 131                 SELECT INTO n_depth
 132                     type.depth
 133                 FROM
 134                     actor.org_unit_type type
 135                         INNER JOIN actor.org_unit ou
 136                             ON ( ou.ou_type = type.id )
 137                 WHERE
 138                     ou.id = n_work_ou;
 139                 --
 140                 IF NOT FOUND THEN
 141                         CONTINUE;        -- Maybe raise exception?
 142                 END IF;
 143                 --
 144                 -- Compare the depth of the work org unit to the
 145                 -- minimum depth, and branch accordingly
 146                 --
 147                 IF n_depth = n_min_depth THEN
 148                         --
 149                         -- The org unit is at the right depth, so return it.
 150                         --
 151                         RETURN NEXT n_work_ou;
 152                 ELSIF n_depth > n_min_depth THEN
 153                         --
 154                         -- Traverse the org unit tree toward the root,
 155                         -- until you reach the minimum depth determined above
 156                         --
 157                         n_curr_depth := n_depth;
 158                         n_curr_ou := n_work_ou;
 159                         WHILE n_curr_depth > n_min_depth LOOP
 160                                 SELECT INTO n_curr_ou
 161                                         parent_ou
 162                                 FROM
 163                                         actor.org_unit
 164                                 WHERE
 165                                         id = n_curr_ou;
 166                                 --
 167                                 IF FOUND THEN
 168                                         n_curr_depth := n_curr_depth - 1;
 169                                 ELSE
 170                                         --
 171                                         -- This can happen only if the hierarchy defined in
 172                                         -- actor.org_unit is corrupted, or out of sync with
 173                                         -- the depths defined in actor.org_unit_type.
 174                                         -- Maybe we should raise an exception here, instead
 175                                         -- of silently ignoring the problem.
 176                                         --
 177                                         n_curr_ou = NULL;
 178                                         EXIT;
 179                                 END IF;
 180                         END LOOP;
 181                         --
 182                         IF n_curr_ou IS NOT NULL THEN
 183                                 RETURN NEXT n_curr_ou;
 184                         END IF;
 185                 ELSE
 186                         --
 187                         -- The permission applies only at a depth greater than the work org unit.
 188                         -- Use connectby() to find all dependent org units at the specified depth.
 189                         --
 190                         FOR n_curr_ou IN
 191                                 SELECT id
 192                                 FROM actor.org_unit_descendants_distance(n_work_ou)
 193                                 WHERE
 194                                         distance = n_min_depth - n_depth
 195                         LOOP
 196                                 RETURN NEXT n_curr_ou;
 197                         END LOOP;
 198                 END IF;
 199                 --
 200         END LOOP;
 201         --
 202         RETURN;
 203         --
 204 END;
 205 $$ LANGUAGE 'plpgsql' ROWS 1;
 206
 207
 208 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd(
 209         user_id    IN INTEGER,
 210         perm_code  IN TEXT
 211 )
 212 RETURNS SETOF INTEGER AS $$
 213 --
 214 -- Return a set of all the org units for which a given user has a given
 215 -- permission, granted either directly or through inheritance from a parent
 216 -- org unit.
 217 --
 218 -- The permissions apply to a minimum depth of the org unit hierarchy, and
 219 -- to the subordinates of those org units, for the org unit(s) to which the
 220 -- user is assigned.
 221 --
 222 -- For purposes of this function, the permission.usr_work_ou_map table
 223 -- assigns users to org units.  I.e. we ignore the home_ou column of actor.usr.
 224 --
 225 -- The result set may contain duplicates, which should be eliminated
 226 -- by a DISTINCT clause.
 227 --
 228 DECLARE
 229         n_head_ou     INTEGER;
 230         n_child_ou    INTEGER;
 231 BEGIN
 232         FOR n_head_ou IN
 233                 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
 234         LOOP
 235                 --
 236                 -- The permission applies only at a depth greater than the work org unit.
 237                 --
 238                 FOR n_child_ou IN
 239             SELECT id
 240             FROM actor.org_unit_descendants(n_head_ou)
 241                 LOOP
 242                         RETURN NEXT n_child_ou;
 243                 END LOOP;
 244         END LOOP;
 245         --
 246         RETURN;
 247         --
 248 END;
 249 $$ LANGUAGE 'plpgsql' ROWS 1;
 250
 251 COMMIT;
 252
 253 \qecho The tablefunc database extension is no longer necessary for Evergreen.
 254 \qecho Unless you use some of its functions in your own scripts, you may
 255 \qecho want to run the following command in the database to drop it:
 256 \qecho DROP EXTENSION tablefunc;