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 CREATE TRIGGER maintain_perm_i18n_tgr
  31     AFTER UPDATE ON permission.perm_list
  32     FOR EACH ROW EXECUTE PROCEDURE oils_i18n_id_tracking('ppl');
  33
  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,
  40         description             TEXT,
  41         application_perm        TEXT
  42 );
  43 CREATE INDEX grp_tree_parent_idx ON permission.grp_tree (parent);
  44
  45 CREATE TABLE permission.grp_penalty_threshold (
  46     id          SERIAL          PRIMARY KEY,
  47     grp         INT             NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  48     org_unit    INT             NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  49     penalty     INT             NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  50     threshold   NUMERIC(8,2)    NOT NULL,
  51     CONSTRAINT penalty_grp_once UNIQUE (grp,penalty,org_unit)
  52 );
  53
  54 CREATE TABLE permission.grp_perm_map (
  55         id              SERIAL  PRIMARY KEY,
  56         grp             INT     NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  57         perm            INT     NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
  58         depth           INT     NOT NULL,
  59         grantable       BOOL    NOT NULL DEFAULT FALSE,
  60                 CONSTRAINT perm_grp_once UNIQUE (grp,perm)
  61 );
  62
  63 CREATE TABLE permission.usr_perm_map (
  64         id              SERIAL  PRIMARY KEY,
  65         usr             INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  66         perm            INT     NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
  67         depth           INT     NOT NULL,
  68         grantable       BOOL    NOT NULL DEFAULT FALSE,
  69                 CONSTRAINT perm_usr_once UNIQUE (usr,perm)
  70 );
  71
  72 CREATE TABLE permission.usr_object_perm_map (
  73         id              SERIAL  PRIMARY KEY,
  74         usr             INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  75         perm            INT     NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
  76     object_type TEXT NOT NULL,
  77     object_id   TEXT NOT NULL,
  78         grantable       BOOL    NOT NULL DEFAULT FALSE,
  79                 CONSTRAINT perm_usr_obj_once UNIQUE (usr,perm,object_type,object_id)
  80 );
  81
  82 CREATE INDEX uopm_usr_idx ON permission.usr_object_perm_map (usr);
  83
  84 CREATE TABLE permission.usr_grp_map (
  85         id      SERIAL  PRIMARY KEY,
  86         usr     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  87         grp     INT     NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  88                 CONSTRAINT usr_grp_once UNIQUE (usr,grp)
  89 );
  90
  91 CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$
  92         SELECT  a.*
  93         FROM    connectby('permission.grp_tree'::text,'parent'::text,'id'::text,'name'::text,$1::text,100,'.'::text)
  94                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
  95                 JOIN permission.grp_tree a ON a.id::text = t.keyid::text
  96         ORDER BY
  97                 CASE WHEN a.parent IS NULL
  98                         THEN 0
  99                         ELSE 1
 100                 END, a.name;
 101 $$ LANGUAGE SQL STABLE;
 102
 103 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
 104         SELECT  DISTINCT ON (usr,perm) *
 105           FROM  (
 106                         (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
 107                                         UNION ALL
 108                         (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
 109                           FROM  permission.grp_perm_map p
 110                           WHERE p.grp IN (
 111                                 SELECT  (permission.grp_ancestors(
 112                                                 (SELECT profile FROM actor.usr WHERE id = $1)
 113                                         )).id
 114                                 )
 115                         )
 116                                         UNION ALL
 117                         (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
 118                           FROM  permission.grp_perm_map p
 119                           WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
 120                 ) AS x
 121           ORDER BY 2, 3, 1 DESC, 5 DESC ;
 122 $$ LANGUAGE SQL STABLE;
 123
 124 CREATE TABLE permission.usr_work_ou_map (
 125         id      SERIAL  PRIMARY KEY,
 126         usr     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 127         work_ou INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 128                 CONSTRAINT usr_work_ou_once UNIQUE (usr,work_ou)
 129 );
 130
 131 CREATE OR REPLACE FUNCTION permission.usr_can_grant_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
 132 DECLARE
 133         r_usr   actor.usr%ROWTYPE;
 134         r_perm  permission.usr_perm_map%ROWTYPE;
 135 BEGIN
 136
 137         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
 138
 139         IF r_usr.active = FALSE THEN
 140                 RETURN FALSE;
 141         END IF;
 142
 143         IF r_usr.super_user = TRUE THEN
 144                 RETURN TRUE;
 145         END IF;
 146
 147         FOR r_perm IN   SELECT  *
 148                           FROM  permission.usr_perms(iuser) p
 149                                 JOIN permission.perm_list l
 150                                         ON (l.id = p.perm)
 151                           WHERE (l.code = tperm AND p.grantable IS TRUE)
 152                 LOOP
 153
 154                 PERFORM *
 155                   FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
 156                   WHERE id = r_usr.home_ou;
 157
 158                 IF FOUND THEN
 159                         RETURN TRUE;
 160                 ELSE
 161                         RETURN FALSE;
 162                 END IF;
 163         END LOOP;
 164
 165         RETURN FALSE;
 166 END;
 167 $$ LANGUAGE PLPGSQL;
 168
 169 CREATE OR REPLACE FUNCTION permission.usr_has_home_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
 170 DECLARE
 171         r_usr   actor.usr%ROWTYPE;
 172         r_perm  permission.usr_perm_map%ROWTYPE;
 173 BEGIN
 174
 175         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
 176
 177         IF r_usr.active = FALSE THEN
 178                 RETURN FALSE;
 179         END IF;
 180
 181         IF r_usr.super_user = TRUE THEN
 182                 RETURN TRUE;
 183         END IF;
 184
 185         FOR r_perm IN   SELECT  *
 186                           FROM  permission.usr_perms(iuser) p
 187                                 JOIN permission.perm_list l
 188                                         ON (l.id = p.perm)
 189                           WHERE l.code = tperm
 190                                 OR p.perm = -1 LOOP
 191
 192                 PERFORM *
 193                   FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
 194                   WHERE id = r_usr.home_ou;
 195
 196                 IF FOUND THEN
 197                         RETURN TRUE;
 198                 ELSE
 199                         RETURN FALSE;
 200                 END IF;
 201         END LOOP;
 202
 203         RETURN FALSE;
 204 END;
 205 $$ LANGUAGE PLPGSQL;
 206
 207 CREATE OR REPLACE FUNCTION permission.usr_has_work_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
 208 DECLARE
 209         r_woum  permission.usr_work_ou_map%ROWTYPE;
 210         r_usr   actor.usr%ROWTYPE;
 211         r_perm  permission.usr_perm_map%ROWTYPE;
 212 BEGIN
 213
 214         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
 215
 216         IF r_usr.active = FALSE THEN
 217                 RETURN FALSE;
 218         END IF;
 219
 220         IF r_usr.super_user = TRUE THEN
 221                 RETURN TRUE;
 222         END IF;
 223
 224         FOR r_perm IN   SELECT  *
 225                           FROM  permission.usr_perms(iuser) p
 226                                 JOIN permission.perm_list l
 227                                         ON (l.id = p.perm)
 228                           WHERE l.code = tperm
 229                                 OR p.perm = -1
 230                 LOOP
 231
 232                 FOR r_woum IN   SELECT  *
 233                                   FROM  permission.usr_work_ou_map
 234                                   WHERE usr = iuser
 235                         LOOP
 236
 237                         PERFORM *
 238                           FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
 239                           WHERE id = r_woum.work_ou;
 240
 241                         IF FOUND THEN
 242                                 RETURN TRUE;
 243                         END IF;
 244
 245                 END LOOP;
 246
 247         END LOOP;
 248
 249         RETURN FALSE;
 250 END;
 251 $$ LANGUAGE PLPGSQL;
 252
 253 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 $$
 254 DECLARE
 255         r_usr   actor.usr%ROWTYPE;
 256         res     BOOL;
 257 BEGIN
 258
 259         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
 260
 261         IF r_usr.active = FALSE THEN
 262                 RETURN FALSE;
 263         END IF;
 264
 265         IF r_usr.super_user = TRUE THEN
 266                 RETURN TRUE;
 267         END IF;
 268
 269         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;
 270
 271         IF FOUND THEN
 272                 RETURN TRUE;
 273         END IF;
 274
 275         IF target_ou > -1 THEN
 276                 RETURN permission.usr_has_perm( iuser, tperm, target_ou);
 277         END IF;
 278
 279         RETURN FALSE;
 280
 281 END;
 282 $$ LANGUAGE PLPGSQL;
 283
 284 CREATE OR REPLACE FUNCTION permission.usr_has_object_perm ( INT, TEXT, TEXT, TEXT ) RETURNS BOOL AS $$
 285     SELECT permission.usr_has_object_perm( $1, $2, $3, $4, -1 );
 286 $$ LANGUAGE SQL;
 287
 288 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( INT, TEXT, INT ) RETURNS BOOL AS $$
 289         SELECT  CASE
 290                         WHEN permission.usr_has_home_perm( $1, $2, $3 ) THEN TRUE
 291                         WHEN permission.usr_has_work_perm( $1, $2, $3 ) THEN TRUE
 292                         ELSE FALSE
 293                 END;
 294 $$ LANGUAGE SQL;
 295
 296
 297 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
 298         user_id    IN INTEGER,
 299         perm_code  IN TEXT
 300 )
 301 RETURNS SETOF INTEGER AS $$
 302 --
 303 -- Return a set of all the org units for which a given user has a given
 304 -- permission, granted directly (not through inheritance from a parent
 305 -- org unit).
 306 --
 307 -- The permissions apply to a minimum depth of the org unit hierarchy,
 308 -- for the org unit(s) to which the user is assigned.  (They also apply
 309 -- to the subordinates of those org units, but we don't report the
 310 -- subordinates here.)
 311 --
 312 -- For purposes of this function, the permission.usr_work_ou_map table
 313 -- defines which users belong to which org units.  I.e. we ignore the
 314 -- home_ou column of actor.usr.
 315 --
 316 -- The result set may contain duplicates, which should be eliminated
 317 -- by a DISTINCT clause.
 318 --
 319 DECLARE
 320         b_super       BOOLEAN;
 321         n_perm        INTEGER;
 322         n_min_depth   INTEGER;
 323         n_work_ou     INTEGER;
 324         n_curr_ou     INTEGER;
 325         n_depth       INTEGER;
 326         n_curr_depth  INTEGER;
 327 BEGIN
 328         --
 329         -- Check for superuser
 330         --
 331         SELECT INTO b_super
 332                 super_user
 333         FROM
 334                 actor.usr
 335         WHERE
 336                 id = user_id;
 337         --
 338         IF NOT FOUND THEN
 339                 return;                         -- No user?  No permissions.
 340         ELSIF b_super THEN
 341                 --
 342                 -- Super user has all permissions everywhere
 343                 --
 344                 FOR n_work_ou IN
 345                         SELECT
 346                                 id
 347                         FROM
 348                                 actor.org_unit
 349                         WHERE
 350                                 parent_ou IS NULL
 351                 LOOP
 352                         RETURN NEXT n_work_ou;
 353                 END LOOP;
 354                 RETURN;
 355         END IF;
 356         --
 357         -- Translate the permission name
 358         -- to a numeric permission id
 359         --
 360         SELECT INTO n_perm
 361                 id
 362         FROM
 363                 permission.perm_list
 364         WHERE
 365                 code = perm_code;
 366         --
 367         IF NOT FOUND THEN
 368                 RETURN;               -- No such permission
 369         END IF;
 370         --
 371         -- Find the highest-level org unit (i.e. the minimum depth)
 372         -- to which the permission is applied for this user
 373         --
 374         -- This query is modified from the one in permission.usr_perms().
 375         --
 376         SELECT INTO n_min_depth
 377                 min( depth )
 378         FROM    (
 379                 SELECT depth
 380                   FROM permission.usr_perm_map upm
 381                  WHERE upm.usr = user_id
 382                    AND (upm.perm = n_perm OR upm.perm = -1)
 383                                 UNION
 384                 SELECT  gpm.depth
 385                   FROM  permission.grp_perm_map gpm
 386                   WHERE (gpm.perm = n_perm OR gpm.perm = -1)
 387                 AND gpm.grp IN (
 388                            SELECT       (permission.grp_ancestors(
 389                                         (SELECT profile FROM actor.usr WHERE id = user_id)
 390                                 )).id
 391                         )
 392                                 UNION
 393                 SELECT  p.depth
 394                   FROM  permission.grp_perm_map p
 395                   WHERE (p.perm = n_perm OR p.perm = -1)
 396                     AND p.grp IN (
 397                                 SELECT (permission.grp_ancestors(m.grp)).id
 398                                 FROM   permission.usr_grp_map m
 399                                 WHERE  m.usr = user_id
 400                         )
 401         ) AS x;
 402         --
 403         IF NOT FOUND THEN
 404                 RETURN;                -- No such permission for this user
 405         END IF;
 406         --
 407         -- Identify the org units to which the user is assigned.  Note that
 408         -- we pay no attention to the home_ou column in actor.usr.
 409         --
 410         FOR n_work_ou IN
 411                 SELECT
 412                         work_ou
 413                 FROM
 414                         permission.usr_work_ou_map
 415                 WHERE
 416                         usr = user_id
 417         LOOP            -- For each org unit to which the user is assigned
 418                 --
 419                 -- Determine the level of the org unit by a lookup in actor.org_unit_type.
 420                 -- We take it on faith that this depth agrees with the actual hierarchy
 421                 -- defined in actor.org_unit.
 422                 --
 423                 SELECT INTO n_depth
 424                     type.depth
 425                 FROM
 426                     actor.org_unit_type type
 427                         INNER JOIN actor.org_unit ou
 428                             ON ( ou.ou_type = type.id )
 429                 WHERE
 430                     ou.id = n_work_ou;
 431                 --
 432                 IF NOT FOUND THEN
 433                         CONTINUE;        -- Maybe raise exception?
 434                 END IF;
 435                 --
 436                 -- Compare the depth of the work org unit to the
 437                 -- minimum depth, and branch accordingly
 438                 --
 439                 IF n_depth = n_min_depth THEN
 440                         --
 441                         -- The org unit is at the right depth, so return it.
 442                         --
 443                         RETURN NEXT n_work_ou;
 444                 ELSIF n_depth > n_min_depth THEN
 445                         --
 446                         -- Traverse the org unit tree toward the root,
 447                         -- until you reach the minimum depth determined above
 448                         --
 449                         n_curr_depth := n_depth;
 450                         n_curr_ou := n_work_ou;
 451                         WHILE n_curr_depth > n_min_depth LOOP
 452                                 SELECT INTO n_curr_ou
 453                                         parent_ou
 454                                 FROM
 455                                         actor.org_unit
 456                                 WHERE
 457                                         id = n_curr_ou;
 458                                 --
 459                                 IF FOUND THEN
 460                                         n_curr_depth := n_curr_depth - 1;
 461                                 ELSE
 462                                         --
 463                                         -- This can happen only if the hierarchy defined in
 464                                         -- actor.org_unit is corrupted, or out of sync with
 465                                         -- the depths defined in actor.org_unit_type.
 466                                         -- Maybe we should raise an exception here, instead
 467                                         -- of silently ignoring the problem.
 468                                         --
 469                                         n_curr_ou = NULL;
 470                                         EXIT;
 471                                 END IF;
 472                         END LOOP;
 473                         --
 474                         IF n_curr_ou IS NOT NULL THEN
 475                                 RETURN NEXT n_curr_ou;
 476                         END IF;
 477                 ELSE
 478                         --
 479                         -- The permission applies only at a depth greater than the work org unit.
 480                         -- Use connectby() to find all dependent org units at the specified depth.
 481                         --
 482                         FOR n_curr_ou IN
 483                                 SELECT ou::INTEGER
 484                                 FROM connectby(
 485                                                 'actor.org_unit',         -- table name
 486                                                 'id',                     -- key column
 487                                                 'parent_ou',              -- recursive foreign key
 488                                                 n_work_ou::TEXT,          -- id of starting point
 489                                                 (n_min_depth - n_depth)   -- max depth to search, relative
 490                                         )                             --   to starting point
 491                                         AS t(
 492                                                 ou text,            -- dependent org unit
 493                                                 parent_ou text,     -- (ignore)
 494                                                 level int           -- depth relative to starting point
 495                                         )
 496                                 WHERE
 497                                         level = n_min_depth - n_depth
 498                         LOOP
 499                                 RETURN NEXT n_curr_ou;
 500                         END LOOP;
 501                 END IF;
 502                 --
 503         END LOOP;
 504         --
 505         RETURN;
 506         --
 507 END;
 508 $$ LANGUAGE 'plpgsql';
 509
 510
 511 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd(
 512         user_id    IN INTEGER,
 513         perm_code  IN TEXT
 514 )
 515 RETURNS SETOF INTEGER AS $$
 516 --
 517 -- Return a set of all the org units for which a given user has a given
 518 -- permission, granted either directly or through inheritance from a parent
 519 -- org unit.
 520 --
 521 -- The permissions apply to a minimum depth of the org unit hierarchy, and
 522 -- to the subordinates of those org units, for the org unit(s) to which the
 523 -- user is assigned.
 524 --
 525 -- For purposes of this function, the permission.usr_work_ou_map table
 526 -- assigns users to org units.  I.e. we ignore the home_ou column of actor.usr.
 527 --
 528 -- The result set may contain duplicates, which should be eliminated
 529 -- by a DISTINCT clause.
 530 --
 531 DECLARE
 532         n_head_ou     INTEGER;
 533         n_child_ou    INTEGER;
 534 BEGIN
 535         FOR n_head_ou IN
 536                 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
 537         LOOP
 538                 --
 539                 -- The permission applies only at a depth greater than the work org unit.
 540                 -- Use connectby() to find all dependent org units at the specified depth.
 541                 --
 542                 FOR n_child_ou IN
 543                         SELECT ou::INTEGER
 544                         FROM connectby(
 545                                         'actor.org_unit',   -- table name
 546                                         'id',               -- key column
 547                                         'parent_ou',        -- recursive foreign key
 548                                         n_head_ou::TEXT,    -- id of starting point
 549                                         0                   -- no limit on search depth
 550                                 )
 551                                 AS t(
 552                                         ou text,            -- dependent org unit
 553                                         parent_ou text,     -- (ignore)
 554                                         level int           -- (ignore)
 555                                 )
 556                 LOOP
 557                         RETURN NEXT n_child_ou;
 558                 END LOOP;
 559         END LOOP;
 560         --
 561         RETURN;
 562         --
 563 END;
 564 $$ LANGUAGE 'plpgsql';
 565
 566
 567 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at(
 568         user_id    IN INTEGER,
 569         perm_code  IN TEXT
 570 )
 571 RETURNS SETOF INTEGER AS $$
 572 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( $1, $2 );
 573 $$ LANGUAGE 'sql';
 574
 575
 576 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all(
 577         user_id    IN INTEGER,
 578         perm_code  IN TEXT
 579 )
 580 RETURNS SETOF INTEGER AS $$
 581 SELECT DISTINCT * FROM permission.usr_has_perm_at_all_nd( $1, $2 );
 582 $$ LANGUAGE 'sql';
 583
 584
 585 COMMIT;
 586