Crawl Central Authentication/Single Sign On


If you are interested in helping with tiles, vaults, patches or documentation, this is the place for that.

Shoals Surfer

Posts: 268

Joined: Sunday, 23rd March 2014, 23:51

Post Monday, 11th July 2016, 04:02

Crawl Central Authentication/Single Sign On

(Mods: If this isn't the right place to post/discuss this, feel free to move it to wherever you feel it is appropriate)

One thing that's been brought up often has been making a single sign-on style system for Crawl Scoring in order to resolve issues involved with how the Scoring website collects it's data, such as some players registering another player's name on a server where the latter had not yet registered their name (whether or not it is malicious) and influencing that player's score page/streaks. I've been working on a potential solution to this through the introduction of a central authentication website that would either be integrated into Scoring itself, or working with it as a companion site. I was going to wait until I had at least a semi-functional prototype (which I am currently developing using C#/.NET) running before posting anything about it here, but I've been busy/slacking for most of the free time that I would have to mess around with making it for the past couple months. Thus, I'm sharing all the design documentation I've created thus far. Even though I will still be working on it myself, I feel it may be beneficial to anyone with more free time/more work ethic to have this information in case they may feel that I am working too slowly towards creating it.

------------------------------------------------------

What is Crawl Central Authentication?

The idea behind Crawl Central Authentication (or CCA, from now on) is to allow users to create a centralized account for players to link any accounts they may have on any server that is connected to Scoring. This will enable players who have multiple accounts on a single server (or different nicknames on multiple servers) to properly aggregate and score their Crawl games on a single Scoring page, as well as protecting their records from malicious players. CCA will achieve this through the use of a web-based interface that would share information with the Scoring website.

How does it work in general?

A user will register an account on the CCA website that is tied to their email. After successfully registering, a player will then be able to choose a specific account from any Scoring-associated server to link to their CCA account (as long as they have not already been linked to another CCA account), the list of which is generated based off of the information that Scoring currently pulls to create the leaderboards/score pages. The system then requires the user to verify the link by proving that they have access to the server account. Once verified, this change is propagated to the CCA database (or the Scoring database, if the two are integrated), which is then used to make sure any morgues/games associated with that server account (as well as any other linked accounts associated with that CCA account) are aggregated under a single Scoring player page based on the CCA account. In the instances of an account not being linked to a CCA account, there are two potential outcomes. First, if the account is likely to be a shared or open account (for example, *robin accounts), there will be an administration page to 'whitelist' an account nickname; any account nickname that is whitelisted will follow the current Scoring rules of compiling all morgues/games from all Scoring-associated servers whenever the nicknames match, as well as preventing any CCA user from linking an account with that nickname from any server. Second, if an account is not linked or whitelisted, it will be displayed on the Scoring website under page with the player name of "<server>/<nickname>", allowing the games to still be displayed on the Scoring leaderboards, as well as allowing players who only play on one server to still have a Scoring page without needing to register a CCA account if they do not desire to do so.

Why is this specific approach the one that is being taken?

The advantages of designing a system like this (instead of other potential solutions to the single sign-on issue) are:
-No 'always online' requirement: The linking process is designed to be a one-time verification, and thus will not require the user to create a CCA account to play Crawl, nor will a player be restricted from playing on any server if anything happens to the CCA website. Likewise, it does not 'phone home' to verify every time a player wants to play.
-Minimal traffic/server pinging required: CCA will only be required to ping the servers once during the verification process (for a total of one time per account, lasting forever), meaning that the servers will not see any noticeable increase in traffic that would hinder the performance of the server.
-No changes to Crawl servers: By making CCA work off of the collected data that Scoring already gathers, no patches or changes need to be made to any current or future servers, meaning that there will be minimal issues with compatibility or ensuring all servers update when deploying CCA.
-No changes to current server accounts: By allowing CCA users to create a single account that links any number of existing nicknames (same or different, from the same or multiple servers), the only set-up required to preparing accounts for it is for the user to manage their account a single time upon initial registration (and then a bit of small maintenance every time they make a new account they want to add)
-No requirement to register: Scores are still preserved and visible on the leaderboards even if a player does not register; the only thing lost if a user does not register is automatically aggregating the accounts together under a single page.

There are some disadvantages to this system that should be mentioned, to put the full picture into perspective:
-Centralized verification: If the database containing the CCA data goes down, aggregation of data for Scoring will not be performed correctly until it comes back up. This will not hinder actual play for anybody, but may be inconvenient for leaderboard/score page purposes until restored.
-Modifications to Scoring: Scoring scripts that create the player pages will need to be adjusted to receive the CCA data first to determine how to sort/aggregate the player pages properly, which means some modifications to the create/update scripts as well as a potential adjustment to existing database structure. It may also require updates to any scripts that query the database for information.
-Modifications to Tournament Scripts: Tournament scripts will need to be adjusted in order to work with how CCA adjusts the way that Scoring creates Players.
-Initial User Registration: There will be an initial influx where existing players who want to link their accounts. The process for doing so may be short, but will have heavy traffic during this period that may cause stress/lag on any of the end points associated.
-Backup Requirements: As the intention is for users to not have to constantly re-link their accounts, the information stored in relation to CCA will need to be backed up in a reliable and consistent manner to ensure minimal information in lost in case it needs to be restored.

More specific information?

See below if you want a bigger breakdown as to how everything 'should' function/work once completed. Skip past the dashed lines if you aren't interested.

------------------------------------------------------

DATABASE
Image

Accounts represents all server/nickname combinations from logfiles pulled from all scoring-integrated servers (populated on creation and through updates by querying for distinct nickname+server combos from log files). Users is for accounts created on the CCA site itself; currently, email is the unique identifier for accounts to allow players to share/change nicknames if desired (but can be changed to make nicknames unique if that is not preferable), and Role is to designate whether the user has admin/moderator rights on the CCA site. Authentications is a joining table to track links between Accounts and Users whenever a user links their CCA account to a server account. Depending on if these tables are added to the Scoring database, a fourth table (or an index) should be added for linking Accounts to Players or whatever associated table would keep track of tying into the CCA data (adjusted as described below under Scoring Aggregation).

WEB PAGES

Login/Logout/Register/Forgot Password/Master Page (Unregistered/User/Admin/Mod)
-For the most part, these pages can be ripped from the web app template that Visual Studio creates (with any extraneous features stripped out/removed), and modified to fit the needs of the web site

Edit Profile (User/Admin/Mod)
-Same as above, but Edit Profile may require more customization. Only listed separately to highlight permission differences.

Link Account (User/Admin/Mod)
-Link Account is for the actual linking of the user's CCA account with a server account. One of two methods will be used (unsure which one to use yet, or to include both):
a) The user must select the server their account is located on. Once selected, the will be prompted to log in to their account via WebSocket. If they successfully log in and the server account has not yet been registered or whitelisted, a new entry is made in Authentications to link the user's CCA account to the logged in server account.
b) It will bring the user to a page with a two stage dropdown list selection: the first is selecting the server that contains the server account, the second being the name of the account to be linked (generated from Accounts that are not Whitelisted or already in Authentications). Once selected, they are given a randomly generated string to insert at the top of the server account's rcfile. The user then clicks a Verify button which sends a request to the server to scrape that account's rcfile and check the top line to compare against the generated string. If it matches, a new entry is made in Authentications to link the user's CCA to the selected server account. The string can then be removed afterwards.
-In either case, the link cannot be reversed by a normal user once made. Additionally, it should detect and time out a user if too many failed attempts to verify occur in a short period of time to prevent easily DDoSing a server.
-The list of servers should probably be hard coded in order to ensure connection strings to each of the servers is not leaked publicly (and thus, no Manage Servers page). This is unlikely to be an issue due to how infrequently a new server needs to be added to the list that contributes to Scoring.

