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?
Best Answer
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 theDENY
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.