When delving into access control system design, one might assume that it is asolved problem and that all the important decisions have already been made.After all, many successful applications already exist, and each of them hassolved its own access control challenge. While there is no need to re-invent thewheel, there are many places where innovation can still occur.

To start, let's put access control in simple terms. When a user authenticatesthemselves, the system reads their permissions and then determines theoperations that they are allowed to perform. To help with this, users are oftencategorized into groups or assigned specific roles, and thus synchronize theirpermissions with a wider bucket.

While this appears straightforward, nuanced details can significantly impact theoverall functionality of an otherwise solid RBAC pattern. In crafting QuestDB'saccess control solution, we encountered pivotal decisions that shaped ourapproach.

Groups or Roles?

Our first dilemma lies in naming the entity that we use to organize users intocollectives. Role seems like a good choice. A role implies that functionality isbundled and then granted to relevant users who fit the role's description. Rolesare created, assigned to users, and their tasks are defined. In a perfect world,it's neat and tidy.

However, here's the catch — unfortunately, reality falls short of perfection!

The initial simplicity soon crumbles as applications evolve, giving rise to atangled and overlapping assortment of roles. These roles may then stray fromtheir original purpose and lose their authentic alignment with their intendedfunctionalities. Instead, they morph into a jumble of permissions for diverseusers.

An analyst might be part developer. One developer may need a single permissionthat would be destructive in the hands of other another developer. And then youneed another admin, but they're only half an admin. It starts clean, butcomplexity soon catches up. The challenge lies in maintaining clean roles amidthis complexity.

While roles appear attractive at first, the term groups tends to offer moreflexibility and thus, in theory, greater clarity. Consequently, the integrationof both groups and roles often leads to even bigger confusion when inheritanceis involved.

Exploring Inheritance

Undoubtedly, groups are essential for efficient access control. Organized groupssimplify the process of permission distribution. Access can be granted to agroup of users in a single statement. Adding users into that group to theninherit those permissions makes intuitive sense.

However, the necessity of nested group hierarchies warrants a second look. Thequestion is whether to construct an intricate group hierarchy or be content witha singular level of inheritance.

The risk of an intricate hierarchy is that it has the potential to escalate intoan unmanageable tree-like structure, or even a complex graph, if the systemallows it. These configurations will soon perplex even the most skilledadministrators.

Just take a quick look at the example below:

Learning from experience, it became evident that multi-level inheritance resultsin confusion and mismanagement, thus making it a challenge to trace the originsand implications of permissions. Opting for a single-level inheritance approachgroups users well, but without nesting. This promotes transparency andpredictability.

Considering Service Accounts

Beyond an administrator's organizational preferences, the application using thedatabase will present its own critical questions that one must consider. Forexample, should a clear distinction exist between individual and applicationaccounts?

The database logic alone might not necessitate any distinction. But theapplication accounts may benefit from strict limitations to prevent inadvertentmisuse. A common trap is when an application inherits an overly-permissivecapability by accident. Depending on how your groups or roles are arranged, itcan be easy to do. We need something to help ensure that we do not make thismistake

The creation of service accounts serves this purpose. Service accounts mirrorstandard users, differing only in their exclusion from group affiliations. Theiraccess is explicitly defined, which prevents unintentional overreach andmis-assignment.

Here is a sample arrangement that shows groups, users, and service accounts:

And the diagram below illustrates how the above users and service accounts couldbe used to establish connections with the database:

Enabling Real-time Changes

Even with service accounts in place, there are still scenarios whereapplications may inadvertently impact the database. At times, there is a needfor prompt user access revocation. In these difficult scenarios, real-timeenforcement of access control changes becomes crucial.

True security must be instantaneous. From a development perspective, it would beconvenient to delay or wait until a user reconnects to enforce new permissions.Instead, as a preferred alternative, we will apply a copy-on-write solution.This approach avoids synchronization during permission checks, while changes aresafely applied in real time without hindering performance.

By creating a new copy of the access control list when changes are needed, youcan ensure that ongoing permission checks proceed without blocking, even duringthe modification process. Once the changes are ready, the new access controllist can be swapped in using an atomic operation, maintaining data consistencyand minimizing the impact on performance.

The following code snippets illustrate how copy-on-write can be used to updateaccess lists. First, let's look at the AccessListStore class which holds theaccess lists, and provides an API for modifications:

class AccessListStore {
    // the map contains users with their access lists.
    // an access list is a set of permissions granted to a user.
    // access lists are versioned: 0, 1, 2, 3...
    // they also have a flag which indicates if they are still valid or not,
    // only the access list with the latest version number can be valid
    ConcurrentMap<User, AccessList> accessLists;
    // returns the latest version of the user's access list
    AccessList getAccessList(User user) {
        return accessLists.get(user);
    }
    // granting a new permission to a user
    void grant(User user, Permission permission) {
        boolean successful = false;
        while (!successful) {
            // lookup the user's access list
            AccessList current = getAccessList(user);
            // current: { version = 0, valid = true, permissions=[] }
            // create a copy of the current access list, only the version is bumped
            AccessList next = current.rollVersion();
            // next: { version = 1, valid = true, permissions=[] }
            // add the new permission to the cloned access list
            next.add(permission);
            // next: { version = 1, valid = true, permissions=[permission] }
            // publish the updated access list.
            // replace is an atomic operation,
            // it is successful only if current is still in the map.
            // if current is not in the map anymore, another thread was faster,
            // and next is based on a stale, invalid access list.
            // we have to re-try using the latest version as our new current
            // until we are successful (see the condition of the while loop above).
            successful = accessLists.replace(user, current, next);
            // if the access list is successfully updated,
            // we should invalidate the old version.
            // anyone using current will see that their version is stale now,
            // and they should get the updated access list from the map.
            if (successful) {
                current.invalidate();
            }
        }
    }
}

Then we can look at the SecurityContext class, which belongs to a specific userand can be used to authorize the operations to be executed by the user:

class SecurityContext {
    User user;
    AccessListStore accessListStore;
    // access list holding the user's permissions
    AccessList accessList;
    void authorize(Permission permission) {
        // refresh access list if it is stale,
        // check should be executed always on the latest version
        if (!accessList.isValid())
            accessList = accessListStore.getAccessList(user);
        }
        // check for the permission,
        // throw an exception if access is not granted
        if (!accessList.hasPermission(permission)) {
            throw new SecurityException("Access denied");
        }
    }
}

Conclusion

Innovation consistently finds its space, adapting on top of establishedsolutions, even in thoroughly explored domains such as access control.

These nuanced adjustments can be the differentiating factor that determineswhether users of one application either struggle with frustration or feelseamless satisfaction — or, at the very least, experience no addeddatabase-related woes!

Want to an easy-to-follow tutorial for QuestDB access control? Checkout ourarticle:QuestDB Enterprise: Role-based Access Control Walkthrough.