Thinking Outside the Box

Developers almost always follow the most logical approach when trying to solve a problem. This is a good trait but sometimes it produces a suboptimal solution. One such case can be found in a typical security system.

The Problem

Let me start by painting the scenario. We have a security solution where our users and groups come from active directory and we hold the permissions associated with those entities in a local database (Oracle, SQL Server, etc).

Our Security System

Every time we logon, we need to get a list of groups that the user belongs to and then use this list of groups in all of our permission checks to see whether or not the user has permission to access a specific object or UI element.

To illustrate this scenario, I will use an example where we have a user (our Development Manager) that belongs to 3 groups, Managers, Developers and DBA’s. When this user logs into active directory our software will receive this list of 3 groups for him.

User Hierarchy

While solving the solution, we would normally go through a couple of iterations. This blog entry will follow that process.

The Brute Force Solution (First Pass)

In this solution, our database consists of a single table with the group ID, the objects and whether or not we have permission to access the object. In this approach, we simply iterate through all of the ID’s and query the database to get the various permissions. These are then consolidated together. Extending our example from above, we could have a permission table that looks something like this:

Permission Table

Every time we need to check permissions for our user above, we would need to run 3 queries against the database.

SELECT Permission FROM PermissionTable WHERE
Object = ‘Object 1’ AND Entity = ‘Group 1’
SELECT Permission FROM PermissionTable WHERE 
Object = ‘Object 1’ AND Entity = ‘Group 2’
SELECT Permission FROM PermissionTable WHERE 
Object = ‘Object 1’ AND Entity = ‘Group 3’

We could stop the moment we receive a ‘Deny’ permission as this would prevent access regardless of the remaining results, but you get the idea. This is obviously not an optimal solution.

While very few developers will end up going this route, I have included it so that those new to software development will understand the process that developers normally go through while working towards a solution.

A User/Group Table (Second Pass)

This is the solution that most developers end up with. With this solution, our database consists of a two tables. The first one is the same one we used previously with the group ID, the objects and whether or not we have permission to access the object. The second is a table that contains the relationships between our users and the groups that they are linked to.

For this approach, things get a little more complicated. When a user first logs on, we need to perform the following actions:

1. Delete all rows from the User/Groups table that contain a reference to our user.
2. Add entries for each of the groups to the User/Group table.

After that, it’s plain sailing and we can join against this table and perform a single query to get the results for our permission check. It does not make sense to search the table first as we would have to check each and every group to make sure that the user still belongs to these groups.

Again going back to our example above, we would have the following as our two tables.

User/Group Permissions

When our user first logs in we would perform the following 4 queries:

DELETE FROM UserGroups WHERE User = ‘User 1’
INSERT INTO UserGroups (User, Group) 
VALUES (‘User 1’,‘Group 1’)
INSERT INTO UserGroups (User, Group) 
VALUES (‘User 1’,‘Group 2’)
INSERT INTO UserGroups (User, Group) 
VALUES (‘User 1’,‘Group 3’)

We could get our list of permissions back from the DB with a query that would look something like this:

SELECT Permission FROM PermissionTable WHERE 
Object = ‘Object 1’ AND Group IN 
(SELECT Group from UserGroups WHERE User = ‘User 1’)

This can be optimised to return a simple True/False including the Deny permissions.

This is normally where things stop. We have a fairly good solution that does the job but it has a few flaws:

1. There is a fair amount of work required every time the person logs onto the system and we constantly delete and then re-add entries to the User/Groups table, even when there are no changes.
2. We don’t have an easy way to update these relationships once the person is logged in. We would have to go through our full logon procedure every time.
3. Very often a number of people will belong to the same set of groups. In this solution, every user who has the same user/group relationships would receive a duplicate set of entries in the user/groups table with the only difference being their user id.

The Elegant Solution (Third Pass)

This brings me to what I call the elegant solution. This solution focuses on trying to limit the database interactions and updates so that our user’s relationships can be updated on the fly as and when they are required.

As with the previous solution, our database consists of a two tables. The first one is the same one we used in both cases previously with the group ID, the objects and whether we have permission to access the object or not. The second is a table that contains the relationships with our groups. The big difference here is that we don’t use the user ID in our relationship table but rather use a hash. This hash is calculated by sorting all of the groups, appending them together and then performing an MD5/SHA hash on the resulting text.

Our actions are similar to the previous pass when the users first log in but we are now able to check whether our group set is already in the database. We would go through the following actions at logon:

1. Sort and Hash Groups.
2. Search database for our hash. If the hash is missing then add entries for each of the groups to the Hash/Group table.

From this point onwards, we just use our hash instead of the user ID in the queries and as before, we can join against this table and perform a single query to get the results for our permission check.

With our example from above, we would have the following as our two tables.

Hash/Group Permissions

When our user logs in we would perform a query similar to the one below to check if we have already added this combination:

SELECT COUNT(*) FROM HashGroups where 
Hash = ‘04ce9deac2717a4f230cdab2e82eba3b’

Depending on the outcome, it may be necessary to add our groups but this would only happen the first time a user logged in with our group combination. For subsequent users logging in with the same groups, we would find that the hash already exists and skip this step.

INSERT INTO HashGroups (Hash, Group) 
VALUES (‘04ce9deac2717a4f230cdab2e82eba3b’,‘Group 1’)
INSERT INTO HashGroups (Hash, Group) 
VALUES (‘04ce9deac2717a4f230cdab2e82eba3b’,‘Group 2’)
INSERT INTO HashGroups (Hash, Group) 
VALUES (‘04ce9deac2717a4f230cdab2e82eba3b’,‘Group 3’)

Getting the list of permissions back from the DB with a query would also be pretty simple and would look something like this:

SELECT Permission FROM PermissionTable WHERE 
Object = ‘Object 1’ AND Group IN 
(SELECT Group from HashGroups WHERE 
Hash = ‘04ce9deac2717a4f230cdab2e82eba3b’)

This can be optimised to return a simple True/False including the Deny permissions.

We could periodically check for updates to the users groups and then recalculate the hash and go through the logon process again. You could also enhance the security of the solution by including some extra information in the hash such as the first character from the first 5 groups.

This approach would also work well for a scenario where you have a security system with internal authentication and hierarchical roles.