4 INSERT INTO config.upgrade_log (version) VALUES ('0175'); -- From patch by Jason Stephenson (applied by miker)
6 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
10 RETURNS SETOF INTEGER AS $$
12 -- Return a set of all the org units for which a given user has a given
13 -- permission, granted directly (not through inheritance from a parent
16 -- The permissions apply to a minimum depth of the org unit hierarchy,
17 -- for the org unit(s) to which the user is assigned. (They also apply
18 -- to the subordinates of those org units, but we don't report the
19 -- subordinates here.)
21 -- For purposes of this function, the permission.usr_work_ou_map table
22 -- defines which users belong to which org units. I.e. we ignore the
23 -- home_ou column of actor.usr.
25 -- The result set may contain duplicates, which should be eliminated
26 -- by a DISTINCT clause.
38 -- Check for superuser
48 return; -- No user? No permissions.
51 -- Super user has all permissions everywhere
61 RETURN NEXT n_work_ou;
66 -- Translate the permission name
67 -- to a numeric permission id
77 RETURN; -- No such permission
80 -- Find the highest-level org unit (i.e. the minimum depth)
81 -- to which the permission is applied for this user
83 -- This query is modified from the one in permission.usr_perms().
85 SELECT INTO n_min_depth
89 FROM permission.usr_perm_map upm
90 WHERE upm.usr = user_id
91 AND (upm.perm = n_perm OR upm.perm = -1)
94 FROM permission.grp_perm_map gpm
95 WHERE (gpm.perm = n_perm OR gpm.perm = -1)
97 SELECT (permission.grp_ancestors(
98 (SELECT profile FROM actor.usr WHERE id = user_id)
103 FROM permission.grp_perm_map p
104 WHERE (p.perm = n_perm OR p.perm = -1)
106 SELECT (permission.grp_ancestors(m.grp)).id
107 FROM permission.usr_grp_map m
108 WHERE m.usr = user_id
113 RETURN; -- No such permission for this user
116 -- Identify the org units to which the user is assigned. Note that
117 -- we pay no attention to the home_ou column in actor.usr.
123 permission.usr_work_ou_map
126 LOOP -- For each org unit to which the user is assigned
128 -- Determine the level of the org unit by a lookup in actor.org_unit_type.
129 -- We take it on faith that this depth agrees with the actual hierarchy
130 -- defined in actor.org_unit.
135 actor.org_unit_type type
136 INNER JOIN actor.org_unit ou
137 ON ( ou.ou_type = type.id )
142 CONTINUE; -- Maybe raise exception?
145 -- Compare the depth of the work org unit to the
146 -- minimum depth, and branch accordingly
148 IF n_depth = n_min_depth THEN
150 -- The org unit is at the right depth, so return it.
152 RETURN NEXT n_work_ou;
153 ELSIF n_depth > n_min_depth THEN
155 -- Traverse the org unit tree toward the root,
156 -- until you reach the minimum depth determined above
158 n_curr_depth := n_depth;
159 n_curr_ou := n_work_ou;
160 WHILE n_curr_depth > n_min_depth LOOP
161 SELECT INTO n_curr_ou
169 n_curr_depth := n_curr_depth - 1;
172 -- This can happen only if the hierarchy defined in
173 -- actor.org_unit is corrupted, or out of sync with
174 -- the depths defined in actor.org_unit_type.
175 -- Maybe we should raise an exception here, instead
176 -- of silently ignoring the problem.
183 IF n_curr_ou IS NOT NULL THEN
184 RETURN NEXT n_curr_ou;
188 -- The permission applies only at a depth greater than the work org unit.
189 -- Use connectby() to find all dependent org units at the specified depth.
194 'actor.org_unit', -- table name
196 'parent_ou', -- recursive foreign key
197 n_work_ou::TEXT, -- id of starting point
198 (n_min_depth - n_depth) -- max depth to search, relative
199 ) -- to starting point
201 ou text, -- dependent org unit
202 parent_ou text, -- (ignore)
203 level int -- depth relative to starting point
206 level = n_min_depth - n_depth
208 RETURN NEXT n_curr_ou;
217 $$ LANGUAGE 'plpgsql';