Please bear with the following description as the problem is a little complex:
In my application it is very common for some amount of data to be in the system prior to a user registering an account -- notably name and possibly a school affiliation and past competitive results. The vast majority of people will never need nor have an account. Likewise for teams, teams can either be created from scratch or more commonly partially pre-populated from past public results. Authorization is handled on a role-basis within a team, ie a given user may be given the "Captain" or a lessor role over a paticular team.
So I have a number of similar, but slightly different, cases where I need the ability to link existing data with new users or the ability to elevate permissions of various users -- all in a secure manner.
Starting with the "simplest" in my mind:
Existing User, requests "Captain" role for an existing team:
Permission elevation request is generated, resolution by site admin requires out-of-band confirmation from publicly available team contact information (most teams have captain contact information on their school sponsored website) (Captains can grant Captain and lower access within their team to other team members, sparing the site admin some of the requests). I can't think of any secure, in-band mechanism.
New User, existing person record:
Display a list of possible matches (ie matching/near-matching names) along with any team affiliation if known. Also display the non-selectable matches due to existing linkages with users (aka an already "claimed" person record). User may report a misclaimed person record to the site admin for investigation/resolution of claim. User may
select a single non-claimed entry. If non-claimed entry has no pre-existing team-membership, no further actions required. If non-claimed has a team-membership, an pending-approval record is created with request information forwarded to the team (any team role can approve). (Out-of-band verification of team-membership using combination of name and email addresss as "authentication" to the team.) (The worst result of an improper lnkage is the exposure of mailing address and phone number, but it is extremely unlikely such would have been entered into the person record of a non-user)
Can anyone think of any improvements? Has anyone else had to deal with a similar system and can offer related advice?
Thanks
Sync-ing user records with pre-existing name data...
Moderator: General Moderators
I am not completely sure if I understand you right but here is some advice:
every member's database entry should have some serurity fields like e.g. role (none, normal, admin, captain etc.). You could do a team affiliation table by setting each team to a double value. 1,2,4,8,16,32,64,128..... This way each member could be in different teams and you can find out which by simply comparing the added entry. (example: entry would be 25. so the member is in team 16 + 8 + 1)
The rest of what each role might be allowed to do has to be hardcoded in the pages and you can do all requirements you need.
every member's database entry should have some serurity fields like e.g. role (none, normal, admin, captain etc.). You could do a team affiliation table by setting each team to a double value. 1,2,4,8,16,32,64,128..... This way each member could be in different teams and you can find out which by simply comparing the added entry. (example: entry would be 25. so the member is in team 16 + 8 + 1)
The rest of what each role might be allowed to do has to be hardcoded in the pages and you can do all requirements you need.
... but make sure it is NOT a double if you intend to do it, it more or less must be an int, varying sizes allows different number of "options", tinyint = 8, int = 32 etc. double is floating point and also an estimation, having the values accidentally round from 256 to 255 would be a total disaster.AGISB wrote:...team to a double value. 1,2,4,8,16,32,64,128...
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Thanks, but that's not the problem. I'm showing the relevant portions of the schema below, perhaps it will help me explain the problem.
(The only non-standard SQL syntax above is the 'SERIAL' datatype from PostGreSQL -- roughly equal to the auto_increment type from MySQL, if that helps you understand it.)
Ok, notice that there is some repetition in the team_membership and the user_team_roles tables. The former is worried about "people" and their membership on a team -- perhaps with some descriptive text about their position and an eas way to find the person in charge, regardless of their title. The latter is concerned with user permissions over a team. The seperation is required for two reasons:
1. Delegation -- perhaps the Captain isn't computer literate and delegates all web-stuff to someone else, perhaps this person isn't even a regular team officer. This person needs to have the "Captain" role for permission, but be identified as a "Captain" for information display about the team -- even if tagged with an alternate title.
2. (Approachinng my problem): Not all "people" are "users". Given a team roster the system can auto-populate the needed "people" and "team_membership" -- it can do nothing for "users" and "user_team_roles".
OK hopefully that background helps:
The problem:
Suppose we have a team pre-loaded from a roster, with no one on the team pre-existing as a user. Someone registers for a user-account now, with a matching name as a team member(say the Captain) and indicates membership on the entered team. Now the system can't blindly trust this -- this could be a member of a rival team trying to hijack it.
I can't send out an email to all members at team creation time, the source of the "rosters" doesn't include email address, plus that'll be extremely close to spamming. Which means I can't have a "follow this link to create an account linked to this person" style solution.
I beleive this requires out-of-band,human-in-the-loop verification. I wish it didn't, but... So my current solution, is that the account creation process creates a new (duplicate) person record for the new user and adds an entry to a pending_permissions table (this table is slightly in flux, it currently handles several types of permission requests, I think I'll need to split it up)
Type can be either "Site","Comp","Team".
Level depends on Type ["Admin","Staff"],["Coordinator","Comptroller","Registrar","Staff"],["Captain","Treasurer","Registration","Member"]
Details holds the compname or the teamid or '' for site.
Misc holds extra information (you can see why this should be three seperate relations.)
However, this is structured more for "permission elevation requests" not for handling some of these initial "linkages" between people and users.
However, what I think I need is a "Membership Request" that has the form:
"[username] from [email_adddy] is requessting to be indentified with [full name] on [teamname] as[rolename/position] with confirmation details at [location]"
possible multiple such requests need to be created (one per each team on which the matched person is a member)
[Location] should be a publicly addresssable website, with official team contact email addresses.
Resolution: The site admin goes to the location (attempts to verify that its a legitamte site and not a phishing-like forgery) and sends an email to the official contact address for the team, or captain is explicitly listed. Detailing the nature of the request (including the email address of the requesting user) and asking for confirmation to proceed.
Once a Captain or Registration role is configured they can handle these requests internally, normally matching email address against their own internal roster, etc,
There are several other variations of the "synchronization" problem as mentioned in the OP, but this is the heart of it. Does the problem make sense? Can you see ways of improving/simpligying the suggested solution?
Code: Select all
CREATE TABLE people (
peopleid SERIAL PRIMARY KEY,
firstname TEXT NOT NULL,
lastname TEXT NOT NULL
);
CREATE TABLE teams (
teamid SERIAL PRIMARY KEY,
name TEXT NOT NULL,
abbreviation TEXT NOT NULL DEFAULT ''
);
CREATE TABLE team_roles (
rolename TEXT PRIMARY KEY,
description TEXT NOT NULL DEFAULT ''
);
INSERT INTO team_positions (rolename) VALUES ('Captain');
INSERT INTO team_positions (rolename) VALUES ('Treasurer');
INSERT INTO team_positions (rolename) VALUES ('Registration');
INSERT INTO team_positions (rolename) VALUES ('Member');
CREATE TABLE team_membership (
teamid INT NOT NULL REFERENCES teams ON UPDATEE CASCADE ON DELETE CASCADE,
peopleid INT NOT NULL REFERENCES people ON UPDATE CADCADE ON DELETE CASCADE,
rolename TEXT NOT NULL DEFAULT 'Member' REFERENCES team_roles ON UPDATE CASCADE ON DELETE RESTRICT
position_label TEXT NOT NULL DEFAULT ''
PRIMARY KEY(teamid,peopleid,rolename)
);
-- position_label allows customization of the titles of team roles,
-- for instance some teams have captains, others have presidents, etc
CREATE TABLE users (
username TEXT PRIMARY KEY,
status_name TEXT NOT NULL DEFAULT 'PENDING' REFERENCES user_status ON UPDATE CASCADE ON DELETE RESTRICT,
siteadmin BOOLEAN NOT NULL DEFAULT FALSE,
hashed TEXT NOT NULL,
peopleid INT NOT NULL REFERENCES people ON UPDATE CASCADE ON DELETE RESTRICT
)
-- the user_status "e;lookup table"e; is not shown here just sets up a
-- "e;dynamic domain"e; holding "e;PENDING"e;,"e;ACTIVE"e;,"e;DELETED"e;,"e;BANNED"e;
CREATE TABLE user_team_roles (
username TEXT NOT NULL REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE,
teamid INT NOT NULL REFERENCES teams ON UPDATE CASCADE ON DELETE CASCADE,
rolename TEXT NOT NULL REFERENCES team_roles ON UPDATE CASCADE ON DELETE
CASCADE,
PRIMARY KEY(username,teamid,rolenamee)
);Ok, notice that there is some repetition in the team_membership and the user_team_roles tables. The former is worried about "people" and their membership on a team -- perhaps with some descriptive text about their position and an eas way to find the person in charge, regardless of their title. The latter is concerned with user permissions over a team. The seperation is required for two reasons:
1. Delegation -- perhaps the Captain isn't computer literate and delegates all web-stuff to someone else, perhaps this person isn't even a regular team officer. This person needs to have the "Captain" role for permission, but be identified as a "Captain" for information display about the team -- even if tagged with an alternate title.
2. (Approachinng my problem): Not all "people" are "users". Given a team roster the system can auto-populate the needed "people" and "team_membership" -- it can do nothing for "users" and "user_team_roles".
OK hopefully that background helps:
The problem:
Suppose we have a team pre-loaded from a roster, with no one on the team pre-existing as a user. Someone registers for a user-account now, with a matching name as a team member(say the Captain) and indicates membership on the entered team. Now the system can't blindly trust this -- this could be a member of a rival team trying to hijack it.
I can't send out an email to all members at team creation time, the source of the "rosters" doesn't include email address, plus that'll be extremely close to spamming. Which means I can't have a "follow this link to create an account linked to this person" style solution.
I beleive this requires out-of-band,human-in-the-loop verification. I wish it didn't, but... So my current solution, is that the account creation process creates a new (duplicate) person record for the new user and adds an entry to a pending_permissions table (this table is slightly in flux, it currently handles several types of permission requests, I think I'll need to split it up)
Code: Select all
CREATE TABLE pending_permissions (
pending_id SERIAL PRIMARY KEY,
username TEXT NOT NULL REFERNCES users ON UPDATE CASCADE ON DELETE CASCADE,
type TEXT NOT NULL
level TEXT NOT NULL,
detals TEXT NOT NULL,
misc TEXT NOT NULL DEFAULT ''
);Level depends on Type ["Admin","Staff"],["Coordinator","Comptroller","Registrar","Staff"],["Captain","Treasurer","Registration","Member"]
Details holds the compname or the teamid or '' for site.
Misc holds extra information (you can see why this should be three seperate relations.)
However, this is structured more for "permission elevation requests" not for handling some of these initial "linkages" between people and users.
However, what I think I need is a "Membership Request" that has the form:
"[username] from [email_adddy] is requessting to be indentified with [full name] on [teamname] as[rolename/position] with confirmation details at [location]"
possible multiple such requests need to be created (one per each team on which the matched person is a member)
[Location] should be a publicly addresssable website, with official team contact email addresses.
Resolution: The site admin goes to the location (attempts to verify that its a legitamte site and not a phishing-like forgery) and sends an email to the official contact address for the team, or captain is explicitly listed. Detailing the nature of the request (including the email address of the requesting user) and asking for confirmation to proceed.
Once a Captain or Registration role is configured they can handle these requests internally, normally matching email address against their own internal roster, etc,
There are several other variations of the "synchronization" problem as mentioned in the OP, but this is the heart of it. Does the problem make sense? Can you see ways of improving/simpligying the suggested solution?