Account Whitelist (Admin/Mod)
-Account Whitelist shows a distinct list of nicknames from the Accounts table, where whitelisted is true. An admin/mod can select from a distinct list of nicknames from all servers (that are not already whitelisted and where no shared nickname is already connected to an existing entry in Authentications) to add to the whitelist, or select a name on the whitelist to remove. When a name is added or removed to/from the whitelist, it sets Whielisted to true/false for all entries with a matching nickname. Whenever a new entry is made to Accounts, it should check to see if the nickname matches any others that are whitelisted, and whitelist the new entry if it is. When an account is whitelisted, it follows the same score aggregation rules that current scoring does (if the name is the same across any server, aggregate those scores under a single page). This is intended mainly as support for *robin or other shared accounts without needing to create and link a CCA account for them by hand.

Unlink Account (Admin/Mod)
-Unlink Account allows an admin/mod to remove a link from a CCA account, for the purposes of fixing malicious activity. The admin/mod selects the CCA account, then selects the link on that account to remove. This ensures players do not just toss away accounts whenever they feel like and cause potential stress on the database/servers.

Manage Permissions (Admin/Mod)
-Manage Permissions lets an admin/mod promote or demote a user from the role of moderator. Moderators and administrators have the same level of permissions in terms of access and actions for the most part, with the exception that no user can be promoted or demoted to/from administrator from within the website. Administrators, unlike mods, should be hard set with a SQL query to the database only (and only when first launching the website, preferably), and should be whomever is the owner/maintainer of the CCA server/website, as well as any other trusted individuals. This is to ensure that a malicious user finding a way to be promoted to moderator is limited in the damage they can perform (as in, they can't lock everyone out through the website). This can be pared down to only having a moderator level, but this may create trust issues in the future.

SCORING INTEGRATION/AGGREGATION

Scoring should be modified to have players be generated through the following process:
1) CCA populates/updates Accounts whenever an update would be pulled by Scoring. Accounts should not have records removed or IDs modified, nor should it be rebuilt, in order to make sure Authentications isn't invalidated. If this has to occur for whatever reason, then there may need to be an email warning about users needing to re-verify/re-link accounts.

