More preparation for PostgreSQL 8.3 support:
[Evergreen.git] / 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'::text,'parent'::text,'id'::text,'name'::text,$1::text,100,'.'::text)
82                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
83                 JOIN permission.grp_tree a ON a.id::text = t.keyid::text
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