27cf87bed7e3e5339c42684e54976d3819b6dfe1
[working/Evergreen.git] / Open-ILS / src / sql / Pg / 006.schema.permissions.sql
1 /*
2  * Copyright (C) 2004-2008  Georgia Public Library Service
3  * Copyright (C) 2008  Equinox Software, Inc.
4  * Mike Rylander <miker@esilibrary.com> 
5  *
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.
10  *
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.
15  *
16  */
17
18
19 DROP SCHEMA IF EXISTS permission CASCADE;
20
21 BEGIN;
22 CREATE SCHEMA permission;
23
24 CREATE TABLE permission.perm_list (
25         id              SERIAL  PRIMARY KEY,
26         code            TEXT    NOT NULL UNIQUE,
27         description     TEXT
28 );
29 CREATE INDEX perm_list_code_idx ON permission.perm_list (code);
30
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,
37         description             TEXT,
38         application_perm        TEXT
39 );
40 CREATE INDEX grp_tree_parent_idx ON permission.grp_tree (parent);
41
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,org_unit)
49 );
50
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 UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
55         depth           INT     NOT NULL,
56         grantable       BOOL    NOT NULL DEFAULT FALSE,
57                 CONSTRAINT perm_grp_once UNIQUE (grp,perm)
58 );
59
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 UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
64         depth           INT     NOT NULL,
65         grantable       BOOL    NOT NULL DEFAULT FALSE,
66                 CONSTRAINT perm_usr_once UNIQUE (usr,perm)
67 );
68
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 UPDATE CASCADE 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)
77 );
78
79 CREATE INDEX uopm_usr_idx ON permission.usr_object_perm_map (usr);
80
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)
86 );
87
88 CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$
89         SELECT  a.*
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
93         ORDER BY
94                 CASE WHEN a.parent IS NULL
95                         THEN 0
96                         ELSE 1
97                 END, a.name;
98 $$ LANGUAGE SQL STABLE;
99
100 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
101         SELECT  DISTINCT ON (usr,perm) *
102           FROM  (
103                         (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
104                                         UNION ALL
105                         (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
106                           FROM  permission.grp_perm_map p
107                           WHERE p.grp IN (
108                                 SELECT  (permission.grp_ancestors(
109                                                 (SELECT profile FROM actor.usr WHERE id = $1)
110                                         )).id
111                                 )
112                         )
113                                         UNION ALL
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))
117                 ) AS x
118           ORDER BY 2, 3, 1 DESC, 5 DESC ;
119 $$ LANGUAGE SQL STABLE;
120
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)
126 );
127
128 CREATE OR REPLACE FUNCTION permission.usr_can_grant_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
129 DECLARE
130         r_usr   actor.usr%ROWTYPE;
131         r_perm  permission.usr_perm_map%ROWTYPE;
132 BEGIN
133
134         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
135
136         IF r_usr.active = FALSE THEN
137                 RETURN FALSE;
138         END IF;
139
140         IF r_usr.super_user = TRUE THEN
141                 RETURN TRUE;
142         END IF;
143
144         FOR r_perm IN   SELECT  *
145                           FROM  permission.usr_perms(iuser) p
146                                 JOIN permission.perm_list l
147                                         ON (l.id = p.perm)
148                           WHERE (l.code = tperm AND p.grantable IS TRUE)
149                 LOOP
150
151                 PERFORM *
152                   FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
153                   WHERE id = r_usr.home_ou;
154
155                 IF FOUND THEN
156                         RETURN TRUE;
157                 ELSE
158                         RETURN FALSE;
159                 END IF;
160         END LOOP;
161
162         RETURN FALSE;
163 END;
164 $$ LANGUAGE PLPGSQL;
165
166 CREATE OR REPLACE FUNCTION permission.usr_has_home_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
167 DECLARE
168         r_usr   actor.usr%ROWTYPE;
169         r_perm  permission.usr_perm_map%ROWTYPE;
170 BEGIN
171
172         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
173
174         IF r_usr.active = FALSE THEN
175                 RETURN FALSE;
176         END IF;
177
178         IF r_usr.super_user = TRUE THEN
179                 RETURN TRUE;
180         END IF;
181
182         FOR r_perm IN   SELECT  *
183                           FROM  permission.usr_perms(iuser) p
184                                 JOIN permission.perm_list l
185                                         ON (l.id = p.perm)
186                           WHERE l.code = tperm
187                                 OR p.perm = -1 LOOP
188
189                 PERFORM *
190                   FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
191                   WHERE id = r_usr.home_ou;
192
193                 IF FOUND THEN
194                         RETURN TRUE;
195                 ELSE
196                         RETURN FALSE;
197                 END IF;
198         END LOOP;
199
200         RETURN FALSE;
201 END;
202 $$ LANGUAGE PLPGSQL;
203
204 CREATE OR REPLACE FUNCTION permission.usr_has_work_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
205 DECLARE
206         r_woum  permission.usr_work_ou_map%ROWTYPE;
207         r_usr   actor.usr%ROWTYPE;
208         r_perm  permission.usr_perm_map%ROWTYPE;
209 BEGIN
210
211         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
212
213         IF r_usr.active = FALSE THEN
214                 RETURN FALSE;
215         END IF;
216
217         IF r_usr.super_user = TRUE THEN
218                 RETURN TRUE;
219         END IF;
220
221         FOR r_perm IN   SELECT  *
222                           FROM  permission.usr_perms(iuser) p
223                                 JOIN permission.perm_list l
224                                         ON (l.id = p.perm)
225                           WHERE l.code = tperm
226                                 OR p.perm = -1
227                 LOOP
228
229                 FOR r_woum IN   SELECT  *
230                                   FROM  permission.usr_work_ou_map
231                                   WHERE usr = iuser
232                         LOOP
233
234                         PERFORM *
235                           FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
236                           WHERE id = r_woum.work_ou;
237
238                         IF FOUND THEN
239                                 RETURN TRUE;
240                         END IF;
241
242                 END LOOP;
243
244         END LOOP;
245
246         RETURN FALSE;
247 END;
248 $$ LANGUAGE PLPGSQL;
249
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 $$
251 DECLARE
252         r_usr   actor.usr%ROWTYPE;
253         res     BOOL;
254 BEGIN
255
256         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
257
258         IF r_usr.active = FALSE THEN
259                 RETURN FALSE;
260         END IF;
261
262         IF r_usr.super_user = TRUE THEN
263                 RETURN TRUE;
264         END IF;
265
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;
267
268         IF FOUND THEN
269                 RETURN TRUE;
270         END IF;
271
272         IF target_ou > -1 THEN
273                 RETURN permission.usr_has_perm( iuser, tperm, target_ou);
274         END IF;
275
276         RETURN FALSE;
277
278 END;
279 $$ LANGUAGE PLPGSQL;
280
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 );
283 $$ LANGUAGE SQL;
284
285 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( INT, TEXT, INT ) RETURNS BOOL AS $$
286         SELECT  CASE
287                         WHEN permission.usr_has_home_perm( $1, $2, $3 ) THEN TRUE
288                         WHEN permission.usr_has_work_perm( $1, $2, $3 ) THEN TRUE
289                         ELSE FALSE
290                 END;
291 $$ LANGUAGE SQL;
292
293
294 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
295         user_id    IN INTEGER,
296         perm_code  IN TEXT
297 )
298 RETURNS SETOF INTEGER AS $$
299 --
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
302 -- org unit).
303 --
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.)
308 --
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.
312 --
313 -- The result set may contain duplicates, which should be eliminated
314 -- by a DISTINCT clause.
315 --
316 DECLARE
317         b_super       BOOLEAN;
318         n_perm        INTEGER;
319         n_min_depth   INTEGER; 
320         n_work_ou     INTEGER;
321         n_curr_ou     INTEGER;
322         n_depth       INTEGER;
323         n_curr_depth  INTEGER;
324 BEGIN
325         --
326         -- Check for superuser
327         --
328         SELECT INTO b_super
329                 super_user
330         FROM
331                 actor.usr
332         WHERE
333                 id = user_id;
334         --
335         IF NOT FOUND THEN
336                 return;                         -- No user?  No permissions.
337         ELSIF b_super THEN
338                 --
339                 -- Super user has all permissions everywhere
340                 --
341                 FOR n_work_ou IN
342                         SELECT
343                                 id
344                         FROM
345                                 actor.org_unit
346                         WHERE
347                                 parent_ou IS NULL
348                 LOOP
349                         RETURN NEXT n_work_ou; 
350                 END LOOP;
351                 RETURN;
352         END IF;
353         --
354         -- Translate the permission name
355         -- to a numeric permission id
356         --
357         SELECT INTO n_perm
358                 id
359         FROM
360                 permission.perm_list
361         WHERE
362                 code = perm_code;
363         --
364         IF NOT FOUND THEN
365                 RETURN;               -- No such permission
366         END IF;
367         --
368         -- Find the highest-level org unit (i.e. the minimum depth)
369         -- to which the permission is applied for this user
370         --
371         -- This query is modified from the one in permission.usr_perms().
372         --
373         SELECT INTO n_min_depth
374                 min( depth )
375         FROM    (
376                 SELECT depth 
377                   FROM permission.usr_perm_map upm
378                  WHERE upm.usr = user_id 
379                    AND (upm.perm = n_perm OR upm.perm = -1)
380                                 UNION
381                 SELECT  gpm.depth
382                   FROM  permission.grp_perm_map gpm
383                   WHERE (gpm.perm = n_perm OR gpm.perm = -1)
384                 AND gpm.grp IN (
385                            SELECT       (permission.grp_ancestors(
386                                         (SELECT profile FROM actor.usr WHERE id = user_id)
387                                 )).id
388                         )
389                                 UNION
390                 SELECT  p.depth
391                   FROM  permission.grp_perm_map p 
392                   WHERE (p.perm = n_perm OR p.perm = -1)
393                     AND p.grp IN (
394                                 SELECT (permission.grp_ancestors(m.grp)).id 
395                                 FROM   permission.usr_grp_map m
396                                 WHERE  m.usr = user_id
397                         )
398         ) AS x;
399         --
400         IF NOT FOUND THEN
401                 RETURN;                -- No such permission for this user
402         END IF;
403         --
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.
406         --
407         FOR n_work_ou IN
408                 SELECT
409                         work_ou
410                 FROM
411                         permission.usr_work_ou_map
412                 WHERE
413                         usr = user_id
414         LOOP            -- For each org unit to which the user is assigned
415                 --
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.
419                 --
420                 SELECT INTO n_depth
421                     type.depth
422                 FROM
423                     actor.org_unit_type type
424                         INNER JOIN actor.org_unit ou
425                             ON ( ou.ou_type = type.id )
426                 WHERE
427                     ou.id = n_work_ou;
428                 --
429                 IF NOT FOUND THEN
430                         CONTINUE;        -- Maybe raise exception?
431                 END IF;
432                 --
433                 -- Compare the depth of the work org unit to the
434                 -- minimum depth, and branch accordingly
435                 --
436                 IF n_depth = n_min_depth THEN
437                         --
438                         -- The org unit is at the right depth, so return it.
439                         --
440                         RETURN NEXT n_work_ou;
441                 ELSIF n_depth > n_min_depth THEN
442                         --
443                         -- Traverse the org unit tree toward the root,
444                         -- until you reach the minimum depth determined above
445                         --
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
450                                         parent_ou
451                                 FROM
452                                         actor.org_unit
453                                 WHERE
454                                         id = n_curr_ou;
455                                 --
456                                 IF FOUND THEN
457                                         n_curr_depth := n_curr_depth - 1;
458                                 ELSE
459                                         --
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.
465                                         --
466                                         n_curr_ou = NULL;
467                                         EXIT;
468                                 END IF;
469                         END LOOP;
470                         --
471                         IF n_curr_ou IS NOT NULL THEN
472                                 RETURN NEXT n_curr_ou;
473                         END IF;
474                 ELSE
475                         --
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.
478                         --
479                         FOR n_curr_ou IN
480                                 SELECT ou::INTEGER
481                                 FROM connectby( 
482                                                 'actor.org_unit',         -- table name
483                                                 'id',                     -- key column
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
488                                         AS t(
489                                                 ou text,            -- dependent org unit
490                                                 parent_ou text,     -- (ignore)
491                                                 level int           -- depth relative to starting point
492                                         )
493                                 WHERE
494                                         level = n_min_depth - n_depth
495                         LOOP
496                                 RETURN NEXT n_curr_ou;
497                         END LOOP;
498                 END IF;
499                 --
500         END LOOP;
501         --
502         RETURN;
503         --
504 END;
505 $$ LANGUAGE 'plpgsql';
506
507
508 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd(
509         user_id    IN INTEGER,
510         perm_code  IN TEXT
511 )
512 RETURNS SETOF INTEGER AS $$
513 --
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
516 -- org unit.
517 --
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
520 -- user is assigned.
521 --
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.
524 --
525 -- The result set may contain duplicates, which should be eliminated
526 -- by a DISTINCT clause.
527 --
528 DECLARE
529         n_head_ou     INTEGER;
530         n_child_ou    INTEGER;
531 BEGIN
532         FOR n_head_ou IN
533                 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
534         LOOP
535                 --
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.
538                 --
539                 FOR n_child_ou IN
540                         SELECT ou::INTEGER
541                         FROM connectby( 
542                                         'actor.org_unit',   -- table name
543                                         'id',               -- key column
544                                         'parent_ou',        -- recursive foreign key
545                                         n_head_ou::TEXT,    -- id of starting point
546                                         0                   -- no limit on search depth
547                                 )
548                                 AS t(
549                                         ou text,            -- dependent org unit
550                                         parent_ou text,     -- (ignore)
551                                         level int           -- (ignore)
552                                 )
553                 LOOP
554                         RETURN NEXT n_child_ou;
555                 END LOOP;
556         END LOOP;
557         --
558         RETURN;
559         --
560 END;
561 $$ LANGUAGE 'plpgsql';
562
563
564 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at(
565         user_id    IN INTEGER,
566         perm_code  IN TEXT
567 )
568 RETURNS SETOF INTEGER AS $$
569 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( $1, $2 );
570 $$ LANGUAGE 'sql';
571
572
573 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all(
574         user_id    IN INTEGER,
575         perm_code  IN TEXT
576 )
577 RETURNS SETOF INTEGER AS $$
578 SELECT DISTINCT * FROM permission.usr_has_perm_at_all_nd( $1, $2 );
579 $$ LANGUAGE 'sql';
580
581
582 COMMIT;
583