2) Scoring generates Player pages/entries while sorting through logfiles/Accounts based on the following three categories:
a) If a logfile belongs to a server account that is linked to a CCA account, aggregate under a personal scoring page where the scores are aggregated from all logfiles from accounts linked to that CCA UserID (checked through Authentications). The CCA User nickname should be displayed on scoreboards when linking to this page.
b) If a logfile belongs to a server account that is not linked to a CCA account but is whitelisted, aggregate under a personal scoring page where the scores are aggregated from all logfiles from accounts matching the AccountNick (or, similar to how the current system generates Scoring pages). The Account nickname should be displayed on the scoreboards when linking to this page.
c) If a logfile belongs to a server account that is neither linked or whitelisted, aggregate under a personal scoring page where the scores are aggregated from all logfiles belonging only to that specific server+nickname combo. It should be displayed as "<server>/<nickname>" on the scoreboards when linking to this page.

3) There are two methods to generating URLs for Player pages on Scoring:
a) Use the Player unique ID (?playerID=****). This method keeps the IDs based on what Scoring already stores, but has the issue of potentially shuffling IDs and screwing up bookmarks if/when the table is re-initialized.
b) Give what category/type the Player falls under in the URL (ex. ?type=r&userID=****, ?type=w&AccountNick=****, ?type=u&AccountID=****). This is a more stable link, but requires more communication with the CCA data, as well as potentially requiring adding another field to the Players table to accommodate it.

SECURITY

Given that this is a web application, it needs to be scanned with a security vulnerability detection tool to ensure that at least the majority of exploits (cross site scripting, SQL injection, exposed back-end, connection strings, or web.config settings, etc.) are sanitized and protected against once it is finished and put together. This should be redone if the source code has been modified since the last scan. Likewise, the database the information is stored in should also ensure that passwords (and potentially user emails) are encrypted.
------------------------------------------------------

If there are any questions, comments, etc...please state them here and I will answer to the best of my ability. Again, I will be working on this when I have free time and am willing/able to hobby around with it.

For this message the author Floodkiller has received thanks: 7
all before, Brannock, chequers, dpeg, floatboth, vergil, Wahaha

Ziggurat Zagger

Posts: 5809

Joined: Tuesday, 30th October 2012, 19:06

Post Monday, 11th July 2016, 06:12

Re: Crawl Central Authentication/Single Sign On

1. What advantage is there in using C#/.NET given that the current ecosystem for crawl and crawl development is c++, and (mostly) linux? (Or was the choice simple expediency, aka that's what you are familiar with and just felt like using it)

