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 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)
  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 COMMIT;
 294