Should I explicitly DENY UPDATE to columns that should not be updated?

Question

I am used to working in very secure environments and so I design my permissions to a very fine degree of granularity. One thing that I normally do is to explicitly DENY users the ability to UPDATE columns that should never be updated.

For example:

create table dbo.something (
    created_by varchar(50) not null,
    created_on datetimeoffset not null
);

These two columns should never be changed once the value has been set. Therefore I would explicitly DENY the UPDATE permission on them.

Recently, during a team meeting a developer raised the point that the logic to ensure the fields never get updated should be contained within the application layer and not the database layer in the event that "they need to update the values for some reason". To me that sounds like typical dev mentality (I know, I used to be one!)

I am the senior architect at my company and I have always worked on the principle of least amount of privileges required to get the app to work. All permissions are audited regularly.

What is the best practice in this scenario?

Answer 1

The argument doesn't make sense. I always want the controls and constraints as close to the data as possible. Putting it in the application layer means it only affects the people using the application layer, and also assumes that the code will be bug-free and the security around those code paths will be bulletproof. Those are big assumptions.

If they absolutely need to be updated, then that can be done by a person unaffected by the explicit DENY, or the person can be temporarily moved into a role that is unaffected, or the DENY can be temporarily removed. These are things that are easy for you, as the DBA, to set up auditing around. In the app? Not so much.

Answer 2

I completely agree with @Aaron on the technical aspect of this.

Beyond that I would say, regarding best practices:

1. Given that it's the DBA's job / responsibility to protect the data, default approach should be to do just that, as the DBA sees fit, and require a solid business case for making a change. A hypothetical-future-potential-somewhat-possible-given-certain-conditions-that-will-be-brainstormed-later-and-confirmed-well-after-that-but-maybe-changed-later-or-might-never-actually-happen reason (i.e. "for some reason") seems a bit flimsy of a justification, especially when the topic is changing a company standard / practice.

