This question is on program architecture and relationship between DAL, Security and BLL in case, when simultaneous write in several storage objects is needed. I address it to Microsoft SQL Server functionality, but it is more common question and in general does not depend on used (R)DBMS.

I have read this Q&A:


but it is more concerned on DAL vs Security than DAL vs BLL vs Security, and I am looking for more low-level implementation details.


We have a relational DB. We have a pack of applications, accessing this DB by network (some SQL client). We only can have full control on server with DB.

In DB, we have three tables: tblCommonObjects, tblActionsA and tblActionsB.

We have two groups of users, GroupA and GroupB.

We should implement the following logic:

  • tblActionsA can only be accessed by GroupA (read-write)
  • tblActionsB can only be accessed by GroupB (read-write)
  • tblCommonObjects can be read-only accessed by both groups, but writes in tblCommonObjects can be only done together with corresponding write in tblActionsA or tblActionsB. For each write in tblActionsA(B) there can be 0 or more writes in tblCommonObjects

Number of actions and groups will increase. Number of tblCommonObjects tables will increase, and, in general, CommonObject is a complicated structure with child objects.

Business logic, security and data consistency are prioritized over performance.

As I see, it can be done in these ways:

  1. Create stored procedures uspWriteObjectActionsA and uspWriteObjectActionsB with table-valued or xml parameter, containing data to insert/update tblCommonObjects. Parse parameters inside of stored procs and insert/update correspondingly. Grant write permissions only to stored procs.

Pros: More or less easy to implement

Cons: Doing this, we definitely are moving DAL (maybe partially) into SQL Server. Stored procs could become complicated in case of adding more groups and actions

  1. Divide tblCommonObjects into tblCommonObjectsA and tblCommonObjectsB. Create view tblCommonObjects for read access.

Pros: More or less easy to implement

Cons: May be complicated in case of adding more groups and actions. Kills DRY.

  1. Use SOA and implement some layer (e.g. SOAP or WCF services) between DBMS and applications. Check permissions in this layer.

Pros: DAL can be implemented in this layer.

Cons: Permissions are not checked in RDBMS, and permissions system must be implemented manually. More complicated solution at all, involving SOAP or WCF hosting. More complicated testing.

As I understand, mixing DAL and BLL is common practice and is used, for example, in Entity Framework.

Finally, the question: do some best-practices exist for such requirement of simultaneous write in several DB objects without giving permissions to everything and everyone and mixing layers in monolith rock?

  • What are you describing here that cannot be done with ordinary database concurrency? – Robert Harvey Nov 9 '16 at 14:46
  • I want to avoid direct inserts/updates in CommonObjects table, each insert/update must have corresponding insert/update in Actions table. Concurrency doesn't help here, at least I can't see how. – Marvin Nov 11 '16 at 10:51
  • Is not this a trigger job? Just update actions whenever objects are. – Basilevs Jan 8 '17 at 17:13

What a long question.

with .net and mssql you can use TransactionScope across databases and don't need access to the underlying db connection to do so.

Although you would have to rely on it supporting transaction scope.

More generally you have a design problem if your transaction crosses domain boundaries.

Move the tables into the same db/repository if possible or change the flow to deal with the possible inconsistency of data.


On a more careful read of your question it seems even more complicated than I first thought.

You want to enforce changes as well as referential integrity. This is beyond the scope of what you should attempt to do in the db in my opinion.

You should definitely hide your db behind a service layer in any case. But I would question this requirement. would you allow a set of changes that result in no change to the common object as a whole? how would you prevent this? and if you dont why enforce the requirement ti change the object

  • Sorry for long question. Tried to explain problem, but seemingly failed. You want to enforce changes as well as referential integrity. - this is very accurate, thanks for this sentence. For your question. I wouldn't prevent this, but user still must do Action to change CommonObject and user can't change CommonObject directly. Maybe this part is more about how and where to check permissions. – Marvin Nov 9 '16 at 14:22

Your Answer

