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 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);
31 CREATE TABLE permission.grp_tree (
32 id SERIAL PRIMARY KEY,
33 name TEXT NOT NULL UNIQUE,
34 parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
35 usergroup BOOL NOT NULL DEFAULT TRUE,
36 perm_interval INTERVAL DEFAULT '3 years'::interval NOT NULL,
40 CREATE INDEX grp_tree_parent_idx ON permission.grp_tree (parent);
42 CREATE TABLE permission.grp_penalty_threshold (
43 id SERIAL PRIMARY KEY,
44 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
45 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
46 penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
47 threshold NUMERIC(8,2) NOT NULL,
48 CONSTRAINT penalty_grp_once UNIQUE (grp,penalty)
51 CREATE TABLE permission.grp_perm_map (
52 id SERIAL PRIMARY KEY,
53 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
54 perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
56 grantable BOOL NOT NULL DEFAULT FALSE,
57 CONSTRAINT perm_grp_once UNIQUE (grp,perm)
60 CREATE TABLE permission.usr_perm_map (
61 id SERIAL PRIMARY KEY,
62 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
63 perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
65 grantable BOOL NOT NULL DEFAULT FALSE,
66 CONSTRAINT perm_usr_once UNIQUE (usr,perm)
69 CREATE TABLE permission.usr_object_perm_map (
70 id SERIAL PRIMARY KEY,
71 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
72 perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
73 object_type TEXT NOT NULL,
74 object_id TEXT NOT NULL,
75 grantable BOOL NOT NULL DEFAULT FALSE,
76 CONSTRAINT perm_usr_obj_once UNIQUE (usr,perm,object_type,object_id)
79 CREATE INDEX uopm_usr_idx ON permission.usr_object_perm_map (usr);
81 CREATE TABLE permission.usr_grp_map (
82 id SERIAL PRIMARY KEY,
83 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
84 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
85 CONSTRAINT usr_grp_once UNIQUE (usr,grp)
88 CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$
90 FROM connectby('permission.grp_tree'::text,'parent'::text,'id'::text,'name'::text,$1::text,100,'.'::text)
91 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
92 JOIN permission.grp_tree a ON a.id::text = t.keyid::text
94 CASE WHEN a.parent IS NULL
98 $$ LANGUAGE SQL STABLE;
100 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
101 SELECT DISTINCT ON (usr,perm) *
103 (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
105 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
106 FROM permission.grp_perm_map p
108 SELECT (permission.grp_ancestors(
109 (SELECT profile FROM actor.usr WHERE id = $1)
114 (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
115 FROM permission.grp_perm_map p
116 WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
118 ORDER BY 2, 3, 1 DESC, 5 DESC ;
119 $$ LANGUAGE SQL STABLE;
121 CREATE TABLE permission.usr_work_ou_map (
122 id SERIAL PRIMARY KEY,
123 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
124 work_ou INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
125 CONSTRAINT usr_work_ou_once UNIQUE (usr,work_ou)
128 CREATE OR REPLACE FUNCTION permission.usr_can_grant_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
130 r_usr actor.usr%ROWTYPE;
131 r_perm permission.usr_perm_map%ROWTYPE;
134 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
136 IF r_usr.active = FALSE THEN
140 IF r_usr.super_user = TRUE THEN
144 FOR r_perm IN SELECT *
145 FROM permission.usr_perms(iuser) p
146 JOIN permission.perm_list l
148 WHERE (l.code = tperm AND p.grantable IS TRUE)
152 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
153 WHERE id = r_usr.home_ou;
166 CREATE OR REPLACE FUNCTION permission.usr_has_home_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
168 r_usr actor.usr%ROWTYPE;
169 r_perm permission.usr_perm_map%ROWTYPE;
172 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
174 IF r_usr.active = FALSE THEN
178 IF r_usr.super_user = TRUE THEN
182 FOR r_perm IN SELECT *
183 FROM permission.usr_perms(iuser) p
184 JOIN permission.perm_list l
190 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
191 WHERE id = r_usr.home_ou;
204 CREATE OR REPLACE FUNCTION permission.usr_has_work_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
206 r_woum permission.usr_work_ou_map%ROWTYPE;
207 r_usr actor.usr%ROWTYPE;
208 r_perm permission.usr_perm_map%ROWTYPE;
211 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
213 IF r_usr.active = FALSE THEN
217 IF r_usr.super_user = TRUE THEN
221 FOR r_perm IN SELECT *
222 FROM permission.usr_perms(iuser) p
223 JOIN permission.perm_list l
229 FOR r_woum IN SELECT *
230 FROM permission.usr_work_ou_map
235 FROM actor.org_unit_descendants(target_ou,r_perm.depth)
236 WHERE id = r_woum.work_ou;
250 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 $$
252 r_usr actor.usr%ROWTYPE;
256 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
258 IF r_usr.active = FALSE THEN
262 IF r_usr.super_user = TRUE THEN
266 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;
272 IF target_ou > -1 THEN
273 RETURN permission.usr_has_perm( iuser, tperm, target_ou);
281 CREATE OR REPLACE FUNCTION permission.usr_has_object_perm ( INT, TEXT, TEXT, TEXT ) RETURNS BOOL AS $$
282 SELECT permission.usr_has_object_perm( $1, $2, $3, $4, -1 );
285 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( INT, TEXT, INT ) RETURNS BOOL AS $$
287 WHEN permission.usr_has_home_perm( $1, $2, $3 ) THEN TRUE
288 WHEN permission.usr_has_work_perm( $1, $2, $3 ) THEN TRUE
294 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
298 RETURNS SETOF INTEGER AS $$
300 -- Return a set of all the org units for which a given user has a given
301 -- permission, granted directly (not through inheritance from a parent
304 -- The permissions apply to a minimum depth of the org unit hierarchy,
305 -- for the org unit(s) to which the user is assigned. (They also apply
306 -- to the subordinates of those org units, but we don't report the
307 -- subordinates here.)
309 -- For purposes of this function, the permission.usr_work_ou_map table
310 -- defines which users belong to which org units. I.e. we ignore the
311 -- home_ou column of actor.usr.
313 -- The result set may contain duplicates, which should be eliminated
314 -- by a DISTINCT clause.
323 n_curr_depth INTEGER;
326 -- Check for superuser
336 return; -- No user? No permissions.
339 -- Super user has all permissions everywhere
349 RETURN NEXT n_work_ou;
354 -- Translate the permission name
355 -- to a numeric permission id
365 RETURN; -- No such permission
368 -- Find the highest-level org unit (i.e. the minimum depth)
369 -- to which the permission is applied for this user
371 -- This query is modified from the one in permission.usr_perms().
373 SELECT INTO n_min_depth
377 FROM permission.usr_perm_map upm
378 WHERE upm.usr = user_id
379 AND upm.perm = n_perm
382 FROM permission.grp_perm_map gpm
383 WHERE gpm.perm = n_perm
385 SELECT (permission.grp_ancestors(
386 (SELECT profile FROM actor.usr WHERE id = user_id)
391 FROM permission.grp_perm_map p
392 WHERE p.perm = n_perm
394 SELECT (permission.grp_ancestors(m.grp)).id
395 FROM permission.usr_grp_map m
396 WHERE m.usr = user_id
401 RETURN; -- No such permission for this user
404 -- Identify the org units to which the user is assigned. Note that
405 -- we pay no attention to the home_ou column in actor.usr.
411 permission.usr_work_ou_map
414 LOOP -- For each org unit to which the user is assigned
416 -- Determine the level of the org unit by a lookup in actor.org_unit_type.
417 -- We take it on faith that this depth agrees with the actual hierarchy
418 -- defined in actor.org_unit.
423 actor.org_unit_type type
424 INNER JOIN actor.org_unit ou
425 ON ( ou.ou_type = type.id )
430 CONTINUE; -- Maybe raise exception?
433 -- Compare the depth of the work org unit to the
434 -- minimum depth, and branch accordingly
436 IF n_depth = n_min_depth THEN
438 -- The org unit is at the right depth, so return it.
440 RETURN NEXT n_work_ou;
441 ELSIF n_depth > n_min_depth THEN
443 -- Traverse the org unit tree toward the root,
444 -- until you reach the minimum depth determined above
446 n_curr_depth := n_depth;
447 n_curr_ou := n_work_ou;
448 WHILE n_curr_depth > n_min_depth LOOP
449 SELECT INTO n_curr_ou
457 n_curr_depth := n_curr_depth - 1;
460 -- This can happen only if the hierarchy defined in
461 -- actor.org_unit is corrupted, or out of sync with
462 -- the depths defined in actor.org_unit_type.
463 -- Maybe we should raise an exception here, instead
464 -- of silently ignoring the problem.
471 IF n_curr_ou IS NOT NULL THEN
472 RETURN NEXT n_curr_ou;
476 -- The permission applies only at a depth greater than the work org unit.
477 -- Use connectby() to find all dependent org units at the specified depth.
482 'actor.org_unit', -- table name
484 'parent_ou', -- recursive foreign key
485 n_work_ou::TEXT, -- id of starting point
486 (n_min_depth - n_depth) -- max depth to search, relative
487 ) -- to starting point
489 ou text, -- dependent org unit
490 parent_ou text, -- (ignore)
491 level int -- depth relative to starting point
494 level = n_min_depth - n_depth
496 RETURN NEXT n_curr_ou;
505 $$ LANGUAGE 'plpgsql';
508 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd(
512 RETURNS SETOF INTEGER AS $$
514 -- Return a set of all the org units for which a given user has a given
515 -- permission, granted either directly or through inheritance from a parent
518 -- The permissions apply to a minimum depth of the org unit hierarchy, and
519 -- to the subordinates of those org units, for the org unit(s) to which the
522 -- For purposes of this function, the permission.usr_work_ou_map table
523 -- assigns users to org units. I.e. we ignore the home_ou column of actor.usr.
525 -- The result set may contain duplicates, which should be eliminated
526 -- by a DISTINCT clause.
533 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
536 -- The permission applies only at a depth greater than the work org unit.
537 -- Use connectby() to find all dependent org units at the specified depth.
542 'actor.org_unit', -- table name
544 'parent_ou', -- recursive foreign key
545 n_head_ou::TEXT, -- id of starting point
546 0 -- no limit on search depth
549 ou text, -- dependent org unit
550 parent_ou text, -- (ignore)
551 level int -- (ignore)
554 RETURN NEXT n_child_ou;
561 $$ LANGUAGE 'plpgsql';
564 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at(
568 RETURNS SETOF INTEGER AS $$
569 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( $1, $2 );
573 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all(
577 RETURNS SETOF INTEGER AS $$
578 SELECT DISTINCT * FROM permission.usr_has_perm_at_all_nd( $1, $2 );