2. What sort of "proof that you have access to that account" did you have in mind? (I can't see a way to do it that doesn't involve either supplying CCA with your credentials, which is really awful from a security point of view (see phishing e-mails for an example of why this is bad) Or alternately making modifications to crawl's server software to send some sort of secure authentication message back to CCA, which you said this wouldn't require at all)

3. Do you plan to open the source for the server, (presumably when you have something that more-or-less basically works?)

4. I assume that in order to make use of the aggregation account data, there will need to be some sort of API for scripts which tabulate scores, have you given any thought to how that should or will look?

5. You don't mention hosting or any sort of web security layer, am I correct that the expectation is that you'll be using standard certificate authentication to secure connections to the server?
Spoiler: show
This high quality signature has been hidden for your protection. To unlock it's secret, send 3 easy payments of $9.99 to me, by way of your nearest theta band or ley line. Complete your transmission by midnight tonight for a special free gift!

Tomb Titivator

Posts: 800

Joined: Monday, 29th September 2014, 09:04

Post Monday, 11th July 2016, 07:04

Re: Crawl Central Authentication/Single Sign On

riffing off Siegurt's questions...

1. I would support writing this application in Python. The existing crawl tiles server is written in Python, and the scoreboard I'm working on is written in Python

2. I think the plan was to add a special string to your rc file

5. Not every crawl server currently uses HTTPS, so there's no real way to securely connect to any server. It seems unlikely to be a problem for this sort of application though (although I'd encourage other server admins to deploy HTTPS anyway)


My questions: how can scoring pages / servers / the general public uniquely identify a CCA account? In your UML only the email field is unique, but that's presumably private. My suggestion would be to make nicknames unique as well, functioning in a similar way as to facebook `/username` slugs.

This solution is (hopefully) not the final form of Crawl's online authentication, since it requires users to register at least two accounts (1x CCA, 1x server account). Do you have thoughts on how this design could be upgraded to "true SSO", where users register once on CCA and can then play on all servers with the same name?

Shoals Surfer

Posts: 268

Joined: Sunday, 23rd March 2014, 23:51

Post Monday, 11th July 2016, 12:25

Re: Crawl Central Authentication/Single Sign On

Siegurt wrote:1. What advantage is there in using C#/.NET given that the current ecosystem for crawl and crawl development is c++, and (mostly) linux? (Or was the choice simple expediency, aka that's what you are familiar with and just felt like using it)

chequers wrote:1. I would support writing this application in Python. The existing crawl tiles server is written in Python, and the scoreboard I'm working on is written in Python

It is mainly a choice of expediency, although if I would have to choose a second language to do it in, it would be Python. C++ is a bit too hefty for web development; it can work, but there are quicker choices for what isn't intended to be anything larger than what is essentially just registration and verification. A secondary advantage would be using LINQ to communicate with the database, which would future proof it somewhat in case the type of database is changed in the future. If the site is written in something other than Python, however, it is probably going to be a companion site instead of a full integration.

Siegurt wrote:2. What sort of "proof that you have access to that account" did you have in mind? (I can't see a way to do it that doesn't involve either supplying CCA with your credentials, which is really awful from a security point of view (see phishing e-mails for an example of why this is bad) Or alternately making modifications to crawl's server software to send some sort of secure authentication message back to CCA, which you said this wouldn't require at all)

chequers wrote:2. I think the plan was to add a special string to your rc file

Chequers would be correct (as well as being option 2 under Link Account). This would have the advantage of making sure login credentials aren't leaked through either side, at the expense of a bit more legwork for the user to verify (which I don't consider to be significant enough for a one time verification). Option 1 for Link Account, and where I feel this question is pointed towards, is an artifact of brainstorming when Gammafunk suggested it, comparing to how his chat bot currently works. If he wants to come explain how this would work (while keeping user credentials secure while communicating between the servers), I believe it would be easier on the user. However, without an explanation, I would proceed with option 2, which is a process that is already seen to work through how the tournament scripts identify clans. Option 2 would need more fleshing out in regards to servers that still report morgues, but no longer allow access to login/editing rcfiles (like CSZO).

Siegurt wrote:3. Do you plan to open the source for the server, (presumably when you have something that more-or-less basically works?)

Yes, my intention is to throw it onto git once it is in a semi-functional state and continue development there. I cannot speak for anyone else if they choose to take this up and finish it instead of me.

