Access Control for Admin Tools

	Author:	“No Bugs” Hare  Follow:
	Job Title:	Sarcastic Architect
	Hobbies:	Thinking Aloud, Arguing with Managers, Annoying HRs, Calling a Spade a Spade, Keeping Tongue in Cheek

[rabbit_ddmog vol=”7″ chap=”Chapter 26(b) from “beta” Volume VII”]

Last but certainly not least when speaking about Admin Tools, we need to have some kind of access control. The reason for it is simple –

BTW, it stands even when all your CSRs are completely honest: even a perfectly honest CSR can press a wrong button in that-form-which-was-intended-ONLY-for-admins – and bring your system to knees without really understanding what is going on.

On the other hand, for the bottom line of your company (and our current purposes) it doesn’t really matter why the system has stopped – because of malicious intent, or because of using the wrong tool for the job. What matters – is that

“There are many ways to implement access control; some of them don’t work at all, some do work but are cumbersome, and some do work while being very simple.There are many ways to implement access control; some of them don’t work at all,¹ some do work but are cumbersome, and some do work while being very simple. We’ll concentrate on one of the latter approaches – which was seen to work really good in a real-world game company with over a thousand employees-who-need-access-admin-tools.

Prerequisites

To deal with our task, we’ll assume that:

• Each person who needs accessing admin tools, has an account.
  • 2FA Two-factor authentication (also known as 2FA) is a method of confirming a user's claimed identity by utilizing a combination of two different components.— Wikipedia —How to authenticate these admin accounts – depends, though at least for highly-privileged accounts some kind of two-factor authentication (2FA) is desirable.
  • Another question is whether admin accounts should be just ordinary player accounts with an ‘admin’ flag – or a completely separate DB table. I’ve seen both these approaches working reasonably well, though probably, if starting a new system, I’d vote for having a separate table for admin accounts. Actually, the whole question here is about “are there more similarities or differences between player accounts and admin accounts?” – and more often in the long run we’ll see that there are more differences than similarities.
• Each of the actions we’re doing, has an “Action Verb” associated with it, and the code calls a special function which indicates whether currently-logged-in user has right to perform “Action Verb”. For example – a report showing player data, MUST call something like userHasAccess(current_session,”ViewPlayer”) – and abort if the result is false. In a similar manner, a Control admin tool stopping the system, MUST check that userHasAccess(current_session,”StopSystem”) – and so on.

Mapping

As soon as we got our accounts and our “Action Verbs” (hardcoded, as defined above), all we need to do – is to provide some kind of mapping between them.

In theory – we could even hardcode this mapping into our code; OTOH, on the practice, such hardcoding is highly undesirable. Instead –

Also let’s note that because we have both read-only reports and tools – we need to have this access information easily retrievable both via “DB Object” (this will be used by tools), and directly from SQL DB without any additional layer (used by reports, it is usually ok as long as we’re checking permissions for a read-only report). The latter means that we do want the retrieval of this mapping (more specifically – answering a question of “whether this user has this Action Verb allowed?”) to be available via an efficient SQL request.

Take 1: Simple Mapping

This “efficient SQL” requirement effectively rules out trees of arbitrary depth within SQL (such arbitrary trees are notoriously poorly performing in SQL).

Still, we can easily have the following tables in our SQL DB:

CREATE TABLE GROUPS(
  GROUP_ID INT,
  GROUP_NAME VARCHAR(30),
  PRIMARY_KEY(GROUP_ID),
 UNIQUE(GROUP_NAME) --or CONSTRAINT, or whatever-else
);

CREATE TABLE GROUPS_VERBS(
  GROUP_ID INT,
  VERB VARCHAR(30),-- ACTUALLY, IT IS BETTER TO USE VERB_ID,
                   -- BUT FOR OUR EXAMPLE IT WILL DO
  PRIMARY_KEY(GROUP_ID,VERB)
    -- ORDER WITHIN PRIMARY_KEY MAY MATTER: 
    --                   AS WE’LL BE USUALLY LOOKING
    --                   VIA GROUP_ID, HAVING IT
    --                   AS THE FIRST FIELD IN INDEX
    --                   WILL USUALLY HELP
);