2. Never trust someone who wants to make changes to something that should never change ;-), (even more so if they don't even know why they want to).

3. Tell the developer that they are welcome to add such logic to the app code to prevent those updates. But, also that you are not going to remove the DENY. If / when the day ever comes (and it might not probably won't) that someone gets an error trying to update one of these columns, then you can have a discussion about whether or not you will remove the DENY, which will require actual, solid justification for why someone would be updating that value in the first place.

   Point being: there should be a real business case driving what people spend their time on. Time is in high demand yet short supply, so you (and everyone else) have more important things to be doing than changing the system based on someone's opinion. There will always be a variety of opinions (spaces vs tabs, anyone?) and you could spend years changing this back and forth if that developer leaves and is replaced by one who strongly objects to those fields being updatable. If no customers are asking for this (or something that requires it), and there is no tangible benefit (even delayed benefit such as cleaning up technical debt, which is hard to show the ROI on, but is very worth-while given that the chances of that time spent not resulting in actual cost savings in the long-run are slim to none), then either close the request or put it on the backlog at a low priority, even in cases where idealism says that it should be changed (this is not one of those cases, but mentioned for those that think that it is). Idealism is great for conversations, but companies cannot pay rent, utilities, employees, taxes, etc with ideals.

4. @jpmc26 is correct about the need for communication, but not exactly correct regarding what needs to be communicated. Yes, you should listen to what others are requesting and seek to understand their reasoning, which includes asking questions if you are unclear about anything.

   HOWEVER, the database is not subservient to the application, and database professionals (admins, engineers, whatever name your company uses) are not subservient to developers (as seems to be implied in that answer). You don't work for the developers, you work for the company, same as they do. This is a team effort and you shouldn't beg forgiveness for doing your job. That said, us computery types aren't (generally) known for our inter-human communication skills, so, you really need to make sure that others understand you, what your reasoning is, what your responsibilities are, AND how this stuff actually works.

   I put in that last part because there is a high degree of misunderstanding, misinformation, and lack of knowledge out there (even some right here on this very page). For example, there seems to be this notion that all rules are business rules. We need to explain that there is a distinction between data rules and business rules (@Aaron referred to this as "workflow constraint vs data constraint" in a comment on the question), and that while most data naturally belongs to the application, some data actually belongs to the data model. Should a DBA dictate to the developers how application data will be constrained? Of course, not. It is our job to offer up how the application data can be constrained. If a violation of a business rule related to application data could cause harm, and the app isn't the 100% only way to manipulate the data, then perhaps a check constraint might really help (and they aren't difficult to change or remove).

   BUT, coming from the other direction, developers shouldn't dictate how data model data (i.e. meta-data) is handled. This includes audit fields (such as the created_on / created_by columns) and the PK / FK columns (these values are only supposed to be known internally and not given to customers). This data isn't what the app stores about customers (even if the app can see the values and even use them, such as with IDs), it's what the data model stores about the app's data.

   So it makes sense to use data rules to protect data model data. And doing so does not imply that you are about to start adding constraints or restrictions on the application data. BUT, it will be difficult to move the conversation forward in a truly productive way if this distinction is not understood.

So:

1. Yes, I like the idea of the explicit DENY on the audit columns, and have proposed the same at places I have worked in the past as well.

2. Anecdotally, I had a very similar conversation with a lead developer (a very good one), maybe in 2000, when I started adding foreign keys. He argued (quite earnestly) that it was unnecessary over-engineering / idealism (something like that, it has been 17 years since that conversation) and not worth the performance hit. He was quite clear that cleaning up related data should be handled in the app layer. (yes, I did add the FKs because he wasn't going to be the one to clean up the orphaned data that his code would inevitably create)

   Years later he apologized for making that argument ;-)

Answer 3

This is probably an XY-problem. The developer is probably not especially concerned with blocking updates to a truly constant field like created_on. This particular example is an extremely modest constraint.

The developer is probably concerned that the DBA team (which includes you) intends to add so many or such complex restrictions that it starts to impede their ability to work effectively, or that when something out of the ordinary arises or something changes, the DBA team is going to resist the changes and impede the developer team's ability to make progress. This is a relatively reasonable concern. Bureaucracies and losing the ability to effect the changes needed are real occurrences, and encoding too many or complex constraints can have negative effects on performance and on the ability to respond to changes in requirements.

The developer may not even realize that this is the nature of their concerns. They are likely used to having free reign of the database, and giving up that level of freedom is difficult, especially if you know you've never abused it. So their sense of concern about losing the ability to do what they need to could well be vague and ill-defined.

So there are a couple things you should do to assuage these fears:

1. Communicate heavily with the developers. Make sure you understand the needs of the features they're trying to build, and make sure you're responsive when changes come along. Listen to what they have to say, and work hard to find a solution that balances their concerns with yours. Be willing to bend when they have legitimate needs. Make sure they know you're they're ally in building the software.
2. Be cautious about putting in restrictions. Restrictions, even ones meant to provide integrity and security, can make it harder to adapt to change or deal with unforeseen circumstances. So understand that each restriction you add is just as likely to have a cost associated with it as it is likely to save costs (with the possible exception of primary and foreign keys, which have virtually no downsides). Be confident that the restrictions you impose are really needed or beneficial.
3. I don't see any sign you're doing this, but I want to mention it for any other readers: don't view the data or the database as your or your team's responsibility. The data is an asset to the entire company. Without a system to store it (the database) and scripts, tools, or applications to create, update, and fetch it, the data is worthless. Because everyone must use this asset, the data is everyone's responsibility. The database itself is only one part of getting value from the data.

Answer 4

You have conflicting statements

• Columns that should never be updated
• They need to update the values for some reason

Is it really up to you to dictate the first?

You throw in least privilege to get the app to work without proof the app will never will need to update the value.

Who is responsible for data integrity?

With SQL constraints can you guarantee data integrity? No you cannot as there are often business rules that go beyond what a database can do.

VendorID should never change but what if two vendors merge. Never say never.

If the app team contaminates the data and they said they needed that authority then it is on them. If the app teams works for you then you can dictate.

The appropriate question is should the app ever update the data.