Siegurt wrote:4. I assume that in order to make use of the aggregation account data, there will need to be some sort of API for scripts which tabulate scores, have you given any thought to how that should or will look?

I have not given this much thought, as I have not taken the time as of yet to look in detail at how scoring currently handles query scripts. This would probably not be something that CCA would handle, as it is not concerned with the score specifics, but it would need to be thought through and implemented on Scoring before CCA would be released (which CCA is intended to communicate with, either in an integrated or companion state).

Siegurt wrote:5. You don't mention hosting or any sort of web security layer, am I correct that the expectation is that you'll be using standard certificate authentication to secure connections to the server?

chequers wrote:5. Not every crawl server currently uses HTTPS, so there's no real way to securely connect to any server. It seems unlikely to be a problem for this sort of application though (although I'd encourage other server admins to deploy HTTPS anyway)

I have not listed hosting information as I would not plan to host this myself. As for security, it would likely just be the standard, aside from other small things like ensuring connection to other servers/the database is performed through a class layer to avoid leaking that information on the back end.

Chequers: This information would lean me further towards implementing Option 2 over Option 1 in Link Account (again, unless Gammafunk can explain), as it would not matter if somebody were to intercept your rcfile (which is already public) or a garbage string compared to potentially intercepting user login info.

chequers wrote:My questions: how can scoring pages / servers / the general public uniquely identify a CCA account? In your UML only the email field is unique, but that's presumably private. My suggestion would be to make nicknames unique as well, functioning in a similar way as to facebook `/username` slugs.

That's a good point. I originally envisioned it through the idea of people being able to use whatever nickname they wanted (even if another is using it already), but it just seems like too much hassle to get it to work. However, if Nicknames are to be unique, a Manage Users page will also need to be added to allow for easier moderation if someone wants to steal a bunch of names and squat on them. I don't feel that is asking for much though.

chequers wrote:This solution is (hopefully) not the final form of Crawl's online authentication, since it requires users to register at least two accounts (1x CCA, 1x server account). Do you have thoughts on how this design could be upgraded to "true SSO", where users register once on CCA and can then play on all servers with the same name?

As I've stated, if a user is only going to be playing on one server under one account, then they would not be required to make an account on CCA (as their score page would remain accurate, as well as secure against anyone who tries to make an account on a different server to maliciously interfere with them). Only players who play on multiple servers or with multiple accounts would need to register to ensure their overall score page remains accurate.

A 'true' single sign-on system would be easier to develop IMO (although it would require some modification to WebTiles servers to phone home to a central point, which would require transition to HTTPS, which they honestly should already do). Unfortunately, a 'true' single-sign on is also going to require at least a form of 'always on'. Even if the system only requires a server to authenticate once when the user logs in, it is still vulnerable to taking down all of online Crawl and preventing log-in/play (similar to DRM) if the central server goes down or becomes too clogged to process requests (in the situation of being DDoSed). The intention of this approach is to avoid that by compartmentalizing the centralization to something that would only affect Scoring if taken down/hindered, but would not affect the individual servers. Likewise, adopting 'true' single sign-on would require any server looking to join Scoring to either moderate all existing accounts for proper merger (as well as properly separating existing malicious accounts), or have a requirement to start fresh with single sign-on and abandon any existing morgues.

For this message the author Floodkiller has received thanks:
Brannock

Tomb Titivator

Posts: 800

Joined: Monday, 29th September 2014, 09:04

Post Tuesday, 12th July 2016, 00:12

Re: Crawl Central Authentication/Single Sign On

FWIW, you can see the UML for the scoreboard that zxc & I are working on here (and a live copy here):

https://github.com/zxc23/dcss-scoreboar ... cs/uml.png

We've already got the concept of players vs server accounts, so it should reasonably easily integrate with this sort of CCA. What our scoreboard would need is an API we can query and ask "what player is this account associated with"? That could be a dynamic API or a periodically updated static JSON document of all existing mappings.

Tomb Titivator

Posts: 800

Joined: Monday, 29th September 2014, 09:04

Post Friday, 12th August 2016, 00:59

Re: Crawl Central Authentication/Single Sign On

Any news on this project? Any initial code you can put up?

Shoals Surfer

Posts: 268

