From 36566f764385dae0b829ff12f5a1d6437ffb3827 Mon Sep 17 00:00:00 2001 From: miker Date: Mon, 20 Jun 2005 20:16:35 +0000 Subject: [PATCH] adding permission tables git-svn-id: svn://svn.open-ils.org/ILS/trunk@879 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Postgres/005.schema.actors.sql | 27 ----- .../src/sql/Postgres/006.schema.permissions.sql | 121 +++++++++++++++++++++ 2 files changed, 121 insertions(+), 27 deletions(-) create mode 100644 Open-ILS/src/sql/Postgres/006.schema.permissions.sql diff --git a/Open-ILS/src/sql/Postgres/005.schema.actors.sql b/Open-ILS/src/sql/Postgres/005.schema.actors.sql index 2a2aa5cb54..84ccbbf2c5 100644 --- a/Open-ILS/src/sql/Postgres/005.schema.actors.sql +++ b/Open-ILS/src/sql/Postgres/005.schema.actors.sql @@ -179,33 +179,6 @@ CREATE TABLE actor.usr_access_entry ( CONSTRAINT usr_once_per_ou UNIQUE (usr,org_unit) ); - -CREATE TABLE actor.perm_group ( - id SERIAL PRIMARY KEY, - name TEXT NOT NULL, - ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) -); - -CREATE TABLE actor.permission ( - id SERIAL PRIMARY KEY, - name TEXT NOT NULL UNIQUE, - code TEXT NOT NULL UNIQUE -); - -CREATE TABLE actor.perm_group_permission_map ( - id SERIAL PRIMARY KEY, - permission INT NOT NULL REFERENCES actor.permission (id), - perm_group INT NOT NULL REFERENCES actor.perm_group (id), - CONSTRAINT perm_once_per_group UNIQUE (permission, perm_group) -); - -CREATE TABLE actor.perm_group_usr_map ( - id BIGSERIAL PRIMARY KEY, - usr INT NOT NULL REFERENCES actor.usr (id), - perm_group INT NOT NULL REFERENCES actor.perm_group (id), - CONSTRAINT usr_once_per_group UNIQUE (usr, perm_group) -); - CREATE TABLE actor.usr_address ( id SERIAL PRIMARY KEY, valid BOOL NOT NULL DEFAULT TRUE, diff --git a/Open-ILS/src/sql/Postgres/006.schema.permissions.sql b/Open-ILS/src/sql/Postgres/006.schema.permissions.sql new file mode 100644 index 0000000000..bff1f8c590 --- /dev/null +++ b/Open-ILS/src/sql/Postgres/006.schema.permissions.sql @@ -0,0 +1,121 @@ +DROP SCHEMA permission CASCADE; + +BEGIN; +CREATE SCHEMA permission; + +CREATE TABLE permission.perm_list ( + id SERIAL PRIMARY KEY, + code TEXT NOT NULL UNIQUE +); +CREATE INDEX perm_list_code_idx ON permission.perm_list (code); +INSERT INTO permission.perm_list VALUES (DEFAULT,'EVERYTHING'); +INSERT INTO permission.perm_list VALUES (DEFAULT,'OPAC_LOGIN'); + +CREATE TABLE permission.grp_tree ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL UNIQUE, + parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT +); +CREATE INDEX grp_tree_parent ON permission.grp_tree (parent); +INSERT INTO permission.grp_tree VALUES (DEFAULT,'Users'); +INSERT INTO permission.grp_tree VALUES (DEFAULT,'Admin',1); + +CREATE TABLE permission.grp_perm_map ( + id SERIAL PRIMARY KEY, + grp INT NOT NULL REFERENCES permission.grp_tree (id), + perm INT NOT NULL REFERENCES permission.perm_list (id), + depth INT NOT NULL REFERENCES actor.org_unit_type (id), + CONSTRAINT perm_grp_once UNIQUE (grp,perm) +); +INSERT INTO permission.grp_perm_map VALUES (DEFAULT,1,2,(SELECT id FROM actor.org_unit_type WHERE depth = 0 LIMIT 1)); +INSERT INTO permission.grp_perm_map VALUES (DEFAULT,2,1,(SELECT id FROM actor.org_unit_type WHERE depth = 0 LIMIT 1)); + +CREATE TABLE permission.usr_perm_map ( + id SERIAL PRIMARY KEY, + usr INT NOT NULL REFERENCES actor.usr (id), + perm INT NOT NULL REFERENCES permission.perm_list (id), + depth INT NOT NULL REFERENCES actor.org_unit_type (id), + CONSTRAINT perm_usr_once UNIQUE (usr,perm) +); + +CREATE TABLE permission.usr_grp_map ( + id SERIAL PRIMARY KEY, + usr INT NOT NULL REFERENCES actor.usr (id), + grp INT NOT NULL REFERENCES permission.grp_tree (id), + CONSTRAINT usr_grp_once UNIQUE (usr,grp) +); + +INSERT INTO permission.usr_grp_map (usr,grp) + SELECT id, (SELECT id FROM permission.grp_tree WHERE parent IS NULL LIMIT 1) FROM actor.usr; + +INSERT INTO permission.usr_grp_map (usr,grp) + SELECT 1, id FROM permission.grp_tree WHERE name = 'Admin'; + +CREATE OR REPLACE RULE add_usr_to_group AS + ON INSERT TO actor.usr DO ALSO + INSERT INTO permission.usr_grp_map (usr, grp) VALUES ( + NEW.id, + (SELECT id FROM permission.grp_tree WHERE parent IS NULL LIMIT 1) + ); + + +CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$ + SELECT a.* + FROM connectby('permission.grp_tree','parent','id','name',$1,'100','.') + AS t(keyid text, parent_keyid text, level int, branch text,pos int) + JOIN permission.grp_tree a ON a.id = t.keyid + ORDER BY + CASE WHEN a.parent IS NULL + THEN 0 + ELSE 1 + END, a.name; +$$ LANGUAGE SQL STABLE; + +CREATE OR REPLACE FUNCTION permission.usr_perms ( iuser INT ) RETURNS SETOF permission.usr_perm_map AS $$ +DECLARE + u_perm permission.usr_perm_map%ROWTYPE; + grp permission.usr_grp_map%ROWTYPE; + g_list permission.grp_tree%ROWTYPE; +BEGIN + FOR u_perm IN SELECT * FROM permission.usr_perm_map WHERE usr = iuser LOOP + RETURN NEXT u_perm; + END LOOP; + + FOR grp IN SELECT * + FROM permission.usr_grp_map + WHERE usr = iuser LOOP + + FOR g_list IN SELECT * + FROM permission.grp_ancestors( grp.grp ) LOOP + + FOR u_perm IN SELECT DISTINCT p.id, iuser AS usr, p.perm, p.depth + FROM permission.grp_perm_map p + JOIN permission.usr_grp_map m ON (m.grp = p.grp) + WHERE m.grp = g_list.id LOOP + + RETURN NEXT u_perm; + + END LOOP; + END LOOP; + END LOOP; + RETURN; +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION permission.usr_has_perm ( iuser INT, tperm TEXT ) RETURNS BOOL AS $$ +BEGIN + PERFORM TRUE + FROM permission.usr_perms(iuser) p + JOIN permission.perm_list l + ON (l.id = p.perm) + WHERE l.code = tperm; + IF FOUND THEN + RETURN TRUE; + ELSE + RETURN FALSE; + END IF; +END; +$$ LANGUAGE PLPGSQL; + +COMMIT; + -- 2.11.0