2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
19 DROP SCHEMA IF EXISTS permission CASCADE;
22 CREATE SCHEMA permission;
24 CREATE TABLE permission.perm_list (
25 id SERIAL PRIMARY KEY,
26 code TEXT NOT NULL UNIQUE,
29 CREATE INDEX perm_list_code_idx ON permission.perm_list (code);
30 CREATE TRIGGER maintain_perm_i18n_tgr
31 AFTER UPDATE ON permission.perm_list
32 FOR EACH ROW EXECUTE PROCEDURE oils_i18n_id_tracking('ppl');
34 CREATE TABLE permission.grp_tree (
35 id SERIAL PRIMARY KEY,
36 name TEXT NOT NULL UNIQUE,
37 parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
38 usergroup BOOL NOT NULL DEFAULT TRUE,
39 perm_interval INTERVAL DEFAULT '3 years'::interval NOT NULL,
41 application_perm TEXT,
42 hold_priority INT NOT NULL DEFAULT 0
44 CREATE INDEX grp_tree_parent_idx ON permission.grp_tree (parent);
46 CREATE TABLE permission.grp_penalty_threshold (
47 id SERIAL PRIMARY KEY,
48 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
49 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
50 penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
51 threshold NUMERIC(8,2) NOT NULL,
52 CONSTRAINT penalty_grp_once UNIQUE (grp,penalty,org_unit)
55 CREATE TABLE permission.grp_perm_map (
56 id SERIAL PRIMARY KEY,
57 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
58 perm INT NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
60 grantable BOOL NOT NULL DEFAULT FALSE,
61 CONSTRAINT perm_grp_once UNIQUE (grp,perm)
64 CREATE TABLE permission.usr_perm_map (
65 id SERIAL PRIMARY KEY,
66 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
67 perm INT NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
69 grantable BOOL NOT NULL DEFAULT FALSE,
70 CONSTRAINT perm_usr_once UNIQUE (usr,perm)
73 CREATE TABLE permission.usr_object_perm_map (
74 id SERIAL PRIMARY KEY,
75 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
76 perm INT NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
77 object_type TEXT NOT NULL,
78 object_id TEXT NOT NULL,
79 grantable BOOL NOT NULL DEFAULT FALSE,
80 CONSTRAINT perm_usr_obj_once UNIQUE (usr,perm,object_type,object_id)
83 CREATE INDEX uopm_usr_idx ON permission.usr_object_perm_map (usr);
85 CREATE TABLE permission.usr_grp_map (
86 id SERIAL PRIMARY KEY,
87 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
88 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
89 CONSTRAINT usr_grp_once UNIQUE (usr,grp)
92 CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$
94 FROM connectby('permission.grp_tree'::text,'parent'::text,'id'::text,'name'::text,$1::text,100,'.'::text)
95 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
96 JOIN permission.grp_tree a ON a.id::text = t.keyid::text
98 CASE WHEN a.parent IS NULL
102 $$ LANGUAGE SQL STABLE;
104 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
105 SELECT DISTINCT ON (usr,perm) *
107 (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
109 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
110 FROM permission.grp_perm_map p
112 SELECT (permission.grp_ancestors(
113 (SELECT profile FROM actor.usr WHERE id = $1)
118 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
119 FROM permission.grp_perm_map p
120 WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
122 ORDER BY 2, 3, 1 DESC, 5 DESC ;
123 $$ LANGUAGE SQL STABLE;
125 CREATE TABLE permission.usr_work_ou_map (
126 id SERIAL PRIMARY KEY,
127 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
128 work_ou INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
129 CONSTRAINT usr_work_ou_once UNIQUE (usr,work_ou)
132 CREATE OR REPLACE FUNCTION permission.usr_can_grant_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
134 r_usr actor.usr%ROWTYPE;
135 r_perm permission.usr_perm_map%ROWTYPE;
138 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
140 IF r_usr.active = FALSE THEN
144 IF r_usr.super_user = TRUE THEN
148 FOR r_perm IN SELECT *
149 FROM permission.usr_perms(iuser) p
150 JOIN permission.perm_list l
152 WHERE (l.code = tperm AND p.grantable IS TRUE)
156 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
157 WHERE id = r_usr.home_ou;
170 CREATE OR REPLACE FUNCTION permission.usr_has_home_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
172 r_usr actor.usr%ROWTYPE;
173 r_perm permission.usr_perm_map%ROWTYPE;
176 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
178 IF r_usr.active = FALSE THEN
182 IF r_usr.super_user = TRUE THEN
186 FOR r_perm IN SELECT *
187 FROM permission.usr_perms(iuser) p
188 JOIN permission.perm_list l
194 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
195 WHERE id = r_usr.home_ou;
208 CREATE OR REPLACE FUNCTION permission.usr_has_work_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
210 r_woum permission.usr_work_ou_map%ROWTYPE;
211 r_usr actor.usr%ROWTYPE;
212 r_perm permission.usr_perm_map%ROWTYPE;
215 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
217 IF r_usr.active = FALSE THEN
221 IF r_usr.super_user = TRUE THEN
225 FOR r_perm IN SELECT *
226 FROM permission.usr_perms(iuser) p
227 JOIN permission.perm_list l
233 FOR r_woum IN SELECT *
234 FROM permission.usr_work_ou_map
239 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
240 WHERE id = r_woum.work_ou;
254 CREATE OR REPLACE FUNCTION permission.usr_has_object_perm ( iuser INT, tperm TEXT, obj_type TEXT, obj_id TEXT, target_ou INT ) RETURNS BOOL AS $$
256 r_usr actor.usr%ROWTYPE;
260 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
262 IF r_usr.active = FALSE THEN
266 IF r_usr.super_user = TRUE THEN
270 SELECT TRUE INTO res FROM permission.usr_object_perm_map WHERE usr = r_usr.id AND object_type = obj_type AND object_id = obj_id;
276 IF target_ou > -1 THEN
277 RETURN permission.usr_has_perm( iuser, tperm, target_ou);
285 CREATE OR REPLACE FUNCTION permission.usr_has_object_perm ( INT, TEXT, TEXT, TEXT ) RETURNS BOOL AS $$
286 SELECT permission.usr_has_object_perm( $1, $2, $3, $4, -1 );
289 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( INT, TEXT, INT ) RETURNS BOOL AS $$
291 WHEN permission.usr_has_home_perm( $1, $2, $3 ) THEN TRUE
292 WHEN permission.usr_has_work_perm( $1, $2, $3 ) THEN TRUE
298 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
302 RETURNS SETOF INTEGER AS $$
304 -- Return a set of all the org units for which a given user has a given
305 -- permission, granted directly (not through inheritance from a parent
308 -- The permissions apply to a minimum depth of the org unit hierarchy,
309 -- for the org unit(s) to which the user is assigned. (They also apply
310 -- to the subordinates of those org units, but we don't report the
311 -- subordinates here.)
313 -- For purposes of this function, the permission.usr_work_ou_map table
314 -- defines which users belong to which org units. I.e. we ignore the
315 -- home_ou column of actor.usr.
317 -- The result set may contain duplicates, which should be eliminated
318 -- by a DISTINCT clause.
327 n_curr_depth INTEGER;
330 -- Check for superuser
340 return; -- No user? No permissions.
343 -- Super user has all permissions everywhere
353 RETURN NEXT n_work_ou;
358 -- Translate the permission name
359 -- to a numeric permission id
369 RETURN; -- No such permission
372 -- Find the highest-level org unit (i.e. the minimum depth)
373 -- to which the permission is applied for this user
375 -- This query is modified from the one in permission.usr_perms().
377 SELECT INTO n_min_depth
381 FROM permission.usr_perm_map upm
382 WHERE upm.usr = user_id
383 AND (upm.perm = n_perm OR upm.perm = -1)
386 FROM permission.grp_perm_map gpm
387 WHERE (gpm.perm = n_perm OR gpm.perm = -1)
389 SELECT (permission.grp_ancestors(
390 (SELECT profile FROM actor.usr WHERE id = user_id)
395 FROM permission.grp_perm_map p
396 WHERE (p.perm = n_perm OR p.perm = -1)
398 SELECT (permission.grp_ancestors(m.grp)).id
399 FROM permission.usr_grp_map m
400 WHERE m.usr = user_id
405 RETURN; -- No such permission for this user
408 -- Identify the org units to which the user is assigned. Note that
409 -- we pay no attention to the home_ou column in actor.usr.
415 permission.usr_work_ou_map
418 LOOP -- For each org unit to which the user is assigned
420 -- Determine the level of the org unit by a lookup in actor.org_unit_type.
421 -- We take it on faith that this depth agrees with the actual hierarchy
422 -- defined in actor.org_unit.
427 actor.org_unit_type type
428 INNER JOIN actor.org_unit ou
429 ON ( ou.ou_type = type.id )
434 CONTINUE; -- Maybe raise exception?
437 -- Compare the depth of the work org unit to the
438 -- minimum depth, and branch accordingly
440 IF n_depth = n_min_depth THEN
442 -- The org unit is at the right depth, so return it.
444 RETURN NEXT n_work_ou;
445 ELSIF n_depth > n_min_depth THEN
447 -- Traverse the org unit tree toward the root,
448 -- until you reach the minimum depth determined above
450 n_curr_depth := n_depth;
451 n_curr_ou := n_work_ou;
452 WHILE n_curr_depth > n_min_depth LOOP
453 SELECT INTO n_curr_ou
461 n_curr_depth := n_curr_depth - 1;
464 -- This can happen only if the hierarchy defined in
465 -- actor.org_unit is corrupted, or out of sync with
466 -- the depths defined in actor.org_unit_type.
467 -- Maybe we should raise an exception here, instead
468 -- of silently ignoring the problem.
475 IF n_curr_ou IS NOT NULL THEN
476 RETURN NEXT n_curr_ou;
480 -- The permission applies only at a depth greater than the work org unit.
481 -- Use connectby() to find all dependent org units at the specified depth.
486 'actor.org_unit', -- table name
488 'parent_ou', -- recursive foreign key
489 n_work_ou::TEXT, -- id of starting point
490 (n_min_depth - n_depth) -- max depth to search, relative
491 ) -- to starting point
493 ou text, -- dependent org unit
494 parent_ou text, -- (ignore)
495 level int -- depth relative to starting point
498 level = n_min_depth - n_depth
500 RETURN NEXT n_curr_ou;
509 $$ LANGUAGE 'plpgsql';
512 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd(
516 RETURNS SETOF INTEGER AS $$
518 -- Return a set of all the org units for which a given user has a given
519 -- permission, granted either directly or through inheritance from a parent
522 -- The permissions apply to a minimum depth of the org unit hierarchy, and
523 -- to the subordinates of those org units, for the org unit(s) to which the
526 -- For purposes of this function, the permission.usr_work_ou_map table
527 -- assigns users to org units. I.e. we ignore the home_ou column of actor.usr.
529 -- The result set may contain duplicates, which should be eliminated
530 -- by a DISTINCT clause.
537 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
540 -- The permission applies only at a depth greater than the work org unit.
541 -- Use connectby() to find all dependent org units at the specified depth.
546 'actor.org_unit', -- table name
548 'parent_ou', -- recursive foreign key
549 n_head_ou::TEXT, -- id of starting point
550 0 -- no limit on search depth
553 ou text, -- dependent org unit
554 parent_ou text, -- (ignore)
555 level int -- (ignore)
558 RETURN NEXT n_child_ou;
565 $$ LANGUAGE 'plpgsql';
568 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at(
572 RETURNS SETOF INTEGER AS $$
573 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( $1, $2 );
577 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all(
581 RETURNS SETOF INTEGER AS $$
582 SELECT DISTINCT * FROM permission.usr_has_perm_at_all_nd( $1, $2 );