19

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?

Improve this question
3
  • 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, 2012 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, 2012 at 14:42
  • Can you explain how this will be "breaking a lot of applications that are using connection pooling"?
    – Aaron Bertrand
    Sep 22, 2012 at 17:36

3 Answers 3

Reset to default
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;
Improve this answer
3
  • 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, 2012 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, 2012 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, 2012 at 2:55
6 
-- 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;
Improve this answer
2

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
2
  • You don't need to create a new user to do this...
    – Aaron Bertrand
    Sep 22, 2012 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, 2012 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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