HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Restrict update on certain columns. Only allow stored procedure to update those columns

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storedupdatethosecolumnsprocedureallowrestrictcertainonly

Problem

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not using the stored procedures designed to update it.

I am considering implementing this using triggers and a token table. The idea I am considering is to have a token table. the stored procedures will have to first insert values in the token table. Then update the price columns. The update trigger will check if the token exists in the token table for the updated row. If found, it would continue. if the token is not found, it will throw an exception and make the update transaction fail.

Is there a good/better way to implement this restriction?

Solution

SQL Server allows column-level permissions. Just for example:

GRANT UPDATE ON dbo.Person (FirstName, LastName) TO SampleRole;
DENY UPDATE ON dbo.Person (Age, Salary) TO SampleRole;

Code Snippets

GRANT UPDATE ON dbo.Person (FirstName, LastName) TO SampleRole;
DENY UPDATE ON dbo.Person (Age, Salary) TO SampleRole;

Context

StackExchange Database Administrators Q#24734, answer score: 23

Revisions (0)

No revisions yet.