3 SELECT evergreen.upgrade_deps_block_check('0982', :eg_version);
5 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
9 RETURNS SETOF INTEGER AS $$
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
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.)
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.
24 -- The result set may contain duplicates, which should be eliminated
25 -- by a DISTINCT clause.
37 -- Check for superuser
47 return; -- No user? No permissions.
50 -- Super user has all permissions everywhere
60 RETURN NEXT n_work_ou;
65 -- Translate the permission name
66 -- to a numeric permission id
76 RETURN; -- No such permission
79 -- Find the highest-level org unit (i.e. the minimum depth)
80 -- to which the permission is applied for this user
82 -- This query is modified from the one in permission.usr_perms().
84 SELECT INTO n_min_depth
88 FROM permission.usr_perm_map upm
89 WHERE upm.usr = user_id
90 AND (upm.perm = n_perm OR upm.perm = -1)
93 FROM permission.grp_perm_map gpm
94 WHERE (gpm.perm = n_perm OR gpm.perm = -1)
96 SELECT (permission.grp_ancestors(
97 (SELECT profile FROM actor.usr WHERE id = user_id)
102 FROM permission.grp_perm_map p
103 WHERE (p.perm = n_perm OR p.perm = -1)
105 SELECT (permission.grp_ancestors(m.grp)).id
106 FROM permission.usr_grp_map m
107 WHERE m.usr = user_id
112 RETURN; -- No such permission for this user
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.
122 permission.usr_work_ou_map
125 LOOP -- For each org unit to which the user is assigned
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.
134 actor.org_unit_type type
135 INNER JOIN actor.org_unit ou
136 ON ( ou.ou_type = type.id )
141 CONTINUE; -- Maybe raise exception?
144 -- Compare the depth of the work org unit to the
145 -- minimum depth, and branch accordingly
147 IF n_depth = n_min_depth THEN
149 -- The org unit is at the right depth, so return it.
151 RETURN NEXT n_work_ou;
152 ELSIF n_depth > n_min_depth THEN
154 -- Traverse the org unit tree toward the root,
155 -- until you reach the minimum depth determined above
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
168 n_curr_depth := n_curr_depth - 1;
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.
182 IF n_curr_ou IS NOT NULL THEN
183 RETURN NEXT n_curr_ou;
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.
192 FROM actor.org_unit_descendants_distance(n_work_ou)
194 distance = n_min_depth - n_depth
196 RETURN NEXT n_curr_ou;
205 $$ LANGUAGE 'plpgsql' ROWS 1;
208 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd(
212 RETURNS SETOF INTEGER AS $$
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
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
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.
225 -- The result set may contain duplicates, which should be eliminated
226 -- by a DISTINCT clause.
233 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
236 -- The permission applies only at a depth greater than the work org unit.
240 FROM actor.org_unit_descendants(n_head_ou)
242 RETURN NEXT n_child_ou;
249 $$ LANGUAGE 'plpgsql' ROWS 1;
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;