Joined: Sunday, 23rd March 2014, 23:51

Post Monday, 15th August 2016, 14:58

Re: Crawl Central Authentication/Single Sign On

Unfortunately, nothing concrete. Had a lot of job-related work that I needed to take home and do over the past month, so I didn't feel like spending the free time I had working on this all that much.

Working with the UML you posted, I did come up with the rough structure that would work for passing the data between the two databases (semi-pseudo SQL to explain, almost certainly not functional):

Updating CCA Accounts table with up-to-date information from Scoring
Step 1:
Update CCA's Accounts table with any accounts that were recently created and stored in Scoring.

  Code:
INSERT INTO CCA.Accounts (CCA.Accounts.AccountNick,CCA.Accounts.ServerName)
SELECT Scoring.public_accounts.name, Scoring.public_servers.name
FROM Scoring.public_accounts
JOIN Scoring.public_servers
ON Scoring.public_accounts.server_id = Scoring.public_servers.id
WHERE Scoring.public_accounts.blacklisted = false AND Scoring.public_servers.name+"_"+Scoring.public_accounts.name NOT IN
(SELECT CCA.Accounts.ServerName+"_"+CCA.Accounts.AccountNick
FROM CCA.Accounts);


Step 2:
Update Accounts Whitelisted where the name matches a whitelisted account's name.

  Code:
UPDATE Accounts
SET Whitelisted = true
WHERE AccountNick IN
(SELECT DISTINCT AccountNick
FROM Accounts
WHERE Whitelisted = true);


Updating Scoring with what player name to link each account to

Setup:
Create 4 views on CCA: LinkedAccounts, WhitelistedAccounts, UnlinkedAccounts, PlayerAccountBreakdown:

  Code:
CREATE VIEW LinkedAccounts
SELECT Users.Nickname AS Player, Accounts.ServerName AS Server, Accounts.AccountNick Account
FROM Accounts
JOIN Authentications
ON Accounts.AccountID = Authentications.AccountID
JOIN Users
ON Authentications.UserID = Users.UserID;

CREATE VIEW WhitelistedAccounts
SELECT AccountNick AS Player, ServerName AS Server, AccountNick AS Account
FROM Accounts
WHERE Whitelisted = true;

CREATE VIEW UnlinkedAccounts
SELECT ServerName+"_"+AccountNick AS Player, ServerName AS Server, AccountNick AS Account
FROM Accounts
WHERE Whitelisted = false AND AccountID NOT IN (SELECT AccountID
FROM Authentications);

CREATE VIEW PlayerAccountBreakdown
SELECT Player, Server, Account
FROM LinkedAccounts
UNION
SELECT Player, Server, Account
FROM WhitelistedAccounts
UNION
SELECT Player, Server, Account
FROM UnlinkedAccounts


Step 1:
When CCA is asked about player/server links from Scoring, run Update Accounts first as seen above.

Step 2:
Return the PlayerAccountBreakdown view (a compilation of all pairings of player names to server accounts based on the categories of Linked, Whitelisted, and Unlinked) to Scoring.

Step 2a:
Scoring adds new players to public_players from PlayerAccountBreakdown if they don't already exist:

  Code:
INSERT INTO Scoring.public_players (Scoring.public_players.name)
SELECT CCA.PlayerAccountBreakdown.Player
FROM CCA.PlayerAccountBreakdown
WHERE CCA.PlayerAccountBreakdown.Player NOT IN
(SELECT Scoring.public_players.name)
FROM Scoring.public_players);


Step 2b:
Assign each Scoring account to a Scoring player based off the information from PlayerAccountBreakdown. This will probably have to be scripted to create UPDATE statements for each account where public_accounts.player_id will be changed (based off of PlayerAccountBreakdown and matching the Player, Server, and Account fields with the respective tables in Scoring), as I can't think of a clean catch-all UPDATE method that could do it all at once at this moment.

Step 2c:
Delete any players from public_players are no longer attached to any accounts for cleanup purposes.

This all assumes that a change to public_accounts can propagate outwards to fix other tables on update.

For this message the author Floodkiller has received thanks: 2
Brannock, PleasingFungus

Return to Contributions

Who is online

Users browsing this forum: No registered users and 1 guest

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group.
Designed by ST Software for PTF.