Handling data content security

Question

So let's say that in your application you have to handle data content security, in such a way that application defines several "Entities" that have to be secured so that user cannot view, edit etc. certain code ranges. Let's say we have secured entities such as Location, Department, Division, ProductLine etc. and each user gets associated with a range of codes for each of the entities. Would you in such scenario de-normalize db so that all codes that user is authorized for are stored on the user profile table, rather then having join tables? One could use a regex expression for example to specify the ragnes, includes and excludes etc. I've used this approach in data-warehousing application rather successfully, but for the transactional system I am not sure, as everybody here is saying, no to it. But to me it looks rather silly to have to join with all this tables, hydrate entities just to get the list of the codes, so that I can do User.HasAccessTo<TEntity>(entityId).

Answer 1

There is an alternative approach that we use where I work.

Administrating the system is complicated, so we have a notion of roles, or profiles if you wish. A role or profile specify which actions a user can exert, and on which data.

Then, we have a tree-structure for the users. A user belong to an office, which belongs to a department, which belongs to a division etc...

For administration, each of the elements in the tree can be associated any profile (and several of them if necessary).

If we were to keep this as is however, this would lead to query bloat, since a simple question like you stipulate may user X modify Y ? would require to gather all the profiles, gather all the roles attributes to each profiles, and then finally develop the roles to check if the action is authorized.

In order to simplify the query, a denormalized version of the tables is therefore created periodically (say hourly), and the queries are run against the denormalized version (stored in a sqlite database).

Of course, it means that you have some time before a modification done by an administrator take effect (you have to wait until the denormalization + replication), but it works pretty well since you have the best of both worlds:

• administrators have a normalized view
• queries are ultrafast

The trick is to have a proper 'replication' mechanism. Using sqlite database you first generate the file, and then you copy it on each machine, put the incoming transaction (read-only) on hold and wait until there is not transaction being executing, move the new database in place of the old one, and resume the pending transactions. It works pretty well, but it means you don't want to update the database file every second either.