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