Open-ILS/src/sql/Pg/006.schema.permissions.sql

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