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     penalty     INT             NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  46     threshold   NUMERIC(8,2)    NOT NULL,
  47     CONSTRAINT penalty_grp_once UNIQUE (grp,penalty)
  48 );
  49
  50 CREATE TABLE permission.grp_perm_map (
  51         id              SERIAL  PRIMARY KEY,
  52         grp             INT     NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  53         perm            INT     NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  54         depth           INT     NOT NULL,
  55         grantable       BOOL    NOT NULL DEFAULT FALSE,
  56                 CONSTRAINT perm_grp_once UNIQUE (grp,perm)
  57 );
  58
  59 CREATE TABLE permission.usr_perm_map (
  60         id              SERIAL  PRIMARY KEY,
  61         usr             INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  62         perm            INT     NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  63         depth           INT     NOT NULL,
  64         grantable       BOOL    NOT NULL DEFAULT FALSE,
  65                 CONSTRAINT perm_usr_once UNIQUE (usr,perm)
  66 );
  67
  68 CREATE TABLE permission.usr_object_perm_map (
  69         id              SERIAL  PRIMARY KEY,
  70         usr             INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  71         perm            INT     NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  72     object_type TEXT NOT NULL,
  73     object_id   TEXT NOT NULL,
  74         grantable       BOOL    NOT NULL DEFAULT FALSE,
  75                 CONSTRAINT perm_usr_obj_once UNIQUE (usr,perm,object_type,object_id)
  76 );
  77
  78 CREATE INDEX uopm_usr_idx ON permission.usr_object_perm_map (usr);
  79
  80 CREATE TABLE permission.usr_grp_map (
  81         id      SERIAL  PRIMARY KEY,
  82         usr     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  83         grp     INT     NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  84                 CONSTRAINT usr_grp_once UNIQUE (usr,grp)
  85 );
  86
  87 CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$
  88         SELECT  a.*
  89         FROM    connectby('permission.grp_tree'::text,'parent'::text,'id'::text,'name'::text,$1::text,100,'.'::text)
  90                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
  91                 JOIN permission.grp_tree a ON a.id::text = t.keyid::text
  92         ORDER BY
  93                 CASE WHEN a.parent IS NULL
  94                         THEN 0
  95                         ELSE 1
  96                 END, a.name;
  97 $$ LANGUAGE SQL STABLE;
  98
  99 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
 100         SELECT  DISTINCT ON (usr,perm) *
 101           FROM  (
 102                         (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
 103                                         UNION ALL
 104                         (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
 105                           FROM  permission.grp_perm_map p
 106                           WHERE p.grp IN (
 107                                 SELECT  (permission.grp_ancestors(
 108                                                 (SELECT profile FROM actor.usr WHERE id = $1)
 109                                         )).id
 110                                 )
 111                         )
 112                                         UNION ALL
 113                         (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
 114                           FROM  permission.grp_perm_map p
 115                           WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
 116                 ) AS x
 117           ORDER BY 2, 3, 1 DESC, 5 DESC ;
 118 $$ LANGUAGE SQL STABLE;
 119
 120 CREATE TABLE permission.usr_work_ou_map (
 121         id      SERIAL  PRIMARY KEY,
 122         usr     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 123         work_ou INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 124                 CONSTRAINT usr_work_ou_once UNIQUE (usr,work_ou)
 125 );
 126
 127 CREATE OR REPLACE FUNCTION permission.usr_can_grant_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
 128 DECLARE
 129         r_usr   actor.usr%ROWTYPE;
 130         r_perm  permission.usr_perm_map%ROWTYPE;
 131 BEGIN
 132
 133         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
 134
 135         IF r_usr.active = FALSE THEN
 136                 RETURN FALSE;
 137         END IF;
 138
 139         IF r_usr.super_user = TRUE THEN
 140                 RETURN TRUE;
 141         END IF;
 142
 143         FOR r_perm IN   SELECT  *
 144                           FROM  permission.usr_perms(iuser) p
 145                                 JOIN permission.perm_list l
 146                                         ON (l.id = p.perm)
 147                           WHERE (l.code = tperm AND p.grantable IS TRUE)
 148                 LOOP
 149
 150                 PERFORM *
 151                   FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
 152                   WHERE id = r_usr.home_ou;
 153
 154                 IF FOUND THEN
 155                         RETURN TRUE;
 156                 ELSE
 157                         RETURN FALSE;
 158                 END IF;
 159         END LOOP;
 160
 161         RETURN FALSE;
 162 END;
 163 $$ LANGUAGE PLPGSQL;
 164
 165 CREATE OR REPLACE FUNCTION permission.usr_has_home_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
 166 DECLARE
 167         r_usr   actor.usr%ROWTYPE;
 168         r_perm  permission.usr_perm_map%ROWTYPE;
 169 BEGIN
 170
 171         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
 172
 173         IF r_usr.active = FALSE THEN
 174                 RETURN FALSE;
 175         END IF;
 176
 177         IF r_usr.super_user = TRUE THEN
 178                 RETURN TRUE;
 179         END IF;
 180
 181         FOR r_perm IN   SELECT  *
 182                           FROM  permission.usr_perms(iuser) p
 183                                 JOIN permission.perm_list l
 184                                         ON (l.id = p.perm)
 185                           WHERE l.code = tperm
 186                                 OR p.perm = -1 LOOP
 187
 188                 PERFORM *
 189                   FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
 190                   WHERE id = r_usr.home_ou;
 191
 192                 IF FOUND THEN
 193                         RETURN TRUE;
 194                 ELSE
 195                         RETURN FALSE;
 196                 END IF;
 197         END LOOP;
 198
 199         RETURN FALSE;
 200 END;
 201 $$ LANGUAGE PLPGSQL;
 202
 203 CREATE OR REPLACE FUNCTION permission.usr_has_work_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
 204 DECLARE
 205         r_woum  permission.usr_work_ou_map%ROWTYPE;
 206         r_usr   actor.usr%ROWTYPE;
 207         r_perm  permission.usr_perm_map%ROWTYPE;
 208 BEGIN
 209
 210         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
 211
 212         IF r_usr.active = FALSE THEN
 213                 RETURN FALSE;
 214         END IF;
 215
 216         IF r_usr.super_user = TRUE THEN
 217                 RETURN TRUE;
 218         END IF;
 219
 220         FOR r_perm IN   SELECT  *
 221                           FROM  permission.usr_perms(iuser) p
 222                                 JOIN permission.perm_list l
 223                                         ON (l.id = p.perm)
 224                           WHERE l.code = tperm
 225                                 OR p.perm = -1
 226                 LOOP
 227
 228                 FOR r_woum IN   SELECT  *
 229                                   FROM  permission.usr_work_ou_map
 230                                   WHERE usr = iuser
 231                         LOOP
 232
 233                         PERFORM *
 234                           FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
 235                           WHERE id = r_woum.work_ou;
 236
 237                         IF FOUND THEN
 238                                 RETURN TRUE;
 239                         END IF;
 240
 241                 END LOOP;
 242
 243         END LOOP;
 244
 245         RETURN FALSE;
 246 END;
 247 $$ LANGUAGE PLPGSQL;
 248
 249 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 $$
 250 DECLARE
 251         r_usr   actor.usr%ROWTYPE;
 252         res     BOOL;
 253 BEGIN
 254
 255         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
 256
 257         IF r_usr.active = FALSE THEN
 258                 RETURN FALSE;
 259         END IF;
 260
 261         IF r_usr.super_user = TRUE THEN
 262                 RETURN TRUE;
 263         END IF;
 264
 265         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;
 266
 267         IF FOUND THEN
 268                 RETURN TRUE;
 269         END IF;
 270
 271         IF target_ou > -1 THEN
 272                 RETURN permission.usr_has_perm( iuser, tperm, target_ou);
 273         END IF;
 274
 275         RETURN FALSE;
 276
 277 END;
 278 $$ LANGUAGE PLPGSQL;
 279
 280 CREATE OR REPLACE FUNCTION permission.usr_has_object_perm ( INT, TEXT, TEXT, TEXT ) RETURNS BOOL AS $$
 281     SELECT permission.usr_has_object_perm( $1, $2, $3, $4, -1 );
 282 $$ LANGUAGE SQL;
 283
 284 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( INT, TEXT, INT ) RETURNS BOOL AS $$
 285         SELECT  CASE
 286                         WHEN permission.usr_has_home_perm( $1, $2, $3 ) THEN TRUE
 287                         WHEN permission.usr_has_work_perm( $1, $2, $3 ) THEN TRUE
 288                         ELSE FALSE
 289                 END;
 290 $$ LANGUAGE SQL;
 291
 292 COMMIT;
 293