Should Data Access Layer mirror my Database Configuration?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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