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