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?

  • 1
    You can use a view for column based security. That'll be much more elegant than a trigger. Give users permission on the view but not underlying data. – Thomas Stringer Sep 22 '12 at 14:34
  • That is a good point. But the problem remains unsolved without breaking a lot of applications that are using connection pooling. – Elias Sep 22 '12 at 14:42
  • Can you explain how this will be "breaking a lot of applications that are using connection pooling"? – Aaron Bertrand Sep 22 '12 at 17:36

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;
|improve this answer|||||
  • Thanks Michal, but this solution won't work for me because, my application is a web application that is using connection pooling. All users connect using the same SQL Server Connection String. – Elias Sep 22 '12 at 15:01
  • 1
    @Elias I don't understand. The connection string connects as a specific user, right? So replace SampleRole with that user... – Aaron Bertrand Sep 22 '12 at 17:29
  • column level permissions are the way to go here. That and ensuring that the developers know that making changes to the values via t-sql directly will destroy the system. – mrdenny Sep 23 '12 at 2:55
-- prevent your web app user from updating that column directly:

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

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

CREATE PROCEDURE dbo.UpdateYourTable
  @ProductID INT,
  @Price DECIMAL(10,2)

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

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

GRANT EXEC ON dbo.UpdateYourTable TO WebApplicationUserName;
|improve this answer|||||

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.

|improve this answer|||||
  • You don't need to create a new user to do this... – Aaron Bertrand Sep 22 '12 at 17:34
  • @aaronbertrand you're right - for so,e reasonni was thinking you'd do "execute as creator", but that's not a thing - you can do "execute as owner" as long as the user that owns the schema had rights to update that table. If the stored proc is in dbo, then you're covered. I'll update my answer. – SqlRyan Sep 22 '12 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.