1 DROP SCHEMA permission CASCADE;
4 CREATE SCHEMA permission;
6 CREATE TABLE permission.perm_list (
8 code TEXT NOT NULL UNIQUE
10 CREATE INDEX perm_list_code_idx ON permission.perm_list (code);
12 INSERT INTO permission.perm_list VALUES (-1,'EVERYTHING');
13 INSERT INTO permission.perm_list VALUES (2, 'OPAC_LOGIN');
14 INSERT INTO permission.perm_list VALUES (4, 'STAFF_LOGIN');
15 INSERT INTO permission.perm_list VALUES (5, 'MR_HOLDS');
16 INSERT INTO permission.perm_list VALUES (6, 'TITLE_HOLDS');
17 INSERT INTO permission.perm_list VALUES (7, 'VOLUME_HOLDS');
18 INSERT INTO permission.perm_list VALUES (8, 'COPY_HOLDS');
19 INSERT INTO permission.perm_list VALUES (9, 'REQUEST_HOLDS');
20 INSERT INTO permission.perm_list VALUES (10, 'REQUEST_HOLDS_OVERRIDE');
21 INSERT INTO permission.perm_list VALUES (11, 'VIEW_HOLDS');
22 INSERT INTO permission.perm_list VALUES (13, 'DELETE_HOLDS');
23 INSERT INTO permission.perm_list VALUES (14, 'UPDATE_HOLDS');
24 INSERT INTO permission.perm_list VALUES (15, 'RENEW_CIRC');
25 INSERT INTO permission.perm_list VALUES (16, 'VIEW_USER_FINES_SUMMARY');
26 INSERT INTO permission.perm_list VALUES (17, 'VIEW_USER_TRANSACTIONS');
27 INSERT INTO permission.perm_list VALUES (18, 'UPDATE_MARC');
28 INSERT INTO permission.perm_list VALUES (19, 'CREATE_ORIGINAL_MARC');
29 INSERT INTO permission.perm_list VALUES (20, 'IMPORT_MARC');
30 INSERT INTO permission.perm_list VALUES (21, 'CREATE_VOLUME');
31 INSERT INTO permission.perm_list VALUES (22, 'UPDATE_VOLUME');
32 INSERT INTO permission.perm_list VALUES (23, 'DELETE_VOLUME');
33 INSERT INTO permission.perm_list VALUES (24, 'CREATE_COPY');
34 INSERT INTO permission.perm_list VALUES (25, 'UPDATE_COPY');
35 INSERT INTO permission.perm_list VALUES (26, 'DELETE_COPY');
36 INSERT INTO permission.perm_list VALUES (27, 'RENEW_HOLD_OVERRIDE');
37 INSERT INTO permission.perm_list VALUES (28, 'CREATE_USER');
38 INSERT INTO permission.perm_list VALUES (29, 'UPDATE_USER');
39 INSERT INTO permission.perm_list VALUES (30, 'DELETE_USER');
40 INSERT INTO permission.perm_list VALUES (31, 'VIEW_USER');
41 INSERT INTO permission.perm_list VALUES (32, 'COPY_CHECKIN');
42 INSERT INTO permission.perm_list VALUES (33, 'CREATE_TRANSIT');
43 INSERT INTO permission.perm_list VALUES (34, 'VIEW_PERMISSION');
44 INSERT INTO permission.perm_list VALUES (35, 'CHECKIN_BYPASS_HOLD_FULFILL');
45 INSERT INTO permission.perm_list VALUES (36, 'CREATE_PAYMENT');
47 SELECT SETVAL('permission.perm_list_id_seq'::TEXT, 37);
49 CREATE TABLE permission.grp_tree (
50 id SERIAL PRIMARY KEY,
51 name TEXT NOT NULL UNIQUE,
52 parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT
54 CREATE INDEX grp_tree_parent ON permission.grp_tree (parent);
56 INSERT INTO grp_tree VALUES (1, 'Users', NULL);
57 INSERT INTO grp_tree VALUES (2, 'Patrons', 1);
58 INSERT INTO grp_tree VALUES (3, 'Staff', 1);
59 INSERT INTO grp_tree VALUES (4, 'Catalogers', 3);
60 INSERT INTO grp_tree VALUES (5, 'Circulators', 3);
62 SELECT SETVAL('permission.grp_tree_id_seq'::TEXT, 6);
64 CREATE TABLE permission.grp_perm_map (
65 id SERIAL PRIMARY KEY,
66 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE,
67 perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE,
69 CONSTRAINT perm_grp_once UNIQUE (grp,perm)
72 INSERT INTO permission.grp_perm_map VALUES (1, 1, 2, 0);
73 INSERT INTO permission.grp_perm_map VALUES (12, 1, 5, 0);
74 INSERT INTO permission.grp_perm_map VALUES (13, 1, 6, 0);
75 INSERT INTO permission.grp_perm_map VALUES (15, 4, 8, 2);
76 INSERT INTO permission.grp_perm_map VALUES (22, 4, 18, 0);
77 INSERT INTO permission.grp_perm_map VALUES (23, 4, 19, 0);
78 INSERT INTO permission.grp_perm_map VALUES (24, 4, 20, 0);
79 INSERT INTO permission.grp_perm_map VALUES (38, 4, 21, 2);
80 INSERT INTO permission.grp_perm_map VALUES (34, 4, 22, 2);
81 INSERT INTO permission.grp_perm_map VALUES (39, 4, 23, 2);
82 INSERT INTO permission.grp_perm_map VALUES (40, 4, 24, 2);
83 INSERT INTO permission.grp_perm_map VALUES (35, 4, 25, 2);
84 INSERT INTO permission.grp_perm_map VALUES (11, 3, 4, 0);
85 INSERT INTO permission.grp_perm_map VALUES (14, 3, 7, 2);
86 INSERT INTO permission.grp_perm_map VALUES (16, 3, 9, 0);
87 INSERT INTO permission.grp_perm_map VALUES (17, 3, 11, 0);
88 INSERT INTO permission.grp_perm_map VALUES (19, 3, 15, 0);
89 INSERT INTO permission.grp_perm_map VALUES (20, 3, 16, 0);
90 INSERT INTO permission.grp_perm_map VALUES (21, 3, 17, 0);
91 INSERT INTO permission.grp_perm_map VALUES (26, 3, 27, 0);
92 INSERT INTO permission.grp_perm_map VALUES (27, 3, 28, 0);
93 INSERT INTO permission.grp_perm_map VALUES (28, 3, 29, 0);
94 INSERT INTO permission.grp_perm_map VALUES (29, 3, 30, 0);
95 INSERT INTO permission.grp_perm_map VALUES (44, 3, 31, 0);
96 INSERT INTO permission.grp_perm_map VALUES (30, 3, 32, 0);
97 INSERT INTO permission.grp_perm_map VALUES (31, 3, 33, 0);
98 INSERT INTO permission.grp_perm_map VALUES (32, 3, 34, 0);
99 INSERT INTO permission.grp_perm_map VALUES (33, 3, 35, 0);
100 INSERT INTO permission.grp_perm_map VALUES (41, 3, 36, 0);
102 SELECT SETVAL('permission.grp_perm_map_id_seq'::TEXT, 44);
105 CREATE TABLE permission.usr_perm_map (
106 id SERIAL PRIMARY KEY,
107 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
108 perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE,
110 CONSTRAINT perm_usr_once UNIQUE (usr,perm)
113 CREATE TABLE permission.usr_grp_map (
114 id SERIAL PRIMARY KEY,
115 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
116 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE,
117 CONSTRAINT usr_grp_once UNIQUE (usr,grp)
121 INSERT INTO permission.usr_perm_map (usr,perm,depth) VALUES (1,-1,0);
123 CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$
125 FROM connectby('permission.grp_tree','parent','id','name',$1,'100','.')
126 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
127 JOIN permission.grp_tree a ON a.id = t.keyid
129 CASE WHEN a.parent IS NULL
133 $$ LANGUAGE SQL STABLE;
135 CREATE OR REPLACE FUNCTION permission.usr_perms ( iuser INT ) RETURNS SETOF permission.usr_perm_map AS $$
137 u_perm permission.usr_perm_map%ROWTYPE;
138 grp permission.usr_grp_map%ROWTYPE;
139 g_list permission.grp_tree%ROWTYPE;
141 FOR u_perm IN SELECT * FROM permission.usr_perm_map WHERE usr = iuser LOOP
145 FOR g_list IN SELECT *
146 FROM permission.grp_ancestors(
154 FOR u_perm IN SELECT DISTINCT -p.id, iuser AS usr, p.perm, p.depth
155 FROM permission.grp_perm_map p
156 WHERE p.grp = g_list.id LOOP
164 FROM permission.usr_grp_map
165 WHERE usr = iuser LOOP
167 FOR g_list IN SELECT *
168 FROM permission.grp_ancestors( grp.grp ) LOOP
170 FOR u_perm IN SELECT DISTINCT -p.id, iuser AS usr, p.perm, p.depth
171 FROM permission.grp_perm_map p
172 JOIN permission.usr_grp_map m ON (m.grp = p.grp)
173 WHERE m.grp = g_list.id LOOP
185 CREATE OR REPLACE FUNCTION permission.usr_has_perm ( iuser INT, tperm TEXT, target INT ) RETURNS BOOL AS $$
187 r_usr actor.usr%ROWTYPE;
188 r_perm permission.usr_perm_map%ROWTYPE;
191 SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
193 FOR r_perm IN SELECT *
194 FROM permission.usr_perms(iuser) p
195 JOIN permission.perm_list l
201 FROM actor.org_unit_descendants(target,r_perm.depth)
202 WHERE id = r_usr.home_ou;