-- -- /www/doc/sql/user-groups.sql -- -- Author: Philip Greenspun (philg@mit.edu), 11/15/98 -- -- augmented 3/98 by Tracy Adams (teadams@mit.edu) -- to handle -- a) Database-driven roles and action permissions -- b) Creates group_type "administration" for site and -- module administration -- c) Permission system that allows programmers to ask -- "Can user x do y?" -- -- $Id: user-groups.sql,v 1.2 2005/09/07 14:03:27 brianm Exp $ -- -- allows administrators and users to set up groups of users -- and then place users in those groups with roles -- for example, the groups could be hospitals and then a user -- could be associated with Hospital X as "physician" and with -- Hospital Y as "physician". -- -- a group type might be 'hospital' or 'cardiac_center' -- or 'professional_society' -- we keep group_type short because we will be building tables -- with group_type as the name, e.g., "hospital_info" will store -- the extra information specified in user_group_type_fields -- for groups of type "hospital" -- if pretty_name is "Cardiac Center" then pretty_plural -- is "Cardiac Centers" -- approval_policy of "open" means users can create groups of -- this type and they are immediately live -- of "closed" means that only admins can create groups of this type -- of "wait" means that users are offered the option to create -- but then an admin must approve create table user_group_types ( group_type varchar(20) primary key, pretty_name varchar(50) not null, pretty_plural varchar(50) not null, approval_policy varchar(30) not null, default_new_member_policy varchar(30) default 'open' not null, -- if group_module_administration=full, then group administrators have full control of which modules -- they can use (they can add, remove, enable and disable modules) -- if group_module_administration=enabling, then group administrators have authority to enable and -- disable modules but cannot add or remove modules -- if group_module_administration=none, the group administrators have no control over modules -- modules are explicitly set for the user group type by the system administrator group_module_administration varchar(20), -- does this group type support virtual group directories -- if has_virtual_directory_p is t, then virtual url /$group_type can be used instead of /groups -- to access the groups of this type has_virtual_directory_p char(1) default 'f' check(has_virtual_directory_p in ('t','f')), -- if has_virtual_directory_p is t and group_type_public_directory is not null, then files in -- group_type_public_directory will be used instead of files in default /groups directory -- notice also that these files will be used only when the page is accessed through /$group_type url's group_type_public_directory varchar(200), -- if has_virtual_directory_p is t and group_type_admin_directory is not null, then files in -- group_type_admin_directory will be used instead of files in default /groups/admin directory -- notice also that these files will be used only when the page is accessed through /$group_type url's group_type_admin_directory varchar(200), -- if has_virtual_directory_p is t and group_public_directory is not null, then files in -- group_public_directory will be used instead of files in default /groups/group directory -- notice also that these files will be used only when the page is accessed through /$group_type url's group_public_directory varchar(200), -- if has_virtual_directory_p is t and group_admin_directory is not null, then files in -- group_admin_directory will be used instead of files in default /groups/admin/group directory -- notice also that these files will be used only when the page is accessed through /$group_type url's group_admin_directory varchar(200) ); alter table user_group_types add constraint group_type_module_admin_check check ( (group_module_administration is not null) and (group_module_administration in ('full', 'enabling', 'none'))); -- fields of info that are required for each group type -- these will be stored in a separate table, called -- ${group_type}_info (e.g., "hospital_info") create table user_group_type_fields ( group_type not null references user_group_types, column_name varchar(30) not null, pretty_name varchar(50) not null, -- something generic and suitable for handing to AOLserver, -- e.g., boolean or text column_type varchar(50) not null, -- something nitty gritty and Oracle-specific, e.g., -- char(1) instead of boolean -- things like "not null" column_actual_type varchar(100) not null, column_extra varchar(100), -- Sort key for display of columns. sort_key integer not null ); create sequence user_group_sequence start with 1; create table user_groups ( group_id integer primary key, group_type not null references user_group_types, group_name varchar(100), short_name varchar(100) unique not null, admin_email varchar(100), registration_date date default sysdate not null, creation_user not null references users(user_id), creation_ip_address varchar(50) not null, approved_p char(1) check (approved_p in ('t','f')), active_p char(1) default 't' check(active_p in ('t','f')), existence_public_p char(1) default 't' check (existence_public_p in ('t','f')), new_member_policy varchar(30) default 'open' not null, spam_policy varchar(30) default 'open' not null, constraint user_groups_spam_policy_check check(spam_policy in ('open','closed','wait')), -- are the administrators notified of new membership? email_alert_p char(1) default 'f' check (email_alert_p in ('t','f')), -- should we use the multi-role based multi_role_p char(1) default 'f' check (multi_role_p in ('t','f')), -- can the user group administration control roles and actions? -- If f, only site admin pages will have the functionality to modify role-action mappings. This is a way to "lock in" permission system. group_admin_permissions_p char(1) default 'f' check (group_admin_permissions_p in ('t','f')), index_page_enabled_p char(1) default 'f' check (index_page_enabled_p in ('t','f')), -- this is index page content body clob, -- html_p for the index page content html_p char(1) default 'f' check (html_p in ('t','f')), -- let's keep track of when these records are modified modification_date date, modifying_user integer references users, -- add a parent_group_id to support subgroups parent_group_id references user_groups(group_id) ); -- index parent_group_id to make parent lookups quick! create index user_groups_parent_grp_id_idx on user_groups(parent_group_id); create or replace function user_group_group_type (v_group_id IN user_groups.group_id%TYPE) return varchar IS v_group_type user_group_types.group_type%TYPE; BEGIN select group_type into v_group_type from user_groups where group_id=v_group_id; return v_group_type; END user_group_group_type; / show errors -- this is the helper function for function short_name_from_group_name bellow create or replace function short_name_from_group_name2 (v_short_name IN user_groups.short_name%TYPE, v_identifier IN integer) return varchar IS v_new_short_name user_groups.short_name%TYPE; cursor c1 is select short_name from user_groups where short_name=v_short_name || decode(v_identifier, 0, '', v_identifier); BEGIN OPEN c1; FETCH c1 into v_new_short_name; if c1%NOTFOUND then select v_short_name || decode(v_identifier, 0, '', v_identifier) into v_new_short_name from dual; return v_new_short_name; else return short_name_from_group_name2(v_short_name, v_identifier+1); end if; END short_name_from_group_name2; / show errors -- this function generates unique short_name from the group_nams -- v_group_name is the group_name of the group, this function will first transform group_name by making it lower case, -- and substituting spaces and underscores with dashes. thus, if group_name is Photographers, the transformed group_name -- will be photographers. then, this function will keep adding numbers to it until it makes it unique (e.g. if short_names -- photographers and photographers1 already exist this function will return photographers2) create or replace function short_name_from_group_name (v_group_name IN user_groups.group_name%TYPE) return varchar IS BEGIN return short_name_from_group_name2(lower(substr(translate(v_group_name, '_ ','--'), 1, 80)), 0); END short_name_from_group_name; / show errors -- this procedure sets the short_name of all the groups in the user_group -- table using short_name_from_group_name function -- notice that simple update using short_name_from_group_name could not be -- performed because function short_name_from_group_name is used while -- user_groups is mutating (ORA-04091) create or replace procedure generate_short_names_for_group IS v_group_id user_groups.group_id%TYPE; v_group_name user_groups.group_name%TYPE; v_short_name user_groups.short_name%TYPE; cursor c1 is select group_id, group_name from user_groups; BEGIN OPEN c1; LOOP FETCH c1 INTO v_group_id, v_group_name; EXIT WHEN c1%NOTFOUND; v_short_name:= short_name_from_group_name(v_group_name); update user_groups set short_name=v_short_name where group_id=v_group_id; END LOOP; END generate_short_names_for_group; / show errors create or replace trigger user_group_approved_p_tr before insert on user_groups for each row declare group_type_row user_group_types%ROWTYPE; begin if :new.approved_p is null then select * into group_type_row from user_group_types ugt where ugt.group_type = :new.group_type; if group_type_row.approval_policy = 'open' then :new.approved_p := 't'; else :new.approved_p := 'f'; end if; end if; end; / show errors -- role = 'administrator' is magic and lets the person add other -- members create table user_group_map ( group_id not null references user_groups, user_id not null references users, -- define in this order because we want to -- quickly see if user X belongs to requested group -- and/or which groups a user belongs to role varchar(200), registration_date date default sysdate not null, -- keep track of who did this and from where mapping_user not null references users(user_id), -- store the string, separated by dots, e.g., 18.23.10.101 -- make it large enough to handle IPv6 (128 bits) mapping_ip_address varchar(50) not null, -- this unique constraint permits a user to have multiple -- roles for a group, e.g., one person could have the -- roles of CEO and CTO for a user group of type 'company' -- we use a unique constraint instead of a primary key -- because role can be null simple membership in a group -- would have null for the role unique (group_id, user_id, role) ); -- holds people who've asked to be in a group but aren't -- approved yet create table user_group_map_queue ( group_id integer references user_groups, user_id integer references users, ip_address varchar(50), queue_date date default sysdate, primary key (group_id, user_id) ); -- stores the roles used by each group -- only meaningful for groups that use a -- multi_role permission system create table user_group_roles ( group_id integer not null references user_groups, role varchar(200), creation_date date default sysdate not null, creation_user integer not null references users, creation_ip_address varchar(200) not null, primary key (group_id, role) ); -- stores the actions used by each group -- only used in multi-role mode create table user_group_actions ( group_id integer not null references user_groups, action varchar(200), creation_date date default sysdate not null, creation_user integer not null references users, creation_ip_address varchar(200) not null, primary key (group_id, action) ); -- maps roles to allowed actions create table user_group_action_role_map ( group_id integer not null references user_groups, role varchar(200) not null, action varchar(200) not null, creation_date date default sysdate not null, creation_user not null references users, creation_ip_address varchar(200) not null, primary key (group_id, role, action) ); ---MODULE ADMINISTRATION -- this table lets you ask "what is the user group that corresponds -- to a particular module/submodule of the ACS?" -- module would be something like "classifieds" and submodule -- would be a classifieds domain; for a bboard, the module would -- be "bboard" and the submodules individual bboard topics -- NOTE: Most tables named grouptype_info are autogenerated -- This is the one _info table that is created by hand create table administration_info ( group_id integer not null references user_groups, module varchar(300) not null, submodule varchar(300), --- link to the module administration page url varchar(300), unique(module, submodule) ); declare n_system_group_types integer; begin select count(*) into n_system_group_types from user_group_types where group_type = 'administration'; if n_system_group_types = 0 then insert into user_group_types (group_type, pretty_name, pretty_plural, approval_policy, default_new_member_policy, group_module_administration) values ('administration', 'Administration', 'Administration Groups', 'closed', 'closed', 'full'); insert into user_group_type_fields (group_type, column_name, pretty_name, column_type, column_actual_type, column_extra, sort_key) values ('administration', 'module', 'Module', 'text', 'varchar(300)', 'not null', 1); insert into user_group_type_fields (group_type, column_name, pretty_name, column_type, column_actual_type, column_extra, sort_key) values ('administration', 'submodule', 'Submodule', 'text', 'varchar(300)','', 2); -- so that we can offer admins links from their workspace insert into user_group_type_fields (group_type, column_name, pretty_name, column_type, column_actual_type, column_extra, sort_key) values ('administration', 'url', 'URL', 'text', 'varchar(100)','',3); end if; end; / -- creates a new group of type "administration"; does nothing if the group is -- already defined create or replace procedure administration_group_add (pretty_name IN varchar, v_short_name IN varchar, v_module IN varchar, v_submodule IN varchar, v_multi_role_p IN varchar, v_url IN varchar ) IS v_group_id integer; n_administration_groups integer; v_system_user_id integer; BEGIN if v_submodule is null then select count(group_id) into n_administration_groups from administration_info where module = v_module and submodule is null; else select count(group_id) into n_administration_groups from administration_info where module = v_module and submodule = v_submodule; end if; if n_administration_groups = 0 then -- call procedure defined in community-core.sql to get system user v_system_user_id := system_user_id; select user_group_sequence.nextval into v_group_id from dual; insert into user_groups (group_id, group_type, short_name, group_name, creation_user, creation_ip_address, approved_p, existence_public_p, new_member_policy, multi_role_p) values (v_group_id, 'administration', v_short_name, pretty_name, v_system_user_id, '0.0.0.0', 't', 'f', 'closed', v_multi_role_p); insert into administration_info (group_id, module, submodule, url) values (v_group_id, v_module, v_submodule, v_url); end if; end; / show errors --- Define an administration group for site wide administration begin administration_group_add ('Site-Wide Administration', 'site_wide', 'site_wide', '', 'f', '/admin/'); end; / --- returns the group_id of the site_wide administration group create or replace function system_administrator_group_id return integer as v_group_id integer; begin select group_id into v_group_id from administration_info where module = 'site_wide' and submodule is null; return v_group_id; end; / --- Add the system user to the site-wide administration group declare v_system_group_id integer; v_system_user_id integer; n_user_id integer; begin v_system_user_id := system_user_id; v_system_group_id := system_administrator_group_id; select count(user_id) into n_user_id from user_group_map where user_id = v_system_user_id and group_id = v_system_group_id; if n_user_id = 0 then insert into user_group_map (group_id, user_id, role, mapping_user, mapping_ip_address) values (v_system_group_id, v_system_user_id, 'administrator', v_system_user_id, '0.0.0.0'); end if; end; / -- Some query functions create or replace function ad_group_member_p (v_user_id IN user_group_map.user_id%TYPE, v_group_id IN user_group_map.group_id%TYPE) return char IS ad_group_member_p char(1); BEGIN -- maybe we should check the validity of user_id and group_id; -- we're not doing it for now, because it would slow this function -- down with 2 extra queries select decode(count(*), 0, 'f', 't') into ad_group_member_p from user_group_map where user_id = v_user_id and group_id = v_group_id and rownum < 2; return ad_group_member_p; END ad_group_member_p; / show errors create or replace function ad_user_has_role_p (v_user_id IN user_group_map.user_id%TYPE, v_group_id IN user_group_map.group_id%TYPE, v_role IN user_group_map.role%TYPE) return char IS ad_user_has_role_p char(1); BEGIN -- maybe we should check the validity of user_id and group_id; -- we're not doing it for now, because it would slow this function -- down with 2 extra queries select decode(count(*), 0, 'f', 't') into ad_user_has_role_p from user_group_map where user_id = v_user_id and group_id = v_group_id and role = v_role; return ad_user_has_role_p; END ad_user_has_role_p; / show errors create or replace function ad_group_member_admin_role_p (v_user_id IN integer, v_group_id IN integer) return varchar IS n_rows integer; BEGIN select count(*) into n_rows from user_group_map where user_id = v_user_id and group_id = v_group_id and lower(role) = 'administrator'; IF n_rows > 0 THEN return 't'; ELSE return 'f'; END IF; END; / show errors create or replace function ad_admin_group_member_p (v_module IN varchar, v_submodule IN varchar, v_user_id IN integer) return varchar IS n_rows integer; BEGIN select count(*) into n_rows from user_group_map where user_id = v_user_id and group_id in (select group_id from administration_info where (module = v_module and submodule = v_submodule) or module = 'site_wide'); IF n_rows > 0 THEN return 't'; ELSE return 'f'; END IF; END; / show errors -- This table records additional fields to be recorded per user who belongs -- to a group of a particular type. -- Each field can be associated with a role within a group. This allows -- us to present a role-specific set of fields for users to add/edit. -- If the role field is empty we present the field to all members -- of the group of specified group_type. aegrumet@arsdigita.com, 2000-03-10 create table user_group_type_member_fields ( group_type varchar(20) references user_group_types, role varchar(200), field_name varchar(200) not null, field_type varchar(20) not null, -- short_text, long_text, boolean, date, etc. -- Sort key for display of columns. sort_key integer not null, -- We can't make this a primary key since role can be NULL. -- The unique constraint creates an index. unique (group_type, role, field_name) ); -- Contains information about fields to gather per user for a user group. -- Cannot contain a field_name that appears in the -- user_group_type_member_fields table for the group type this group belongs to. create table user_group_member_fields ( group_id integer references user_groups, field_name varchar(200) not null, field_type varchar(20) not null, -- short_text, long_text, boolean, date, etc. sort_key integer not null, primary key (group_id, field_name) ); -- View that brings together all field information for a user group, from -- user_group_type_member_fields and user_group_member_fields. -- We throw in the sort keys prepended by 'a' or 'b' so we can display -- them in the correct order, with the group type fields first. create or replace view all_member_fields_for_group as select group_id, field_name, field_type, 'a' || sort_key as sort_key from user_group_type_member_fields ugtmf, user_groups ug where ugtmf.group_type = ug.group_type union select group_id, field_name, field_type, 'b' || sort_key as sort_key from user_group_member_fields; -- Contains extra field information for a particular user. These fields -- were defined either in user_group_type_member_fields or -- user_group_member_fields create table user_group_member_field_map ( group_id integer references user_groups, user_id integer references users, field_name varchar(200) not null, field_value varchar(4000), primary key (group_id, user_id, field_name) ); -- this table is used when group administrators are not allowed to handle module administration -- (allow_module_administration_p is set to 0 for this group type) -- all groups of this group type will have only modules set up for which mapping in this table exists create sequence group_type_modules_id_sequence start with 1; create table user_group_type_modules_map ( group_type_module_id integer primary key, group_type references user_group_types not null, module_key references acs_modules not null ); ----------------------------------------------------------------------------------------------------------- -- created by ahmeds@mit.edu on Thu Jan 13 21:29:11 EST 2000 -- -- supports a system for spamming members of a user group -- -- group_member_email_preferences table retains email preferences of members -- that belong to a particular group create table group_member_email_preferences ( group_id references user_groups not null, user_id references users not null , dont_spam_me_p char (1) default 'f' check(dont_spam_me_p in ('t','f')), primary key (group_id, user_id) ); -- group_spam_history table holds the spamming log for this group create sequence group_spam_id_sequence start with 1; create table group_spam_history ( spam_id integer primary key, group_id references user_groups not null, sender_id references users(user_id) not null, sender_ip_address varchar(50) not null, from_address varchar(100), subject varchar(200), body clob, send_to varchar (50) default 'members', creation_date date not null, -- approved_p matters only for spam policy='wait' -- approved_p = 't' indicates administrator approved the mail -- approved_p = 'f' indicates administrator disapproved the mail, so it won't be listed for approval again -- approved_p = null indicates the mail is not approved/disapproved by the administrator yet approved_p char(1) default null check (approved_p is null or approved_p in ('t','f')), send_date date, -- this holds the number of intended recipients n_receivers_intended integer default 0, -- we'll increment this after every successful email n_receivers_actual integer default 0 ); -- This function returns the number of members all of the subgroups of -- one group_id has. Note that since we made subgroups go 1 level down -- only, this function only looks for groups whose parent is the specified -- v_parent_group_id create or replace function user_groups_number_subgroups (v_group_id IN integer) return integer IS v_count integer; BEGIN select count(1) into v_count from user_groups where parent_group_id = v_group_id; return v_count; END; / show errors; -- We need to be able to answer "How many total members are there in all -- of my subgroups?" create or replace function user_groups_number_submembers (v_parent_group_id IN integer) return integer IS v_count integer; BEGIN select count(1) into v_count from user_group_map where group_id in (select group_id from user_groups where parent_group_id=v_parent_group_id); return v_count; END; / show errors; -- While doing a connect by, we need to count the number of members in -- user_group_map. Since we can't join with a connect by, we create -- this function create or replace function user_groups_number_members (v_group_id IN integer) return integer IS v_count integer; BEGIN select count(1) into v_count from user_group_map where group_id=v_group_id; return v_count; END; / show errors; -- easy way to get the user_group from an id. This is important when -- using connect by in your table and it also makes the code using -- user subgroups easier to read (don't have to join an additional -- user_groups tables). However, it is recommended that you only -- use this pls function when you have to or when it truly saves you -- from some heinous coding create or replace function user_group_name_from_id (v_group_id IN integer) return varchar IS v_group_name user_groups.group_name%TYPE; BEGIN if v_group_id is null then return ''; end if; select group_name into v_group_name from user_groups where group_id = v_group_id; return v_group_name; END; / show errors; -- With subgroups, we needed an easy way to add adminstration groups -- and tie them to parents create or replace procedure administration_subgroup_add (pretty_name IN varchar, v_short_name IN varchar, v_module IN varchar, v_submodule IN varchar, v_multi_role_p IN varchar, v_url IN varchar, v_parent_module IN varchar) IS v_group_id integer; n_administration_groups integer; v_system_user_id integer; v_parent_id integer; BEGIN if v_submodule is null then select count(group_id) into n_administration_groups from administration_info where module = v_module and submodule is null; else select count(group_id) into n_administration_groups from administration_info where module = v_module and submodule = v_submodule; end if; if n_administration_groups = 0 then -- call procedure defined in community-core.sql to get system user v_system_user_id := system_user_id; select user_group_sequence.nextval into v_group_id from dual; insert into user_groups (group_id, group_type, short_name, group_name, creation_user, creation_ip_address, approved_p, existence_public_p, new_member_policy, multi_role_p) values (v_group_id, 'administration', v_short_name, pretty_name, v_system_user_id, '0.0.0.0', 't', 'f', 'closed', v_multi_role_p); insert into administration_info (group_id, module, submodule, url) values (v_group_id, v_module, v_submodule, v_url); end if; Begin select ai.group_id into v_parent_id from administration_info ai, user_groups ug where ai.module = v_parent_module and ai.group_id != v_group_id and ug.group_id = ai.group_id and ug.parent_group_id is null; Exception when others then null; End; update user_groups set parent_group_id = v_parent_id where group_id = v_group_id; end; / show errors -- Adds the specified field_name and field_type to a group with group id v_group_id -- if the member field already exists for this group, does nothing -- if v_sort_key is not specified, the member_field will be added with sort_key -- 1 greater than the current max create or replace procedure user_group_member_field_add (v_group_id IN integer, v_field_name IN varchar, v_field_type IN varchar, v_sort_key IN integer) IS n_groups integer; BEGIN -- make sure we don't violate the unique constraint of user_groups_member_fields select decode(count(1),0,0,1) into n_groups from all_member_fields_for_group where group_id = v_group_id and field_name = v_field_name; if n_groups = 0 then -- member_field is new - add it insert into user_group_member_fields (group_id, field_name, field_type, sort_key) values (v_group_id, v_field_name, v_field_type, v_sort_key); end if; end; / show errors; -- function to create new groups of a specified type -- This is useful mostly when loading your modules - simply use this -- function to create the groups you need create or replace procedure user_group_add (v_group_type IN varchar, v_pretty_name IN varchar, v_short_name IN varchar, v_multi_role_p IN varchar) IS n_groups integer; v_system_user_id integer; BEGIN -- make sure we don't violate the unique constraint of user_groups.short_name select decode(count(1),0,0,1) into n_groups from user_groups where upper(short_name)=upper(v_short_name); if n_groups = 0 then -- call procedure defined in community-core.sql to get system user v_system_user_id := system_user_id; -- create the actual group insert into user_groups (group_id, group_type, short_name, group_name, creation_user, creation_ip_address, approved_p, existence_public_p, new_member_policy, multi_role_p) values (user_group_sequence.nextval, v_group_type, v_short_name, v_pretty_name, v_system_user_id, '0.0.0.0', 't', 'f', 'closed', v_multi_role_p); end if; end; / show errors -- returns a list of all the groups a user is in, separated by -- commas create or replace function group_names_of_user ( v_user_id IN Integer) Return varchar2 IS counter integer; return_string varchar(2000); CURSOR c_user_groups is select group_name from user_groups, user_group_map where user_groups.group_id = user_group_map.group_id and user_group_map.user_id = v_user_id; BEGIN counter := 0; for v_group_data in c_user_groups LOOP counter := counter + 1; if counter = 1 then return_string := v_group_data.group_name; else return_string := return_string || ', ' || v_group_data.group_name; end if; End Loop; Return return_string; END; / show errors -- returns a list of all the groups a user is in, separated by -- commas create or replace function group_names_of_user_by_type ( p_user_id IN Integer, p_group_type IN varchar) Return varchar2 IS v_counter integer; v_return_string varchar(2000); CURSOR c_user_groups is select group_name from user_groups, user_group_map where user_groups.group_id = user_group_map.group_id and user_groups.group_type = p_group_type and user_group_map.user_id = p_user_id; BEGIN v_counter := 0; for v_group_data in c_user_groups LOOP v_counter := v_counter + 1; if v_counter = 1 then v_return_string := v_group_data.group_name; else v_return_string := v_return_string || ', ' || v_group_data.group_name; end if; End Loop; Return v_return_string; END; / show errors