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 ROWS 1;
104 CREATE OR REPLACE FUNCTION permission.grp_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
105 WITH RECURSIVE grp_ancestors_distance(id, distance) AS (
108 SELECT pgt.parent, gad.distance+1
109 FROM permission.grp_tree pgt JOIN grp_ancestors_distance gad ON (pgt.id = gad.id)
110 WHERE pgt.parent IS NOT NULL
112 SELECT * FROM grp_ancestors_distance;
113 $$ LANGUAGE SQL STABLE ROWS 1;
115 CREATE OR REPLACE FUNCTION permission.grp_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
116 WITH RECURSIVE grp_descendants_distance(id, distance) AS (
119 SELECT pgt.id, gdd.distance+1
120 FROM permission.grp_tree pgt JOIN grp_descendants_distance gdd ON (pgt.parent = gdd.id)
122 SELECT * FROM grp_descendants_distance;
123 $$ LANGUAGE SQL STABLE ROWS 1;
125 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
126 SELECT DISTINCT ON (usr,perm) *
128 (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
130 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
131 FROM permission.grp_perm_map p
133 SELECT (permission.grp_ancestors(
134 (SELECT profile FROM actor.usr WHERE id = $1)
139 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
140 FROM permission.grp_perm_map p
141 WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
143 ORDER BY 2, 3, 1 DESC, 5 DESC ;
144 $$ LANGUAGE SQL STABLE ROWS 10;
146 CREATE TABLE permission.usr_work_ou_map (
147 id SERIAL PRIMARY KEY,
148 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
149 work_ou INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
150 CONSTRAINT usr_work_ou_once UNIQUE (usr,work_ou)
153 CREATE OR REPLACE FUNCTION permission.usr_can_grant_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
155 r_usr actor.usr%ROWTYPE;
156 r_perm permission.usr_perm_map%ROWTYPE;
159 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
161 IF r_usr.active = FALSE THEN
165 IF r_usr.super_user = TRUE THEN
169 FOR r_perm IN SELECT *
170 FROM permission.usr_perms(iuser) p
171 JOIN permission.perm_list l
173 WHERE (l.code = tperm AND p.grantable IS TRUE)
177 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
178 WHERE id = r_usr.home_ou;
191 CREATE OR REPLACE FUNCTION permission.usr_has_home_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
193 r_usr actor.usr%ROWTYPE;
194 r_perm permission.usr_perm_map%ROWTYPE;
197 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
199 IF r_usr.active = FALSE THEN
203 IF r_usr.super_user = TRUE THEN
207 FOR r_perm IN SELECT *
208 FROM permission.usr_perms(iuser) p
209 JOIN permission.perm_list l
215 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
216 WHERE id = r_usr.home_ou;
229 CREATE OR REPLACE FUNCTION permission.usr_has_work_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
231 r_woum permission.usr_work_ou_map%ROWTYPE;
232 r_usr actor.usr%ROWTYPE;
233 r_perm permission.usr_perm_map%ROWTYPE;
236 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
238 IF r_usr.active = FALSE THEN
242 IF r_usr.super_user = TRUE THEN
246 FOR r_perm IN SELECT *
247 FROM permission.usr_perms(iuser) p
248 JOIN permission.perm_list l
254 FOR r_woum IN SELECT *
255 FROM permission.usr_work_ou_map
260 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
261 WHERE id = r_woum.work_ou;
275 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 $$
277 r_usr actor.usr%ROWTYPE;
281 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
283 IF r_usr.active = FALSE THEN
287 IF r_usr.super_user = TRUE THEN
291 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;
297 IF target_ou > -1 THEN
298 RETURN permission.usr_has_perm( iuser, tperm, target_ou);
306 CREATE OR REPLACE FUNCTION permission.usr_has_object_perm ( INT, TEXT, TEXT, TEXT ) RETURNS BOOL AS $$
307 SELECT permission.usr_has_object_perm( $1, $2, $3, $4, -1 );
310 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( INT, TEXT, INT ) RETURNS BOOL AS $$
312 WHEN permission.usr_has_home_perm( $1, $2, $3 ) THEN TRUE
313 WHEN permission.usr_has_work_perm( $1, $2, $3 ) THEN TRUE
319 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
323 RETURNS SETOF INTEGER AS $$
325 -- Return a set of all the org units for which a given user has a given
326 -- permission, granted directly (not through inheritance from a parent
329 -- The permissions apply to a minimum depth of the org unit hierarchy,
330 -- for the org unit(s) to which the user is assigned. (They also apply
331 -- to the subordinates of those org units, but we don't report the
332 -- subordinates here.)
334 -- For purposes of this function, the permission.usr_work_ou_map table
335 -- defines which users belong to which org units. I.e. we ignore the
336 -- home_ou column of actor.usr.
338 -- The result set may contain duplicates, which should be eliminated
339 -- by a DISTINCT clause.
348 n_curr_depth INTEGER;
351 -- Check for superuser
361 return; -- No user? No permissions.
364 -- Super user has all permissions everywhere
374 RETURN NEXT n_work_ou;
379 -- Translate the permission name
380 -- to a numeric permission id
390 RETURN; -- No such permission
393 -- Find the highest-level org unit (i.e. the minimum depth)
394 -- to which the permission is applied for this user
396 -- This query is modified from the one in permission.usr_perms().
398 SELECT INTO n_min_depth
402 FROM permission.usr_perm_map upm
403 WHERE upm.usr = user_id
404 AND (upm.perm = n_perm OR upm.perm = -1)
407 FROM permission.grp_perm_map gpm
408 WHERE (gpm.perm = n_perm OR gpm.perm = -1)
410 SELECT (permission.grp_ancestors(
411 (SELECT profile FROM actor.usr WHERE id = user_id)
416 FROM permission.grp_perm_map p
417 WHERE (p.perm = n_perm OR p.perm = -1)
419 SELECT (permission.grp_ancestors(m.grp)).id
420 FROM permission.usr_grp_map m
421 WHERE m.usr = user_id
426 RETURN; -- No such permission for this user
429 -- Identify the org units to which the user is assigned. Note that
430 -- we pay no attention to the home_ou column in actor.usr.
436 permission.usr_work_ou_map
439 LOOP -- For each org unit to which the user is assigned
441 -- Determine the level of the org unit by a lookup in actor.org_unit_type.
442 -- We take it on faith that this depth agrees with the actual hierarchy
443 -- defined in actor.org_unit.
448 actor.org_unit_type type
449 INNER JOIN actor.org_unit ou
450 ON ( ou.ou_type = type.id )
455 CONTINUE; -- Maybe raise exception?
458 -- Compare the depth of the work org unit to the
459 -- minimum depth, and branch accordingly
461 IF n_depth = n_min_depth THEN
463 -- The org unit is at the right depth, so return it.
465 RETURN NEXT n_work_ou;
466 ELSIF n_depth > n_min_depth THEN
468 -- Traverse the org unit tree toward the root,
469 -- until you reach the minimum depth determined above
471 n_curr_depth := n_depth;
472 n_curr_ou := n_work_ou;
473 WHILE n_curr_depth > n_min_depth LOOP
474 SELECT INTO n_curr_ou
482 n_curr_depth := n_curr_depth - 1;
485 -- This can happen only if the hierarchy defined in
486 -- actor.org_unit is corrupted, or out of sync with
487 -- the depths defined in actor.org_unit_type.
488 -- Maybe we should raise an exception here, instead
489 -- of silently ignoring the problem.
496 IF n_curr_ou IS NOT NULL THEN
497 RETURN NEXT n_curr_ou;
501 -- The permission applies only at a depth greater than the work org unit.
502 -- Use connectby() to find all dependent org units at the specified depth.
507 'actor.org_unit', -- table name
509 'parent_ou', -- recursive foreign key
510 n_work_ou::TEXT, -- id of starting point
511 (n_min_depth - n_depth) -- max depth to search, relative
512 ) -- to starting point
514 ou text, -- dependent org unit
515 parent_ou text, -- (ignore)
516 level int -- depth relative to starting point
519 level = n_min_depth - n_depth
521 RETURN NEXT n_curr_ou;
530 $$ LANGUAGE 'plpgsql' ROWS 1;
533 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd(
537 RETURNS SETOF INTEGER AS $$
539 -- Return a set of all the org units for which a given user has a given
540 -- permission, granted either directly or through inheritance from a parent
543 -- The permissions apply to a minimum depth of the org unit hierarchy, and
544 -- to the subordinates of those org units, for the org unit(s) to which the
547 -- For purposes of this function, the permission.usr_work_ou_map table
548 -- assigns users to org units. I.e. we ignore the home_ou column of actor.usr.
550 -- The result set may contain duplicates, which should be eliminated
551 -- by a DISTINCT clause.
558 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
561 -- The permission applies only at a depth greater than the work org unit.
562 -- Use connectby() to find all dependent org units at the specified depth.
567 'actor.org_unit', -- table name
569 'parent_ou', -- recursive foreign key
570 n_head_ou::TEXT, -- id of starting point
571 0 -- no limit on search depth
574 ou text, -- dependent org unit
575 parent_ou text, -- (ignore)
576 level int -- (ignore)
579 RETURN NEXT n_child_ou;
586 $$ LANGUAGE 'plpgsql' ROWS 1;
589 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at(
593 RETURNS SETOF INTEGER AS $$
594 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( $1, $2 );
595 $$ LANGUAGE 'sql' ROWS 1;
598 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all(
602 RETURNS SETOF INTEGER AS $$
603 SELECT DISTINCT * FROM permission.usr_has_perm_at_all_nd( $1, $2 );
604 $$ LANGUAGE 'sql' ROWS 1;