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_grp_map (
  43         id      SERIAL  PRIMARY KEY,
  44         usr     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
  45         grp     INT     NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE,
  46                 CONSTRAINT usr_grp_once UNIQUE (usr,grp)
  47 );
  48
  49 CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$
  50         SELECT  a.*
  51         FROM    connectby('permission.grp_tree','parent','id','name',$1,'100','.')
  52                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
  53                 JOIN permission.grp_tree a ON a.id = t.keyid
  54         ORDER BY
  55                 CASE WHEN a.parent IS NULL
  56                         THEN 0
  57                         ELSE 1
  58                 END, a.name;
  59 $$ LANGUAGE SQL STABLE;
  60
  61 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
  62         SELECT  DISTINCT ON (usr,perm) *
  63           FROM  (
  64                         (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
  65                                         UNION ALL
  66                         (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
  67                           FROM  permission.grp_perm_map p
  68                           WHERE p.grp IN (
  69                                 SELECT  (permission.grp_ancestors(
  70                                                 (SELECT profile FROM actor.usr WHERE id = $1)
  71                                         )).id
  72                                 )
  73                         )
  74                                         UNION ALL
  75                         (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
  76                           FROM  permission.grp_perm_map p
  77                           WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
  78                 ) AS x
  79           ORDER BY 2, 3, 1 DESC, 5 DESC ;
  80 $$ LANGUAGE SQL STABLE;
  81
  82 CREATE TABLE permission.usr_work_ou_map (
  83         id      SERIAL  PRIMARY KEY,
  84         usr     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
  85         work_ou INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE,
  86                 CONSTRAINT usr_work_ou_once UNIQUE (usr,work_ou)
  87 );
  88
  89 CREATE OR REPLACE FUNCTION permission.usr_can_grant_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
  90 DECLARE
  91         r_usr   actor.usr%ROWTYPE;
  92         r_perm  permission.usr_perm_map%ROWTYPE;
  93 BEGIN
  94
  95         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
  96
  97         IF r_usr.active = FALSE THEN
  98                 RETURN FALSE;
  99         END IF;
 100
 101         IF r_usr.super_user = TRUE THEN
 102                 RETURN TRUE;
 103         END IF;
 104
 105         FOR r_perm IN   SELECT  *
 106                           FROM  permission.usr_perms(iuser) p
 107                                 JOIN permission.perm_list l
 108                                         ON (l.id = p.perm)
 109                           WHERE (l.code = tperm AND p.grantable IS TRUE)
 110                 LOOP
 111
 112                 PERFORM *
 113                   FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
 114                   WHERE id = r_usr.home_ou;
 115
 116                 IF FOUND THEN
 117                         RETURN TRUE;
 118                 ELSE
 119                         RETURN FALSE;
 120                 END IF;
 121         END LOOP;
 122
 123         RETURN FALSE;
 124 END;
 125 $$ LANGUAGE PLPGSQL;
 126
 127 CREATE OR REPLACE FUNCTION permission.usr_has_home_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
 148                                 OR p.perm = -1 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_work_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
 166 DECLARE
 167         r_woum  permission.usr_work_ou_map%ROWTYPE;
 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
 188                 LOOP
 189
 190                 FOR r_woum IN   SELECT  *
 191                                   FROM  permission.usr_work_ou_map
 192                                   WHERE usr = iuser
 193                         LOOP
 194
 195                         PERFORM *
 196                           FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
 197                           WHERE id = r_woum.work_ou;
 198
 199                         IF FOUND THEN
 200                                 RETURN TRUE;
 201                         END IF;
 202
 203                 END LOOP;
 204
 205         END LOOP;
 206
 207         RETURN FALSE;
 208 END;
 209 $$ LANGUAGE PLPGSQL;
 210
 211 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( INT, TEXT, INT ) RETURNS BOOL AS $$
 212         SELECT  CASE
 213                         WHEN permission.usr_has_home_perm( $1, $2, $3 ) THEN TRUE
 214                         WHEN permission.usr_has_work_perm( $1, $2, $3 ) THEN TRUE
 215                         ELSE FALSE
 216                 END;
 217 $$ LANGUAGE SQL;
 218
 219 COMMIT;
 220