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

Question

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?

score 23 · Accepted Answer · answered Sep 22 '12 at 14:51

23

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;

answered Sep 22 '12 at 14:51

score 6 · Answer 2 · answered Sep 22 '12 at 17:33

-- prevent your web app user from updating that column directly:

DENY UPDATE ON dbo.YourTable(Price) TO WebApplicationUserName;
GO

-- create a stored procedure while logged in as sysadmin:

CREATE PROCEDURE dbo.UpdateYourTable
  @ProductID INT,
  @Price DECIMAL(10,2)
WITH EXECUTE AS OWNER
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE dbo.YourTable 
    SET Price = @Price
    WHERE ProductID = @ProductID;
END
GO

-- grant explicit access only to that stored procedure to the web app user:

GRANT EXEC ON dbo.UpdateYourTable TO WebApplicationUserName;

score 2 · Answer 3 · edited Feb 28 '14 at 17:56

If all your users have the same login (ouch, BTW) then here is another option

revoke update rights from that user (or role, if you're doing it that way).
Alter the stored proc with the "execute as owner" clause on it
then the stored proc will run with the rights of the user who owns the schema in which it resides (if its in dbo, then you're already covered).

The regular application users will lack update rights to that table, so they'll be unable to update it any other way.

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

3 Answers3