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 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 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 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