I am trying to decide how (or whether) my Data Access Layer should deal with existing security and integrity protections on the database. The architect in me says that separation of concerns is a priority and duplication of duties is wasteful so I should leave the database to handle access control and let the Object Relational Mapping be a dumb middle-man between the business logic and the persistence layers. But the DBA in me really doesn't like the idea of creating models that allow other developers to attempt to handle the underlying data in ways that they should not (as defined by constraints, relationships and account security on the database).

How "smart" should my DAL be? Should my DAL take into account access privileges (such as who can read/write) to create an object-oriented accessible version of my persistence layer? Or should it be a true pass-through and should I expect my business-layer to respect security to which it may not have immediate visibility? More probably something in-between?

In other words... if I have a database account which only provides SELECT (read-only) rights to table MYTABLE, should the Data Access Layer object for MYTABLE reflect these permissions in what methods are allowed to be called against this model? What about when I have a single entity which could be accessed in different ways depending on which account (or service) is hitting it? Should I have multiple models of the same underlying data?

If you cannot tell from my wording above, I would prefer to have a smarter DAL that ensures my database controls are being at least somewhat respected (without the need to throw a bunch of runtime errors). But I don't know where to draw the line between "business logic" and "data access logic" and I hate the idea of duplicate work.

  • What is a "database control?" Aug 8, 2016 at 15:03
  • @RobertHarvey Sorry, it's a term my Enterprise uses a lot - I'll see if I can edit the question to make it more clear. Basically I would define a control as any process, functionality or configuration which offers security protections (such as role setup, specialized grants, restricted system privileges) and protects data integrity (such as data constraints, Foreign key relationships, and normalized design).
    – user190064
    Aug 8, 2016 at 16:16
  • OK. Why do you feel the DAL needs to be worried about that? Aug 8, 2016 at 16:20
  • 1
    Assuming that fixing the database is not an option, I would leverage the capabilities of Hibernate as much as possible to enforce the necessary constraints. If that is insufficient, I would put the remaining constraints in a service layer. Fixing the database first is vastly preferred. Aug 8, 2016 at 16:48
  • 1
    Make an estimate of system requirements and establishes whether the current strategy makes possible to achieve the expectations you have or not. And its cost in resources (time, employees). Weaving the flag of Costs is usually the way to do project managers to listen to you carefully. Data model in any project is serious business. It's the base, the fundation of any project. Poorly DM definition is an important risk to take care about before to start to type a sigle line of code.
    – Laiv
    Sep 1, 2016 at 8:50

3 Answers 3


This question seems like it pertains to your question in that it asks something that appears to be implied: should you create a database user for each user accessing the application. And my answer would be an Unreserved NO!

With regards to primary keys and foreign keys, I'd highly recommend you implement both in your database, barring that it should fall upon your Data Access Layer.

One anti-pattern I've seen (and that has been documented by many others) is the Anemic Model. Where your object mappings to the DB are little more than data transfer objects (setters and getters) that will be operated on by other elements in the application.

Hibernate is a pretty powerful O/RM because it goes beyond just the mapping from the database to objects but it provides true persistence ignorance allowing your object models to be much more expressive of the business logic.

For example instead of having some operation that takes a Customer and inserts an Order object using the customer id, you'd have a createOrder operation on the customer that adds a new order into your Customer.orders attribute and returns it so you can add line items to it before finally calling save on the context and everything go to the database in one fell swoop.

Read about Domain-Driven Design to see how to make more expressive data layer that does more than just shuttling information back and forth from the database.

  • Thank you @MichaelBrown! This is a very helpful answer and I think actually gives me some terms for what I'm seeing on my application. I believe that our DAL does currently suffer from an Anemic Model. The database certainly still needs to be redesigned and I think this is the fundamental problem with integrity. But I have started reading about Domain Driven Design and I think this technique could very much help me (in collaboration with a refactor of the database) to ensure my data is being stored and accessed in a well thought out manner.
    – user190064
    Aug 9, 2016 at 13:14
  • Wonderful, if you'd like some guidance wrt refactoring your database/implementing DDD just let me know Aug 10, 2016 at 15:36

A RDBMS is a great piece of software that do a lot of things beyond just putting data into data files.

I will not enumerate here the functions of a RDBMS, but you may guess you would need to write tomes of code just to emulate a tiny fraction of what it can do.

It's no longer one-app<->one-database world.

Several apps can access the same database, and one single app can access several databases. So trying to enforce all referential integrity and permissions in "the app" makes me ask "what app?, the Android one?, the iPhone one?, the Web one?, the desktop one?, etc". There are scores of ways a database user can access a database. "The app" is just one of them.

The RDBMS should enforce referential integrity, control access to objects, etc. If you want to do the same in "the app", good, cause you will save a trip to the database but I recommend that you do that cost-effectively and not as a rule.

The DAL should capture exceptions and act accordingly.

And other thing: how do you plan to keep your DAL in sync with database permissions?

In databases, users are being created all the time and permissions are being granted and revoked all the time, how will the DAL generate code to reflect that?

A temporary junior DBA could help. Maintenance of a codebase is costly. The more code is business layer oriented or final user oriented, the better. Investing programming time in mundane tasks as making sure no duplicates are inserted in PK-less tables or a non-related row inserted in a FK-less table is a waste of developer's time. Programmer's time is expensive.

  • Your answer assumes that the OP actually has the ability to fix the database. Aug 8, 2016 at 16:45
  • Great points.. keeping things in sync is probably the biggest argument against trying to make a smart DAL. I continue to be bothered by allowing code to perform actions which violate integrity (specifically because this app had poor database protections to begin with) but the smart move would be to attack the root of the problem (which would be issues in the database).
    – user190064
    Aug 8, 2016 at 16:47
  • @RobertHarvey A temporary junior DBA could help. Maintenance of a codebase is costly. The more code is business layer oriented or final user oriented, the better. Investing programming time in mundane tasks as making sure no duplicates are inserted in PK-less tables or a non-related row inserted in a FK-less table is a waste of developer's time. Which is expensive. Aug 8, 2016 at 16:56

If you see a DAL object as a proxy for a persistent object, it should offer the same functionality.

On the other hand, the functionality that your db object offers might be user (authorisation) dependent thus discover only at command execution that it fails. So that seems a fair limit. And emulating the db in your DAL to anticipate potential error not only would be an ambitious redundant work, against separation of concerns, but it is doomed to fail: you can't prevent concurent user to locks data, nor anticipate all referential integrity and db-triggers that the dba could add over the time. And what would happen if you move to a db with a different authorisation scheme (eg oracle to mongodb or aerospike) ?

Speaking about separation of concerns, if i understand your statement, you want not replicate db logic but only prevent a user to perform operations he's no supposed to do. Is this really a DAL responsibility ? Isn't user authorisations and roles a matter of business logic and db authorisations only a way to implement it ? I'd then suggest to reconsider your architecture:

  • have some business object to represent business authorisations (and take these into accout in your user interface) and if needed, in your DAL foresee a way to instantiate business authorization objects from db authorizations (and only if this is really the way you'll manage business authorisations)
  • see if table level / view level or even query level access management is really the right level of abstraction for the DAL
  • This is well said... and I agree that replicating most database functionality is impossible (as you pretty much would have to create your own RDBMS!).
    – user190064
    Aug 8, 2016 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.