fixing perm lookup bug
[Evergreen.git] / 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 INSERT INTO permission.perm_list VALUES (-1,'EVERYTHING');
14 INSERT INTO permission.perm_list VALUES (2, 'OPAC_LOGIN');
15 INSERT INTO permission.perm_list VALUES (4, 'STAFF_LOGIN');
16 INSERT INTO permission.perm_list VALUES (5, 'MR_HOLDS');
17 INSERT INTO permission.perm_list VALUES (6, 'TITLE_HOLDS');
18 INSERT INTO permission.perm_list VALUES (7, 'VOLUME_HOLDS');
19 INSERT INTO permission.perm_list VALUES (8, 'COPY_HOLDS');
20 INSERT INTO permission.perm_list VALUES (9, 'REQUEST_HOLDS');
21 INSERT INTO permission.perm_list VALUES (10, 'REQUEST_HOLDS_OVERRIDE');
22 INSERT INTO permission.perm_list VALUES (11, 'VIEW_HOLDS');
23 INSERT INTO permission.perm_list VALUES (13, 'DELETE_HOLDS');
24 INSERT INTO permission.perm_list VALUES (14, 'UPDATE_HOLDS');
25 INSERT INTO permission.perm_list VALUES (15, 'RENEW_CIRC');
26 INSERT INTO permission.perm_list VALUES (16, 'VIEW_USER_FINES_SUMMARY');
27 INSERT INTO permission.perm_list VALUES (17, 'VIEW_USER_TRANSACTIONS');
28 INSERT INTO permission.perm_list VALUES (18, 'UPDATE_MARC');
29 INSERT INTO permission.perm_list VALUES (19, 'CREATE_ORIGINAL_MARC');
30 INSERT INTO permission.perm_list VALUES (20, 'IMPORT_MARC');
31 INSERT INTO permission.perm_list VALUES (21, 'CREATE_VOLUME');
32 INSERT INTO permission.perm_list VALUES (22, 'UPDATE_VOLUME');
33 INSERT INTO permission.perm_list VALUES (23, 'DELETE_VOLUME');
34 INSERT INTO permission.perm_list VALUES (24, 'CREATE_COPY');
35 INSERT INTO permission.perm_list VALUES (25, 'UPDATE_COPY');
36 INSERT INTO permission.perm_list VALUES (26, 'DELETE_COPY');
37 INSERT INTO permission.perm_list VALUES (27, 'RENEW_HOLD_OVERRIDE');
38 INSERT INTO permission.perm_list VALUES (28, 'CREATE_USER');
39 INSERT INTO permission.perm_list VALUES (29, 'UPDATE_USER');
40 INSERT INTO permission.perm_list VALUES (30, 'DELETE_USER');
41 INSERT INTO permission.perm_list VALUES (31, 'VIEW_USER');
42 INSERT INTO permission.perm_list VALUES (32, 'COPY_CHECKIN');
43 INSERT INTO permission.perm_list VALUES (33, 'CREATE_TRANSIT');
44 INSERT INTO permission.perm_list VALUES (34, 'VIEW_PERMISSION');
45 INSERT INTO permission.perm_list VALUES (35, 'CHECKIN_BYPASS_HOLD_FULFILL');
46 INSERT INTO permission.perm_list VALUES (36, 'CREATE_PAYMENT');
47 INSERT INTO permission.perm_list VALUES (37, 'SET_CIRC_LOST');
48 INSERT INTO permission.perm_list VALUES (38, 'SET_CIRC_MISSING');
49 INSERT INTO permission.perm_list VALUES (39, 'SET_CIRC_CLAIMS_RETURNED');
50
51 SELECT SETVAL('permission.perm_list_id_seq'::TEXT, 40);
52
53 CREATE TABLE permission.grp_tree (
54         id              SERIAL  PRIMARY KEY,
55         name            TEXT    NOT NULL UNIQUE,
56         parent          INT     REFERENCES permission.grp_tree (id) ON DELETE RESTRICT,
57         description     TEXT
58 );
59 CREATE INDEX grp_tree_parent ON permission.grp_tree (parent);
60
61 INSERT INTO permission.grp_tree VALUES (1, 'Users', NULL);
62 INSERT INTO permission.grp_tree VALUES (2, 'Patrons', 1);
63 INSERT INTO permission.grp_tree VALUES (3, 'Staff', 1);
64 INSERT INTO permission.grp_tree VALUES (4, 'Catalogers', 3);
65 INSERT INTO permission.grp_tree VALUES (5, 'Circulators', 3);
66
67 SELECT SETVAL('permission.grp_tree_id_seq'::TEXT, 6);
68
69 CREATE TABLE permission.grp_perm_map (
70         id              SERIAL  PRIMARY KEY,
71         grp             INT     NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE,
72         perm            INT     NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE,
73         depth           INT     NOT NULL,
74         grantable       BOOL    NOT NULL DEFAULT FALSE,
75                 CONSTRAINT perm_grp_once UNIQUE (grp,perm)
76 );
77
78 INSERT INTO permission.grp_perm_map VALUES (1, 1, 2, 0); 
79 INSERT INTO permission.grp_perm_map VALUES (12, 1, 5, 0);
80 INSERT INTO permission.grp_perm_map VALUES (13, 1, 6, 0);
81 INSERT INTO permission.grp_perm_map VALUES (15, 4, 8, 2);
82 INSERT INTO permission.grp_perm_map VALUES (22, 4, 18, 0);
83 INSERT INTO permission.grp_perm_map VALUES (23, 4, 19, 0);
84 INSERT INTO permission.grp_perm_map VALUES (24, 4, 20, 0);
85 INSERT INTO permission.grp_perm_map VALUES (38, 4, 21, 2);
86 INSERT INTO permission.grp_perm_map VALUES (34, 4, 22, 2);
87 INSERT INTO permission.grp_perm_map VALUES (39, 4, 23, 2);
88 INSERT INTO permission.grp_perm_map VALUES (40, 4, 24, 2);
89 INSERT INTO permission.grp_perm_map VALUES (35, 4, 25, 2);
90 INSERT INTO permission.grp_perm_map VALUES (11, 3, 4, 0);
91 INSERT INTO permission.grp_perm_map VALUES (14, 3, 7, 2);
92 INSERT INTO permission.grp_perm_map VALUES (16, 3, 9, 0);
93 INSERT INTO permission.grp_perm_map VALUES (17, 3, 11, 0);
94 INSERT INTO permission.grp_perm_map VALUES (19, 3, 15, 0);
95 INSERT INTO permission.grp_perm_map VALUES (20, 3, 16, 0);
96 INSERT INTO permission.grp_perm_map VALUES (21, 3, 17, 0);
97 INSERT INTO permission.grp_perm_map VALUES (26, 3, 27, 0);
98 INSERT INTO permission.grp_perm_map VALUES (27, 3, 28, 0);
99 INSERT INTO permission.grp_perm_map VALUES (28, 3, 29, 0);
100 INSERT INTO permission.grp_perm_map VALUES (29, 3, 30, 0);
101 INSERT INTO permission.grp_perm_map VALUES (44, 3, 31, 0);
102 INSERT INTO permission.grp_perm_map VALUES (30, 3, 32, 0);
103 INSERT INTO permission.grp_perm_map VALUES (31, 3, 33, 0);
104 INSERT INTO permission.grp_perm_map VALUES (32, 3, 34, 0);
105 INSERT INTO permission.grp_perm_map VALUES (33, 3, 35, 0);
106 INSERT INTO permission.grp_perm_map VALUES (41, 3, 36, 0);
107 INSERT INTO permission.grp_perm_map VALUES (45, 3, 37, 0);
108 INSERT INTO permission.grp_perm_map VALUES (46, 3, 38, 0);
109 INSERT INTO permission.grp_perm_map VALUES (47, 3, 39, 0);
110
111 SELECT SETVAL('permission.grp_perm_map_id_seq'::TEXT, 48);
112
113
114 CREATE TABLE permission.usr_perm_map (
115         id              SERIAL  PRIMARY KEY,
116         usr             INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
117         perm            INT     NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE,
118         depth           INT     NOT NULL,
119         grantable       BOOL    NOT NULL DEFAULT FALSE,
120                 CONSTRAINT perm_usr_once UNIQUE (usr,perm)
121 );
122
123 CREATE TABLE permission.usr_grp_map (
124         id      SERIAL  PRIMARY KEY,
125         usr     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
126         grp     INT     NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE,
127                 CONSTRAINT usr_grp_once UNIQUE (usr,grp)
128 );
129
130 -- Admin user
131 INSERT INTO permission.usr_perm_map (usr,perm,depth) VALUES (1,-1,0);
132
133 CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$
134         SELECT  a.*
135         FROM    connectby('permission.grp_tree','parent','id','name',$1,'100','.')
136                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
137                 JOIN permission.grp_tree a ON a.id = t.keyid
138         ORDER BY
139                 CASE WHEN a.parent IS NULL
140                         THEN 0
141                         ELSE 1
142                 END, a.name;
143 $$ LANGUAGE SQL STABLE;
144
145 CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
146         SELECT  DISTINCT ON (usr,perm) *
147           FROM  (
148                         (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
149                                         UNION ALL
150                         (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
151                           FROM  permission.grp_perm_map p
152                           WHERE p.grp IN (
153                                 SELECT  (permission.grp_ancestors(
154                                                 (SELECT profile FROM actor.usr WHERE id = $1)
155                                         )).id
156                                 )
157                         )
158                                         UNION ALL
159                         (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
160                           FROM  permission.grp_perm_map p 
161                           WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
162                 ) AS x
163           ORDER BY 2, 3, 1 DESC, 5 DESC ;
164 $$ LANGUAGE SQL STABLE;
165
166 CREATE OR REPLACE FUNCTION permission.usr_can_grant_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
167 DECLARE
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
183         FOR r_perm IN   SELECT  *
184                           FROM  permission.usr_perms(iuser) p
185                                 JOIN permission.perm_list l
186                                         ON (l.id = p.perm)
187                           WHERE (l.code = tperm AND p.grantable IS TRUE)
188                 LOOP
189
190                 PERFORM *
191                   FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
192                   WHERE id = r_usr.home_ou;
193
194                 IF FOUND THEN
195                         RETURN TRUE;
196                 ELSE
197                         RETURN FALSE;
198                 END IF;
199         END LOOP;
200
201         RETURN FALSE;
202 END;
203 $$ LANGUAGE PLPGSQL;
204
205 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
206 DECLARE
207         r_usr   actor.usr%ROWTYPE;
208         r_perm  permission.usr_perm_map%ROWTYPE;
209 BEGIN
210
211         SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
212
213         IF r_usr.active = FALSE THEN
214                 RETURN FALSE;
215         END IF;
216
217         IF r_usr.super_user = TRUE THEN
218                 RETURN TRUE;
219         END IF;
220
221
222         FOR r_perm IN   SELECT  *
223                           FROM  permission.usr_perms(iuser) p
224                                 JOIN permission.perm_list l
225                                         ON (l.id = p.perm)
226                           WHERE l.code = tperm
227                                 OR p.perm = -1 LOOP
228
229                 PERFORM *
230                   FROM  actor.org_unit_descendants(target_ou,r_perm.depth)
231                   WHERE id = r_usr.home_ou;
232
233                 IF FOUND THEN
234                         RETURN TRUE;
235                 ELSE
236                         RETURN FALSE;
237                 END IF;
238         END LOOP;
239
240         RETURN FALSE;
241 END;
242 $$ LANGUAGE PLPGSQL;
243
244 COMMIT;
245