CREATE TABLE GROUPS_ACCOUNTS(
  GROUP_ID INT,
  ACCOUNT_ID INT,
  PRIMARY_KEY(ACCOUNT_ID,GROUP_ID)
    -- ORDER WITHIN PRIMARY_KEY MAY MATTER: 
    --                   AS WE’LL BE USUALLY LOOKING
    --                   VIA ACCOUNT_ID, HAVING IT
    --                   AS THE FIRST FIELD IN INDEX
    --                   WILL USUALLY HELP
);

Then, the following operations can be easily (and efficiently) supported:

• All the tables GROUPS, GROUPS_VERBS, and GROUPS_ACCOUNTS should be editable via Admin Tools (NB: the admin tool which allows to do it should have its own “Action Verb” too, as it is one of the most dangerous actions in the system)
• Checking whether certain ACCOUNT has certain VERB – is a simple SELECT over a trivial JOIN of GROUPS_ACCOUNTS with GROUPS_VERBS (ON GROUP_ID). On any half-decent SQL DB it is completed instantly.

This simplistic model will allow your management to create group CSR, to decide which operations are allowed for all CSRs, and to add/remove CSRs to this group (and then there will be groups SENIOR_CSR, PAYMENT_CSR, MANAGEMENT, and so on).

Take 2: “Compiled” Mappings

“when number of active CSRs goes above 50-100 (and/or number of GROUPS goes above 8-10) – this simplistic model will likely start to be too restricting for CSR management folksThe simple model discussed above, will usually be sufficient for a while. However, at the point when number of active CSRs goes above 50-100 (and/or number of GROUPS goes above 8-10) – this simplistic model will likely start to be too restricting for CSR management folks. In particular – they will want to have hierarchies of the groups (so a group can include other groups – or, in other words, PAYMENT_CSRs should be able to inherit all the permissions/VERBs of CSRs – plus add a few other permissions too); another popular request is to have “negative groups” (so that there is an option to inherit “everything except these few VERBs”).

At this point, you are likely to say “hey! But it means arbitrary trees, which you’ve already ruled out!”. You’re right, but fortunately for me, I have a good answer ;-).

I still do NOT want to retrieve arbitrary trees from SQL every time we need to make an access control check. However, we can observe that to answer our eternal question “whether the user X has permission Y” – any kind of the permission tree can be represented via even simpler mapping than the one above: we can have one single relation table

CREATE TABLE ACCOUNTS_VERBS(
  ACCOUNT_ID INT,
  VERB VARCHAR(30),-- AS ABOVE, IT IS BETTER TO USE VERB_ID,
                   -- BUT FOR OUR EXAMPLE IT WILL DO
  PRIMARY_KEY(ACCOUNT_ID,VERB)
);

Alternatively – we could keep the tables discussed in “Take 1” intact.

Then, we can do the following:

• Store the groups tree in SQL – but to retrieve it only to show it to management-like admins when they want to modify it.
• Whenever they decide to modify the access tree, in addition to modifying the tree, we can calculate a new content of the ACCOUNTS_VERBS table² which will provide exactly the same access as described in the tree (this is certainly possible and not a rocket science) and re-populate ACCOUNT_VERBS table with the new content (in the same ACID transaction as modifying the tree itself).
  • BTW, this can be implemented very easily within the architecture-I-recommend (discussed in Vol. III and Vol. VI), which requires that all the modifications MUST go via separated “DB Server”, so adding an in-memory calculation of the “compiled” rights is trivial.
  • Of course, it will lead to a multi-row update, which is a relatively heavy operation for OLTP – but (a) as it is still just hundreds of rows (and not millions), and (b) as changing access rights tends to happen about 100’000x less frequently than checking them – this is still an extremely good deal performance-wise.

“This approach means that we can have our cake (have tree-like structures to express access rights) and eat it too (have very fast SQL for checking access)This approach means that we can have our cake (have tree-like structures to express access rights) and eat it too (have very fast SQL for checking access). Moreover, we can start with a simpler Take 1 – and to add “compiling” the tree into flat access relation table(s) more-or-less easy later, when/if it becomes necessary.

That is my story of access rights for Admin Tools.

[[To Be Continued…

This concludes beta Chapter 26(c) from the upcoming book “Development and Deployment of Multiplayer Online Games (from social games to MMOFPS, with social games in between)”.

Stay tuned for beta Chapter 27(a), where we’ll start discussing ways of OLTP DB optimization]]

Don't like this post? Comment↯ below. You do?! Please share:

Acknowledgement

Cartoons by Sergey Gordeev from Gordeev Animation Graphics, Prague.