Should I explicitly DENY UPDATE to columns that should not be updated? 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?
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.